[Gambas-user] Connection.Tables

[tobias]

Benoît Minisini schrieb:
>> hi,
>> i noticed something in my test with Connection.Edit() (sqlite3):
>> i created a table with connection.exec()
>>
>> hConnection.Exec("create table test(id integer primary key, name
>> varchar(10));")
>>
>> then inserted some data
>> hConnection.Exec("insert into test(name) values(\"Aaron\");")
>> hConnection.Exec("insert into test(name) values('Zacharias');")
>>
>> now, because i thought, this is the only way to prevent the error "table
>> test has no primary key", i set the primary key property of the table
>> object:
>> hConnection.Tables["test"].PrimaryKey = ["id"]
>>
>> not this brought me "Read-only property".
>> is this because i created the table via exec() and there is no
>> synchronization (possible)?
>>
>> regards,
>> tobi
>>
> 
> You can't update the primary index of a table once it has been created anyway.
> 
> I have understood where the bug with primary key comes from. It is just a 
> matter of case: write "integer" in upper case, and the database driver will 
> detect the primary key!
> 
> To understand all that, you must be aware that SQLite is a non-typed database, 
> i.e. the field datatypes given in the CREATE TABLE statement are mostly 
> ignored. A SQLite field can hold any datatype, whatever its definition!
> 
> "Mostly", because there is a big exception: if you declare a field as "integer 
> primary key", you actually declare a 64 bits integer-only field that will 
> increment automatically as each record creation. And this special primary key 
> does not appear in the index list returned by SQLite (because it is actually 
> an index present in each table, named "rowid").
> 
> See http://www.sqlite.org/lang_createtable.html for more information.
> 
> To detect it, I added a test on the table SQL declaration: if I don't see any 
> index in the table, and if the table has an integer field, I assume it is the 
> primary key. 
> 
> The test is not perfect yet (a table could have an "integer" field and no 
> primary key), but, more important, it was buggy, as it assumed that "integer" 
> was written in upper case!
> 
> I will fix that in the next revision, and your "id integer primary key" will 
> be correctly detected as the primary key of the table.
> 
> Note that if you create your table by using the Gambas interface, and not by 
> sending "CREATE TABLE" statements directly, you won't have the problem, 
> because Gambas only uses "integer" when creating an autoincrement integer 
> field. For all other integer fields, it uses "INT4".
> 
> Regards,
> 
oh, thank you very very much. this will correct all my problems. of 
course, i use the gambas interface in serious programs but this method 
exists too and i try my best to document and discover everything ;-)

thank you,
tobi