Sorry your browser is not supported!

Set Window On

result = Sqlite Open Database( 1, "test.s3db", "", "" )

n = Sqlite Create Table( 1, "original", "myColumn INTEGER" )
Print "create table success: ", n

n = Sqlite Copy Table( 1, "original", "copy" )
Print "copy table success: ", n

n = Sqlite Delete Table( 1, "original" )
n = Sqlite Delete Table( 1, "copy" )
n = Sqlite Close Database( 1 )

Print
Print "---------------------"
Print "Press any key to exit"
Wait Key
END

My result in DBPro:
*
*   Column_0 *   Column_1 *   Column_2 *
*
*  Record_1a *  Record_1b *  Record_1c *
*  Record_2a *  Record_2b *  Record_2c *
*

Your helpfile describe result as:
****************************************
*   Column_0 *   Column_1 *   Column_2 *
****************************************
*  Record_1a *  Record_1b *  Record_1c *
*  Record_2a *  Record_2b *  Record_2c *
****************************************

data$ = Sqlite Table As PrintString$(1, "table")
Print data$

outputs:

*
*   Column_0 *   Column_1 *   Column_2 *
*
*  Record_1a *  Record_1b *  Record_1c *
*  Record_2a *  Record_2b *  Record_2c *
*

****************************************
*   Column_0 *   Column_1 *   Column_2 *
****************************************
*  Record_1a *  Record_1b *  Record_1c *
*  Record_2a *  Record_2b *  Record_2c *
****************************************

ID     Username    Password
-----  ----------  -----------
1      user01      password01  
2      user        pass
3      name        word  

Rem DBPRO SETUP
Set Window On
Set Window Title "SQLITE Wrapper"
Set Text Font "Consolas"
Set Text Size 24

Rem OPEN DATABASE
result = Sqlite Open Database( 1, "mydata.s3db", "user", "" )

Rem SET TABLE DATA
table_name$ = "mytable"
column_def$ = "Column_0 INTEGER, Column_1 TEXT, Column_2 TEXT"


Rem CREATE TABLE
result = Sqlite Create Table( 1, table_name$+"1", column_def$ )
result = Sqlite Create Table( 1, table_name$+"2", column_def$ )

Rem TABLE COUNT
table_count = Sqlite Table Count( 1 )
Print "total tables: ", table_count

Rem TABLE NAME
For table = 1 To table_count
    result$ = Sqlite Get Table Name$( 1, table )
    Print "table name: ", result$
Next table


Rem CLEAN UP
result = Sqlite Delete Table( 1, table_name$+"1" )
result = Sqlite Delete Table( 1, table_name$+"2" )
result = Sqlite Close Database( 1 )

Rem WAIT KEY AND EXIT APPLICATION
Print
Print "---------------------"
Print "Press any key to exit"
Wait Key
END

Rem DBPRO SETUP
Set Window On
Set Window Title "SQLITE Wrapper"
Sync On : Sync

Rem OPEN DATABASE
result = Sqlite Open Database( 1, "mydata.s3db", "user", "" )

Rem MAKE A TABLE AND POPULATE
Print "creating table "; : Sync
result = Sqlite Create Table( 1, "tableA", "lastname TEXT, FirstName TEXT, JobCode TEXT, Salary TEXT" )
result = Sqlite Create Table( 1, "tableB", "lastname TEXT, FirstName TEXT, JobCode TEXT, Salary TEXT" )

sql$ = "INSERT INTO tableA VALUES ('Family', 'Adams', 'PT1', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )
sql$ = "INSERT INTO tableA VALUES ('Family', 'Bdams', 'TA2', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )
sql$ = "INSERT INTO tableB VALUES ('Family', 'Cdams', 'DF1', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )
sql$ = "INSERT INTO tableB VALUES ('Family', 'Ddams', 'TA2', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )
sql$ = "INSERT INTO tableB VALUES ('Family', 'Edams', 'TA3', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )

Print "..done" : sync





Rem MAKE TABLE FROM QUERY
sql$ = ""
sql$ = sql$ + "CREATE TABLE tableC AS "
sql$ = sql$ + " SELECT 'lastname', 'FirstName', 'JobCode' "
sql$ = sql$ + " FROM tableA, tableB; "
sql$ = sql$ + " WHERE instr(JobCode, 'TA');"

