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 15 Sep 2017 13:35 #2663

  • NickFriend
  • NickFriend's Avatar
  • Topic Author


  • Posts: 242
  • 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 15 Sep 2017 14:20 #2665

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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

    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 15 Sep 2017 15:51 #2667

    • NickFriend
    • NickFriend's Avatar
    • Topic Author


  • Posts: 242
  • 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 15 Sep 2017 16:09 #2669

    • NickFriend
    • NickFriend's Avatar
    • Topic Author


  • Posts: 242
  • 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 15 Sep 2017 22:47 #2679

    • Chris
    • Chris's Avatar


  • Posts: 3836
  • 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

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

    Converting VO memo field data to a SQL Db 16 Sep 2017 03:45 #2680

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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

    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 16 Sep 2017 03:57 #2681

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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

    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 16 Sep 2017 05:03 #2682

    • Chris
    • Chris's Avatar


  • Posts: 3836
  • 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 16 Sep 2017 05:08 #2683

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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

    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 16 Sep 2017 05:53 #2684

    • Chris
    • Chris's Avatar


  • Posts: 3836
  • 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 16 Sep 2017 06:45 #2685

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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

    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 16 Sep 2017 08:23 #2686

    • Chris
    • Chris's Avatar


  • Posts: 3836
  • 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 16 Sep 2017 09:10 #2687

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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

    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 16 Sep 2017 09:37 #2688

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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: riedmann.it/download/XS_DBFBinaryRead.zip , completed with your message as readme.

    Wolfgang
    Wolfgang Riedmann
    Meran, South Tyrol, Italy

    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 16 Sep 2017 16:49 #2694

    • Chris
    • Chris's Avatar


  • Posts: 3836
  • 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 16 Sep 2017 17:32 #2695

    • FFF
    • FFF's Avatar


  • Posts: 1398
  • 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
    Regards
    Karl (X# 2.14.0.4; Xide 1.33; W8.1/64 German)

    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 16 Sep 2017 17:39 #2696

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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

    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 16 Sep 2017 17:42 #2697

    • Chris
    • Chris's Avatar


  • Posts: 3836
  • 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 16 Sep 2017 17:44 #2698

    • wriedmann
    • wriedmann's Avatar


  • Posts: 3297
  • 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

    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 18 Sep 2017 10:33 #2703

    • NickFriend
    • NickFriend's Avatar
    • Topic Author


  • Posts: 242
  • 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