SQLite extender to replace/augment array functions

[cssyphus]

I finally have some cycles between urgent @TODO items to review this matter. Some time ago, if I am not misremembering, Jim suggested that one could use SQLite functionality to replace reliance on more complex WB array functions... Do I have that right?

I just began looking at the new SQLite extender and am quite excited to see memory-only SQLite DBs... I could imagine creating a memory-only SQLite DB instead of a WB array and being able to more easily do things like search for substrings within table cells (ttbomk WB arrays can only search/locate strings that match complete elements), or report the max value for a column, etc.

If I am not slicing into the rough re my understanding, does anyone have a quick example of creating a 3 or 4 row SQLite table and, for example, searching for a substring within a column, or returning the max value for a row?

I am experienced with MySQL/MariaDB so no problems figuring out SQL syntax, but a simple-but-complete extender example would be most helpful if anyone has such an example they previously constructed for themselves whilst blazing the trail.

[JTaylor]

If you look in the SQLite Help file under the Examples section it does most, if not all, of what you are wanting.

JIm

[spl]

Your biggest challenge, if scripting SQL to create SQLite tables will be working with SQLite 'Affinity'... which is something out of reach for a help-file.

[cssyphus]

Okay, no worries, I was hoping someone had a quick'n dirty crud-like example. Of course I reviewed the examples in the help file before posting - they are why I posted. I will post what I requested when I have it worked out. Was just checking if I could cut that corner.

[JTaylor]

Code Select Expand


  AddExtender("ilcsl44i.dll")

  WorldDb = "test.sqlite"
 
  ; Build the Sqlite db if needed.
  Db = slConnect(WorldDb)
  SQL = 'DROP TABLE IF EXISTS test;':@CRLF
  SQL = SQL:'CREATE TABLE "test" ( "id"    INTEGER NOT NULL, "lname"    TEXT NOT NULL, "fname"    TEXT NOT NULL, "age"    INTEGER, PRIMARY KEY("id" AUTOINCREMENT) );':@CRLF
  SQL = SQL:"Insert into test (lname, fname, age) values ('Smith','John',50);":@CRLF
  SQL = SQL:"Insert into test (lname, fname, age) values ('Smith','Jane',45);":@CRLF
  SQL = SQL:"Insert into test (lname, fname, age) values ('Smith','Joe',15);":@CRLF
  SQL = SQL:"Insert into test (lname, fname, age) values ('Smith','Mary',12);":@CRLF
  SQL = SQL:"Insert into test (lname, fname, age) values ('Smith','Henry',8);":@CRLF
  slExecute(Db, SQL)


  SQL = "SELECT Max(age) FROM test;
  max_age = slExecute(Db, SQL)
  Message("M.AGE",max_age[0,0])


  SQL = "SELECT id, lname, fname, age FROM test where fname like 'Jo%%';
  names = slExecute(Db, SQL)
  For x = 0 to ArrInfo(names,1)-1
    Message("Names",names[x,0]:" ":names[x,1]:", ":names[x,2]:" ":names[x,3])
  Next


  slClose(Db)




[spl]

Probably off-topic but one could easily create a fabricated recordset and execute getrow() to create an array, w/out need for an extender [or perhaps an SQLite in Memory table can act like a getrows() array]?

[JTaylor]

The SQLite Extender returns query results in an Array.

Jim

[spl]

The SQLite Extender returns query results in an Array.

Jim

I know. I used the extender in several scripts. So the snippet you posted could also have
WorldDb = ":memory:"

rather than

WorldDb = "test.sqlite"

[cssyphus]

Thanks guys, you just leapfrogged me ahead. Great example - exactly what I was looking for. Much obliged.

[JTaylor]

Sorry, read that incorrectly.

Jim

I know. I used the extender in several scripts. So the snippet you posted could also have
WorldDb = ":memory:"

rather than

WorldDb = "test.sqlite"

[td]

Your biggest challenge, if scripting SQL to create SQLite tables will be working with SQLite 'Affinity'... which is something out of reach for a help-file.