result = Sqlite Begin Sql Query( 1, sql$ )

result = Sqlite MAke Table From Sql Query Records( 1, "tableC" )
Print result
Print Sqlite Get Last Error$()
Sync

result = Sqlite Finish Sql Query( 1 )







Rem DELETE TABLE
result = Sqlite Delete Table( 1, "tableA" )
result = Sqlite Delete Table( 1, "tableB" )
result = Sqlite Delete Table( 1, "tableC" )

Rem CLOSE DATABASE
result = Sqlite Close Database( 1 )

Rem WAIT KEY AND EXIT APPLICATION
Print
Print "---------------------"
Print "Press any key to exit" : Sync
Wait Key
END

Rem DBPRO SETUP
Set Window On
Set Window Title "SQLITE Wrapper"
Sync On : Sync

Rem OPEN DATABASE
result = Sqlite Open Database( 1, "mydata.s3db", "user", "" )

Rem MAKE A TABLE AND POPULATE
Print "creating table "; : Sync
result = Sqlite Create Table( 1, "tableA", "lastname TEXT, FirstName TEXT, JobCode TEXT, Salary INTEGER" )
result = Sqlite Create Table( 1, "tableB", "lastname TEXT, FirstName TEXT, JobCode TEXT, Salary INTEGER" )

sql$ = "INSERT INTO tableA VALUES ('Family', 'Adams', 'PT1', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )
sql$ = "INSERT INTO tableA VALUES ('Family', 'Bdams', 'TA2', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )
sql$ = "INSERT INTO tableB VALUES ('Family', 'Cdams', 'DF1', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )
sql$ = "INSERT INTO tableB VALUES ('Family', 'Ddams', 'TA2', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )
sql$ = "INSERT INTO tableB VALUES ('Family', 'Edams', 'TA3', 12345);" : result = Sqlite Updating SQL Statement( 1, sql$ )

Print "..done" : sync





Rem MAKE TABLE FROM QUERY
sql$ = "SELECT * FROM tableA WHERE JobCode LIKE '%TA%';"

result = Sqlite Begin Sql Query( 1, sql$ )
result = Sqlite MAke Table From Sql Query Records( 1, "tableC" )
result = Sqlite Finish Sql Query( 1 )

Rem PRINT TABLE C
Print Sqlite Table As Printstring$( 1, "tableC", 10 )






Rem DELETE TABLE
result = Sqlite Delete Table( 1, "tableA" )
result = Sqlite Delete Table( 1, "tableB" )
result = Sqlite Delete Table( 1, "tableC" )

Rem CLOSE DATABASE
result = Sqlite Close Database( 1 )

Rem WAIT KEY AND EXIT APPLICATION
Set Cursor 0, 400
Print
Print "---------------------"
Print "Press any key to exit" : Sync
Wait Key
END

Column_0    Column_1    Column_2 

Record_1a   Record_1b   Record_1c 
Record_2a   Record_2b   Record_2c 

Rem DBPRO SETUP
Set Window On
Set Window Title "SQLITE Wrapper"
Set Text Font "Consolas"
Set Text Size 24

Rem OPEN DATABASE
result = Sqlite Open Database( 1, "mydata.s3db", "user", "" )

Rem SET TABLE DATA
table_name$ = "mytable"
column_def$ = "Column_0 INTEGER, Column_1 TEXT, Column_2 TEXT"


Rem CREATE TABLE
result = Sqlite Create Table( 1, table_name$+"1", column_def$ )
result = Sqlite Create Table( 1, table_name$+"2", column_def$ )

Rem RETRIEVE DATA
sql$ = "SELECT name FROM sqlite_master WHERE type='table'"
result = Sqlite Begin Sql Query( 1, sql$ )
    
    table_count = Sqlite Record Row Count( 1 )
    For table = 1 To table_count
        name$ = Sqlite Record Current Row String$( 1, 0 )
        Print "Table_"+Str$(table)+"  - ";
        Print name$
        n = Sqlite Next Record Row( 1 )
    Next table

result = Sqlite Finish Sql Query( 1 )


Rem CLEAN UP
result = Sqlite Delete Table( 1, table_name$+"1" )
result = Sqlite Delete Table( 1, table_name$+"2" )
result = Sqlite Close Database( 1 )

