SQLite Query Failing Strangely

Databases
1

In the following code:
queryString is an array of valid SQLite select queries
rsQuery is an array of recordsets

The principle is loop through the queries (queryString(n)) and generate a recordset (rsQuery(n)) for each one.

Database connection etc is active. All the basics are done. Problem is I am testing with just one query “Select * from Products” and it is raising an error without fail every time regardless of the query. near “S”: syntax error . Bizarre can anyone help I am pulling my hair out

[code] for N as integer = 0 to UBound(queryString)

  if trim(queryString(n)) <> "" then

    ReDim rsQuery(n)

    rsQuery(N) = mDatabase.ActiveDatabase.SQLSelect(trim(queryString(N)))
    
    if mDatabase.ActiveDatabase.Error then
      MsgBox mDatabase.ActiveDatabase.ErrorMessage
      Exit Sub
    end if
    
    ListRecordset(rsQuery(0),lvwQuery)
    
  end if
next N[/code]
2

Check if your database is configured to be SQL case-sensitive or not. Which Database are you using ?

3

What is the actual text of queryString(n) ?
If your error is coming from the database then the error is in the query string itself.

4

That’s why I’m confused. It’s a simple

Select * from orders

And orders does exist.

5

Doesn’t get much simpler than that. Have you tried it in a SQL query tool outside of Xojo?
Copy and paste the query from the debugger.

6

Again… Check if your database is configured to be SQL case-sensitive.

If you don’t know how to do that, at least try again your Query with uppercases:

SELECT * FROM orders
7

[quote=80947:@Peter Fargo]Doesn’t get much simpler than that. Have you tried it in a SQL query tool outside of Xojo?
Copy and paste the query from the debugger.[/quote]
Yes absolutely no problem getting rs back

8

[quote=80948:@Guy Rabiller]Again… Check if your database is configured to be SQL case-sensitive.

If you don’t know how to do that, at least try again your Query with uppercases:

SELECT * FROM orders

No its not case sensitive

9

The odd thing is the error message above. I would expect it to say near “Select”: syntax error. Its almost as if the query is not being sent correctly

10

Replace

rsQuery(N) = mDatabase.ActiveDatabase.SQLSelect(trim(queryString(N)))

with

Dim selectString As String = trim(queryString(N)) Break // check that selectString is correct by copying it from the debugger's variable text field and pasting // into Navicat, SequelPro, or whatever tool you use to manage your databases.

And don’t forget to check the returned RecordSet for being Nil (directly after SQLSelect line).

11

Best guess: The query isn’t what you think it is. Add this to the error handler code:

        if mDatabase.ActiveDatabase.Error then
          MsgBox mDatabase.ActiveDatabase.ErrorMessage
          System.DebugLog queryString( N )
          System.DebugLog EncodeHex( queryString( N ), true )
          Exit Sub
        end if

Then check the log, especially the hex version. I suspect you have a hidden character in there.

Or use Eli’s approach, as he beat me to it.

BTW, are you sure you’ve properly connected to your database?

12

[quote=80960:@Kem Tekinay] if mDatabase.ActiveDatabase.Error then
MsgBox mDatabase.ActiveDatabase.ErrorMessage
System.DebugLog queryString( N )
System.DebugLog EncodeHex( queryString( N ), true )
Exit Sub
end if[/quote]

Mmm - debug log is showing query string to be “s” only. Bizarre because I have MsgBox just before that line and its showing the full query.

Hex is :
73 00 65 00 6C 00 65 00 63 00 74 00 20 00 2A 00 20 00 66 00 72 00 6F 00 6D 00 20 00 6F 00 72 00 64 00 65 00 72 00 73 00

13

Your query is encoded as UTF-16. Convert (or define) as UTF-8 and try again.

Edit: Sorry, I meant, define as UTF-16 if needed, then convert to UTF-8.

14

Yes you got wide strings.

This is odd because by default Xojo compiles as UTF-8. Where do your queries come from ?

You should try with:

rsQuery(N) = mDatabase.ActiveDatabase.SQLSelect( trim(queryString(N)).ConvertEncoding(Encodings.UTF8) )
15

[quote=80962:@Kem Tekinay]Your query is encoded as UTF-16. Convert (or define) as UTF-8 and try again.

Edit: Sorry, I meant, define as UTF-16 if needed, then convert to UTF-8.[/quote]

You are a lifesaving genius… I have scratched my head for 2 days over this. Fully working now. For my future reference how do you tell it is UTF16?

[quote=80964:@Guy Rabiller]Yes you got wide strings.

This is odd because by default Xojo compiles as UTF-8. Where do your queries come from ?

You should try with:

rsQuery(N) = mDatabase.ActiveDatabase.SQLSelect( trim(queryString(N)).ConvertEncoding(Encodings.UTF8) )

The query text is from a CustomEditField instance. It must be encoded UTF16 for some reason

16

I could tell because UTF-16 uses a minimum of 2 bytes per character, hence the NULLs between the bytes. You can tell by checking the encoding in the debugger. Eli’s recommendation of pulling the string out of the array first will help with that.

BTW, rediminsioning the array as you go is slow. Redim before the loop instead.

    redim rsQuery( queryString.Ubound )

    for N as integer = 0 to UBound(queryString)
      dim thisQuery as string = queryString( N ).ConvertEncoding( Encodings.UTF8 ).Trim
      if thisQuery <> "" then

        rsQuery(N) = mDatabase.ActiveDatabase.SQLSelect( thisQuery )
…
17

Noted, I have updated this