Processing SQlite3 records in a table

[casman]

Hello Forum.

I was wondering is I can please request some assistance with a few questions on processing records in an SQlite3 table.
I am new to working with SQlite3 databases.

As an example, I have a simple table that contains Zip codes. The DDL for the table is:
CREATE TABLE Zip (
    Zip TEXT,
    Latitude TEXT,
    Longitude TEXT,
    City TEXT,
    Alt_City TEXT,
    State TEXT,
    County TEXT
);

I have written a simple script to process a few SQL statements:

Code Select Expand



    cSQ=Dirscript():"Sqlite3.exe"
    If ! FileExist(cSQ) Then Terminate(@TRUE,"Cannot Continue",cSQ : " is missing.")
   
    cDB=Dirscript():"zips.db"
    If ! FileExist(cDB) then Then Terminate(@TRUE,"Cannot Continue",cDB : " is missing.")

    cConn= "DRIVER=SQLite3 ODBC Driver;Database=":cDB:";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    oConn=CreateObject("ADODB.Connection")
    oConn.Open(cConn)

    oRS=oConn.Execute("select Zip,City,County from Zip WHERE State = 'NC' LIMIT 10;")
    Message("",oRS.GetString())

    oRS=oConn.Execute("select State, count(*) from Zip group by State;")
    Message("",oRS.GetString())

    oRS=oConn.Execute("select State from Zip where State like 'N%%';")
    Message("",oRS.GetString())

    oRS=oConn.Execute("select State from Zip where State like 'N_';")
    Message("",oRS.GetString())

    oRS.Close()
    oRS=0
    oConn.Close()
    oConn=0

Exit



The first two statements work fine and return the selected records, however, when it runs the last two statements with the LIKE clause, nothing is returned. When I run the statements with LIKE 'N%' or LIKE 'N_' in SQliteStudio, they both work fine.

And suggestions on how to use the LIKE clause in the SELECT statement, or why it would be failing in the WinBatch script would be greatly appreciated.

My next request for assistance follows.

In all the examples I have found for Winbatch interacting with SQlite3, the queries return some set of records and display the results, however, I cannot find an example on how to setup a loop so that i can read the records in a table, one record at a time, do some manipulation, then display the result, one record at a time.

Any example of some code on how this can be done, or if somebody can point me to some sample code would be greatly appreciated.

Thank you in advance

Regards,
Casman.

[stanl]

I see you taken some hints from the Tech Database. I posted several scripts from SQLite circa 2010-2011 and I think I may have an example of a loop in my archives which I will post when found. It is strange the WB like syntax didn't work for you, but I should have the zip.db or can download it and can test.

[casman]

Thanks for the reply. Sorry, forgot to mention the sample was based on the info I found on the tech database. I look forward to your sample code thanks.

[stanl]

Yeah, that was a lot of old stuff I posted.  I have attached a modification of the script. I included an error handler.

First - in order to get the loop snippet as part of the script to work, had to add version=3 to the connect string.

Next - the LIKE or GLOB clauses don't appear to work with the free SQLite3 ODBC driver. Devart has a driver you have to purchase which handles LIKE and think the .NET assembly for SQLite would work with LIKE.

I didn't attach the zips.db for size reasons so getting suggestions from other contributors on this board would be difficult as they could not test the script. But that file [or how to build it] is available in the Tech DB, just search for SQLite.

But, something to play with.

[td]

I tried the LIKE operator with the latest version of the "Werner" ODBC driver for Sqlite 3.whatever and it works as expected.

Code (winbatch) Select Expand

;;; SqliteLikeTest.wbt
strDB = DirScript():"SMS.SQLITE"

;set up the standard Connection
strConn= "DRIVER=SQLite3 ODBC Driver;Database=":strDB:";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"

objConn=CreateObject("ADODB.Connection")
objConn.Open(strConn)

; Get a recordset
objRs = objConn.Execute("SELECT SMS, Carrier FROM Carriers WHERE SMS LIKE '@m%%';")
   
lRecords = '   SMS                                          Carrier':@CRLF
lRecords := objRs.GetString()

Message("The @m's", lRecords)
exit

The Sqlite dotNet assembly works as well but is a tiny bit slower.

[stanl]

I also played with it a little more.  Going back to the original db and code the OP posted:

using the @ as Tony did in his post

Code (WINBATCH) Select Expand


cSQL="select State from Zip where State LIKE '@N%%';"


will return the message with No Records Found

But tried

Code (WINBATCH) Select Expand


cSQL="select State from Zip where State LIKE 'N%%' GROUP BY State;"


and the Getstring() part worked - although the group by a little redundant.

