fbpx
Welcome, Guest
Username: Password: Remember me
Visual Objects

Please use this forum to post questions about Visual Objects and Vulcan.NET
  • Page:
  • 1
  • 2

TOPIC: Converting VO memo field data to a SQL Db

Converting VO memo field data to a SQL Db 1 year 9 months ago #2663

  NickFriend's Avatar Topic Author NickFriend Offline Posts: 193
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.
LOCAL cValue AS STRING  	 
__OLEClientByteArrayAsString(BAS_ONCE) 	 
cValue:=oTXControl:SaveToMemory(9,FALSE) 	 
oServer:FieldPut(#DOCDATA, cValue)

To get the data back out we use
LOCAL uValue AS USUAL
uValue:=oServer:FieldGet(#DOCDATA)

then use the TXControl LoadFromMemory method which takes back in a byte array.
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

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2665

  wriedmann's Avatar wriedmann Online Posts: 1573
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
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2667

  NickFriend's Avatar Topic Author NickFriend Offline Posts: 193
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

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2669

  NickFriend's Avatar Topic Author NickFriend Offline Posts: 193
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

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2679

  Chris's Avatar Chris Offline Posts: 1181
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:\Test\TestBin.dbf"
cBinary := "C:\Test\adv.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
XSharp Development Team
chris(at)xsharp.eu
Attachments:

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2680

  wriedmann's Avatar wriedmann Online Posts: 1573
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
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Last edit: by wriedmann. Reason: fix typo

Converting VO memo field data to a SQL Db 1 year 9 months ago #2681

  wriedmann's Avatar wriedmann Online Posts: 1573
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
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2682

  Chris's Avatar Chris Offline Posts: 1181
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
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2683

  wriedmann's Avatar wriedmann Online Posts: 1573
Hi Chris,

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

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2684

  Chris's Avatar Chris Offline Posts: 1181
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
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2685

  wriedmann's Avatar wriedmann Online Posts: 1573
Hi Chris,

I have now made two different applications: a VO Console application that creates a DBF and reads all DLL files from a directory into a DBF, and a X# application that reads the DBF and compares with the data on the disk.

Unfortunately there are differences. Since I have to stop now until later today, I attach a zip file with both applications.
If you have time, maybe you can find my error.

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it
Attachments:

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2686

  Chris's Avatar Chris Offline Posts: 1181
Hi Wolfgang,

That's because the dbf is generated in OEM mode, so you will need to use the appropriate codepage. Just add this in the beginning of your x# code and it should work correctly now (it does on my machine, hopefully it does on yours, too):

LOCAL nCodePage AS INT
nCodePage := (INT)Vulcan.Runtime.State.DosCodePage
AdjustBinaryData.Initialize(Encoding.GetEncoding(nCodepage))

as I said, if everything else works as expected, you will not need to do this manually, the code can read the codepage directly from the dbf instead.

Btw, I just realized that most of the code in the AdjustBinaryData class is redundant and it can be written in a lot more simple way:

#using System.Text

STATIC CLASS AdjustBinaryData
STATIC PRIVATE enc AS Encoding
STATIC CONSTRUCTOR()
Initialize(Encoding.Default)
RETURN
STATIC METHOD Initialize(oEncoding AS Encoding) AS VOID
enc := oEncoding
RETURN
STATIC METHOD BeforeSaveBytes(abInput AS BYTE[]) AS STRING
RETURN enc:GetString(abInput)
STATIC METHOD AfterReadToBytes(cInput AS STRING) AS BYTE[]
RETURN enc:GetBytes(cInput)
END CLASS

:-)

Chris
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2687

  wriedmann's Avatar wriedmann Online Posts: 1573
Hi Chris,

a short note: it works!

I'll complete it that it recognizes the ansi flag of the DBF and post both applications here and on my server. Of course I will add your copyright to the class.

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2688

  wriedmann's Avatar wriedmann Online Posts: 1573
Hi Chris,

again: thank you very, very much!

Attached to this message you can find a corrected application for both VO and X# (XIDE export). The XIDE program checks the Ansi flag of the DBF file and initalizes the converter class accordingly.

The only thing I'm missing now is a VO compatible Crypt function that takes and returns a byte array.

And I have uploaded the same zip file to my webserver: https://riedmann.it/download/XS_DBFBinaryRead.zip , completed with your message as readme.

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it
Attachments:

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2694

  Chris's Avatar Chris Offline Posts: 1181
Hi Wolfgang,

You're welcome, and thanks for testing! Glad to see after so many years that there's an easy solution for that after all.

Btw, the code I posted previously for getting the codepage was a quick and dirty way to do it, assuming the dbf was created in the same machine. Proper way to do it, by using a DBServer obejct is:

#define DBI_CODEPAGE 41
nCodePage := oServer:Info(DBI_CODEPAGE)
AdjustBinaryData.Initialize(Encoding.GetEncoding(nCodepage))

as for a VO-compatible Crypt() that works with a byte array, here it is below:

Chris


FUNCTION CryptByteArray(aBytes AS BYTE[] , cKey AS STRING) AS BYTE[]
LOCAL pKey := String2Psz(cKey) AS PSZ

LOCAL aRet AS BYTE[]
aRet := BYTE[]{aBytes:Length}

FOR LOCAL n := 1 AS INT UPTO aBytes:Length
aRet[n] := aBytes[n]
NEXT

// just make sure the GC does not move the array around
BEGIN FIXED LOCAL pBytes := aRet AS BYTE PTR
Crypt4(pBytes , pKey , (DWORD)aRet:Length , (DWORD)cKey:Length)
END
RETURN aRet
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2695

  FFF's Avatar FFF Away Posts: 577

Crypt4(pBytes , pKey , (DWORD)aRet:Length , (DWORD)cKey:Length)
END
RETURN aRet

FWIW, "Crypt4" ? If that's no typo, the name is rather "burned", as google knows this as a probably undecryptable trojan <g>

Karl

Please Log in or Create an account to join the conversation.

Last edit: by FFF. Reason: typo

Converting VO memo field data to a SQL Db 1 year 9 months ago #2696

  wriedmann's Avatar wriedmann Online Posts: 1573
Hi Chris,

great! Thank you!

I have to check the crypt() function next week, or maybe tomorrow, because now I have VO work to do.

Regarding the right encoding: I have added a second static Initialize method the the AdjustBinaryData class:
static method Initialize( oServer as Vulcan.VO.DbServer ) as void
local nCodePage	as int
	
nCodePage := oServer:Info( DBI_CODEPAGE )
_oEncoding := Encoding.GetEncoding( nCodepage )
	
return

and have put that class into my VulcanInterface library.

The adjusted sample is both attached to this message and on my download page (it may be easier to find than down here in the forums).

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it
Attachments:

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2697

  Chris's Avatar Chris Offline Posts: 1181
Hi Karl,

Heh, didn't know that! No, it's just the Crypt() function, but the strongly typed version with 4 params, thus the "4" in the name. Similar to SubStr2(), SubStr3() etc.

Chris
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2698

  wriedmann's Avatar wriedmann Online Posts: 1573
Hi Karl,

this function is defined in the VulcanRTFuncs library (says at least XIDE...)

Wolfgang

I was curious too, and XIDE showed me the prototype:
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it
Attachments:

Please Log in or Create an account to join the conversation.

Converting VO memo field data to a SQL Db 1 year 9 months ago #2703

  NickFriend's Avatar Topic Author NickFriend Offline Posts: 193
Hi Chris,

Thanks for your input in this. I seem to have a slightly different issue as well.

For some unknown reason the original developers of our app chose to use RDDINFO(_SET_MEMOBLOCKSIZE,31). I include a call to this in my XSharp code, but I get a runtime error from the Vulcan assemblies when I try to do a FieldGet on the memo field. If I edit the DBF to use memoblock size 32 and remove the call to RDDINFO, then I can read the memofield correctly. The error is not very informative...

Vulcan.NET Runtime Error
Error Code: 0 [Unknown error]
Subsystem:
Function: FIELDGET
Call Stack:
at Vulcan.Error.Throw(__Usual[] $args)
at VulcanRTFuncs.Functions.DefError(Error oError)
at Codeblocks.$CB_$Error$_930_22.Eval(__Usual[] <evalargs>)
at VulcanRTFuncs.Functions.Eval(Codeblock cbCodeBlock, __Usual[] args)
at Vulcan.VO.DbServer.Error(__Usual[] $args)
at Vulcan.VO.DbServer.FIELDGET(__Usual[] $args)
at TestQW10.Exe.Functions.Start() in: C:\Users\Nick\Documents\.....

Any ideas? Thanks

Nick

Please Log in or Create an account to join the conversation.

Last edit: by NickFriend. Reason: Found SDK_Defines assembly with _SET_MEMOBLOCKSIZE define
  • Page:
  • 1
  • 2