DBase access with VB6 1

[simmsey]

I have a dbf file from existing software. I need to read data from the database in VB6. I have found I can use ODBC drivers to do this for most of the fields in the database but have one problem.

The existing program puts a chunk of data into a 6050 character field in binary format. When I return this field in VB through ODBC I find it is truncated. (Probably due to binary data being misinterpreted as end of string)

How can I get the data from this field correctly in VB6?

Any information on causes of this problem would be appreciated.

Regards

Simmsey

 

[1oldfoxman]

I'm not familiar with VB, so my help may be somewhat limited. A character (binary) field is limited in dBase to 254 bytes. Same is true for a character field. According to early version documentation, anyway. Are you sure you aren't needing to reference a memo type field, in which the data record is contained in a separate file?

You didn't say what version of dBase software you're working with that created and maintains the .dbf file. That info may help others smarter than me come up with more knowledgeable answers. A bit more detail about what you are seeing may also help.

 

[simmsey]

1oldfoxman thanks for the input.
The reality is I do not know which version file type I am trying to read.
It doesnt seem to matter in VB if I state Dbase version 3,4 or 5 for the data driver I get the same results.
The files are .dbf created by the existing 3rd party software. There is also a .cdx and .txt file of the same name in the directory. The .txt file says:

stp_cdmac C 30 0
stp_staz C 1 0
stp_cdstp C 30 0
stp_cdmsc C 30 0
stp_cdart C 30 0
stp_data D 8 0
stp_block C 6050 0
dummy C 0 0
stp_key1 'stp_cdmac + stp_staz + stp_cdstp + stp_cdmsc + stp_cdart' '.not. deleted()' 0 0

This lists the fields in the data base. Second column appears to be data type and third column matches field length. The only column that I have problem with is stp_block which typically only shows variably from 10 to 100 characters length in VB. The text file is what makes me think that it is 6050 characters long - that would approximately match the data I'm looking for.

