Querying More Than 255 Characters from FoxPro/DBase Database Field

I've been tasked with integrating an older management system based on
DBF files with my snappy new ASP application to provide users of the
ASP application with real-time data from the management system.

I figure with DBF files, I should use either the DBase drivers or the
FoxPro drivers to connect to the database.

The integration has gone quite smoothly until yesterday. This means
that my connection strings work well based on the DBase drivers, and I
have been able to query data successfully.

What changed yesterday is that a field from the management system has
grown beyond 255 characters. It seems there is a limitation somewhere
with the connection object. The management system handles the large
field just fine. But when I try to query the very same field in my
ASP application via my DBase-based connection object, it returns a
truncated result at the 254th character (too close to 255 characters
for a max varchar field size that I am accustomed to in SQL server).

The data in the DBF is indeed not truncated. Only the result from my
SQL statement is. Switching to the FoxPro driver does not change
this.

Can anyone help me modify the following code so that it converts the
field type to, say, MEMO, so that the queried results are not
truncated? Or perhaps I'm barking up the wrong tree and someone can
tell me where else I've gone wrong. This is just the way I'm working
with the cards I've been dealt.

Thanks,
Matt

---------------------------

Set dbfConn = Server.CreateObject("ADODB.Connection")
'The DBase Connection String
dbfConn.Open "Driver={Microsoft dBASE Driver "_
& "(*.dbf)};DriverID=277;Dbq=C:\ManagementSys\Data\; "

'The Visual FoxPro Connection String
'dbfConn.Open "Driver=Microsoft Visual FoxPro " _
'& "Driver;SourceType=DBF;SourceDB=C:\ManagementSys\D ata\;"_
'& "BackgroundFetch=no;"

Set dbfRS = Server.CreateObject("ADODB.Recordset")

sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"
dbfRS.Open sql, dbfConn

re: Querying More Than 255 Characters from FoxPro/DBase Database Field

(a) Don't use an explicit recordset object that way,
(b) See http://www.aspfaq.com/2188

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/




"Matt Young" <stewartfip@yahoo.com> wrote in message
news:3830b9b0.0401240741.211bf96a@posting.google.c om...[color=blue]
> I've been tasked with integrating an older management system based on
> DBF files with my snappy new ASP application to provide users of the
> ASP application with real-time data from the management system.
>
> I figure with DBF files, I should use either the DBase drivers or the
> FoxPro drivers to connect to the database.
>
> The integration has gone quite smoothly until yesterday. This means
> that my connection strings work well based on the DBase drivers, and I
> have been able to query data successfully.
>
> What changed yesterday is that a field from the management system has
> grown beyond 255 characters. It seems there is a limitation somewhere
> with the connection object. The management system handles the large
> field just fine. But when I try to query the very same field in my
> ASP application via my DBase-based connection object, it returns a
> truncated result at the 254th character (too close to 255 characters
> for a max varchar field size that I am accustomed to in SQL server).
>
> The data in the DBF is indeed not truncated. Only the result from my
> SQL statement is. Switching to the FoxPro driver does not change
> this.
>
> Can anyone help me modify the following code so that it converts the
> field type to, say, MEMO, so that the queried results are not
> truncated? Or perhaps I'm barking up the wrong tree and someone can
> tell me where else I've gone wrong. This is just the way I'm working
> with the cards I've been dealt.
>
> Thanks,
> Matt
>
> ---------------------------
>
> Set dbfConn = Server.CreateObject("ADODB.Connection")
> 'The DBase Connection String
> dbfConn.Open "Driver={Microsoft dBASE Driver "_
> & "(*.dbf)};DriverID=277;Dbq=C:\ManagementSys\Data\; "
>
> 'The Visual FoxPro Connection String
> 'dbfConn.Open "Driver=Microsoft Visual FoxPro " _
> '& "Driver;SourceType=DBF;SourceDB=C:\ManagementSys\D ata\;"_
> '& "BackgroundFetch=no;"
>
> Set dbfRS = Server.CreateObject("ADODB.Recordset")
>
> sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"
> dbfRS.Open sql, dbfConn[/color]

re: Querying More Than 255 Characters from FoxPro/DBase Database Field

Aaron,

Thanks for the pointer. I've tried what you suggested without any
luck. The following code returns the exact same result:

---------------------------
Set dbfConn = Server.CreateObject("ADODB.Connection")
'The DBase Connection String
dbfConn.Open "Driver={Microsoft dBASE Driver "_
& "(*.dbf)};DriverID=277;Dbq=C:\ManagementSys\Data\; "

'The Visual FoxPro Connection String
'dbfConn.Open "Driver=Microsoft Visual FoxPro " _
'& "Driver;SourceType=DBF;SourceDB=C:\ManagementSys\D ata\;"_
'& "BackgroundFetch=no;"

sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"
dbfConn.Execute(sql)
---------------------------

Both the dBase and FoxPro drivers provide the same result.

BUT, there is new information I was not aware of. I was provided with
a text file which describes the different fields I'm working with
here. The particular field I am trying to query is of type ARRAY.

So, if I open up the DBF in notepad, the field appears as one long
string. But if you break it down, each element in the string
represents three pieces of information: a date and two
single-character codes for a total of ten characters. For example:

mmddyyyyax

Where a and x are the single character codes respectively. As more
elements are added to the field, in notepad it begins to look like
this:

mmddyyyyaxmmddyyyyaxmmddyyyyaxmmddyyyyaxmmddyyyyax mmddyyyyax

This has not been a problem until the field got beyond 255 characters.
I'm not sure if it's this ARRAY typing that is causing the issue.

I'd love more suggestions.

Matt

re: Querying More Than 255 Characters from FoxPro/DBase Database Field

> sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"[color=blue]
> dbfConn.Execute(sql)[/color]

What do you expect to happen here? You haven't assigned the results to
anything, you told the database to execute a SELECT statement, and it
probably did that no problem, but didn't send results anywhere because you
didn't tell it to...
[color=blue]
> mmddyyyyax
>
> Where a and x are the single character codes respectively. As more
> elements are added to the field, in notepad it begins to look like
> this:
>
> mmddyyyyaxmmddyyyyaxmmddyyyyaxmmddyyyyaxmmddyyyyax mmddyyyyax[/color]

Sorry, I know nothing about DBF's file format. This seems to be a FoxPro
issue, not an ASP one, so my only suggestion is to post your issue to a
FoxPro group.

re: Querying More Than 255 Characters from FoxPro/DBase Database Field

stewartfip@yahoo.com (Matt Young) wrote in news:3830b9b0.0401261752.3a505f9c@posting.google.c om:[color=blue]
> BUT, there is new information I was not aware of. I was provided with
> a text file which describes the different fields I'm working with
> here. The particular field I am trying to query is of type ARRAY.[/color]

VFP doesn't support an array field type.

Also, have you tried downloading the VFP OLE DB provider from
<http://www.microsoft.com/downloads/details.aspx?familyid=0f43eb58-7a94-4ae1-a59e-965869cb3bc9&displaylang=en>?

Using the ODBC driver requires that you do something with GetChunk() which,
fortunately, I've never needed to do. :-) The OLE DB provider _might_
avoid this.

Garrett Fitzgerald
MailMovers
Seattle, WA