Help with Variables and SQL Syntax

[jim.richards]

Hi All,

I need some guidance on the proper syntax to use in SQL statements when using variables.

This is what I am trying, which fails:

SQLString$ = "'"+fname$+"','"+lname$+"'"
Result = SQLiteExec("Insert Into contacts(fname, lname) Values("+SQLString$+");")

Thanks
Jim

[dmoc]

Jim, try single quotes around text vars. Edit: sorry, looks like you do already, stupid me. Next suggestion: check vars do not include single quotes.

[El_Choni]

You've found a strange bug, i'll fix it for next version (yes, Karbon, I'm on it . Put the SQL string in a single string variable as a workaround:

Code: Select all

SQLString$ = "'"+fname$+"','"+lname$+"'"
SQL$ = "INSERT INTO contacts VALUES("+SQLString$+")"
Result = SQLiteExec(SQL$)

Test code:

Code: Select all

#SQ_OK = 0
If InitSQLite()
  If SQLiteOpen("test.db")
    Result = SQLiteExec("CREATE TABLE contacts (fname, lname)") 
    If Result<>#SQ_OK:Debug SQLiteError(Result):EndIf
    fname$ = "uno"
    lname$ = "dos"
    SQLString$ = "'"+fname$+"', '"+lname$+"'"
    SQL$ = "INSERT INTO contacts VALUES("+SQLString$+")"
    Result = SQLiteExec(SQL$)
    If Result<>#SQ_OK:Debug SQLiteError(Result):EndIf
    Result = SQLiteGetTable("SELECT fname, lname FROM contacts")
    If Result<>#SQ_OK:Debug SQLiteError(Result):EndIf
    Debug SQLiteData(0, 0)
    Debug SQLiteData(0, 1)
    Debug SQLiteData(1, 0)
    Debug SQLiteData(1, 1)
    SQLiteClose()
    DeleteFile("test.db")
  EndIf
EndIf

[jim.richards]

Thanks Guys,

Works fine putting entire statement into a variable. I thought I was really losing it for a while there.

Jim