Also, Tony's code did not use version=3, which I have googled may be itself redundant.  It would be easy to adapt Tony's code to the zips.db and test.  Possibly when building the zips.db in the code I posted in the tech db there is a PRAGMA option missing that accounts for the weird behavior of LIKE in the zips.db. The SQL for the build is below. I only saw one PRAGMA associated with LIKE and that had to do with case sensitivity.... dunno

Code (WINBATCH) Select Expand


cSQL=""
cSQL=cSQL:".echo ON":@CRLF
cSQL=cSQL:"":@CRLF
cSQL=cSQL:"Create Table Zip(Zip TEXT,Latitude TEXT,Longitude TEXT,City TEXT,Alt_City TEXT,State TEXT,County TEXT);":@CRLF
cSQL=cSQL:"":@CRLF
cSQL=cSQL:"PRAGMA cache_size = 400000;":@CRLF
cSQL=cSQL:"PRAGMA synchronous = OFF;":@CRLF
cSQL=cSQL:"PRAGMA journal_mode = OFF;":@CRLF
cSQL=cSQL:"PRAGMA locking_mode = EXCLUSIVE;":@CRLF
cSQL=cSQL:"PRAGMA count_changes = OFF;":@CRLF
cSQL=cSQL:"PRAGMA temp_store = MEMORY;":@CRLF
cSQL=cSQL:"PRAGMA auto_vacuum = NONE;":@CRLF
cSQL=cSQL:"":@CRLF
cSQL=cSQL:'.separator "\t"':@CRLF
cSQL=cSQL:".import ./uszip.txt Zip":@CRLF
cSQL=cSQL:"":@CRLF
;cSQL=cSQL:".quit;":@CRLF
cSQL=cSQL:".exit":@CRLF



[EDIT] - for Tony, I used the 32 bit drivers, see attached

[stanl]

One final test of Tony's code for zip.db

Code (WINBATCH) Select Expand


;;; SqliteLikeTest.wbt
strDB = DirScript():"zips.db"

;set up the standard Connection
strConn= "DRIVER=SQLite3 ODBC Driver;Database=":strDB:";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;version=3;"

objConn=CreateObject("ADODB.Connection")
objConn.Open(strConn)

; Get a recordset
;objRs = objConn.Execute("select State from Zip where State LIKE 'N%%';")
;fails with eof/bof error
objRs = objConn.Execute("select State,City from Zip where State LIKE 'N%%';")
;works
   
lRecords = objRs.GetString()

Message("States Like N", lRecords)
exit


So a complete S.W.A.G. indicates if you select only 1 field for a LIKE then group by might be needed.  Possibly how indexing in SQLite is handled has something to do with it.

Tony, you might try a single field to test the S.W.A.G.  :-X

[td]

I also played with it a little more.  Going back to the original db and code the OP posted:

using the @ as Tony did in his post

Code (WINBATCH) Select Expand


cSQL="select State from Zip where State LIKE '@N%%';"


will return the message with No Records Found

The '@' character is the first character in the "SMS" field of the database I was using.  It has no other significance and would not work in the zips database because the field does not have the at sign as the first character.

[td]

So a complete S.W.A.G. indicates if you select only 1 field for a LIKE then group by might be needed.  Possibly how indexing in SQLite is handled has something to do with it.

Tried a single field instead of two with my simple example and it worked just fine.  I can't say much about the database. It just happened to be the first SQLite database I found in one of my workstation's stuff-I-have-messed-with detritus directories.

[stanl]

Well, open source... take the good with the confusing. If I get a chance I may try indexing the zip.db by state and see if that allows a single field LIKE query.

[stanl]

finally, to put this puppy to sleep,,,

Code (WINBATCH) Select Expand


objRs = objConn.Execute("select distinct State from Zip where State LIKE 'N%%';")


works as does

Code (WINBATCH) Select Expand


cSQL="select distinct State from Zip where State LIKE 'N%%';"
oRS.Open(cSQL,oConn,1,3,1)


[td]

Is that something you would expect from a DB with duplicate fields or is that just an SQLite idiosyncrasy?

[JTaylor]

That is not normal SQL behavior.   Also, does it really solve the problem?  It still leaves an issue where one cannot retrieve all the rows where it matches "N%" or am I missing something???

Jim

[JTaylor]

It appears to be an issue with using "state".   If you include anything else in the query it works.  If you use another column by itself it works.   Wonder if ODBC driver has that as a reserved word and/or a bug where you can't select the "state" by itself???

Jim

[stanl]

That is not normal SQL behavior.   Also, does it really solve the problem.  It still leaves an issue where one cannot retrieve all the rows where it matches "N%" or am I missing something???

Jim