Sqllite affinity

[spl]

Your biggest challenge, if scripting SQL to create SQLite tables will be working with SQLite 'Affinity'... which is something out of reach for a help-file.

Sqllite affinity

My bad. Should have written Jim's help file. And even your reference might take some trail and error to completely absorb

[JTaylor]

Not sure if this applies or not but just to clarify for any future posts I might make regarding SQLite.  If I reference the SQLite Extender, unless I explicitly state otherwise, I am speaking of the one provided by the Winbatch folks.

Jim

[spl]

Not sure if this applies or not but just to clarify for any future posts I might make regarding SQLite.  If I reference the SQLite Extender, unless I explicitly state otherwise, I am speaking of the one provided by the Winbatch folks.

Jim

and maybe add minimal version of WB required.

[cssyphus]

Your biggest challenge, if scripting SQL to create SQLite tables will be working with SQLite 'Affinity'... which is something out of reach for a help-file.

Thanks Stan for that heads-up. I just peeked at the SQLite Datatypes page Tony linked above and it appears that the Affinity stuff is intended to make SQLite easier to work with...?  Can you throw a few scenarios where Affinity could trip us up?  Or were you pointing out that, due to Affinity, the SQLite SQL code is non-standard...?

[td]

SQLite Affinity is part of the SQLite dialect of SQL.  It exists because SQLite is kind of like WinBatch in the sense that it is data type flexible. (That is one reason why SQLite and WinBatch are a good fit.) Anyway, many other databases are type rigid and Affinity provides a way to move data between databases while controlling data type conversions. In other words, you don't need to worry about it unless you are moving data to/from an SQLite database to/from a type rigid target using expressions.

[spl]

Thanks Stan for that heads-up. I just peeked at the SQLite Datatypes page Tony linked above and it appears that the Affinity stuff is intended to make SQLite easier to work with...?  Can you throw a few scenarios where Affinity could trip us up?  Or were you pointing out that, due to Affinity, the SQLite SQL code is non-standard...?

As Tony posted, Affinity should not raise any concerns, unless converting to/from 'typed' columns. I just finished a final script for converting text into both typed Excel and an access table. I can work up a version that converts to SQLite so you can see how affinity works with the DateTime and Numeric columns - it would have to use .NET SQLite as my WB version is pre the SQLite Extender release, but should be simple to convert to Extender methods.

[spl]

The code below, is pretty worthless for this thread, but does illustrate creating a SQLite table from a typed data source. It uses the testdata.xml that can be created from a previous thread. It also uses the SQLite .NET dll and not the WB Extender. But I would point out using a WB Map to translate the ADO datatypes to SQLite. I have attached a jpeg of the Create Statement. I didn't do the extra step of inserting the rows, but that would not be difficult.

Code Select Expand

;Winbatch - 2022C - Import persisted Recordset to SQLite
;Stan Littlefield 6/11/2024
;just a
;========================================================================================
IntControl(73,1,0,0,0)
gosub udfs

;WB Map data types for SQLite Affinity
;ADO data types, .NET types for datatables etc.. would map differently
types = $"11,INTEGER
4,INTEGER
129,TEXT
7,TEXT
131,NUMERIC
3,INTEGER
6,REAL
128,BLOB
14,REAL
5,REAL$"
dTypes= MapCreate(types,'',@lf)
;========================================================================================


cnt = 0
;assumes this file exists, can be created from code in previous post
cXML = dirscript():"testdata.xml"
If ! FileExist(cXML) Then Terminate(@TRUE"File Missing",cXML)

sqlite = dirscript():"testdata.db"
If FileExist(sqlite) Then FileDelete(sqlite)

