I’m trying to figure out a way to determine if a particular column in a RowSet is the Primary Key. The scenario is that elsewhere in the program the RowSet is being retrieved and is passed to my method.
I know I can query the schema to get that information but at the point I need it I don’t have the table name. RowSet.ColumnType will get me the type of data for that column but no info about if it is a Key field. Is there some other means to determine the fieldtype values from the passed RowSet?
ColumnName: A string that is the name of the column.
FieldType: An integer that describes the type of the column (refer to the table below).
[b]IsPrimary[/b]: A boolean that indicates if the column is part of the primary key.
NotNull: A boolean that indicates if the column can be set to NULL.
Length: An integer that describes the length of the field (for some text fields), the precision of numeric data (for some numeric fields) or the default value for a field (for SQLite columns).[/quote]
when I open a database, I read every table, every column in that table, isolate the primary key, every foreign key and trigger
store them in a class hierarchy. then they are available everytime…
What I’m a bit puzzled about is that SQLite does not guarantee the column name in a result set; in principle, it could be anything unless you use AS. Does Xojo get round that somehow?
Not quite right… Using “AS” one can set the name for that column in the result set to anything… Without an “AS” the name will be the name defined for that table column.
That is part of the SQL standard so is true for any SQL database… and why writing generic code to handle results sets in all situations is hard… Besides “AS” the SQLSelect could have been on a view and not a table… or it could have been from across multiple table or…
If you are doing anything beyond reading the data returned, you need knowledge of the select statement to know what to do.
[quote=466453:@Markus Rauch]here is something mentioned documentation.xojo.com/api/databases/database.html#database-tablecolumns[/quote]
I am aware of this but it doesn’t help since it needs the table name and, at the point I need the fieldtype, I don’t have the table name.[quote=466463:@Jean-Yves Pochez]when I open a database, I read every table, every column in that table, isolate the primary key, every foreign key and trigger store them in a class hierarchy. then they are available everytime…[/quote]
That’s what I am doing for the particular table. I was just wondering if there is a cleaner way to get the fieldtype, maybe through Introspection somehow, when you have a RowSet from a non-schema data query and don’t have the table name the RowSet came from.
I’m working on a bug fix in a routine where the original coder (no longer alive) passed in a RowSet created by a Select * From... query.
Since there doesn’t seem to be an easy way to get the fieldtype from the RowSet, I’m just going to continue with storing the table schema for reference when the database is opened.
how often is “select *” used in combination with your method?
its possible to replace the * with all fields? (new fields would be missing if db get more fields …)
so the first column can be your id field?
or you do a “select fieldxy as Id” there to access it by same name.
[quote=466665:@Markus Rauch]how often is “select *” used in combination with your method?
its possible to replace the * with all fields? (new fields would be missing if db get more fields …)
so the first column can be your id field?
or you do a “select fieldxy as Id” there to access it by same name.[/quote]
Several of the rowsets come from queries I don’t have access to so I can’t change them. What I have works, albeit inelegantly, so I’ll reluctantly stick with it.