SQLite and procedures

ricardo · Post by **ricardo** » Sun Jul 20, 2003 7:04 am

Im trying to update the content of some cells in a database (sqlite), but im having a hard time trying to make it work from procedures (and its indispensable for me to do it in this way).

Then i build a little example to isolate the problem, here it is:

Code: Select all

Procedure UpdateTable(Element.s,Content.s,index.s)
  
  TableString.s = "UPDATE test SET " + Element + "='" + Content + "'  WHERE id LIKE '" + index + "'"
  Result = SQLiteExec(TableString)
  Debug TableString
  Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result)
  ProcedureReturn 1
EndProcedure


If InitSQLite()
  DeleteFile("testing.db")
  DBName$ = "testing.db"
  DBHandle = SQLiteOpen(DBName$)
  If DBHandle
    ;Create Database
    Result = SQLiteExec("CREATE TABLE test (id INTEGER PRIMARY KEY unique, element1, element2, element3)")
    Debug "CREATE result = "+Str(Result)+" - "+SQLiteError(Result)
    
    Result = SQLiteExec("INSERT INTO test VALUES(null, 'a', 'b', 'c')")
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result)
    
    Result = SQLiteExec("INSERT INTO test VALUES(null, 'a', 'b', 'c')")
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result)
    
    TableString.s = "UPDATE test SET element3='new content3' WHERE id LIKE '1'"
    Result = SQLiteExec(TableString)
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result)
    
    TableString.s = "UPDATE test SET element2='new content2' WHERE id LIKE '1'"
    Result = SQLiteExec(TableString)
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result)
    
    TableString.s = "UPDATE test SET element1='new content1' WHERE id LIKE '1'"
    Result = SQLiteExec(TableString)
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result)
    
    Result = SQLiteGetTable("SELECT id, element1, element2, element3 FROM test WHERE id LIKE '%'")
    Debug ""
    Debug "Fist UPDATES:"
    Debug SQLiteField(1, "element1")
    Debug SQLiteField(1, "element2")
    Debug SQLiteField(1, "element3")
    Debug ""
    
    If UpdateTable("element1","more new content 1","1")
      If UpdateTable("element2","more new content 2","2")
        If UpdateTable("element3","more new content 3","3")
          Result = SQLiteGetTable("SELECT id, element1, element2, element3 FROM test WHERE id LIKE '%'")
          Debug ""
          Debug "Second UPDATES:"
          Debug SQLiteField(1, "element1")
          Debug SQLiteField(1, "element2")
          Debug SQLiteField(1, "element3")
        EndIf
      EndIf
    EndIf
    
  EndIf
  SQLiteClose()
EndIf
End

In the second updates (the one into procedures) just one cell get updated its content BUT THE RESULT SEAMS TO BE OK and don't know how to make it work since no error is reported.

Thanks in advance for any help

Saboteur · Post by **Saboteur** » Sun Jul 20, 2003 10:16 am

First, you update values where ID = 1

Code: Select all

 TableString.s = "UPDATE test SET element3='new content3' WHERE id LIKE '1'" 
    Result = SQLiteExec(TableString) 
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result)

but later, you try to update values with anothers IDs (1, 2 and 3)

Code: Select all

   If UpdateTable("element1","more new content 1","1") 
      If UpdateTable("element2","more new content 2","2") 
        If UpdateTable("element3","more new content 3","3")

Try this to view the values of table:

Code: Select all

Procedure UpdateTable(Element.s,Content.s,index.s) 
  
  TableString.s = "UPDATE test SET " + Element + "='" + Content + "'  WHERE id=" + index + "" 
  Result = SQLiteExec(TableString) 
  Debug TableString 
  Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result) 
  ProcedureReturn 1 
EndProcedure 

Procedure ViewTable()
  Result = SQLiteGetTable("SELECT id, element1, element2, element3 FROM test") 
  Debug "" 
  Debug "TABLE VALUES:" 
  For f=1 To SQLiteRows()
    c.s=""
    For g=0 To SQLiteCols()-1
      c.s+SQLiteData(f,g)+", "
    Next g
    Debug c
  Next f
  Debug ""
EndProcedure


If InitSQLite() 
  DeleteFile("testing.db") 
  DBName$ = "testing.db" 
  DBHandle = SQLiteOpen(DBName$) 
  If DBHandle 
    ;Create Database 
    Result = SQLiteExec("CREATE TABLE test (id INTEGER PRIMARY KEY unique, element1, element2, element3)") 
    Debug "CREATE result = "+Str(Result)+" - "+SQLiteError(Result) 
    
    Result = SQLiteExec("INSERT INTO test VALUES(null, 'a', 'b', 'c')") 
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result) 
    
    Result = SQLiteExec("INSERT INTO test VALUES(null, 'a', 'b', 'c')") 
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result) 
    
    TableString.s = "UPDATE test SET element3='new content3' WHERE id LIKE '1'" 
    Result = SQLiteExec(TableString) 
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result) 
    
    TableString.s = "UPDATE test SET element2='new content2' WHERE id LIKE '1'" 
    Result = SQLiteExec(TableString) 
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result) 
    
    TableString.s = "UPDATE test SET element1='new content1' WHERE id LIKE '1'" 
    Result = SQLiteExec(TableString) 
    Debug "INSERT result = "+Str(Result)+" - "+SQLiteError(Result) 
    
    Result = SQLiteGetTable("SELECT id, element1, element2, element3 FROM test WHERE id LIKE '%'") 
    Debug "" 
    Debug "Fist UPDATES:" 
    Debug SQLiteField(1, "element1") 
    Debug SQLiteField(1, "element2") 
    Debug SQLiteField(1, "element3") 
    Debug "" 
    
    ViewTable()
    
    If UpdateTable("element1","more new content 1","1") 
      If UpdateTable("element2","more new content 2","2") 
        If UpdateTable("element3","more new content 3","3") 
          Result = SQLiteGetTable("SELECT id, element1, element2, element3 FROM test WHERE id LIKE '%'") 
          Debug "" 
          Debug "Second UPDATES:" 
          Debug SQLiteField(1, "element1") 
          Debug SQLiteField(1, "element2") 
          Debug SQLiteField(1, "element3") 
        EndIf 
      EndIf 
    EndIf 
    
    ViewTable()
    
  EndIf 
  SQLiteClose() 
EndIf 
End

ricardo · Post by **ricardo** » Sun Jul 20, 2003 9:28 pm

Hi,

Thanks!

Tonight (4:00 a.m. here) i was very sleepy so the code has MANY stupid errors, today when i see my code i can't believe that i can't see it!!

Per example:

I was trying to see results like this:

Debug "Fist UPDATES:"
Debug SQLiteField(1, "element1")
Debug SQLiteField(1, "element2")
Debug SQLiteField(1, "element3")

When the logic way is:

Debug "Fist UPDATES:"
Debug SQLiteField(1, "element1")
Debug SQLiteField(2, "element1")
Debug SQLiteField(3, "element1")

Of course i was NEVER going to see what i want to see!!!

Other one: I create 2 rows BUT want to update 3!!

And so on

Thanks for your time and your help Saboteur