Difference between SQLiteDatabase and ODBCDatabase

Databases
1

I have XOJO 2023 R4 on macOS 14.4 .

I have two databases with an identical structure: an SQLite DB and an MSSQL DB.

When I work with the SQLite database and want to read a record that does not exist, my app does not run into the “for each…” loop.

If I am working with the MSSQL database and want to read a record that does not exist, my app runs into the “for each…” loop.

In both cases the RecordSet “dbRows” is not nil but there are only columns with nil values in dbRows.

Could this be an error in ODBCDatabase?

My Code: DB is the SQLiteDatabase and DBo ist the ODBCDatabase

try
  if DBo = nil then
    dbRows = DB.SelectSQL("select * from "+DB_Table_Kunde+" where Kundennummer = '"+iv_kunnr.ToString+"'")
  else
    dbRows = DBo.SelectSQL("select * from "+DB_Table_Kunde+" where Kundennummer = '"+iv_kunnr.ToString+"'")
  end if
  if dbRows <> nil then
    gs_py_syst.subrc = 4
    for each row as DatabaseRow in dbRows
      PufferKunde.Kdnr                     = row.Column("Kundennummer").IntegerValue
      PufferKunde.KundenID                 = row.Column("KundenID").StringValue
      PufferKunde.ErzeugtAm                = row.Column("ErzeugtAm").StringValue
      PufferKunde.Name                     = row.Column("Name").StringValue
      PufferKunde.Vorname                  = row.Column("Vorname").StringValue
2

ODBCDatabase may behave a little bit different, because it depends on the data returned by the driver.
It might be a good idea to implementt a check for RowSet.RowCount > 0, if your ODBC Driver does support this.

3

If I understand correctly, you say that you have no issue with SQLite, but MSSQL through ODBC is messing the data.

You should provide a complete sample for MSSQL (create table, insert few records, and show the problem) and submit the report to Xojo

4

RowCount is not support for MSSQL

5

Yes, you’re right. I should open an issue.

1 Like
6

I would use a While Not dbRows.AfterLastRow … dbRows.MoveToNext…Wend loop instead.

1 Like
7

I will try this as workaround

8

I have created an issue: #75883

9

I have implemented your suggestion as a workaround.

// >>>>> begin of workaround
var lv_RowCount as integer = 0
while not dbRows.AfterLastRow
  lv_RowCount = lv_RowCount + 1
  dbRows.MoveToNextRow
wend
if lv_RowCount = 0 then
  return
end if
// <<<<< end of workaround

It works! Thank you!

1 Like
10

If this silently fails for ODBC connections, such construct should not be allowed, and cause a compiler error if possible, if not possible, raise a runtime exception “Operation not allowed: ODBC rows iterator”

Someone please file an Issue report.

1 Like
11

I think I‘ve done it with #75883

12

I’ll add more information there.

Done.

1 Like
13

Are you sure? Even if the Select returns an empty RowSet, you can still loop through it using AfterLastRow

Youu should:

  1. Do your select
  2. Use a loop like:
while  (dbRows.AfterLastRow=False)
  // handle the columns here
  dbRows.MoveTo NextRow
wend
14

As explained in the Issue #75883

There’s a nasty bug in the Iterator.

There are 2 options to iterate on the records, one works,

The usual:

Do Until dbRows.AfterLastRow
  // whatever
  dbRows.MoveToNextRow
Loop

And one is broken, at least for MSSQL ODBC (if no more hidden cases), those using iterators (for Each).

15

OK. That one should give an exception at runtime, and should be documented to do so (not sure whether the compiler could catch that).

16

Maybe it can be just fixed and work as expected, because I’m almost sure that behind the scenes that’s just some bug in the Iterator interface of RowSet.

17

Mmm, that would be preferable, of course.