Hi,
This is frustrating and I feel it is really simple:
I have main subroutine calling functions which run SQLITE queries.
Each query has two function: One for success and another for error
Success function should return value like (Max Row Number), which should be available at the main subroutine or other functions.
gtotal: how to return its value to main Sub ?
Sub main
DB =SqlOpenDatabase("MyData.db","1.0","Score Database")
sqlList=[]
sqlList[0]=["CREATE TABLE IF NOT EXISTS Games('gn', 'them', 'us');"]
Sql(DB, sqlList)
CheckTable()
end sub
Function CheckTable()
sqlList=[]
sqlList[0]=["SELECT * FROM Games",sqlExist,skipError]
Sql(DB, sqlList)
End Function
Function sqlExist(transaction,results)
DBRecords = results
gtotal=DBRecords.rows.length
End Function
Ok… I know the code you put is the example in the documentation, but I think there are several things to correct.
You are mixing java sintaxis and Basic Sintaxis. This can be done, but you need to put special sintaxis whenever you are reffering to java sintaxis.
The create table sintaxis will work, however you are not defining the datatype of each field, therefore all are created as text.
On the select you are putting two routines: sqlExist and skipError. This last one does not exists, and it must exist.
The gtotal variable must be defined as global.
Your are not doing any insert in the table therefore the value will be always 0 or null
all of the above it is important, however… your real problem is because SQLITE is asynchronous. This means that NSBasic will execute the sql, but it will not wait for the response, but it will execute the next statement. To show this I made an example where this will be displayed.
Dim gtotal
Function Form1_onshow()
DB =SqlOpenDatabase("MyData.db","1.0","Score Database")
sqlList=[]
sqlList[0]=["CREATE TABLE IF NOT EXISTS Games('gn', 'them', 'us');"]
Sql(DB, sqlList)
CheckTable()
Print "2"
MsgBox CDbl(gtotal)
End Function
Function CheckTable()
sqlList=[]
sqlList[0]=["Insert into Games values ('Horror','me','then');"]
sqlList[1]=["SELECT * FROM Games",sqlExist,skipError]
Sql(DB, sqlList)
End Function
Function sqlExist(transaction,results)
DBRecords = results
Print "1"
gtotal=DBRecords.rows.length
TheEnd
End Function
Function skipError()
Print "Error"
End Function