I have also tried opening the dbf in Excel and found that a much larger chunk of stp_block field is returned than through data access in VB (but not 6050 characters I don't think).
I am fairly sure that the data is in this field not in another file.

I can provide the files (less than 100Kb) if anyone is interested in taking a look at them.

 

[MiggyD]

This maybe the key you're looking for. I had to use this only one time and have since deleted the source code so I won't be able to provide you with any snippits. Also, if you use the help files there may syntax changes (since you're using vb6)...The following info is from VB4 on CHUNKing.

------------------------
Syntax
varname = object ! column.GetChunk(numbytes)

Returns all or a portion of the contents of an rdoColumn object with a data type of rdTypeLONGVARBINARY or rdTypeLONGVARCHAR.

Remarks:
Chunk data columns are designed to store binary or text values that can range in size from a few characters to over 1.2GB and are stored in the database on successive data pages. In most cases, chunk data cannot be managed with a single operation so you must use the chunk methods to save and write data. If the ChunkRequired property is True for a column, you must use the GetChunk and AppendChunk methods to manipulate column data.
------------------------

Hope this helps and good luck
--MiggyD

 

[simmsey]

MiggyD Thanks for the help. This looks very promissing. I'll have to investigate using GetChunk and give it a try.
Thanks
Simmsey

 

[simmsey]

GetChunk may be the required method of accessing the type of data I'm after.
Unfortunately the driver in VB is returning the field with type adVarChar and GetChunks does not work with this field type.
It appears that the problem is at a basic level with the DBase driver in VB.

Simmsey

P.S. The Code I've tried using (incase it makes any difference) is:

Private Const connectString As String = "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=c:\simms\dbf26\;" & _
"Exclusive=No;"


Private Sub Command1_Click()

Dim SQLText As String
Dim ADORecordset As ADODB.Recordset
Dim Block As String

'—Create a new recordset --
Set ADORecordset = New ADODB.Recordset

SQLText = "SELECT * FROM MP4_STP;"

Set ADORecordset = New ADODB.Recordset
ADORecordset.CursorLocation = adUseClient
ADORecordset.Open SQLText, connectString, adOpenKeyset, adLockReadOnly

Debug.Print ADORecordset.RecordCount
Text1 = "Recordcount=" & ADORecordset.RecordCount & vbNewLine
Do While Not ADORecordset.EOF
Text1 = Text1 & ADORecordset.Fields(4) & vbNewLine
Block = ADORecordset.Fields(6).GetChunk(6050)
ADORecordset.MoveNext
Loop

ADORecordset.Close
Set ADORecordset = Nothing

End Sub

The line:
Block = ADORecordset.Fields(6).GetChunk(6050)
fails due to the type of the field not being suitable.

 

[ormsk]

Hi

Only just subscribed to this forum, so hope I can still help.

While the file is a .dbf, it may be that the original software wasn't dbase, but a systems that 'broke' away in the mid-80's such as clipper, foxpro etc

Certainly .cdx is NOT a valid dBase file (speaking from versions IV and upwards). Whether the .dbf is any different is unknown to me.

Suggestion:

Do you need to keep accessing the data, or are you looking to convert?

If you are looking to convert, just send me a copy of the file and I will send it back as an ASCII file delimited with commas. Just a thought.

Andy
andyorm@madhousenet.co.uk

 

[simmsey]

Andy,
Thanks for the help. It's fairly likely that the software used was not dbase. To provide more info the files I am trying to read are being generated by custom HMI software on an injection molding press. I do not know which language this HMI software was written in.

This means that the job of collecting data from the .dbf files is not a one time requirment. I need a method of regularly accessing the data as it is changed. (thanks anyway for the offer to convert the files!)

The equipement manufacturer (based in Italy) is the other route I have been pursuing to try and find a method to access the data. So far that route has also been fruitless.
It is looking like this project may not be possible.

 

[1oldfoxman]

The .CDX file is a pretty good clue that the dbf is one created by FoxPro, which instituted the compound index file (.CDX) beginning, I believe with v2.5. Simmsey, you're sure there's not an .FPT file with the same name as the dbf? This would be the memo field file, which is the only way I'm aware of in Foxpro that you could get over 254 bytes in any single field.

Maybe just changing the ODBC driver specification would help? You indicated this is where you think the root problem is. Have you tried any specific FoxPro driver (don't know if it's the same as the dBase one)?

I am intrigued by your findings. Can't promise anything, particularly since I'm not a VB guy, but maybe I can lead you to something/someone useful.

 

[1oldfoxman]

Simmsey:
There are indeed more than 254 bytes in the stp_block field. The first record you sent contains 4,681 bytes while the second contains less than 1,000. Much of the bytes in this field are in "extended" ASCII number range. However, you were right in your original post (I suspect) that the binary data bytes are being interpreted as end of line, end of file, end of medium, etc and that reading the field thru ODBC is thus truncating. I'm still interested in what product placed more than 254 bytes in a character field (and the .CDX file still leads me to FoxPro as the builder/maintainer of this table).

Anyhow, I'm wondering if there is a way from VB to read the individual bytes from this field and test for ASCII 10,25,28, etc that might be causing the end of text read when accessing the field as a whole string. You could then manage those bytes separately in your VB application. In the first record I found the following in the stp_block field (using a FoxPro function ASC()and checking each byte):

Position ASCII Code Description
34 25 End of Medium
78 10 New Line
94 28 File Separator

It might be helpful to review how many bytes of stp_block are actually coming through the read to VB as you are currently doing it. Then look in the stp_block field bytes and see if it is consistently stopping on the same binary character (maybe there is only one you need to deal with). Just a thought.

Sorry I'm not more assistance. I've not tried to access data files via ODBC before.

 

[simmsey]

Thanks 1oldfoxman,

From what I've seen the data in VB always ends with Asc 172. (e.g. first record returns 3 characters - ASCII 239,6,172)
Unfortunately I can't see a way round this situation as the truncation appears to be happening at the ODBC level. I've tried a query with a statement ASC(MID([stp_block],<Value>,1)) where <Value> is the character I'm after, to see if I could query ASCII value character by character but that does not return any more of the data beyond the truncation point (the ASC and MID functions must be being applied in ODBC drivers after the field has already been truncated)

I'm going to investigate a little to see if there are different FoxPro drivers that function differently.
I have a suspicion (from DLL's present) that the original software may be using CodeBase tools for database. (CodeBase supports FoxPro, dBase and Clipper file formats)
CodeBase may be an option I can buy and use in VB to access this data but I am a little wary of investing in anything at the moment until I am more certain that it will be successful.

Again thanks for the help.

 

[1oldfoxman]

Simmsey:
Your welcome. Hope you figure it out. Please let us know how you did it when you get it working. (I know..... glass half full.......)

I'm assuming you've posted this on the VB forum as well.

dennis

 

[BKtechie]

your problem is attempting to perform RDO functions on an ADO recordset. You need to open the file as an RDO recordset. I think VB6 still supports it, but you will want to check to make sure. Hope this helps.