I suffered a typical case of Database Alzheimer's.... concentrated on the OP's code rather than the underlying database. I also had the zip data in Access and to query for SELECT State from Zips where State LIKE "N*" will return 6373 rows, which if you look at my code where I ask for Recordcount() it returns that value -

(1) to Tony's point - although it returns the value for Recordcount() the SQLite code will not produce getstring() for that query. This I consider an SQLite idiosyncrasy.

(2) to Jim's point - the initial code the OP provided for the LIKE queries was ambiguous when I first looked. I assumed the OP wanted how many States began with N, as opposed to how many rows in the db have states hat begin with N. 

I assume the SMS.Sqlite db (which I'm looking for since I have code that uses it in Powershell and one of my posts in the Tech DB concerned Appbase) had distinct rows so selecting a single field would work.

The use of DISTINCT or GROUP BY in either SQLite or Access will come up with 8 rows, the recordcount() for how many states appear as opposed to how many rows have those states.

I have been absorbed with the new SQL Server SQL syntax [ OVER PARTITION etc.], tableau and power BI that I am losing site of fundamentals.   :o :o

[JTaylor]

I agree that the original SQL that caused the problem most likely has no real use but it should work  :)

Jim

[casman]

First off I would like to say a big thank you to everybody for your detailed replies and suggestions. I will follow up the suggestions.

The idea that State might be a reserved word sounds interesting so I will rename State to State1 and see what happens :-)

[stanl]

First off I would like to say a big thank you to everybody for your detailed replies and suggestions. I will follow up the suggestions.

The idea that State might be a reserved word sounds interesting so I will rename State to State1 and see what happens :-)

State is not a key word. I would suggest you re-read the thread carefully to see the issue and the resolution.

https://www.sqlite.org/lang_keywords.html

[JTaylor]

I know "state" is not a keyword to SQLITE.  I wondered if the ODBC driver was doing something with it.

I must have missed it and still don't see it...what is the issue and the resolution?  The only single field that wouldn't work by itself without adding something like distinct, that I could find, was "state".

Jim

[stanl]

I know "state" is not a keyword to SQLITE.  I wondered if the ODBC driver was doing something with it.

I must have missed it and still don't see it...what is the issue and the resolution?  The only single field that wouldn't work by itself without adding something like distinct, that I could find, was "state".

Jim

If you run the query for state like 'N%%' from an ADO Recordset and then look at the RecordCount() property it return something like 7363 which is the number of rows where the state begins with N in the database - so technically the query works. The fact that the results cannot be convert with getstring() is the issue. The db was built from a text file (if the code I posted in 2011 was the source).... so

Could be problem with the way the data was converted
Could be an ADO problem
or, like you said a problem with the Werner driver [though you would think it would have come up years ago]
Could be several other things....

The resolution to make the getstring() to function better was either to add distinct [for unique N states] or add another field [to display all rows with getstring]. And yes, if you run the query in the SQLite DB Browser it will return the state rows.

The query by itself is meaningless except that it does present the issue of SQLite and ADO. I don't have the time right now to set up the query with the SQLite .NET assembly and see if the results are returned in a data adapter.... maybe after a few beers on the weekend. ;D

[JTaylor]

