How to find a FormatException

Scubaticus

Active Member
Licensed User
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?
 

Scubaticus

Active Member
Licensed User
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.
 

Scubaticus

Active Member
Licensed User
I just sent you an email with a link to some sample source & db
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
I also get this problem on the desktop.

SQLite doesn't force the column data type when you insert values, therefore this line:
B4X:
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:
B4X:
    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

Active Member
Licensed User
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

Expert
Licensed User
Longtime User
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

Active Member
Licensed User
"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

Expert
Licensed User
Longtime User
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

Active Member
Licensed User
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

B4X founder
Staff member
Licensed User
Longtime User
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:
B4X:
sqlCmd = "Select cast(len AS TEXT) AS len from Sample"
 
Cookies are required to use this site. You must accept them to continue using the site. Learn more…