Sqlite database Rowset is nil

General
1

Maybe someone knows the answer.
I am using the sqlite client database and everything works great, except…

I have a query that reads:

“SELECT ID FROM CsvMapping where Lower(CsvField)=‘unit’ and divisionID=3”

When I run the query in SQLiteSudio or SQLPro for SQLite against my database the query results return the correct ID, however when I use

Dim records as RowSet = App.DB.SqlSelect(qry)

where qry is the exact content as my example the record count is zero. Meaning the record is not found.

I have multiple join type sql queries that run flawlessly however this simple query doesn’t work.

Any ideas?

2

The code you posted won’t compile. Either you meant RecordSet instead of RowSet, or you meant SelectSQL instead of SQLSelect.

In either case, if you aren’t getting an Exception with RowSet or a Nil result with RecordSet, the problem is likely elsewhere in your code. We’d have to see more.

As an aside, when posting code, please use the code tags. (You used quote tags.) That makes it easier for us to read and help you.

For example:

Dim records as RowSet = App.DB.SqlSelect(qry)

instead of

Dim records as RowSet = App.DB.SqlSelect(qry)

3

By the way having a RowSet be Nil is not the same as having record count = 0.

RowSet = Nil means you had an error in your SQL or, as in this case, you were calling the wrong method (SQLselect instead of SelectSQL). To know that your record count is zero means you must have a RowSet to examine, in which case in won’t be Nil.

4

Forgive my ignorance. How do you add a code tag to replies?

5

  1. Add your code to your post

  2. Select the code with your mouse

  3. Click the </> button.

6

Here is the actual code I use.
I do not receive NIL however the record.RowCount = 0
When I copy and past the actual value of the sql string into a SQLITE db management tool and execute that sql I get the ID value returned.

The SelectSQL should return exactly one row. That’s what baffles me

If App.DB = Nil Then Return


Try
  Dim sql As String = "SELECT ID FROM CsvMapping where LOWER(CsvField)=Lower('"+headerData(index)+"') AND DivisionID="+selectedDivision.recID.ToString
  
  Dim record As RowSet = App.DB.SelectSQL(sql)
  
  record.MoveToFirstRow
  
  If record.RowCount = 1 Then
     csvElem = New cCsvMappingElement(record.Column("ID").IntegerValue)
  End If
  record.Close
Catch e As DatabaseException
  MessageBox(e.Message)
End Try
7

Not sure what the issue is but as a matter of safety you should not put strings into an SQL query. Better write it as:

Dim sql As String = "SELECT ID FROM CsvMapping where LOWER(CsvField)=?1 and DivisionID=?2"
Dim record as RowSet = app.db.SelectSQL (sql, headerData(index).LowerCase, selectedDivision.recID)

See:

1 Like
8

I simplified the call for the example as I use sqlpreparestatements and bind the data. The example is just to simplify the problem statement.
Both solutions will return no records in Xojo which makes it a bit frustrating.

9

Can you break between those lines and check if sql is what you need?
I guess your sql is not exactly as you want, that’s why you get 0 records.

10

I’d also be inclined only to use the sqlite3 CLI tool for such comparisons, as that does the minimum to any SQL it is given and is the tool created by the SQLite authors. SQLiteSudio or SQLPro for SQLite may be doing unknown things to the SQL you give them.

2 Likes
11 
If App.DB = Nil Then Return


Try
  Dim sql As String = "SELECT ID FROM CsvMapping where LOWER(CsvField)=Lower('"+headerData(index)+"') AND DivisionID="+selectedDivision.recID.ToString
  
  Dim record As RowSet = App.DB.SelectSQL(sql)
  
  // record.MoveToFirstRow  // !!!!! Not needed

  If record.RowCount = 0 Then break // just a test, let's inspect the sql string
  
  If record.RowCount = 1 Then
     csvElem = New cCsvMappingElement(record.Column("ID").IntegerValue)
  End If
  record.Close
Catch e As DatabaseException
  MessageBox(e.Message)
End Try

Put a conditional break in the code and inspect your SQL. As Alberto said, you probably are creating something unexpected.

1 Like