Rem WAIT KEY AND EXIT APPLICATION
Print
Print "---------------------"
Print "Press any key to exit"
Wait Key
END

	// fill itemdata() array
	item =0
	n = Sqlite Begin Sql Query( 1, "SELECT * FROM item;" )
		rows = Sqlite Record Row Count( 1 )
		For index = 1 To rows
			Array Insert At Bottom ITEMDATA()
			pos = Array Count( ITEMDATA() )
			ITEMDATA(pos).DB_ID = Sqlite Record Current Row Integer( 1, 0 )
			ITEMDATA(pos).Name = Sqlite Record Current Row String$( 1, 1 )

			temp$ = Sqlite Record Current Row String$( 1, 2 ) // weird bug cause crash if try to read directly into array
			ITEMDATA(pos).Description = temp$

			`temp$ = Sqlite Record Current Row String$( 1, 3 ) // crash
			`ITEMDATA(pos).Icon = Sqlite Record Current Row String$( 1, 3 ) // crash

			ITEMDATA(pos).StackAble = Sqlite Record Current Row Integer( 1, 4 )
			ITEMDATA(pos).StackMax = Sqlite Record Current Row Integer( 1, 5 )
			itemdata_type$ = Sqlite Record Current Row String$( 1, 6 )
			ITEMDATA(pos).reqLevel = Sqlite Record Current Row Integer( 1, 7 )
			itemdata_rarity$ = Sqlite Record Current Row String$( 1, 8 )
			ITEMDATA(pos).DestroySelf = Sqlite Record Current Row Integer( 1, 9 )
			ITEMDATA(pos).Cooldown = Sqlite Record Current Row Integer( 1, 10 )
			ITEMDATA(pos).ApplyDebuff = Sqlite Record Current Row Integer( 1, 11 )
			ITEMDATA(pos).DebuffLink = Sqlite Record Current Row Integer( 1, 12 )
			
			n = Sqlite NExt Record Row( 1 )
			
			addListViewItem table, "", 0
			setListViewItemText table, item, 0, ITEMDATA(pos).Name
			setListViewItemText table, item, 1, itemdata_type$
			Inc item, 1
		Next index
	n = Sqlite Finish Sql Query( 1 )

Set Window On
Sync On
time = Timer()
time = Timer()

Rem LW - Open (or create) "test" database
result = SQLITE Open Database( 1, "test.s3db", "", "" )

Rem LW - Purge database
If SQLITE Table Exist( 1, "mytable" ) Then result = SQLITE Drop Table( 1, "mytable" )
Print "start : ", (timer()-time), "ms" : time = timer()


Rem LW - Make new table
PRint "create table : "; : sync
    column$ = "[ID] INTEGER PRIMARY KEY ASC, "
    column$ = column$ + "[A] TEXT, "
    column$ = column$ + "[B] TEXT, "
    column$ = column$ + "[C] TEXT, "
    column$ = column$ + "[D] INTEGER, "
    column$ = column$ + "[E] INTEGER, "
    column$ = column$ + "[F] INTEGER, "
    column$ = column$ + "[G] TEXT, "
    column$ = column$ + "[H] INTEGER, "
    column$ = column$ + "[I] INTEGER, "
    column$ = column$ + "[J] INTEGER, "
    column$ = column$ + "[K] TEXT, "
    column$ = column$ + "[L] INTEGER, "
    column$ = column$ + "[M] TEXT, "
    column$ = column$ + "[N] TEXT, "
    column$ = column$ + "[O] INTEGER, "
    column$ = column$ + "[P] INTEGER, "
    column$ = column$ + "[Q] INTEGER"
    result = SQLITE Create Table( 1, "mytable", column$ )
Print (timer()-time), "ms" : time = timer()


Rem LW - Add 20 rows using transaction
Print "fill table(trans.): "; : sync
    result = sqlite begin transaction()
    For x = 1 To 20
        columns$ = "A, B"
        values$ = "'abcas aoskds','abcas aoskds'"
        sql$ = "INSERT INTO mytable ("+columns$+") VALUES ("+values$+")"
        result = Sqlite Add To Transaction( sql$ )
    next x
    result = Sqlite Commit Transaction( 1 )
Print (timer()-time), "ms" : time = timer()


