• Welcome, Guest. Please login.
 
April 01, 2020, 02:56:42 am

News:

Welcome to the SQLitening support forums!


Storing UDTs in SQLitening

Started by Frank W. Kelley, January 29, 2011, 09:22:14 pm

Previous topic - Next topic

Frank W. Kelley

I have a program that is currently using Tsunami that I would like to upgrade to SQLitening. The program uses a couple of UDT arrays to store data. I am looking for a way to avoid breaking the UDT into component parts for storage and then re-assembling everything when the data is read.

The ideal solution would be to store each entire UDT array as a single field and then read that field back into the UDT using PEEK$/POKE$. However, a test using this approach reveals that at least one $NUL appears in the resulting string, which would cause problems in SQLitening.

Is there a good solution for this? Initially, I thought of doing a REPLACE to substitute the backspace character for any $NULs then reverse the process when reading the data from the DB, but because integers and currency types are also in the UDT, this approach might end up corrupting the data stream.

Any thoughts?

Fred Meier

You could use the PB Join$ and Parse commands along with the
SQLitening slExeBind command. Sample program below. The only
problem is that you must dim (know the size of) your output
array before using the Parse command.  The PB command
ParseCount/Binary does not seem to work as advertised. 

#Compile Exe
#Include "SQLitening.Inc"

Type Sample_TYPE
   lA as Long
   qA as Quad
End Type

Function PBMain

   Local llDo as Long
   Dim luaSample1 (1 to 10) as Sample_TYPE
   Dim luaSample2 (1 to 10) as Sample_TYPE

   slOpen "", "C"
   slExe "Create Table T1 (F1)"

   ' Load array 1
   for llDo = 1 to ubound(luaSample1)
      luaSample1(llDo).lA = llDo
      luaSample1(llDo).qA = llDo * 2
   next


   ' Put array into database using slExeBind and PB join$
   slExeBind slBuildInsertOrUpdate("T1", "?", "F1"), slBuildBindDat(join$(luaSample1(),binary))

   ' Get array from database using slSelStr and PB parse
   parse slSelStr("Select F1 from T1"), luaSample2(), binary

   ' Compare results
   for llDo = 1 to ubound (luaSample1)
      if luaSample1(llDo).lA <> luaSample2(llDo).lA or luaSample1(llDo).qA <> luaSample2(llDo).qA then exit for
   next
   if llDo = ubound(luaSample1) + 1 then msgbox "Arrays Same" else msgbox "Arrays Different"

End Function


Frank W. Kelley

Your comment "The PB command ParseCount/Binary does not seem to work as advertised." unfortunately appears to be true. When I use the routines outlined above to store binary data, what I get back from SQLite does not match what I put in. I need to do some additional testing, but my suspicion is the problem is in PB 10.

I'll have more on this in a day or so, once I pin down where the corruption is occurring.

Frank W. Kelley

Well, it turns out (as often is the case) the issue was not with PB or with SQLitening. It was my programming error.

Instead of using slExeBind to update the table, I used slExe...quite a difference. Using the proper command resolved the issue and now all is working perfectly.