SQLITE field Name handing issue causes Date bug

  1. ‹ Older
  2. 3 months ago

    Greg O

    May 25 Xojo Inc

    The reason I replied is that to my ears your original response was that his code didn’t do anything. Had you explained why you thought it was useless (as you did in your second comment) it would have been much clearer.

  3. Karen A

    May 25 Pre-Release Testers
    Edited 3 months ago

    @Rick A The topic name should be "Database Field names handling, silent huge bug"

    Is this a problem with all databases using the Xojo Database classes? I don't have any others set up to test.

    - karen

  4. Alberto D

    May 25 Pre-Release Testers

    Thank you for pointing that out, now that it is clear, can we go back to original topic/problem?

  5. Rick A

    May 25 (Brazil. GMT-3:00)
    Edited 3 months ago

    @Karen A Is this a problem with all databases using the Xojo Database classes?

    That' s a problem to Xojo inspect. It can be focused to SQLite only or spread for a common base. That's why it's a "huge thing" needing attention.

  6. Alberto D

    May 25 Pre-Release Testers

    @Karen A Is this a problem with all databases using the Xojo Database classes? I don't have any others set up to test.

    - karen

    My guess is that if you use rs.update (with a space on the name) with other databases, you will have the same issue.

    I don't know if other databases allow spaces or other characters that update can't handle or which characters.

  7. Bob K

    May 25 Pre-Release Testers, Xojo Pro Kansas City

    Most database allow a space but I'm with Norman, just don't do it. It only causes problems down the road and not just in Xojo.

  8. Norman P

    May 25 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    oooh a Bob endorsement ! :)

    spaces in field names in sql are just asking for trouble

  9. Karen A

    May 25 Pre-Release Testers

    @Bob K Most database allow a space but I'm with Norman, just don't do it. It only causes problems down the road and not just in Xojo.

    But as SQL allows it, I think at least teh Xojo Docs should say it is not supported... (though I don't know if the issue is in Xojo's code or code written by SQLite.org )

    Those who are learning tend to think that docs mean what they say and the SQLite docs (which Xojo says to use) do say it is supported.

    I found another issue with the Xojo SQLite plug I mentioned elsewhere ... RecordSet.ColumnType does not follow the SQLITE affinity rules with respect to column data type names. They are very clearly and simply spelled out in the SQLite docs.

    Some column data types that should be seen as Integers are reported to be text, and the same for some columns which should be reported as Doubles.

    It is this type of lack attention to detail that sometime drives me crazy with Xojo and sure tends to waste a lot of my time!

    - karen

  10. Karen A

    May 25 Pre-Release Testers

    @Alberto D;Poo I don't know if other databases allow spaces or other characters that update can't handle or which characters.

    While some DBs may not support it, IIRC it has been part of SQL from early on.

    It comes from back in the day when output was to a terminal screen (or big noisy line printers) allowing one to have nicely formatted easily human readable tables of record output... When you are old you remember such things! ;)

    - karen

  11. Norman P

    May 25 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    @Karen A But as SQL allows it, I think at least teh Xojo Docs should say it is not supported... (though I don't know if the issue is in Xojo's code or code written by SQLite.org )

    Just try this in terminal on macOS which has sqlite 3 installed

    Last login: Fri May 24 21:12:40 on ttys000
    server:~ npalardy$ sqlite3
    SQLite version 3.16.0 2016-11-04 19:09:39
    Enter ".help" for usage hints.
    Connected to a transient in-memory database.
    Use ".open FILENAME" to reopen on a persistent database.
    sqlite> create table foo( "a column") ;
    sqlite> insert into foo( "a column") values (123) ;
    sqlite> select "a column" from foo ;
    123
    sqlite> 

    This would suggest to me the issue in in the plugin

    @Karen A Those who are learning tend to think that docs mean what they say and the SQLite docs (which Xojo says to use) do say it is supported.

    It is
    Supported ≠ good idea to do though

    @Karen A I found another issue with the Xojo SQLite plug I mentioned elsewhere ... RecordSet.ColumnType does not follow the SQLITE affinity rules with respect to column data type names. They are very clearly and simply spelled out in the SQLite docs.

    Some column data types that should be seen as Integers are reported to be text, and the same for some columns which should be reported as Doubles.

    well what does PRAGMA table_info(<table name here>) return for this table ?
    that comes from sqlite and is probably whats used to report the data type for the column

    Here what I get from it running in terminal (note that blob affinity is blank)

    [code]
    sqlite> create table bar ( text1 TEXT, num1 NUM, int1 INT, real1 REAL, blob1 ) ;
    sqlite> PRAGMA table_info(bar) ;
    0|text1|TEXT|0||0
    1|num1|NUM|0||0
    2|int1|INT|0||0
    3|real1|REAL|0||0
    4|blob1||0||0
    sqlite>
    [/quote]

    EDIT :
    or alternately, once you insert some data,

    sqlite> insert into bar( text1 , num1, int1, real1, blob1 ) values ('hello world', 123, 123, 123.45, "foo" ) ;
    sqlite> SELECT typeof(text1), typeof(num1), typeof(int1), typeof(real1), typeof(blob1) FROM bar;
    text|integer|integer|real|text

    and JUST for fun if you add another row that changes affinities in the columns you get fun results

    sqlite> insert into bar( text1 , num1, int1, real1, blob1 ) values (123, 123.56, '123', '123', 123 ) ;
    sqlite> SELECT typeof(text1), typeof(num1), typeof(int1), typeof(real1), typeof(blob1) FROM bar;
    text|integer|integer|real|text
    text|real|integer|real|integer

    so affinity is not "per column for the entire table " but "per column and per row" :)

    I suspect that would explain the variable results you see

  12. Norman P

    May 25 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    Any column can hold any type of data
    THIS the ONE thing that drives me nuts, even though it is by design, in sqlite

  13. Norman P

    May 25 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    based on a quick read of the mysql source code which Xojo provides ithis looks like a bug in the creation of the update statement

    	// start building the UPDATE statement
    	std::string query = "UPDATE ";
    	query += mTable;
    	query += " SET ";
    	
    	// loop over all of the fields, adding each to the UPDATE statement
    	REALcursorUpdate *fld;
    	for (fld = fields; fld; fld = fld->next)
    	{
    		if (mFields[fld->fieldIndex].org_name != nullptr)
    			query += mFields[fld->fieldIndex].org_name;
    		else
    			query += mFields[fld->fieldIndex].name;
    			
    		query += "=";
    

    note that IF the field has a space in it there is NO code to make sure it is quoted (or bracketed or whatever is appropriate for the db)

    Feedback Case #55784

  14. Karen A

    May 25 Pre-Release Testers

    @Norman P Any column can hold any type of data
    THIS the ONE thing that drives me nuts, even though it is by design, in sqlite

    I agree... But it still has affinity runs that Xojo should follow.

    Your previous post misses the point. What that Pragma returns is just the data type specified in the Table definition not the affinity it uses the internally... The Xojo plugin should report the Affinity or a more specific subtype based on the datatype i the table definition ...

    For example by the affinity rules TinyInt should be of Type integer, so Recordset.Column type should report it as type 19 (Int64) but reports it as type 5 (text)... Lot's of issues like that (see below)

    BTW so why can't we just use that pragma and figure out ourselves... Well that would be simple if all Records sets were from a single table and not views or had aliased filenames...

    To handle the general case one needs better field definitions....
    I don't know if its possible but I would love there to also be a:

    RS.ColumnType(Idx as integer) As String

    method that returns the Datatype string as defined in the DB at least SQLite

    This is what that pragma returns for my Test table:

    cid | name | type | notnull | dflt_value | pk
    0 | Key0 | Integer | 1 |  | 1
    1 | aBYTE | BYTE | 0 |  | 0
    2 | anInt | INT | 0 |  | 0
    3 | anInteger | INTEGER | 0 |  | 0
    4 | aTinyInt | TINYINT | 0 |  | 0
    5 | aSmallInt | SMALLINT | 0 |  | 0
    6 | aMediumInt | MEDIUMINT | 0 |  | 0
    7 | aBigInt | BIGINT | 0 |  | 0
    8 | aUNSIGNED BIG INT | UNSIGNED BIG INT | 0 |  | 0
    9 | anInt2 | INT2 | 0 |  | 0
    10 | anInt8 | INT8 | 0 |  | 0
    11 | aREAL | REAL | 0 |  | 0
    12 | aDOUBLE | DOUBLE | 0 |  | 0
    13 | aDOUBLE PRECISION | DOUBLE PRECISION | 0 |  | 0
    14 | aFLOAT | FLOAT | 0 |  | 0
    15 | aNUMERIC | NUMERIC | 0 |  | 0
    16 | aDECIMAL(10,5) | DECIMAL(10,5) | 0 |  | 0
    17 | aBOOLEAN | BOOLEAN | 0 |  | 0
    18 | aDATE | DATE | 0 |  | 0
    19 | aDATETIME | DATETIME | 0 |  | 0
    20 | aTIME | TIME | 0 |  | 0
    21 | aBLOB | BLOB | 0 |  | 0
    22 | aCURRENCY | CURRENCY | 0 |  | 0
  15. Alberto D

    May 25 Pre-Release Testers

    @Norman P Just try this in terminal on macOS which has sqlite 3 installed

    Last login: Fri May 24 21:12:40 on ttys000
    server:~ npalardy$ sqlite3
    SQLite version 3.16.0 2016-11-04 19:09:39
    Enter ".help" for usage hints.
    Connected to a transient in-memory database.
    Use ".open FILENAME" to reopen on a persistent database.
    sqlite> create table foo( "a column") ;
    sqlite> insert into foo( "a column") values (123) ;
    sqlite> select "a column" from foo ;
    123
    sqlite> 

    This would suggest to me the issue in in the plugin

    As you later posted, the problem is with Update, this code works:

    Dim db As New SQLiteDatabase
    call db.connect
    db.SQLExecute( "create table foo (""a column"") ")
    db.SQLExecute( "insert into foo (""a column"") values (123) ")
    dim rs as recordset = DB.SQLSelect("SELECT ""a column"" FROM foo")
    MsgBox rs.Field("a column").StringValue

    so Field can find a column with space on it, Update has a bug and can't handle a column name with space.

  16. Norman P

    May 25 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    If that pragma IS whats being used then I'd agree they should report the more detailed types

    But note that the type can, as my note showed at the end, change by row
    The tables defined affinity really isnt gospel - its more like a suggestion

  17. Norman P

    May 25 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    @Alberto D;Poo As you later posted, the problem is with Update, this code works:

    Dim db As New SQLiteDatabase
    call db.connect
    db.SQLExecute( "create table foo (""a column"") ")
    db.SQLExecute( "insert into foo (""a column"") values (123) ")
    dim rs as recordset = DB.SQLSelect("SELECT ""a column"" FROM foo")
    MsgBox rs.Field("a column").StringValue

    so Field can find a column with space on it, Update has a bug and can't handle a column name with space.

    Sure - thats what my code from terminal was intended to demonstrate and I believe I have reported the bug in a way they can fix it for every db plugin

    at this point I do get to say "not my monkey not my circus" :)

  18. Karen A

    May 25 Pre-Release Testers

    just finish the discussion of SQLite column affinity types and want data type RecordSet.Column type report I added the ColumnType RS.ColumnType reported as the last item teh line... Note that ColumnType 5 is text and how often xojo does not follow the Sqlite rules

    Here are the rules

    The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:
    
    1) If the declared type contains the string "INT" then it is assigned INTEGER affinity.
    
    2) If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
    
    3) If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.
    
    4) If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
    
    Otherwise, the affinity is NUMERIC.

    And here is what RS.ColumnType gives you (last column) for these data types:

    cid | name | type | notnull | dflt_value | pk | RS.ColumnType
    0 | Key0 | Integer | 1 |  | 1 | 19
    1 | aBYTE | BYTE | 0 |  | 0 | 5
    2 | anInt | INT | 0 |  | 0 | 5
    3 | anInteger | INTEGER | 0 |  | 0 | 19
    4 | aTinyInt | TINYINT | 0 |  | 0 | 5
    5 | aSmallInt | SMALLINT | 0 |  | 0 | 3
    6 | aMediumInt | MEDIUMINT | 0 |  | 0 | 5
    7 | aBigInt | BIGINT | 0 |  | 0 | 5
    8 | aUNSIGNED BIG INT | UNSIGNED BIG INT | 0 |  | 0 | 5
    9 | anInt2 | INT2 | 0 |  | 0 | 5
    10 | anInt8 | INT8 | 0 |  | 0 | 5
    11 | aREAL | REAL | 0 |  | 0 | 7
    12 | aDOUBLE | DOUBLE | 0 |  | 0 | 7
    13 | aDOUBLE PRECISION | DOUBLE PRECISION | 0 |  | 0 | 5
    14 | aFLOAT | FLOAT | 0 |  | 0 | 6
    15 | aNUMERIC | NUMERIC | 0 |  | 0 | 5
    16 | aDECIMAL(10,5) | DECIMAL(10,5) | 0 |  | 0 | 13
    17 | aBOOLEAN | BOOLEAN | 0 |  | 0 | 12
    18 | aDATE | DATE | 0 |  | 0 | 8
    19 | aDATETIME | DATETIME | 0 |  | 0 | 5
    20 | aTIME | TIME | 0 |  | 0 | 9
    21 | aBLOB | BLOB | 0 |  | 0 | 14
    22 | aCURRENCY | CURRENCY | 0 |  | 0 | 11
  19. Norman P

    May 25 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    the columns defined affinity does NOT override the actual TYPE of the data in the column
    the pragma tells you the DEFINED affinity but the type function tells you the ACTUAL type of the data in the column (since you can shove anything in any column)
    they do not have to match

    sqlite> create table bar ( text1 TEXT, num1 NUM, int1 INT, real1 REAL, blob1 ) ;
    sqlite> PRAGMA table_info(bar) ;
    0|text1|TEXT|0||0
    1|num1|NUM|0||0
    2|int1|INT|0||0
    3|real1|REAL|0||0
    4|blob1||0||0
    sqlite>
    sqlite> insert into bar( text1 , num1, int1, real1, blob1 ) values ('hello world', 123, 123, 123.45, "foo" ) ;
    sqlite> SELECT typeof(text1), typeof(num1), typeof(int1), typeof(real1), typeof(blob1) FROM bar;
    text|integer|integer|real|text
    sqlite> insert into bar( text1 , num1, int1, real1, blob1 ) values (123, 123.56, '123', '123', 123 ) ;
    sqlite> SELECT typeof(text1), typeof(num1), typeof(int1), typeof(real1), typeof(blob1) FROM bar;
    text|integer|integer|real|text
    text|real|integer|real|integer

    two rows, same affinities defined, but different actual types of data in the columns

    if this were almost ANY other DB my second insert would get rejected since the data does not match the defined types ... but SQLite lets you

    what I cant say is whether the types reported by the sqliteplugin match the inserted data or the defined affinity

    scratch all that :) (since deleting is apparently frowned on)

    I wonder if they just dont match the sqlite return name to something internal and fall back to TEXT in that case

    No source code for the plugin so ....

  20. Karen A

    May 25 Pre-Release Testers
    Edited 3 months ago

    Norm,

    Are you sure of that? I'm not. SQLIte will do dome fancy stuff under teh hood with data but in any case I am working from teh Xojo level..
    There there the following is the case:

    1) RS.ColumnType by it's name should give the type defined for the column rather the table definition, not not individual records. It's not called RS.fieldtype after all! And it can vary by record...

    2) RS.columnType will return what i posted for an empty recordset (tested) so that could only come from the column definitions. That is also true even if I don't insert any records (tested)

    3) I put the right types of data into the fields in the one record I inserted into the table

    So I think you are mistaken...

    BTW the way to find out what type Xojo is using for a field value in specific record one can do: Rs.Field(name).Value.Type

    - Karen

  21. Norman P

    May 25 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    @Karen A Norm,

    Are you sure of that? I'm not. SQLIte will do dome fancy stuff under teh hood with data but in any case I am working from teh Xojo level.

    well the code I posted earlier does report different types for each column from multiple rows
    It would be as if the type is changing - regardless of the affinity defined
    And that doesnt involve Xojo at all

or Sign Up to reply!