sq.bac

'
' Demonstration program for SQlite3
'
' PvE - May 2010, GPL.
' Improved to show more functionality - PvE, February 2015
' ------------------------------------------------------------------------

' Include the binding
INCLUDE "sqlite3.bac"

' Name of the data file - using in-memory database
CONST datafile$ = ":memory:"

' Print version
PRINT NL$, "Using SQLite version: ", DB_VERSION$

' Create a database
mydb = DB_OPEN(datafile$)

' Create table and add data
DB_SQL(mydb, "CREATE TABLE demo(someval INTEGER,  sometxt TEXT);")
DB_SQL(mydb, "INSERT INTO demo VALUES (123, 'Hello');")
DB_SQL(mydb, "INSERT INTO demo VALUES (234, 'cruel');")
DB_SQL(mydb, "INSERT INTO demo VALUES (345, 'world');")
DB_SQL(mydb, "COMMIT;")

'------------------------------------------------------------------------------------------

' Execute query - method #1 using reserved string DB_RESULT$
IF DB_SQL(mydb, "SELECT * FROM demo;") THEN
    PRINT NL$, DB_RESULT$
ELSE
    PRINT NL$, DB_ERROR$
ENDIF

'------------------------------------------------------------------------------------------

' Callback function to obtain results from a query
SUB MyFunc1(NUMBER columns, STRING colname$[], STRING rec$[])

    FOR x = 0 TO columns-1
        PRINT colname$[x], TAB$(1);
    NEXT
    PRINT

    FOR x = 0 TO columns-1
        PRINT rec$[x], TAB$(1);
    NEXT
    PRINT NL$

END SUB

' Execute query - method #2 using callback function
IF NOT(DB_QUERY(mydb, "SELECT * FROM demo;", MyFunc1)) THEN PRINT "Error: ", DB_ERROR$

'------------------------------------------------------------------------------------------

' Count the records - method #1
IF DB_SQL(mydb, "SELECT COUNT(*) FROM demo;") THEN
    PRINT "Method 1 - amount of records: ", MID$(DB_RESULT$, INSTR(DB_RESULT$, NL$) + 1)
ELSE
    PRINT NL$, DB_ERROR$
ENDIF

'------------------------------------------------------------------------------------------

' Callback function to obtain results from a query
SUB MyFunc2(NUMBER columns, STRING colname$[], STRING rec$[])

    PRINT "Method 2 - amount of records: ", rec$[0]

END SUB

' Count the records - method #2
IF NOT(DB_QUERY(mydb, "SELECT COUNT(*) FROM demo;", MyFunc2)) THEN PRINT "Error: ", DB_ERROR$

'------------------------------------------------------------------------------------------

' Close database
res = DB_CLOSE(mydb)