SQLite Journey

[Gary Beene]

Ok, after all the encouragement to look at SQLite, I spent time last night doing some reading on the topic.

Thanks, Chris(H)j, for your post with helpful intro comments.

This first example worked as expected, creating an empty db file.

'Compilable Example:
#Compile Exe
#Dim All
%Unicode = 1
#Include "Win32API.inc"
#Include "sqlite3.inc"

Function PBMain() As Long
   Local hSQL         As Dword              'handle to database
   sqlite3_open  "sql1.db", hSQL
   sqlite3_close hSQL
End Function

[Gary Beene]

This 2nd example appears to work. Just creates a table with 2 rows of data. I haven't gotten to the part yet of reading table data.

'Compilable Example:
#Compile Exe
#Dim All
%Unicode = 1
#Include "Win32API.inc"
#Include "sqlite3.inc"

Function PBMain() As Long
   Local hSQL         As Dword              'handle to database
   Local sqlZ         As StringZ * 200      'query string
   Local nRows, nCols As Long               'row/col in result of query
   Local iResult      As Long               'error # of result of query
   Local pResults     As Dword              'pointer to results pointer array
   Local pError       As StringZ Ptr        'pointer to error msg

   sqlite3_open  "sql.db", hSQL
   sqlZ = "begin transaction;" + _
         "create table Locations(City,State,Population);" + _
         "insert into Locations values ('Dallas','Texas',5000000);" + _
         "insert into Locations values ('OKC','Oklahoma',750000);" + _
         "commit transaction;"    'or  rollback transition;
   iResult = sqlite3_get_table (ByVal hSQL, sqlZ, pResults, nRows, nCols, VarPtr(pError))
   sqlite3_close hSQL
End Function

Jose's include file has this for declaration of the sqlite3_get_table function:

Declare Function sqlite3_get_table CDecl Import "SQLITE3.DLL" Alias "sqlite3_get_table" ( _
   ByVal hDbc As Dword _                                ' sqlite3*
 , ByRef szSql As AsciiZ _                              ' const char *zSql
 , ByRef pazResult As Dword _                           ' char ***pazResult
 , ByRef pnRows As Long _                               ' int *pnRow
 , ByRef pnColumns As Long _                            ' int *pnColumn
 , ByRef pzErrmsg As Dword _                            ' char **pzErrmsg
 ) As Long                                              ' int

Looking through the forum I'm seeing a variety of ways that folks call the function, often with ByVal arguments other than the one specified as ByVal in Jose's includes. Not sure why that is.

I've also seen a comment or two about %Unicode=1 being a problem with SQLite. Is that true? My apps are written with %Unicode=1 in them and having to remove it and adjust the remaining code would be a real pain.

Note: The code above has a correction from an earlier version of this post.

[Stuart McLachlan]

Please accept the advice of someone who has spent the last 30+ years almost constantly working with databases large and small

Start off on the right foot.
Get into the habit of:
1. Using relationships - normalise your data.
2. In SQLite, don't rely on ROWID, create a specific primary key alias for it in every table and use that in all table relationships
3. Specify default column types.

create table States(StatePK INTEGER PRINARY KEY, State TEXT NOT NULL, StateAbbr TEXT NOT NULL);
create table Locations(CityPK INTEGER PRINARY KEY, City TEXT NOT NULL, StateFK INTEGER NOT NULL,Population INTEGER);

[Stuart McLachlan]

> I've also seen a comment or two about %Unicode=1 being a problem with SQLite. Is that true?


The only caveat about SQLITE and UNICODE characters is case sensitivity. The NOCASE constraint makes sort/search case insensitive but only works on the ASCII character set range. It's never caused me a problem.

[Gary Beene]

Hi Stuart!

As someone who has never used a database in my PowerBASIC apps, I'm all ears. I appreciate the suggestions.

My reading so far hasn't exposed me to any of the terminology you just suggested I use. But as I keep reading I'll watch out for it.

[Stuart McLachlan]

If you can ignore all the advertising that wastes far to much real estate, this is a good primer:

https://www.tutorialspoint.com/sqlite/index.htm

[Mike Doty]

Improved version below

[Mike Doty]

Inserts can be checked if they are successful after each insert or optionally after all inserts in a transaction.
Sqlite has sqlite3_change and sqlite3_total_changes
I created a couple of macros to make it easier.
I'm sure there are macros posted on the bbs for many of the these things.

Got rid of having to pass the pesky database handle ghDB
Also demonstrated how "rollback" might be used.

