SQLite vs RealSQL

I have switch my program to SQLite from RealSQL.

As I was adding some new features I got a Database error, which was an Error code 1, syntax error. The error was in code after edit/update a record with an single quote in a field which is the key. I do change the single quote to two single quotes in the SQL statement.

Had no problem adding or reading the record, just updating.

In another table, which has been in the program a while, the same error was encountered when trying to update a record with a single quote.

Just to verify that this was SQLite only and NOT RealSQL I changed the program to use RealSQL and it works as expected… NO error.

Now what do I do? Stay with RealSQL or is there some what to fix this?

You can no longer do an update using rs.edit unless you include the primary key (which every self-respecting table should have) in the recordset. You can still use a SQL Update statement but if you’re updating via a recordset you need the primary key.

Just to understand, you’re using a single quote in a field name, like kem's_field?

Correct. Like " Jim’s Place "

I’m updating all the fields in the record, which include the key. However I don’t understand.

What the difference and where can I look at an example.

Dale has probably hit on it. RealSQL, although also SQLite, implicitly included rowid in its results. The SQLite implementation does not, even if you use “SELECT *” in your statement, so you have to include either rowid in your results, or your own primary key. (The latter would be included in “SELECT *”, so I’m guessing you didn’t define a separate primary key.)

BTW, I didn’t even know that a single-quote was allowed in a field name.

James, try setting a breakpoint and looking at the actual sql being sent to the database. If you can’t see what may be the syntax error, post the string here and we’ll look at it.

Here is my code to update

[code]
rs = gDB.SQLSelect(“SELECT * FROM CGBuz WHERE cg_item = '” + EscapeSQLData(key) + “’”)
if gDB.error then
displayDatabaseError (false,CurrentMethodName)
return
end

if rs = nil or rs.eof = true then
  MsgBox "Error updating CGBuz record=" + CurrentMethodName
  Return
end if

rs.edit
if gDB.error then
  displayDatabaseError (true,CurrentMethodName)
  return
end

// Update the data on the record
rs.field("campground").StringValue = windowcamp.ccCGMain1.tfCGName.Text.trim
rs.field("item").StringValue = item
rs.field("cg_item").StringValue = key
rs.field("tag").StringValue = tag
rs.field("note").StringValue = note
rs.field("rate").StringValue = rate

dim d as new date
rs.Field("mod_time").StringValue = d.SQLDatetime    

// Update the record in the database
rs.Update

if gDB.error then
  displayDatabaseError (true,CurrentMethodName)
  return
end
// Commit changes to the database
gDB.Commit[/code]

Dale, I don’t know how to look to find the update SQL command.

None of those fields has a single-quote in the name.

Do you have a primary key field defined in that table?

[quote=66859:@Kem Tekinay]
BTW, I didn’t even know that a single-quote was allowed in a field name.[/quote]
I think that its the value in the column not the name of the column itself

Oh, that would make more sense. Although you can use a single quote in a field name with square brackets around the name, it doesn’t play well with Xojo’s database methods, and it’s a bad idea anyway.

Horrid
Same as spaces in column names which I think Access used to let you do

FileMaker too will let you use anything in a field name, even if it might complain about some characters like “+” or “-”. Single-quotes and spaces don’t trouble it, nor do they cause any issues within the app.

James, here is the first thing I would do. I would declare a string variable and move the SQL to it and use that string variable in the SQLSELECT statement.

Dim tempString AS String tempString = "SELECT * FROM CGBuz WHERE cg_item = '" + EscapeSQLData(key) + "'" rs = gDB.SQLSelect(tempString)
Then I would set a breakpoint on the rs=… line and when the breakpoint gets hit, the debugger will display the values of the variables, including the tempString. You can then see what is actually being sent to the database engine.

My guess is that there is a value being retrieved that has a single quote in it, thus causing the syntax error in the update. You will probably wind up having to do something like this

rs.field("cg_item").StringValue = EscapeSQLData(key)

on one or more fields.

I just tested and single quotes do not present a problem as a value during a SQLSelect or rs.Update. Here is my test code:

  dim db as new SQLIteDatabase
  db.DatabaseName = "test"
  if not db.Connect then
    AddToResult "Couldn't open db."
    return
  end if
  
  dim sql as string
  
  sql = "CREATE TABLE tester( tests TEXT )"
  db.SQLExecute sql
  if db.Error then
    AddToResult "Whoopsie: " + db.ErrorMessage
    return
  end if
  
  dim rec as new DatabaseRecord
  rec.Column( "tests" ) = "this's"
  
  db.InsertRecord( "tester", rec )
  if db.Error then
    AddToResult "Couldn't insert: " + db.ErrorMessage
    return
  end if
  
  dim rs as RecordSet
  
  sql = "SELECT rowid, tests FROM tester"
  rs = db.SQLSelect( sql )
  if rs is nil then
    AddToResult "Result is nil."
    return
  end if
  rs.Edit
  rs.Field( "tests" ).StringValue = "that's"
  rs.Update
  if db.Error then
    AddToResult db.ErrorMessage
    return
  end if
  
  rs = db.SQLSelect( sql )
  while not rs.EOF
    AddToResult rs.Field( "tests" ).StringValue
    rs.MoveNext
  wend
  return

To repeat what others have said: The old RealSQLdatabase class implicitly added “RowID” to your selection if you did a “select *” and your table did not include an AUTOINCREMENT PRIMARY KEY field. Your actual selection was “select RowID, *”. However, as useful and user-friendly as that was, it did introduce some subtle issues. That was corrected in the new SqliteDatabase, with the caveat that you must explicitly select RowID if you don’t have an AUTOINCREMENT PRIMARY KEY field in your table and you want to use Recordset.Update.

We’re assuming that while you mentioned that the table has a primary key, it doesn’t have an AUTOINCREMENT primary key. If that’s the case, you must select “RowID, " instead of just "”.

I do have a primary key, here is code to create table:

gDB.SQLExecute "create table CGBuz (cg_item varchar unique NOT NULL PRIMARY KEY,"+ _ "version varchar,"+ _ "campground varchar,"+ _ "item varchar,"+ _ "tag varchar,"+ _ "note varchar,"+ _ "rate integer,"+ _ "mod_time varchar)"

The record which is giving the error has a key of: “BarnyardO’Charleys, Lexington, SC”, campground and item make up the key.
So the problem is other than Primary key, unless I am using it wrong.

I have also added RowID to the SELECT statement, it made no difference. Do I need to do something to RowID?

Confirmed: If the primary key has a single quote in its value, rs.Update will not work. You are going to have to use a SQL UPDATE instead as a PreparedStatement, or stop using that field as a primary key and use an alias for rowid as the primary key instead.

James, I just noticed your EscapeSQLData method call. This is not recommended as you should be using a PreparedStatement instead. Do you need an example?

Yes I do. Have not use any, yet.