Database errors with 2019R3.1

I am trying to populate a listbox with data from an SQLite database and am getting a runtime error that I cannot catch or debug.
Not sure if the data is corrupt.

I have a client with an older MS Access application that they want converted to the web. So I used a tool that would convert the .accdb to an SQLite database.
I can open the SQLite database with Xojo, and get a list of the tables. But when I try to load the table data in a list box … some tables load, and some go BOOM.

I tried the new RowSet structure - it worked sometimes, and sometimes the app crashes with an runtime error.
So I switched back to using a RecordSet and … same issue.

When the database is opened a popupmenu gets the list of the tables in the database AND the ListBox.ColumnCount is set to match the column count of the table.
This seems to be working.

But when I click my button to populate the list box, sometimes JOY and sometimes no joy - a runtime error crashes my webapp.

This routines seems to be where the error is.
I tried this with and without the Try…Catch block.

Listbox1.DeleteAllRows
If rs <> Nil Then
rs.MoveFirst

While Not rs.EOF
Listbox1.AddRow
Try
// For I = 1 To rs.FieldCount <----------- Tried to limit the bounds to the FieldCount
For I = 0 To ListBox1.ColumnCount-1 // <— Figured I’d try to limit the bounds a different way
Listbox1.Cell(Listbox1.LastIndex, I) = rs.IdxField(I + 1).StringValue
Next
Catch e As NilObjectException
MessageBox(“Nil Object”)
Catch e As RunTimeException
MessageBox(“An exception of type " + e.Message + " was caught.”)
End Try

rs.MoveNext

Wend
rs.close
Else
Return
End If
rs.Close

So … should this work, or do I have some database corruption from the client. (I’m sure all clients give us perfect databases with no bad data ever). :slight_smile:
The conversion software did not catch table names with spaces. But that was easy enough to fix with an SQLite database editor.

Like I say, some tables load without issues. But others cause a runtime error.
If I replace this code (below) … NO error.

S= rs.IdxField(I + 1).StringValue
// Listbox1.Cell(Listbox1.LastIndex, I)=S

But if I uncomment that line … boom.

So I assume there is some data that is causing the listbox to choke on the data to be imported.

Any help would be a great.

Thanks Eric

When posting code, please use the Code tags to format it. It will help us help you.

You never mentioned what the error was specifically. That might give us some insight.

From what you’ve posted, if there’s a problem with:

Listbox1.Cell(Listbox1.LastIndex, I) = S

then what are the values of Listbox1.LastIndex and I when it crashes? You can use the debugger to see them. Are they as they should be?

There is nothing wrong with RowSet, by the way.

You mentioned editing the database in a SQLite database editor. Using that tool, are you able to view the contents of the problem tables? If that works without issue, then it’s probably not a corrupt database.

When the exception occurs, the IDE is still in the debugger, right? (the debugger doesn’t just go away). If so, you can inspect the various variables and structures to help figure out what’s causing the problem.

I suspect that the line

Listbox1.Cell (Listbox1.LastIndex, I) = rs.IdxField (I + 1).StringValue

is causing the problem when the number of fields in the recordset is different than the number of column in the listbox. Since you may have run out of fields before running out of columns, or vice versa, the code will return an error, probably an OutOfBounds error (but I’m guessing) which you have no specific Catch for. On the tables with the same number of fields as listbox columns everything goes fine.

Without knowing more details of the status at the time of the crash, this is only a guess.

Do you know what’s in s ?

To respond to @Dale Arends & @Kem Tekinay - since the listbox is set to be equal to the number of columns in the table, I should not run out of columns for data. That is, for every column, there should be a idxfield. And I am NOT getting an OutOfBounds error.

@Tim Streater - glad to know RowSet is working for others. I don’t see anything wrong with lastindex, but I’ll double check.

@Jay Madren - Given the size of the database, it would take some time to see if there was corruption.
One issue is that most SQLite browsers will accept table names with spaces. Xojo did not like that, when I changed them in the browser from “Some Table Name” to “Some_Table_Name” that solved the problem with Xojo throwing errors. That said, maybe I need to look at the column names within the database.

@Emile Schwarz - the reason I sent the values to “S” was to see if the recordset would load data into a method variable. Then I could inspect the data when all crashed. So far, I can’t see anything wrong.

I’ve used this code before for other projects. I suspect what I’ll find is it’s not in Xojo, but in the data.

@Kem Tekinay - I’ll use the code tag next time. Sorry about that.

Follow up. … This is the error I am getting:
Runtime Exception
ErrorNumber 0
Message “The data could not be converted to text with this encoding.”

The other issue could be that I don’t know the actual data type coming from the column in the table.

 rs.IdxField(I + 1).StringValue

Maybe I need to read the database schema for the table, and match the datatype of the column for each read.
I’m off to look up how to do that.

[quote=491639:@Eric Bolt]
Maybe I need to read the database schema for the table, and match the datatype of the column for each read.
I’m off to look up how to do that.[/quote]
Does the data in each database column match the defined datatype for that column? Don’t forget that with SQLite, you can put any datatype in any column.

If a column has (say) in integer in it, you’ll need to do such as:

Listbox1.Cell(Listbox1.LastIndex, I) = rs.IdxField(I + 1).Integervalue.ToString

Can you give an example, when you get the “data could not be converted” message, along with the SQLite column type and the actual data value?

You “can’t see anything wrong” as in “the exception occurred and I examined the S variable in the debugger”? What did S contain? Which column is it at the time? (examine value of I).

It is possible to read a string (s in this case) as hex: very valuable information cen be seen here !

But the wrong read of an integer as suggested above seems to be a good guess…

If you are curious, here is what fixed the problem.

[code]
Try

If Encodings.UTF8.IsValidData( rs.ColumnAt(I).StringValue ) Then
S=ReplaceLineEndings(rs.ColumnAt(I).StringValue.DefineEncoding(Encodings.UTF8), EndOfLine.Windows)
[/code]

The rowset was getting data from the database. But some of the characters must not have been valid for a listbox cell.
So I test to see if the data StringValue is valid UTF8.
If so … then I replace the line endings.

That stopped the runtime error.