Rem LW - Retrieve using recordset
Print "retrieve(record) : "; : sync
    sql$ = "SELECT * FROM mytable;"
    n = Sqlite Begin Sql Query( 1, sql$ )
    rows = Sqlite Record Row Count( 1 )
    For row = 1 To rows
        id= Sqlite Record Current Row Integer( 1, 0 )
        a$= Sqlite Record Current Row String$( 1, 1 )
        b$= Sqlite Record Current Row String$( 1, 2 )
        c$= Sqlite Record Current Row String$( 1, 3 )
        n = Sqlite Next Record Row( 1 )
    Next row
    n = Sqlite Finish Sql Query(1)
Print (timer()-time), "ms"
time = timer()


Rem LW - Finish
Print
Print "done"
Sync
wait key
n = Sqlite Close Database( 1 )
END

Set Window On
Set Display Mode 640, 480, 32
Set Window Size 640, 480
Set Window Position (Desktop Width()/2)-320, (Desktop Height()/2)-240
Sync On

Print "Initialize.." : Sync
    GoSub INIT
Print " OK" : Sync


`call_RECORDSET() // crash
`call_ARRAY() // not crash
`call_COMMAND() // crash



Print : Print "done" : Sync
WAIT KEY
    null = Sqlite Close Database( 1 )
END


INIT:
    null = SQLITE Open Database( 1, "test.s3db", "", "" )
    If SQLITE Table Exist( 1, "mytable" ) Then null = SQLITE Drop Table( 1, "mytable" )

    column$ = "[ID] INTEGER PRIMARY KEY ASC, "
    column$ = column$ + "[A] TEXT, "
    column$ = column$ + "[B] TEXT, "
    column$ = column$ + "[C] TEXT, "
    column$ = column$ + "[D] INTEGER"
    null = SQLITE Create Table( 1, "mytable", column$ )

    null = sqlite begin transaction()
    For x = 1 To 20
        columns$ = "A, B"
        values$ = "'abcas aoskds','abcas aoskds'"
        sql$ = "INSERT INTO mytable ("+columns$+") VALUES ("+values$+")"
        null = Sqlite Add To Transaction( sql$ )
    next x
    null = Sqlite Commit Transaction( 1 )
RETURN

Function call_RECORDSET()
    Print "RECORDSET"
        n = Sqlite Begin Sql Query( 1, "SELECT * FROM mytable;" )
            rows = Sqlite Record Row Count( 1 )
            For row = 1 To rows
                Print " index:",row,"[",rows,"]"
                
                print "  [0]"; : sync 
                    id= Sqlite Record Current Row Integer( 1, 0 )
                print " : ok"
                
                print "  [1]"; : sync
                    a$= Sqlite Record Current Row String$( 1, 1 )
                print " : ok"
                
                print "  [2]"; : sync
                    b$= Sqlite Record Current Row String$( 1, 2 )
                print " : ok"
                
                print "  [3]"; : sync
                    c$= Sqlite Record Current Row String$( 1, 3 )
                print " : ok"
                
                n = Sqlite Next Record Row( 1 )
            Next row
        n = Sqlite Finish Sql Query(1)
    Print " OK"
EndFunction

Function call_ARRAY()
    Print "ARRAY"
        n = Sqlite Make Array From Table( 1, "mytable" )
            rows = Sqlite Array Row Count()
            For row = 1 To rows
                Print " index:",row,"[",rows,"]"
                
                print "  [0]"; : sync 
                    id= Sqlite Array Get Integer From Cell( 0, row )
                print " : ok"
                
                print "  [1]"; : sync
                    a$= Sqlite Array Get String From Cell$( 1, row )
                print " : ok"
                
                print "  [2]"; : sync
                    b$= Sqlite Array Get String From Cell$( 2, row )
                print " : ok"
                
                print "  [3]"; : sync
                    c$= Sqlite Array Get String From Cell$( 3, row )
                print " : ok"
            Next row 
        Sqlite Delete Sqlite Array
    Print " OK"
EndFunction

Function call_COMMAND()
    Print "COMMAND"
        rows = Sqlite Table Row Count( 1, "mytable" )
        For row = 1 To rows
            Print " index:",row,"[",rows,"]"
            
            print "  [0]"; : sync 
                id= Sqlite Table Cell Integer( 1, "mytable", 0, row )
            Print " : ok"
            
            print "  [1]"; : sync
                a$= Sqlite Table Cell String$( 1, "mytable", 1, row )
            Print " : ok"
            
            print "  [2]"; : sync
                b$= Sqlite Table Cell String$( 1, "mytable", 2, row )
            Print " : ok"
            
            print "  [3]"; : sync
                c$= Sqlite Table Cell String$( 1, "mytable", 3, row )
            Print " : ok"
        Next row
    Print " OK"
EndFunction

Sync On
Set Window On
Set Window Size 640, 480
Set Display Mode 640, 480, 32
Set Window Position Desktop Width()/2-320, Desktop Height()/2-320
Sync

#CONSTANT DB_ID   1
#CONSTANT DB_NAME "test.s3db"
#CONSTANT DB_USER ""
#CONSTANT DB_PASS ""

GLOBAL sql_rStr As String
GLOBAL sql_rInt As Integer

Print "loading"
    sqlOpenDatabase()
    sqlClearDatabase()
    sqlRecordQuery( "SELECT * FROM mytable ORDER BY ID ASC" )
        Text 175, 0, "OUTPUT"
        For index = 1 To sql_rInt
                MyVariable = sqlRecordInteger( 0 )
                add$ = ""
                If myVariable = 0 Then add$ = " <-- why is this here?"
                Text 175, 15+((index-1)*15), STR$(MyVariable)+add$
                Sync
                sqlNextRecord()
        Next index
    sqlRecordQueryEnd()
Print
Print "done" : SYNC

Wait Key
sqlCloseDatabase()
END

Function sqlOpenDatabase() : n = Sqlite Open Database( DB_ID, DB_NAME, DB_USER, DB_PASS )
EndFunction n

Function sqlCloseDatabase() : n = Sqlite Close Database( DB_ID )
EndFunction n

Function sqlRecordQuery( sql$ As String ) : n = Sqlite Begin Sql Query( DB_ID, sql$ ) : sql_rInt = Sqlite Record Row Count( DB_ID )
EndFunction

Function sqlRecordInteger(cell As Integer) : n = Sqlite Record Row Integer( DB_ID, cell )
EndFunction n

Function sqlNextRecord() : n = Sqlite Next Record Row( DB_ID )
EndFunction

Function sqlRecordQueryEnd() : n = Sqlite Finish Sql Query( DB_ID )
EndFunction

Function sqlClearDatabase()
    If Sqlite Table Exist( DB_ID, "mytable" ) Then n = Sqlite Drop Table( DB_ID, "mytable" )
    n = Sqlite Create Table( 1, "mytable", "ID INTEGER, ABC TEXT" )
    n = Sqlite Begin Transaction()
        For index = 1 To 20
                n = Sqlite Add To Transaction( "INSERT INTO mytable (ID, ABC) VALUES ("+str$(index)+", 'MyTextHere')" )
                add$ = ""
                If (index < 10) Then add$=" "
                Print "ADDING ",add$,"[",index,"] as ID  |" : SYNC
        Next index
    n = Sqlite Commit Transaction( DB_ID )
EndFunction

Rem Project: SQLITE v09g tester
Rem Created: Sunday, March 16, 2014

Rem ***** Main Source File *****
Sync On
Set Window On
Set Window Size 640, 480
Set Display Mode 640, 480, 32
Set Window Position Desktop Width()/2-320, Desktop Height()/2-320
Sync

nulla as integer
nulla = 0

#CONSTANT DB_ID   1
#CONSTANT DB_NAME "test.s3db"
#CONSTANT DB_USER ""
#CONSTANT DB_PASS ""

GLOBAL sql_rStr As String
GLOBAL sql_rInt As Integer

Print "loading"
    sqlOpenDatabase()
    sqlClearDatabase()
    sqlRecordQuery( "SELECT * FROM mytable ORDER BY ID ASC" )
        Text 175, 0, "OUTPUT"
        nulla = SQLITE FIRST RECORD ROW(DB_ID)
        For index = 1 To sql_rInt
                MyVariable = sqlRecordInteger( 0 )
                a$ = SQLITE RECORD ROW STRING$(DB_ID,1)
                add$ = ""
                If myVariable = 0 Then add$ = " <-- why is this here?"
                Text 175, 15+((index-1)*15), STR$(MyVariable)+add$ + "  " + a$
                Sync
                sqlNextRecord()
        Next index
    sqlRecordQueryEnd()
Print
Print "done" : SYNC

Wait Key
sqlCloseDatabase()
END

Function sqlOpenDatabase() : n = Sqlite Open Database( DB_ID, DB_NAME, DB_USER, DB_PASS )
EndFunction n

Function sqlCloseDatabase() : n = Sqlite Close Database( DB_ID )
EndFunction n

Function sqlRecordQuery( sql$ As String ) : n = Sqlite Begin Sql Query( DB_ID, sql$ ) : sql_rInt = Sqlite Record Row Count( DB_ID )
EndFunction

Function sqlRecordInteger(cell As Integer) : n = Sqlite Record Row Integer( DB_ID, cell )
EndFunction n

Function sqlNextRecord() : n = Sqlite Next Record Row( DB_ID )
EndFunction

Function sqlRecordQueryEnd() : n = Sqlite Finish Sql Query( DB_ID )
EndFunction

Function sqlClearDatabase()
    If Sqlite Table Exist( DB_ID, "mytable" ) Then n = Sqlite Drop Table( DB_ID, "mytable" )
    n = Sqlite Create Table( 1, "mytable", "ID INTEGER, ABC TEXT" )
    n = Sqlite Begin Transaction()
        For index = 1 To 20
        		a$ = "This should be with row " + str$(index)
        		a$ = "'" + a$ + "'"
                n = Sqlite Add To Transaction( "INSERT INTO mytable (ID, ABC) VALUES ("+str$(index)+", " + a$ + ")" )
                add$ = ""
                If (index < 10) Then add$=" "
                Print "ADDING ",add$,"[",index,"] as ID  |" : SYNC
        Next index
    n = Sqlite Commit Transaction( DB_ID )
EndFunction

Sync On
Set Window On
Set Window Size 640, 480
Set Display Mode 640, 480, 32
Set Window Position Desktop Width()/2-320, Desktop Height()/2-320
Sync

#CONSTANT DB_ID   1
#CONSTANT DB_NAME "test.s3db"
#CONSTANT DB_USER ""
#CONSTANT DB_PASS ""

n = Sqlite Open Database( DB_ID, DB_NAME, DB_USER, DB_PASS )

If Sqlite Table Exist( DB_ID, "mytable" ) Then n = Sqlite Drop Table( DB_ID, "mytable" )
n = Sqlite Create Table( 1, "mytable", "ID INTEGER, ABC TEXT" )
n = Sqlite Begin Transaction()
    For index = 1 To 20
            t$ = "myTextValue" + str$(index)
            n = Sqlite Add To Transaction( "INSERT INTO mytable (ID, ABC) VALUES ("+str$(index)+", '"+t$+"')" )
    Next index
n = Sqlite Commit Transaction( DB_ID )

sql$ = "SELECT * FROM mytable ORDER BY ID DESC"

n = Sqlite Begin Sql Query( DB_ID, sql$ )

rows = Sqlite Record Row Count( DB_ID )

ty = 0
for r = 0 to rows
    n = sqlite goto record row(DB_ID, r)
    
    thisId = sqlite record row integer(DB_ID, 0)
    thisABC$ = sqlite record row string$(DB_ID, 1)
    
    text 0,ty, "Row: "+str$(r)+" of "+str$(rows)+" ID: "+str$(thisID)+" ABC: "+thisABC$
    inc ty, 20
    
next r

n = sqlite finish sql query(DB_ID)

sync
Wait Key
n = sqlite close database(DB_ID)
END

Rem Project: SQLITE v09h tester
Rem Created: Monday, March 17, 2014

Rem ***** Main Source File *****
Sync On
Set Window On
Set Window Size 640, 480
Set Display Mode 640, 480, 32
Set Window Position Desktop Width()/2-320, Desktop Height()/2-320
Sync

#CONSTANT DB_ID   1
#CONSTANT DB_NAME "test.s3db"
#CONSTANT DB_USER ""
#CONSTANT DB_PASS ""

n = Sqlite Open Database( DB_ID, DB_NAME, DB_USER, DB_PASS )

If Sqlite Table Exist( DB_ID, "mytable" ) Then n = Sqlite Drop Table( DB_ID, "mytable" )
n = Sqlite Create Table( 1, "mytable", "ID INTEGER, ABC TEXT" )
n = Sqlite Begin Transaction()
    For index = 1 To 20
            t$ = "myTextValue" + str$(index)
            n = Sqlite Add To Transaction( "INSERT INTO mytable (ID, ABC) VALUES ("+str$(index)+", '"+t$+"')" )
    Next index
n = Sqlite Commit Transaction( DB_ID )

sql$ = "SELECT * FROM mytable ORDER BY ID DESC"

n = Sqlite Begin Sql Query( DB_ID, sql$ )

rows = Sqlite Record Row Count( DB_ID )

ty = 0
for r = 1 to rows
    n = sqlite goto record row(DB_ID, r)
    
    thisId = sqlite record row integer(DB_ID, 0)
    thisABC$ = sqlite record row string$(DB_ID, 1)
    
    text 0,ty, "Row: "+str$(r)+" of "+str$(rows)+" ID: "+str$(thisID)+" ABC: "+thisABC$
    inc ty, 20
    
next r

n = sqlite finish sql query(DB_ID)

sync
Wait Key
n = sqlite close database(DB_ID)
END

Rem Project: SQLITE v09g tester
Rem Created: Sunday, March 16, 2014

Rem ***** Main Source File *****
Sync On
Set Window On
Set Window Size 640, 480
Set Display Mode 640, 480, 32
Set Window Position Desktop Width()/2-320, Desktop Height()/2-320
Sync

nulla as integer
nulla = 0

#CONSTANT DB_ID   1
#CONSTANT DB_NAME "test.s3db"
#CONSTANT DB_USER ""
#CONSTANT DB_PASS ""

GLOBAL sql_rStr As String
GLOBAL sql_rInt As Integer

Print "loading"
    sqlOpenDatabase()
    sqlClearDatabase()
    sqlRecordQuery( "SELECT * FROM mytable ORDER BY ID ASC" )
        Text 175, 0, "OUTPUT"
        nulla = SQLITE FIRST RECORD ROW(DB_ID)
        For index = 1 To sql_rInt
                MyVariable = sqlRecordInteger( 0 )
                a$ = SQLITE RECORD ROW STRING$(DB_ID,1)
                add$ = ""
                If myVariable = 0 Then add$ = " <-- why is this here?"
                Text 175, 15+((index-1)*15), STR$(MyVariable)+add$ + "  " + a$
                Sync
                sqlNextRecord()
        Next index
    sqlRecordQueryEnd()
Print
Print "done" : SYNC

Wait Key
sqlCloseDatabase()
END

Function sqlOpenDatabase() : n = Sqlite Open Database( DB_ID, DB_NAME, DB_USER, DB_PASS )
EndFunction n

Function sqlCloseDatabase() : n = Sqlite Close Database( DB_ID )
EndFunction n

Function sqlRecordQuery( sql$ As String ) : n = Sqlite Begin Sql Query( DB_ID, sql$ ) : sql_rInt = Sqlite Record Row Count( DB_ID )
EndFunction

Function sqlRecordInteger(cell As Integer) : n = Sqlite Record Row Integer( DB_ID, cell )
EndFunction n

Function sqlNextRecord() : n = Sqlite Next Record Row( DB_ID )
EndFunction

Function sqlRecordQueryEnd() : n = Sqlite Finish Sql Query( DB_ID )
EndFunction

Function sqlClearDatabase()
    If Sqlite Table Exist( DB_ID, "mytable" ) Then n = Sqlite Drop Table( DB_ID, "mytable" )
    n = Sqlite Create Table( 1, "mytable", "ID INTEGER, ABC TEXT" )
    n = Sqlite Begin Transaction()
        For index = 1 To 20
        		a$ = "This should be with row " + str$(index)
        		a$ = "'" + a$ + "'"
                n = Sqlite Add To Transaction( "INSERT INTO mytable (ID, ABC) VALUES ("+str$(index)+", " + a$ + ")" )
                add$ = ""
                If (index < 10) Then add$=" "
                Print "ADDING ",add$,"[",index,"] as ID  |" : SYNC
        Next index
    n = Sqlite Commit Transaction( DB_ID )
EndFunction