I like passing as few values as possible and if I need a result, just read a threaded variable.
The number of rows and columns are always available this way (see gnrows,gncols).
If an error occurs it is also available in gsErr$ without having to pass any values.

Also note that the biggy, the results, are in a threaded array gsArray()
? JOIN$(gsArray(),$CR),,USING$("rows # cols #",gnrows,gncols)

Text can be inserted without having to wrap strings with $SQ using binding.
This allows easily inserting complete documents "as is".
Wrapping strings can be a real pain when documents already have single quoted strings in them.

#INCLUDE "sqlite3.inc"
THREADED ghDB,ger,gnrows,gncols AS DWORD
THREADED gserr,gsArray()        AS STRING
MACRO changecount= sqlite3_changes(ghDB)
MACRO totalchangecount = sqlite3_total_changes(ghDB)
MACRO sqliteopen(p1)=sqlite3_open(p1,ghDB)
MACRO sqliteclose=sqlite3_close(ghDB)

FUNCTION PBMAIN AS LONG

 sqliteopen ("sql.db")
 rs "drop table if exists Locations"
 rs "create table if not exists Locations(City text,State text ,Population integer)"
 rs "begin immediate" [COLOR=#FF0000] 'important: use "begin immediate" or "begin exclusive" on a LAN[/COLOR]
 rs "insert into Locations values ('Dallas','Texas',5000000)"
 rs "insert into Locations values ('OKC','Oklahoma',750000)"
 IF totalchangecount <>2 THEN rs "rollback" ELSE rs "end" 'assumes totalchangecount was 0 before beginning transaction

 rs "select rowid,* from Locations"
 ? JOIN$(gsArray(),$CR),,USING$("rows # cols #",gnrows,gncols)
 sqliteclose

END FUNCTION

FUNCTION rs(s AS STRING) AS LONG
 LOCAL r AS LONG, presults AS DWORD       ' pointer to SQLite's results set
 LOCAL psz() AS ASCIZ PTR                 ' array of pointers to SQLite's result set
 'LOCAL nrows, ncols AS LONG              ' # of rows and columns returned by SQLite
 LOCAL pzer AS ASCIZ PTR                  ' ptr to SQLite's error msg
 ger = SQLite3_Get_Table(BYVAL ghDB, BYVAL STRPTR(s), presults, gnRows,gnCols,BYVAL VARPTR(pzer))
 IF ger THEN
  gserr = "SQLite error in query! :" + @pzer + $CRLF + s
  ? gserr,%MB_ICONERROR,"Ending Program"
  sqlite3_close(ghDB)
  END
 ELSE
  gserr=""
  REDIM psz(0 TO gnrows*gncols-1) AT presults
  IF LTRIM$(LEFT$(TRIM$(s),7)) <> "select " THEN sqlite3_free(pzer):EXIT FUNCTION
  IF gnrows*gncols+gncols-1 < 0 THEN
    ? "No columns or rows",,"Ending Program"
    sqlite3_close(ghDB)
    EXIT FUNCTION 'invalid sql got to here
  END IF
  REDIM gsArray(0 TO gnrows*gncols+gncols-1)'not sure about this
  FOR r = 0 TO UBOUND(gsArray):gsArray(r) = @psz(r):NEXT
 END IF
 sqlite3_free_table(presults) ' free resources
END FUNCTION

[Mike Doty]

Insert 10 files into a table using the file name as a unique key and doing the insert with binding.
I haven't done binding without SQLitening, yet, but it will be very similar.

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG

 LOCAL s,sFile,sBuffer,sArray() AS STRING, x AS LONG

 FOR x = 1 TO 10 'create 10 files
  OPEN USING$("junk#",x) FOR OUTPUT AS #1
  s = USING$("'quoted data' is 'no' problem with binding in junk#'",x)
  PRINT #1,s;
  CLOSE #1
 NEXT

 slopen "junk.db3","C"
 slexe  "drop table if exists t1"
 slexe  "create table if not exists t1(filename text unique,document text)"

 slexe "begin" 'lock database only once for tremendous speed improvement

 FOR x = 1 TO 10  'read 10 files into SQLite table
  sFile = USING$("junk#",x)
  OPEN sFile FOR BINARY AS #1
  GET$ #1,LOF(1),sBuffer 'get data and strop
[B][COLOR=#B22222]  slexebind "insert into t1 values(?,?)",slbuildbinddat(sfile,"T") + slbuildbinddat(sbuffer,"T")[/COLOR][/B]
  CLOSE #1
 NEXT

 slexe "end' 'unlock database

 slselAry  "select * from t1",sArray(),"Q9"  'display everything
 ? JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Bind Test"

END FUNCTION

[Chris Holbrook]

...
Get into the habit of:
1. Using relationships - normalise your data.
2. In SQLite, don't rely on ROWID, create a specific primary key alias for it in every table and use that in all table relationships
3. Specify default column types.

If you are using "databases" as in "repositories for stored data", I agree.

But if you are using some of the facilities of SQLite to do in-memory stuff, where the entire life cycle of your data is controlled by a single program, ROWID can be pretty useful, and untyped columns too.

BTW, here's a tutorial on normalising data for those who are not familiar with the term: https://www.studytonight.com/dbms/da...malization.php

[Chris Holbrook]

Ok, after all the encouragement to look at SQLite, I spent time last night doing some reading on the topic.

Thanks, Chris(H)j, for your post with helpful intro comments.

Gary, it can't have been much more than a decade ago that I started posting SQLite examples, and here you are impetuously diving in! Are you feeling OK? :smile:

[Mike Doty]

Chris,
Could you point me to something with binding?
I'd like to continue with post#8, but add binding to it.
It is so easy working with SQLite with everything in a single function.

I don't know which of your posts would be the best to borrow from.
I work with SQLitening server, but would really like to switch to SQLite for single-user and put everything into a single function to make it even easier.
With Threaded variables and MACROS it is possible.
Post #8 demonstrates just passing a statement. It puts the recordset into a threaded array.

[Gary Beene]

Chris(H) ... I wouldn't want to be accused of acting hastily!

My interest in SQL is for use in gbThreads to store/search threads and to avoid the need to distribute 50K thread files.

[Mike Doty]

Gary,
That is why I'd like to add binding to post #8 so you don't have an excuse. Binding eliminates the unsightly wrapping of strings.
It is easy with SQLitening, but having trouble figuring it out with SQLite and adding to a single function that does everything.
Post #9 is my vision of reading your data into SQLite.
Note: I'd add any indexes after loading the data.

[Dean Gwilliam]

Gary
All normalisation is...is a set of rules to reduce/eliminate data duplication so you don't accidentally change one instance and leave another as it was. Once you've "normalised" your data...you often backtrack on it and reintroduce duplication to make your app faster
i.e. it's a sensible step but not the end of the story.

Data elimination ideally means having your data specified only in one table or another and referring to it's records via codes so you only have to change the data in one place and all the codes stay the same. Hope that helps. This normally means ending up with more tables and indirection than SEEMS intuitively necessary.

[Chris Holbrook]

Mike, here's a quick working example of SQLite native binding on a single column for building and querying a table:

'
' run a simple SQLITE3 queries to populate and query a table using binding
' Chris Holbrook Aug 2019
#compile exe
#dim all
#debug display
#include "WIN32API.INC"
#include "c:\users\chris\mine\chris\sqlite3.inc"

function pbmain()
    local hDB as long           ' database handle
    local s as string           ' ephemeral
    local sSQL as string        ' SQL passed to SRs
    local sval as string        ' string value used by SRs
    local nval as long          ' long value used by SRs
    local i as long             ' ephemeral
    local nparm as long         ' bind parameter position
    local nsteps as long        ' row count returned by steps SR
    local lretval as long       ' VALUE RETURNED BY FUNCTIONS
    local pz  as asciz ptr      'ephemeral
    local pztail as asciz ptr   ' ptr to unprocessed part of SQL
    local pstmt as long         ' ptr to query VM
    local hWin as long          ' text window handle
    '
    txt.window("SQLite3 BINDING EXAMPLE by Chris Holbrook 11 Aug 2019", 100, 100, 24, 80) to hWin
    txt.print "OPEN MEMORY DATABASE"
    if sqlite3_open( ":memory:", hdb) <> %SQLITE_OK then
        txt.print "CAN'T OPEN MEMORY DB!"
        txt.waitkey$
        exit function
    end if
    ' create table
    sSQL = "CREATE TABLE members(ID INTEGER PRIMARY KEY, Name TEXT)":txt.print sSQL
    gosub runquery
    ' insert data using binding
    sSQL = "INSERT INTO members(Name) VALUES(?)":txt.print sSQL
    gosub prepare
    gosub explain
    nparm = 1
    sval = "Jones": gosub bindtext: gosub steps:txt.print sval
    sval = "Singh": gosub bindtext: gosub steps:txt.print sval
    sval = "Li": gosub bindtext: gosub steps:txt.print sval
    sqlite3_clear_bindings(pStmt)
    '  query using one bind parameter
    sSQL = "SELECT ID, name FROM members WHERE Name not like ?":txt.print sSQL
    gosub prepare
    '
    sval = "Singh": gosub bindtext: sqlite3_step(pStmt): txt.print sval
    ' read back each row in the result set
    gosub showem
    '

    ' clean up
    sqlite3_finalize(pStmt) ' discard VM
    sqlite3_close(hDB)
    txt.waitkey$
    exit function
''''''''''
showem:
    s = ""
    sqlite3_reset(pstmt)
    lretval = sqlite3_step(pStmt)
    do
        if lretval = %SQLITE_ERROR then
            sqlite3_close(hDB)
            txt.print "step failed(1): " + s + $crlf + _
                   "error " + str$(lretval)
            txt.waitkey$
            exit function
        end if
        pz = sqlite3_column_text(pStmt, 1)
        txt.print ">    " + using$("#, &", sqlite3_column_int(pStmt, 0), @pz)
        lretval = sqlite3_step(pStmt)
        if lretval = %SQLITE_DONE then exit loop
    wend
    txt.print "done!"
    txt.waitkey$
return
''''''''''
runquery:
    pz = strptr(sSQL)
    lretval = sqlite3_exec(hDB, @pz, %NULL, %NULL, %NUll)
    if (lretval <> %SQLITE_OK) then
        sqlite3_close(hDB)
        txt.print "SQL failed: " + s + $crlf + _
               "error " + str$(lretval)
        txt.waitkey$
        exit function
    end if
return
''''''''''
prepare:
    pz = strptr(sSQL)
    lretval = sqlite3_prepare_v2(hDB, @pz, -1, pStmt, pztail)
    if (lretval <> %SQLITE_OK) then
        txt.print using$("failed to prepare statement" + $crlf + "&" + $crlf + "error: ###", sSQL, lretval)
        sqlite3_close(hDB)
        txt.waitkey$
        exit function
    end if
return
'''''''''''
explain:
    pz = strptr(sSQL)
    txt.print using$("& " + $crlf + _
             "parameter count is ##", _
             @pz, sqlite3_bind_parameter_count(byval pStmt))
return
''''''''''
bindtext:
    pz = strptr(sval)
    lretval = sqlite3_bind_text(pStmt, nparm, @pz, -1, %SQLITE_TRANSIENT)
    if lretval <> %SQLITE_OK then
        txt.print using$("failed to bind text" + $crlf + "&" + $crlf + "error: ###", sval, lretval)
        sqlite3_close(hDB)
        txt.waitkey$
        exit function
    end if
return
''''''''''
bindlong:
    lretval = sqlite3_bind_int(pStmt, nparm, nval)
    if lretval <> %SQLITE_OK then
        txt.print using$("failed to bind long" + $crlf + "###" + $crlf + "error: ###", nval, lretval)
        sqlite3_close(hDB)
        txt.waitkey$
        exit function
    end if
return
'''''''''
steps:
    nsteps = 0
    do
        lretval = sqlite3_step(pStmt)
        select case as long lretval
            case %SQLITE_ERROR
                sqlite3_close(hDB)
                txt.print "step failed(2): " + s + $crlf + _
                       "error " + str$(lretval)
                txt.waitkey$
                exit function
            case %SQLITE_DONE
                sqlite3_reset(pstmt)
                exit loop
            case %SQLITE_ROW
                incr nsteps
                iterate loop
            case else
                txt.print using$( "step: unexpected result(#) from &", lretval, s)
                sqlite3_finalize(pStmt)
                sqlite3_close(hDB)
                txt.waitkey$
                exit function
        end select
    loop
return
end function

[Mike Doty]

Fantastic. It will fit right into my function!! Thank you.

[Stuart McLachlan]

Very nice example.
One quibble
> sSQL = "CREATE TABLE members(ID INTEGER PRIMARY KEY, Name TEXT)"
while NAME is not a SQLite keyword, it is a PB one. As a general rule it is good practice to avoid using any keywords for table/field etc names.

[Chris Holbrook]

One quibble

Fair comment Stuart. I have a couple of quibbles myself, but am out of time to play with it for a day or two.

[Chris Holbrook]

I made an improved version of my code above and posted it to the Source Code Forum here: https://forum.powerbasic.com/forum/u...ts-and-binding