How to find a FormatException

[Scubaticus]

On my mobile I got a FormatException. Only a subname is given.
On the PC it does run without any failure.

Is there a smart way to find out what is groing wrong on the device?

Is there a way the application will behave the same on the PC platform so it won't be such a time consuming issue to find errors not occuring on the PC?

 

[Erel]

Can you post the method code?

Agraham has developed a Debugging library that may help you.
Your other choice is to put msgboxes and see where it fails.

 

[Scubaticus]

I'll take a look at Agrahams debug lib. The MsgBox option is the time consuming part..

I suspect a SQL which returns a null value into a table (dbCmd.ExecuteTable(tableName,0) ...

It would be really nice if such errors also would occur in a PC environment.

 

[Erel]

There are some differences between the desktop and the device environments.
Most of these differences are considered bug, so please report them if you find.

 

[Scubaticus]

I just sent you an email with a link to some sample source & db

 

[Erel]

I also get this problem on the desktop.

SQLite doesn't force the column data type when you insert values, therefore this line:

sqlCmd = "Insert into Sample (len) VALUES('')"

Inserts an empty string, not 0.
Later when you do ExecuteTable, the table column is numeric because you declared the column as Number and the empty string cannot be converted.

You can see the values with your reader:

    dbRdr.Value = dbCmd.ExecuteReader

    Do While dbRdr.ReadNextRow
        Msgbox(dbRdr.GetValue(0))
    Loop

Note that there is no need to use BeginTransaction / EndTransaction unless you intend to do many inserts one after the other.

 

[Scubaticus]

So when I insert a record, basic4PPC does not check the colum type, but as soon as I retrieve the value it looks at the column type?

sounds a little bit strange to me....

I understand you answer, but why not check the column type ALWAYS? This would have give me an error like 'db fieldtype not the same as what you try to put in' pointing me at the right direction!

Perhaps something for the new release?

 

[agraham]

Basic4ppc cannot easily check the data type on insertion as to do so it would it have to parse the SQL command - which is what we are using SQLite for!

It is a feature of SQLite that it employs manifest typing Distinctive Features Of SQLite meaning any column can store any value regardless of the declared column type, a bit like Basic4ppc itself. The error occurs because ExecuteTable sets the column type of the new Table to numeric which then of course cannot accept an empty string as a value. I don't know why Table doesn't conform to Basic4ppcs' usual weak typing and keep numerics as a string as it does for the other coluimn types. There was presumably some good reason for this design decision.

 

[Scubaticus]

"The error occurs because ExecuteTable sets the column type"

If SQLite allows me to put everything in every column. Why is Basic4PPC using the column type of the SQLite database and not handle every SQLite database field as a string?

 

[agraham]

Why is Basic4PPC using the column type of the SQLite database and not handle every SQLite database field as a string?

I already said that I don't know that.

I don't know why Table doesn't conform to Basic4ppcs' usual weak typing ... There was presumably some good reason

 

[Scubaticus]

I already said that I don't know that.

Sorry Agraham.

Perhaps Erel could give an answer. I had a lot off issues with sqlite & basic4ppc tables, but only after accessing the table cells and not immediately after filling them from the database.

Especially if the database is filled from another non b4ppc app there could be data in the table causing the b4ppc app to crash......

 

[Erel]

There were several reasons why it was important to have a String type and a Number type for the table.
If we treat all numbers as strings then sorting of numeric columns will be wrong.
For example the values:
2
1
3
10

will be sorted to:
1
10
2
3

It will also won't be possible to do range filters.

If I remember correctly there was an issue with different regional settings, but I currently do not remember the details.

You can however treat all data as string by using the cast operator in your query:

sqlCmd = "Select cast(len AS TEXT) AS len from Sample"