Cmd.ExecuteTable bug

[Scubaticus]

Attached program & db shows a table with 2 records.
When importing the records into a table with cmd.ExecuteTable it seems the first record is used for the cell format.

The result from the program should be:

Row,A,B,C
0,1,2,3
1,5.5,5.55,5.555

The result however in the table is:

Row,A,B,C
0,1,2,3
1,5,5,5

 

[Erel]

I don't think that it is a bug in ExecuteTable. As far as I see this issue is related to how SQLite implicitly decides on the columns types.
In your case you should explicitly define the types:

sql = "Select cast(sum(n1) as REAL) A, cast(sum(n2) as REAL) B, cast(sum(n3) as REAL) C from tbl1 group by GB"

 

[Scubaticus]

Hi Erel,

Thanks for you quick reply. I understand what you mean, but when executing the sql in SQLite Expert or SQLite 2007 Pro, the result is just what I expected.

Another solution I just found is not to use SUM but TOTAL

From the SQLite manual

The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs are integers.

 

[Scubaticus]

Hi Erel,

Well, I thought I solved the problem by using Total in stead of sum and it works for cmd.executetable.

For reading my database, I use a routine which first read the result direct into the table ( I do this to set the Columns and column types), holding eventually NULL values.

To get rid of them, I reread the result and put the values in the table by a Control(tableName, Table).Cell(celName, rr) = rdrVal

But the result is not what I expect at all! The decomal point is removed.

Of course I take care of not writing null values into the DB, but a DB can be filled with other tools so I have to handle NULL values.

See the attached file for details.

Oops, I just read the manual: GetValue (Index As Int32) As String

 

[Erel]

I do get decimal numbers (5.55, 5.555,...).

ExecuteTable will be fixed in the next update to handle null values properly.

 

[Scubaticus]

Sorry, my mistake. Replace the sql with

sql = "Select cast(n1 as REAL) A, cast(n2 as REAL) B, cast(n3 as REAL) C from tbl1"

But even after I read GetValue returns a string, I still wonder why the decimal point is gone ....

Very nice the NULL values will be fixed in the next release. Can't wait for it!
I probably better set default values in the table fields for none passed values. That will reduce null values to none in db.

Thanks for your quick reply (as usual )