Sqlite and strings in arrays - 2014 R3 different

Databases
1

There is a problem in my code that happens only with 2014 r3; it was fine with r2.1 and earlier. I suspect it has something to do with the new TEXT data type, but I am not familiar yet with the changes that were made in Xojo to understand my issue.

My example below creates an Sqlite database on the desktop and populates a two-field table with text. The likely problem area is where I use an array of strings to populate the second field.

In r2.1 I get the expected msgboxes with the three strings. In r3 I only see the first letter of each. When I examine the database in SQLite Manager (Firefox plugin), I see the following:

  Dim db As SQLiteDatabase = New SQLiteDatabase
  Dim f As FolderItem = SpecialFolder.Desktop.Child("db.sqlite")
  If f.Exists Then f.Delete
  db.DatabaseFile = f
  If db.CreateDatabaseFile = True Then  // created or connected
    db.SQLExecute("CREATE TABLE test(a, b);")
    Dim ps As SQLitePreparedStatement = db.Prepare("INSERT INTO test(a, b) VALUES(?, ?);")
    ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
    For Each s As String In Array("first", "second", "third")
      ps.SQLExecute("hello", s)
    Next s 
  End If
  Dim rs As RecordSet = db.SQLSelect("SELECT * FROM test;")
  Do Until rs.EOF
    MsgBox(rs.Field("b").StringValue)
    rs.MoveNext
  Loop

What am I doing wrong?

2

Runs without problem with 2014 r3 (on OS X).

3

Sorry, should have said that I’m on Windows 7 64bit. Thanks for checking on OSX.

4

Does this change the behavior?

  Dim db As SQLiteDatabase = New SQLiteDatabase
  Dim f As FolderItem = SpecialFolder.Desktop.Child("db.sqlite")
  If f.Exists Then f.Delete
  db.DatabaseFile = f
  If db.CreateDatabaseFile = True Then  // created or connected
    db.SQLExecute("CREATE TABLE test(a, b);")
    Dim ps As SQLitePreparedStatement = db.Prepare("INSERT INTO test(a, b) VALUES(?, ?);")
    ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
    For Each t As Text In Array("first", "second", "third")
      ps.SQLExecute("hello", t)
    Next
  End If
  Dim rs As RecordSet = db.SQLSelect("SELECT * FROM test;")
  Do Until rs.EOF
    MsgBox(rs.Field("b").StringValue)
    rs.MoveNext
  Loop
5

I can verify this on Win 7 x64.

Populating the array separately seems to work around the issue:

Dim myArray() As String = Array("first", "second", "third") For Each s As String in myArray ...

<https://xojo.com/issue/37224>

6

Wow, I go to sleep, wake up, and there’s a solution, a feedback case, and it’s verified and fixed (for the next release, obviously). Am I still dreaming…? :slight_smile:

You guys rule. Thank you.

7

[quote=150457:@Rick Araujo]Does this change the behavior?

Dim db As SQLiteDatabase = New SQLiteDatabase Dim f As FolderItem = SpecialFolder.Desktop.Child("db.sqlite") If f.Exists Then f.Delete db.DatabaseFile = f If db.CreateDatabaseFile = True Then // created or connected db.SQLExecute("CREATE TABLE test(a, b);") Dim ps As SQLitePreparedStatement = db.Prepare("INSERT INTO test(a, b) VALUES(?, ?);") ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT) For Each t As Text In Array("first", "second", "third") ps.SQLExecute("hello", t) Next End If Dim rs As RecordSet = db.SQLSelect("SELECT * FROM test;") Do Until rs.EOF MsgBox(rs.Field("b").StringValue) rs.MoveNext Loop [/quote]

Thanks, Rick. The Xojo team have found the issues in their Feedback case: <https://xojo.com/issue/37224>