Cmd.ExecuteTable bug

Discussion in 'Bug Reports' started by Scubaticus, Feb 24, 2009.

  1. Scubaticus

    Scubaticus Active Member Licensed User

    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    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:
    Code:
    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"
     
  3. Scubaticus

    Scubaticus Active Member Licensed User

    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
     
  4. Scubaticus

    Scubaticus Active Member Licensed User

    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
     
    Last edited: Feb 25, 2009
  5. Erel

    Erel Administrator Staff Member Licensed User

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

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

    Scubaticus Active Member Licensed User

    Sorry, my mistake. Replace the sql with

    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 :))
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice