Querying More Than 255 Characters from FoxPro/DBase Database Field

[Matt Young]

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.CreateOb ject("ADODB.Con nection")
'The DBase Connection String
dbfConn.Open "Driver={Micros oft dBASE Driver "_
& "(*.dbf)};Drive rID=277;Dbq=C:\ ManagementSys\D ata\;"

'The Visual FoxPro Connection String
'dbfConn.Open "Driver=Microso ft Visual FoxPro " _
'& "Driver;SourceT ype=DBF;SourceD B=C:\Management Sys\Data\;"_
'& "BackgroundFetc h=no;"

Set dbfRS = Server.CreateOb ject("ADODB.Rec ordset")

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

[Aaron Bertrand [MVP]]

(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" <st********@yah oo.com> wrote in message
news:38******** *************** ***@posting.goo gle.com...

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.CreateOb ject("ADODB.Con nection")
'The DBase Connection String
dbfConn.Open "Driver={Micros oft dBASE Driver "_
& "(*.dbf)};Drive rID=277;Dbq=C:\ ManagementSys\D ata\;"

'The Visual FoxPro Connection String
'dbfConn.Open "Driver=Microso ft Visual FoxPro " _
'& "Driver;SourceT ype=DBF;SourceD B=C:\Management Sys\Data\;"_
'& "BackgroundFetc h=no;"

Set dbfRS = Server.CreateOb ject("ADODB.Rec ordset")

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

[Matt Young]

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.CreateOb ject("ADODB.Con nection")
'The DBase Connection String
dbfConn.Open "Driver={Micros oft dBASE Driver "_
& "(*.dbf)};Drive rID=277;Dbq=C:\ ManagementSys\D ata\;"

'The Visual FoxPro Connection String
'dbfConn.Open "Driver=Microso ft Visual FoxPro " _
'& "Driver;SourceT ype=DBF;SourceD B=C:\Management Sys\Data\;"_
'& "BackgroundFetc h=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:

mmddyyyyaxmmddy yyyaxmmddyyyyax mmddyyyyaxmmddy yyyaxmmddyyyyax

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

[Aaron Bertrand [MVP]]

> sql = "SELECT fieldX FROM tableY WHERE recordID = 1234"

dbfConn.Execute (sql)
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...
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:

mmddyyyyaxmmddy yyyaxmmddyyyyax mmddyyyyaxmmddy yyyaxmmddyyyyax

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.

[Garrett Fitzgerald]

st********@yaho o.com (Matt Young) wrote in news:38******** *************** ***@posting.goo gle.com:

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.

VFP doesn't support an array field type.

Also, have you tried downloading the VFP OLE DB provider from
<http://www.microsoft.c om/downloads/details.aspx?fa milyid=0f43eb58-7a94-4ae1-a59e-965869cb3bc9&di splaylang=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