[quote=438203:@Emile Schwarz]
@Alberto De ;Poo if db.connect then // end if
This is useless
[/quote]
Its not actually. Its the same as using
call dB.connectChecking against error is a good thing, but doing nothing if one found and let the program continue with the error is what I called useless.
Even if you issue a msgbox inside the If block, you still have a problem. Inserting a Return in the If block is useful (after a user report, of course).
There is code in the shared link, but no stop code if an error occured.
That was why I called that useless; and I wait for the second time that code was shared to flag it (on purpose).
Now, what I said
The reason I replied is that to my ears your original response was that his code didnt do anything. Had you explained why you thought it was useless (as you did in your second comment) it would have been much clearer.
[quote=438228:@Rick Araujo]
The topic name should be “Database Field names handling, silent huge bug”[/quote]
Is this a problem with all databases using the Xojo Database classes? I don’t have any others set up to test.
- karen
Thank you for pointing that out, now that it is clear, can we go back to original topic/problem?
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.
[quote=438239:@Karen Atkocius]Is this a problem with all databases using the Xojo Database classes? I don’t have any others set up to test.
- karen[/quote]
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.
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.
oooh a Bob endorsement ! 
spaces in field names in sql are just asking for trouble
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
[quote=438243:@Alberto De Poo]
I don’t know if other databases allow spaces or other characters that update can’t handle or which characters.[/quote]
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
[quote=438253:@Karen Atkocius]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)
[/quote]
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
It is
Supported ? good idea to do though
well what does PRAGMA table_info(
) 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|textand 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|integerso 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
Any column can hold any type of data
THIS the ONE thing that drives me nuts, even though it is by design, in sqlite
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)
[quote=438263:@Norman Palardy]Any column can hold any type of data
THIS the ONE thing that drives me nuts, even though it is by design, in sqlite[/quote]
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
[quote=438257:@Norman Palardy]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
[/quote]
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.
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
[quote=438269:@Alberto De 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.[/quote]
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”
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
[code]The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:
-
If the declared type contains the string “INT” then it is assigned INTEGER affinity.
-
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.
-
If the declared type for a column contains the string “BLOB” or if no type is specified then the column has affinity BLOB.
-
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.[/code]
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
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 …