SQLITE Functions


#1

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

#2

Hey Wajdy,

SQLITE is kind of tricky. Why dont you register the gtotal as a global variable?

Best Regards,
Adrian


#3

Hi Adrian
It tried to Dim it outside Sub/Fun and still gives me undefined !


#4

Ok… I know the code you put is the example in the documentation, but I think there are several things to correct.

  1. 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.

  2. The create table sintaxis will work, however you are not defining the datatype of each field, therefore all are created as text.

  3. On the select you are putting two routines: sqlExist and skipError. This last one does not exists, and it must exist.

  4. The gtotal variable must be defined as global.

  5. Your are not doing any insert in the table therefore the value will be always 0 or null

  6. 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

Let me know if you have any question.

Best Regards,
Adrian.


#5

Thanks Adrian
gtotal is still undefined !

Wajdy


#6

This comment by mexium is very important:


#7

Thank you guys

As you said: using Form1_onshow() saved me a lot of issues.
Also, since the data is small, i decided to use internal storage as an array

Waj