Okay....I was thinking it was failing with the Open, based all the testing I did since it worked with single field Selects other than the state field (although today it isn't which is weird).   Seems like GetString() doesn't know what to do when there is only one field being selected.  More than one column or some other modifier such as distinct works fine.

Thanks for the clarification.

Jim

[stanl]

This reminds me of several of the threads we used to engage in years past....   Usually followed the pattern of  (1) here is the problem (2) is it defined as a WB problem (3) well whose fault is it (4) here comes Tony [or Deanna] with something we didn't know WB could do  (5) or not  (6) try this (7) or this (8) or this....

and in the end we all learn.

[JTaylor]

Yep.

Jim

[td]

On my way out the door to enjoy a sunny day on Puget Sound.  It has been a long few days moving to a new workstation after my old workstation decided it didn't want to play nice with Windows anymore.  Anyway, tried my original script on the "zips" database with a "LIKE" query on the states field and it appeared to work except for the lack of line breaks between that returned state field values.   Also tried the following not particularly useful .Burgerflipper script and it produces some form of result (slowly.)

Code (winbatch) Select Expand

ObjectClrOption("appbase", "D:\Reinstalls\SQLite\sqlite-netFx20-binary-bundle-Win32-2005-1.0.84.0")
ObjectClrOption("use", "System.Data.SQLite")

objSqlLiteCon = objectClrNew("System.Data.SQLite.SQLiteConnection", "Data Source=%strFile%;Pooling=true")
ObjectClrOption("useany", "System.Data") ;, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
objDataState = ObjectClrNew( "System.Data.ConnectionState")
objSqlLiteCon.Open()
if objSqlLiteCon.State == objDataState.Open 

   ; Query.
   objSqlLiteCmd = objectClrNew("System.Data.SQLite.SQLiteCommand")
   objSqlLiteCmd.Connection  = objSqlLiteCon
   objSqlLiteCmd.CommandText = "SELECT State  FROM Zip where State LIKE 'N%%';"
   objReader = objSqlLiteCmd.ExecuteReader()
   
   ; Create a result.
   lRecords = ''
   while objReader.Read
      lRecords := objReader.GetValue(0):@CRLF
   endwhile
   
   ; Cleanup
   objReader.Close()
   objSqlLiteCon.Close()
   objReader     = 0
   objSqlLiteCmd = 0
endif
objDataState  = 0
objSqlLiteCon = 0

Message("Result", ItemCount(lRecords, @lf))

[stanl]

Tony;

I downloaded the NuGet package for the .NET dll and changed the appbase in your code to point to the NET46 dll.  Worked fine and returned a recordcount of 6374. But then re-ran the ADODB script I posted against the same db and obtained 6373. Might your code include headers??? or maybe an extra LF??? 

[td]

I don't believe the difference is the result of an extra line break.  The difference is very likely the result of how the ItemCount function counts items.  The function considers anything including nothing trailing the last delimiter to be an item while a count of records would not.

[stanl]

I don't believe the difference is the result of an extra line break.  The difference is very likely the result of how the ItemCount function counts items.  The function considers anything including nothing trailing the last delimiter to be an item while a count of records would not.

Makes sense.  Actually your code wasn't slow when I ran it.  Oh, and if one wants to escape WB's escaping the % character, this also worked

Code (WINBATCH) Select Expand


cSQL="select distinct State from Zip where State GLOB 'N*';"


[stanl]

Finally; cannot leave this thread without a shout out to Powershell [PS scripts can be called directly from WB's CLR].  The script below, with changes made for file locations, will produce a nice grid for either all state rows or distinct states.

Code Select Expand


Add-Type -Path "C:\temp\lib\Net46\System.Data.SQLite.dll"  #change as needed
$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection

$con.ConnectionString = "Data Source=C:\scripts\sqlite\zips.db" #change as needed

$con.Open()

$sql = $con.CreateCommand()
$sql.CommandText = "select State from Zip where State LIKE 'N%';"
#$sql.CommandText = "select distinct State from Zip where State LIKE 'N%';" #returns only 8 rows

$adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql

$data = New-Object System.Data.DataSet

[void]$adapter.Fill($data)
$data.tables[0]|out-gridview -Title 'States Beginning With N' 


[td]

Actually your code wasn't slow when I ran it. 

When I said "slow" I wasn't referring to human a discernable timing.  I was referring to profiler based timing.  Of course, if the dataset were large enough, the time difference would become noticeable to an observer without a profiler.

[stanl]

Actually your code wasn't slow when I ran it. 

When I said "slow" I wasn't referring to human a discernable timing.  I was referring to profiler based timing.  Of course, if the dataset were large enough, the time difference would become noticeable to an observer without a profiler.

Show Off :) :)...  In my world a few milliseconds as long as it works is no problem. I've never worked with SQLite in millions of rows but a couple hundred thousand - NP

[td]

The small slowdown isn't related to SQLite and only tangentially related to .Burgerflipper.  It has to do with looping within interpreted code instead of native code.  We try to make a habit of regularly profiling scripts as part of the old but still useful continuous improvement paradigm.

As far as a few milliseconds are concerned, it may not matter to some but it can be a deal breaker for others.  It all depends.

[td]

Did a quick test on a 100,000 record database use SQLite (of course), COM SQLite ODBC, and the SQLite .burgerflipper assembly.   Using the exact same single field LIKE query the results were:

SQLite COM ODBC - .282 seconds.
SQLite dotNet assembly - 110.109 seconds.

Those nanoseconds add up.

[stanl]

Did a quick test on a 100,000 record database use SQLite (of course), COM SQLite ODBC, and the SQLite .burgerflipper assembly.   Using the exact same single field LIKE query the results were:

SQLite COM ODBC - .282 seconds.
SQLite dotNet assembly - 110.109 seconds.

Those nanoseconds add up.

Point Taken.  For SQLite I would prefer to stick with COM.  The dotNet stuff can be confusing (just look at the versions and requirements for System.Data.SQLite). But this thread is a basic 'how to' with pros/cons and caveats. 

[td]

Mostly out of curiosity and if I get a little free time, I will test with the ODBC extender.