Announcement

Collapse

Forum Guidelines

This forum is for finished source code that is working properly. If you have questions about this or any other source code, please post it in one of the Discussion Forums, not here.
See more
See less

SQLite example using prepared statements and binding

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLite example using prepared statements and binding

    There was a discussion here: https://forum.powerbasic.com/forum/u...sqlite-journey

    Code:
    '
    ' run a simple SQLITE3 queries to populate and query a table using binding
    ' Chris Holbrook Aug 2019
    ' examples of populating a table with a single parameter substitution
    ' and querying it with one and two parameter substitutions
    '
    #compile exe
    #dim all
    #debug display
    #include "WIN32API.INC"
    #include "c:\users\chris\mine\chris\sqlite3.inc"  ' path of my copy of 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
    
        'postMessage(hWin, %WM_SETFONT, hfont, %true)
        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, lastname TEXT)":txt.print sSQL
        gosub runquery
        ' insert data using binding
        sSQL = "INSERT INTO members(lastname) VALUES(?)"
        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, lastname FROM members WHERE lastname not like ?"
        gosub prepare
        gosub explain
        '
        sval = "Singh": gosub bindtext: sqlite3_step(pStmt): txt.print sval
        ' read back each row in the result set
        gosub showem
        '
        '  query using two bind parameters
        sqlite3_clear_bindings(pStmt)
        sSQL = "SELECT ID, lastname FROM members WHERE lastname not like ? and ID > ?"
        gosub prepare
        gosub explain
        '
        nparm = 1: sval = "Singh": gosub bindtext: gosub steps: txt.print sval
        nparm = 2: nval = 1      : gosub bindlong: gosub steps: txt.print nval
        ' 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
    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$("& ", @pz)
        txt.print using$("parameter count is ##", _
                        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: & , 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 value ###  , 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

  • #2
    Improved by adding SQLite error message texts and more comments.

    The first time you run it you will see a SQLite error message. In red!
    The challenge is to change the SQL statement which caused it!

    Code:
    '
    ' run a simple SQLITE3 queries to populate and query a table using binding
    ' Chris Holbrook Aug 2019
    ' examples of populating a table with a single parameter substitution
    ' and querying it with one and two parameter substitutions
    '
    #compile exe
    #dim all
    #debug display
    #include "WIN32API.INC"
    #include "c:\users\chris\mine\chris\sqlite3.inc"  ' path of my copy of sqlite3.inc
    macro mShowQermsg
        macrotemp pz
        dim pz as asciz ptr
    
        pz = sqlite3_errmsg(hDB)
        txt.print @pz
        sqlite3_free(pz)
    end macro
    '
    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 nkey as long          ' to hold primary key column value (for debugging!)
        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 17 Aug 2019", 100, 100, 24, 80) to hWin
    
        'postMessage(hWin, %WM_SETFONT, hfont, %true)
        txt.print "OPEN MEMORY DATABASE"
        if sqlite3_open( ":memory:", hdb) <> %SQLITE_OK then
            txt.color = %red
            txt.print "CAN'T OPEN MEMORY DB!"
            txt.waitkey$
            exit function
        end if
        ' create table
        sSQL = "CREATE TABLE members(ID INTEGER PRIMARY KEY, lastname TEXT)":txt.print sSQL
        gosub runquery
        ' insert data using binding
        sSQL = "INSERT INTO members(lastname) VALUES(?)"
        gosub prepare
        gosub explain
        nparm = 1
        ' execute the SQL for each row we want to insert into the database
        sval = "Jones"      : txt.print sval: gosub bindtext: gosub steps
        sval = "Singh"      : txt.print sval: gosub bindtext: gosub steps
        sval = "Dunkerton"  : txt.print sval: gosub bindtext: gosub steps
        sval = "Li"         : txt.print sval: gosub bindtext: gosub steps
        sqlite3_clear_bindings(pStmt)
        '  query using one bind parameter
        sSQL = "SELECT ID, lastname FROM mxembers WHERE lastname not like ?"
        gosub prepare
        gosub explain
        '
        sval = "Singh": gosub bindtext: txt.print sval
        ' read back each row in the result set
        gosub showem
        '
        '  query using two bind parameters
        sqlite3_clear_bindings(pStmt)
        ' SQLite's prepare, bind, and step functions first reqister the SQL statement
        ' with the SQLlite VM, then bind the value(s) we want to substitute, then
        ' execute the query step-wise. Execution (sqlite3_step) can be repeated.
        ' see https://sqlite.org/c3ref/step.html for more information.
        sSQL = "SELECT ID, lastname FROM members WHERE lastname not like ? and ID > ?"
        gosub prepare
        gosub explain
        '
        nparm = 1: sval = "Singh": txt.print sval: gosub bindtext
        nparm = 2: nval = 1      : txt.print nval: gosub bindlong
        ' 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 = ""
        do
            lretval = sqlite3_step(pStmt)
            select case as long lretval
                case %SQLITE_ERROR
                    sqlite3_close(hDB)
                    txt.color = %red
                    txt.print using$("step failed(1): & SQlite error code = ###", s, lretval)
                    mShowQermsg
                    txt.waitkey$
                    exit function
                '
                case %SQLITE_DONE
                    sqlite3_reset(pstmt)
                    exit loop
                '
                case %SQLITE_ROW
                '
                    nkey = sqlite3_column_int(pStmt, 0)
                    pz = sqlite3_column_text(pStmt, 1)
                    txt.color = %blue
                    txt.print ">    " + using$("#, &", nkey, @pz)
                    txt.color = %black
                    iterate loop
                '
                case else
                    txt.color = %red
                    txt.print using$( "step: unexpected result(#) from &", lretval, s)
                    mShowQermsg
                    sqlite3_close(hDB)
                    txt.waitkey$
                    exit function
            end select
        wend
    return
    ''''''''''
    runquery:
        pz = strptr(sSQL)
        ' using SQLite's "One-Step Query Execution Interface"
        ' this says "execute the SQL statements in the database"
        ' For a full explanation of sqlite3_exec,
        ' see https://sqlite.org/c3ref/exec.html
        lretval = sqlite3_exec(hDB, @pz, %NULL, %NULL, %NULL)
        '
        if (lretval <> %SQLITE_OK) then
            sqlite3_close(hDB)
            txt.color = %red
            txt.print using$("SQL failed: &", s)
            txt.print using$("SQLite error code = ###", lretval)
            mShowQermsg
            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.color = %red
            txt.print "failed to prepare statement:"
            txt.print using$("' & '", sSQL)
            txt.print using$("SQLite error code = ###", lretval)
            mShowQermsg
            sqlite3_close(hDB)
            txt.waitkey$
            exit function
        end if
    return
    '''''''''''
    explain:
        pz = strptr(sSQL)
        txt.print using$("& ", @pz)
        txt.print using$("parameter count is ##", _
                        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.color = %red
            txt.print using$("failed to bind text: & , SQLite error code = ###", sval, lretval)
            mShowQermsg
            sqlite3_close(hDB)
            txt.waitkey$
            exit function
        end if
    return
    ''''''''''
    bindlong:
        lretval = sqlite3_bind_int(pStmt, nparm, nval)
        if lretval <> %SQLITE_OK then
            txt.color = %red
            txt.print using$("failed to bind long value ###, SQLite error code = ###", nval, lretval)
            mShowQermsg
            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.color = %red
                    txt.print using$("Step failed(2): &", s)
                    txt.print using$("SQLite error code = ###",lretval)
                    mShowQermsg
                    txt.waitkey$
                    exit function
                '
                case %SQLITE_DONE
                    sqlite3_reset(pstmt)
                    exit loop
                '
                case %SQLITE_ROW
                    incr nsteps
                    iterate loop
                '
                case %SQLITE_BUSY
                    txt.color = %red
                    txt.print using$( "step: unexpected result(#) from &", lretval, s)
                    txt.print "could not get lock on memory DB!"
                    sqlite3_finalize(pStmt)
                    sqlite3_close(hDB)
                    txt.waitkey$
                    exit function
                '
                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

    Comment


    • #3
      Hardly a challenge:

      sSQL = "SELECT ID, lastname FROM mxembers WHERE lastname not like ?"

      Comment


      • #4
        Click image for larger version  Name:	chrisbinding.png Views:	0 Size:	8.6 KB ID:	784033
        Improved by adding SQLite error message texts and more comments.

        The first time you run it you will see a SQLite error message. In red!
        The challenge is to change the SQL statement which caused it!
        Chris,
        Very nice!
        Shows what and where if an error occurs.
        It also demonstrates this using all the different methods of binding and how to read back a recordset.

        Requires
        Jose Roca's sqlite3.inc
        Sqlite3.DLL Precompiled Binaries for Windows 32-bit DLL (x86) for SQLite version www.sqlite.org/download.html

        Code:
        '
        ' run a simple SQLITE3 queries to populate and query a table using binding
        ' Chris Holbrook Aug 2019
        ' examples of populating a table with a single parameter substitution
        ' and querying it with one and two parameter substitutions
        '
        #COMPILE EXE
        #DIM ALL
        #DEBUG DISPLAY
        #INCLUDE "WIN32API.INC"
        #INCLUDE "sqlite3.inc"  ' path of my copy of sqlite3.inc
        MACRO mShowQermsg
            MACROTEMP pz
            DIM pz AS ASCIZ PTR
        
            pz = sqlite3_errmsg(hDB)
            TXT.PRINT @pz
            sqlite3_free(pz)
        END MACRO
        
        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 nkey AS LONG          ' to hold primary key column value (for debugging!)
            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 17 Aug 2019", 100, 100, 24, 80) TO hWin
        
            'postMessage(hWin, %WM_SETFONT, hfont, %true)
            TXT.PRINT "OPEN MEMORY DATABASE"
            IF sqlite3_open( ":memory:", hdb) <> %SQLITE_OK THEN
                TXT.COLOR = %RED
                TXT.PRINT "CAN'T OPEN MEMORY DB!"
                TXT.WAITKEY$
                EXIT FUNCTION
            END IF
            ' create table
            sSQL = "CREATE TABLE members(ID INTEGER PRIMARY KEY, lastname TEXT)":TXT.PRINT sSQL
            GOSUB runquery
            ' insert data using binding
            sSQL = "INSERT INTO members(lastname) VALUES(?)"
            GOSUB prepare
            GOSUB explain
            nparm = 1
            ' execute the SQL for each row we want to insert into the database
            sval = "Jones"      : TXT.PRINT sval: GOSUB bindtext: GOSUB steps
            sval = "Singh"      : TXT.PRINT sval: GOSUB bindtext: GOSUB steps
            sval = "Dunkerton"  : TXT.PRINT sval: GOSUB bindtext: GOSUB steps
            sval = "Li"         : TXT.PRINT sval: GOSUB bindtext: GOSUB steps
            sqlite3_clear_bindings(pStmt)
            '  query using one bind parameter
            sSQL = "SELECT ID, lastname FROM mxembers WHERE lastname not like ?"
            GOSUB prepare
            GOSUB explain
            '
            sval = "Singh": GOSUB bindtext: TXT.PRINT sval
            ' read back each row in the result set
            GOSUB showem
            '
            '  query using two bind parameters
            sqlite3_clear_bindings(pStmt)
            ' SQLite's prepare, bind, and step functions first reqister the SQL statement
            ' with the SQLlite VM, then bind the value(s) we want to substitute, then
            ' execute the query step-wise. Execution (sqlite3_step) can be repeated.
            ' see https://sqlite.org/c3ref/step.html for more information.
            sSQL = "SELECT ID, lastname FROM members WHERE lastname not like ? and ID > ?"
            GOSUB prepare
            GOSUB explain
            '
            nparm = 1: sval = "Singh": TXT.PRINT sval: GOSUB bindtext
            nparm = 2: nval = 1      : TXT.PRINT nval: GOSUB bindlong
            ' 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 = ""
            DO
                lretval = sqlite3_step(pStmt)
                SELECT CASE AS LONG lretval
                    CASE %SQLITE_ERROR
                        sqlite3_close(hDB)
                        TXT.COLOR = %RED
                        TXT.PRINT USING$("step failed(1): & SQlite error code = ###", s, lretval)
                        mShowQermsg
                        TXT.WAITKEY$
                        EXIT FUNCTION
                    '
                    CASE %SQLITE_DONE
                        sqlite3_reset(pstmt)
                        EXIT LOOP
                    '
                    CASE %SQLITE_ROW
                    '
                        nkey = sqlite3_column_int(pStmt, 0)
                        pz = sqlite3_column_text(pStmt, 1)
                        TXT.COLOR = %BLUE
                        TXT.PRINT ">    " + USING$("#, &", nkey, @pz)
                        TXT.COLOR = %BLACK
                        ITERATE LOOP
                    '
                    CASE ELSE
                        TXT.COLOR = %RED
                        TXT.PRINT USING$( "step: unexpected result(#) from &", lretval, s)
                        mShowQermsg
                        sqlite3_close(hDB)
                        TXT.WAITKEY$
                        EXIT FUNCTION
                END SELECT
            WEND
        RETURN
        ''''''''''
        runquery:
            pz = STRPTR(sSQL)
            ' using SQLite's "One-Step Query Execution Interface"
            ' this says "execute the SQL statements in the database"
            ' For a full explanation of sqlite3_exec,
            ' see https://sqlite.org/c3ref/exec.html
            lretval = sqlite3_exec(hDB, @pz, %NULL, %NULL, %NULL)
            '
            IF (lretval <> %SQLITE_OK) THEN
                sqlite3_close(hDB)
                TXT.COLOR = %RED
                TXT.PRINT USING$("SQL failed: &", s)
                TXT.PRINT USING$("SQLite error code = ###", lretval)
                mShowQermsg
                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.COLOR = %RED
                TXT.PRINT "failed to prepare statement:"
                TXT.PRINT USING$("' & '", sSQL)
                TXT.PRINT USING$("SQLite error code = ###", lretval)
                mShowQermsg
                sqlite3_close(hDB)
                TXT.WAITKEY$
                EXIT FUNCTION
            END IF
        RETURN
        '''''''''''
        explain:
            pz = STRPTR(sSQL)
            TXT.PRINT USING$("& ", @pz)
            TXT.PRINT USING$("parameter count is ##", _
                            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.COLOR = %RED
                TXT.PRINT USING$("failed to bind text: & , SQLite error code = ###", sval, lretval)
                mShowQermsg
                sqlite3_close(hDB)
                TXT.WAITKEY$
                EXIT FUNCTION
            END IF
        RETURN
        ''''''''''
        bindlong:
            lretval = sqlite3_bind_int(pStmt, nparm, nval)
            IF lretval <> %SQLITE_OK THEN
                TXT.COLOR = %RED
                TXT.PRINT USING$("failed to bind long value ###, SQLite error code = ###", nval, lretval)
                mShowQermsg
                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.COLOR = %RED
                        TXT.PRINT USING$("Step failed(2): &", s)
                        TXT.PRINT USING$("SQLite error code = ###",lretval)
                        mShowQermsg
                        TXT.WAITKEY$
                        EXIT FUNCTION
                    '
                    CASE %SQLITE_DONE
                        sqlite3_reset(pstmt)
                        EXIT LOOP
                    '
                    CASE %SQLITE_ROW
                        INCR nsteps
                        ITERATE LOOP
                    '
                    CASE %SQLITE_BUSY
                        TXT.COLOR = %RED
                        TXT.PRINT USING$( "step: unexpected result(#) from &", lretval, s)
                        TXT.PRINT "could not get lock on memory DB!"
                        sqlite3_finalize(pStmt)
                        sqlite3_close(hDB)
                        TXT.WAITKEY$
                        EXIT FUNCTION
                    '
                    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
        https://duckduckgo.com instead of google

        Comment

        Working...
        X