;using SQLite .Net dll, need to change to WB Extender
ObjectClrOption("AppBase", "C:\scripts\sqlite\")
ObjectClrOption("use","System.Data.SQLite")
cn= ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=":sqlite
cm = objectClrNew("System.Data.SQLite.SQLiteCommand")
cm.Connection  = cn
cn.Open()
;========================================================================================



Display(2,"Inserting into New Table","testdata")

oRS = Createobject("ADODB.Recordset")
oRS.Open(cXML,"Provider=MsPersist",1,3,256)
recs = oRS.RecordCount
cnt =  oRS.Fields.Count
cCreate = "Create Table testdata (":@LF
i=1
ForEach f in oRS.Fields
   name = f.Name
ColType = f.type
   ;crude code but uses Map for affinity
   If i<cnt
      If MapKeyExist(dTypes,colType)
         cCreate = cCreate:name:" ":dTypes[ColType]:",":@LF
      Else
         cCreate = cCreate:name:"  BLOB":",":@LF
      Endif
   Else
      If MapKeyExist(dTypes,colType)
         cCreate = cCreate:name:" ":dTypes[ColType]:@LF
      Else
         cCreate = cCreate:name:"  BLOB":@LF
      Endif
   Endif
   i+=1
Next
cCreate = cCreate:");"

oRS.Close()
oRS = 0
Message("Create Statement",cCreate)
cm.CommandText = cCreate
cm.ExecuteNonQuery()
Display(2,"Finished","Table Created in ":sqlite)

cn.Close()
Exit

:WBERRORHANDLER
geterror()
Message("Error Encountered",errmsg)
Exit

;========================================================================================

:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine

Return
;========================================================================================


[cssyphus]

Thank you for the examples and explanations. Deeply grateful.

[spl]

Thank you for the examples and explanations. Deeply grateful.

Welcome. I cleaned up the code and the script now both creates the table and inserts rows from the persisted/typed recordset - [sqlite db inside attached zip and can be viewed with SQLite Browser or queried with code]. Could be easily re-coded to use the WB Extender. As the date field is entered as TEXT, there would be some `fanagling` to re-treat it as a date for manipulation. Also, the insert section follows a suggested method for multiple rows from a recordset - not sure if the extender has a better bulk insert??? But if inserting a huge number of rows, the WB variable would crash. Possibly a way to handle the Recordset with getrows() and bulk insert as an array... dunno.

[EDIT]: My bad, SQLite supports BeginTransaction()....Commit()

This is far from the original ask in the thread, which seemed more to imply a small dataset with limited fields for use as an array.  Anyway, updated code below and db attached

Code Select Expand

;Winbatch - 2022C - Import persisted Recordset to SQLite
;Stan Littlefield 6/11/2024
;tested - should create testdata.db in folder where script run
;========================================================================================
IntControl(73,1,0,0,0)
gosub udfs

;WB Map data types for SQLite Affinity
;ADO data types, .NET types for datatables etc.. would map differently
types = $"11,INTEGER
4,INTEGER
129,TEXT
7,TEXT
131,NUMERIC
3,INTEGER
6,REAL
128,BLOB
14,REAL
5,REAL$"
dTypes= MapCreate(types,'',@lf)
;========================================================================================


cnt = 0
;assumes this file exists, can be created from code in previous post
cXML = dirscript():"testdata.xml"
If ! FileExist(cXML) Then Terminate(@TRUE"File Missing",cXML)

sqlite = dirscript():"testdata.db"
If FileExist(sqlite) Then FileDelete(sqlite) ;if used for repeated script runs

;using SQLite .Net dll, need to change to WB Extender
base = "C:\scripts\sqlite\"  ;folder where  System.Data.SQLite.dll located
ObjectClrOption("AppBase", base)
ObjectClrOption("use","System.Data.SQLite")
cn= ObjectClrNew("System.Data.SQLite.SQLiteConnection")
cn.ConnectionString = "Data Source=":sqlite
cm = objectClrNew("System.Data.SQLite.SQLiteCommand")
cm.Connection  = cn
cn.Open()
;========================================================================================

Display(2,"Inserting into New Table","testdata")

oRS = Createobject("ADODB.Recordset")
oRS.Open(cXML,"Provider=MsPersist",1,3,256)
recs = oRS.RecordCount
cnt =  oRS.Fields.Count
cCreate = "Create Table testdata (":@LF
i=1
insert = "INSERT INTO testdata ("
ForEach f in oRS.Fields
  name = f.Name
    ColType = f.type
  typ = "BLOB"
  If MapKeyExist(dTypes,colType) Then typ = dTypes[ColType]
  If i<cnt
      insert = insert:name:","
      cCreate = cCreate:name:" ":typ:",":@LF
  Else
      insert = insert:name:") ":@LF:"VALUES ":@LF
      cCreate = cCreate:name:" ":typ:@LF
  Endif
  i+=1
Next
cCreate = cCreate:");"

Message("Create Statement",cCreate)
cm.CommandText = cCreate
cm.ExecuteNonQuery()
Display(2,"Finished","Table Created in ":sqlite)
i=1
oRS.MoveFirst()
While ! oRS.eof()
  insert = insert:"("
  ForEach f in oRS.Fields
      name = f.Name 
      val = oRS.Collect("%name%")
      If f.type == 129 Then val = Strtrim(val)
      If i<cnt       
        insert = insert:"'":val:"'":","
      Else
        insert = insert:"'":val:"'":"),":@LF
      Endif
      i+=1
    Next
    oRS.MoveNext()
  i=1
EndWhile
oRS.Close()
oRS = 0
insert = Strsub(insert,1,strlen(insert)-2):";"
Message("Insert Statement",insert)
cm.CommandText = insert
cm.ExecuteNonQuery()
cm=0
cn.Close()
Display(2,"Rows Inserted",recs)
Exit

:WBERRORHANDLER
geterror()
Message("Error Encountered",errmsg)
Exit

;========================================================================================

:udfs
#DefineSubRoutine geterror()
  wberroradditionalinfo = wberrorarray[6]
  lasterr = wberrorarray[0]
  handlerline = wberrorarray[1]
  textstring = wberrorarray[5]
  linenumber = wberrorarray[8]
  errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
  Return(errmsg)
#EndSubRoutine

Return

;========================================================================================


[td]

Thank you for the examples and explanations. Deeply grateful.

The online version of the SQLite help file containing multiple examples can be found here:
SQLite Extender Docs

The SQLite extender requires WinBatch version WB2021B or newer. All extenders also require a WinBatch license current when or later than when the extender was released.

[spl]

The SQLite extender requires WinBatch version WB2021B or newer.

I have WB2022C and vcheck cannot install it. I'm assuming the Extender was updated since first release and that accounts  for the failure. Not a big deal, I look at threads like this for users learning more about what SQLite can do regardless of the tool used in WB.

[td]

The SQLite extender is updated when the SQLite library is significantly updated. The SQLite developers do not neglect their product.

All extenders require a license that has a WinBatch expiry that is later than the extender's latest release. The release dates for extenders are shown on the download page. Of course, if you have a version of an extender that works on your version of WinBatch, don't worry about it until you download a newer version of the extender.

Sometimes too much help can be a hindrance because of information overload.

[spl]

Of course, if you have a version of an extender that works on your version of WinBatch, don't worry about it until you download a newer version of the extender.

And, of course I have no 'version' of the extender or I wouldn't have posted as I did. I never downloaded an 'earlier' version. And, you seem to be saying even if your WB version meets the criteria of WB2021B or greater, and you never downloaded an earlier release, if you try to play catch-up you are SOL. That would be a more appropriate statement.

[td]

I was making a general statement about how extender versioning works and not implying anything about your particular status. Most of the comments I post on this forum are made with the larger audience in mind and not intended to address a single individual. This is more or less a public forum.

   

[td]

The SQLite Extender returns query results in an Array.

Jim

Missed this post earlier so neglected to mention that the SQLite extender also returns table rows as maps. The returned WIL map has column names as keys and row column values as WIL map values.

slStmExec Function Documentation

[cssyphus]

See real-world example posted here:

SQLite extender replacing advanced array functions - real world example