Converting VO memo field data to a SQL Db

[NickFriend]

Hi,

I hope someone can help me with the following problem in translating VO behaviour into XSharp.

In our old VO app we use a text editing component TXControl as an ActiveX. We use it to create documents in .DOC format, then save these documents into a memo field in our database.

To save the documents we do the following - the TXControl has a SaveToMemory method which returns a byte array, and we convert that to a string and save it to the database memo field.

Code: Select all

LOCAL cValue AS STRING  	 
__OLEClientByteArrayAsString(BAS_ONCE) 	 
cValue:=oTXControl:SaveToMemory(9,FALSE) 	 
oServer:FieldPut(#DOCDATA, cValue)

To get the data back out we use

Code: Select all

LOCAL uValue AS USUAL
uValue:=oServer:FieldGet(#DOCDATA)

then use the TXControl LoadFromMemory method which takes back in a byte array.

Code: Select all

oTXControl:LoadFromMemory(CreateInstance(#OleBinary,uValue),9,FALSE)

We're writing a brand new version of the program (in C#, we started this project before XSharp was announced) working with SQL Server. These documents will now be stored in a byte[] field and I need to create a method to extract the old data from the DBFs and insert it into the new database.

So the question is, what would be the equivalent to CreateInstance(#OleBinary,uValue) to convert the Usual value returned from FieldGet to a byte[] that I can then save to the SQL Server database?

Thanks

Nick

[wriedmann]

Hi Nick,

with the current Vulcan RDD you will fail.

I'm pretty sure that the memo of your DBF contains binary data, and unfortunately the Vulcan RDD converts the data from Ansi to UTF8, damaging the contents.

To have it work correctly, the RDD should return a byte array instead of a string.

Currently I see three possibilities for you:
- convert the field content to base64 coded values. The the RDD conversion will not destroy the content any more
- use ADS and the ADO.NET driver
- wait until the development team releases their own RDD that will have some binary fieldget method

This is an issue that blocks me from acceeding several of my DBF tables from X# because sometime I store binary contents there, partially zlib compressed data and partially encrypted data.

Wolfgang

[NickFriend]

Hi Wolfgang,

I think you may be right. I've been experimenting with this, and I'm getting exceptions from the Vulcan RDD layer just trying to read the field contents with FieldGet.

Nick

[NickFriend]

Additional information.

I took over maintenance/development of this program a few years back. The original developers had a different technique for handling these fields.

To read the contents they used BlobExport() to create a temporary file from the memo field contents with .doc extension, then opened the file in the TXControl. Afterwards any changes would be saved back to the temporary file and then it would be read back into the database field with BlobImport().

I wanted to remove the use of temporary files like this, but needed to maintain complete compatibility with the existing data stored in users databases. The use of __OLEClientByteArrayAsString(BAS_ONCE) and CreateInstance(#OleBinary,uValue) allowed me to maintain that compatibility but read/write the data directly in memory.

Nick

[Chris]

Guys,

I think I have found a solution for this!

I can't believe I didn't think of this years ago, but it seems it is possible to pre-process binary data in such a way that the DBF encoder will translate it to the correct 0..255 value range on saving and likewise post-process data read from dbf, so that it gets translated back to the same range again.

Please try the attached small class that does this job. You can test it with this sample code (of course you will need to adjust the filenames):

USING System.Text
USING System.IO

FUNCTION Start() AS VOID
LOCAL cDbf AS STRING
LOCAL cBinary AS STRING

cDbf := "C:TestTestBin.dbf"
cBinary := "C:Testadv.png"

IF .not. File.Exists(cDbf)
DBCreate(cDbf , {{"FLD1" , "M" , 10 , 0}} , "DBFCDX")
DBUseArea(,"DBFCDX" , cDbf)
DBAppend()
ELSE
DBUseArea(,"DBFCDX" , cDbf)
ENDIF

LOCAL aBytes AS BYTE[]
LOCAL c AS STRING

// save binary to dbf
aBytes := File.ReadAllBytes(cBinary)
c := AdjustBinaryData.BeforeSaveBytes(aBytes)
FieldPut(1,c)
DBCloseArea()

// load binary from dbf
DBUseArea(,"DBFCDX" , cDbf)
c := AllTrim(FieldGet(1))
aBytes := AdjustBinaryData.AfterReadToBytes(c)
File.WriteAllBytes(cBinary + "_new" , aBytes)
DBCloseArea()

RETURN

This code writes some binary data to a dbf, then reads it back from the dbf and saves it again to an external file. At least in my machine which uses a Greek codepage, it works well, the source and output files are identical. Furthermore, the data saved in the dbf can be read (and written) fine also by VO apps, so it is compatible.

I hope it works ok in your machines as well. If it does, then we finally have a solution for this and the same trick can also be used for writing/reading binary data in regular files with the F*() functions. Will also optimize a bit the code to use a hash table instead of a lookup array that it does now.

Chris

[wriedmann]

Hi Chris,

I have tried to understand what your code is doing, but I'm not sure I was able to do.

Please let me try to explain what your code does: basically it encodes the binary data before writing it to disk, and decodes it after reading it.

If I understand correctly, this is exactly what I (and several other people) are doing for years to store encrypted and/or compressed data in memo fields.

The main problem are not new data - I already have code that takes a byte array, encodes it with Base64 and stores it in a database field (works with both DBF and SQL), and decodes it after read.
The problem is the data we currently have. In some of my VO applications I have a LOT of encrypted and/or compressed data that is in daily use, and before thinking about an access with the Vulcan RDD I have to convert them all to a unicode conversion safe algorithm that works in both VO and X#. Currently Base64 seems the most safe method - at the cost of increasing the size by about 33%.

Therefore I prefer to wait until you have ready your RDD that hopefully will have methods FieldGetBytes() and FieldPutBytes() that return a byte array (in case of FieldGet()) or take a byte array as second parameter (in case of FieldPut()).

Wolfgang

[wriedmann]

Hi Chris,

after looking at your code I have found another issue I see with my applications: You cannot always guarantee that all machines in a network are using the same nation settings, specially if you have machines with different system languages on the network (in my case, I have both Italian and German language machines).

When working with binary data, using different encodings, you can be sure your data will be corrupted.

Therefore in my VO applications, I use SetCollation( #Clipper ), so I can set the encoding per application and not per machine.

So, when using your AdjustBinaryData class, the encoding should be set to a application-wide encoding, like Encoding.UTF8 or similar. If more than one application needs to access that data, all must use the same encoding.

Wolfgang

[Chris]

Hi Wolfgang,

Please try the code that I posted. Try it in both VO and x#. Also try it with your existing data, generated by VO. As I wrote, it should work fine with existing data as well, and new data is written exactly the same way that VO would write it as well, so it should be VO compatible. As for machine encoding, this is just a test version of the code, the final version will be reading the encoding directly from the dbf file, so it should always work correctly, in any machine. I'm only hoping there's not some other severe problem that I haven't thought of yet, so please do test it in your machine to make sure it works in that, too.

As for explaining what the code does, I've been thinking about how to describe this, without making a huge post, as I need to explain also how the internals of the dbf system work in the vulcan rdds (will be very similar in the x# ones). Will make an attempt on this a little later.

Chris

[wriedmann]

Hi Chris,

ok, I will try it - should have a few hours until the day begins, because my night was very short.

Wolfgang

[Chris]

Hi Wolfgang,

To keep it as short as possible, the main difference with VO here, is that VO (and dbf files in general) use 8 bit strings, while .Net has unicode strings (2 bytes per char). So whenever text is read/written from/to dbfs, the .Net RDDs need to make translation between the 2 formats. This is done with the help of the System.Text.Encoding class (note that this does not have to do with encryption, "encoding" here means more like "conversion"), based on the codepage information that is stored in the dbf file.

This works perfectly for "real" text data, but if the field actually contains binary data (regular bytes represented as string characters), then the RDD still translates bytes as if they were normal text. So for example a byte value of 200 could be translated to a unicode value of 5000 when read, which would make sense for text, but corrupts the data if it's binary. The proper fix for that would be to enhance the RDDs to allow specifying which dbf fields are "binary" and should be treated differently, but obviously we can't do that now in the vulcan RDDs (although it was one of the items in our todo list, back in the days).

What the code that I posted does, is to modify the binary data before it is passed to the RDD (for writing to disk) is such a way, that after the RDD modifies it with its encoding class, the resulting data will be again the same as the original one, without the conversion! So for the above sample, a binary value of 200 is first converted to a value of 5000, before sent to FieldPut(). Then, when it is stored to disk, the RDD converts it back to a value of 200, which is what we need! And the opposite is done when reading binary data from the dbf.

So that should make it 100% compatible with VO, unless there's some other problem that I haven't found yet.. Hope I am making some sense in the above description!

Chris