> My advise to the VB guy would be to load the extension instead of trying to get VB to marshal pointers from the C API.
?? I do load the extension although it is compiled std_call, so VB6 can talk to it. RBS On Mon, Nov 27, 2017 at 12:59 AM, petern <[email protected]> wrote: > I pasted that SQL into a SQLite shell and tried it out. > > First off, Affinity (aka pragma table_info.type) column is case collated, > so the LIKE operator should be used: > > select O.* > from SysColumns as O > where ObjectType == 'table' > and IsPrimaryKey == 1 > and Affinity LIKE 'INTEGER' > and not exists (select 1 > from SysColumns as I > where I.ObjectType == O.ObjectType > and I.ObjectName == O.ObjectName > and I.IsPrimaryKey > 1); > > [Side question. Do you find the '==' operator more readable/informative > than the plain '=' SQL convention?] > > The main problem is still AUTOINCREMENT. You may not use AI, but a tool > that reads others' schemas has to deal with that possibility. As well, the > system table sqlite_sequence will have no rows about an AI column when that > AI table has no rows. Therefore, the only general way, without calling the > internal parser as I did in the extension, is to partly parse the SQL of > the CREATE TABLE statement. > > Partial parsing isn't too hard I suppose. There are a couple facts about > the well formed CREATE statement that help. > > 1. The keywords PRIMARY KEY must be two words. > 2. The keyword "AUTOINCREMENT" must come after "KEY" and before the next > comma or closing bracket. > > From those facts it should be feasible to deduce the AUTOINCREMENT status > by use of the instr() and substr() functions. > > Or, one could simply use the native -std=c90 style SQLite extension I > posted for the other Peter... > > My advise to the VB guy would be to load the extension instead of trying to > get VB to marshal pointers from the C API. Though, I suppose that isn't > the worst idea from recent posts. Reading all the rows of a table in > Python to get the count() has to be the best one. In fact, that one is a > classic job interview question. > > > > > > > On Sun, Nov 26, 2017 at 1:41 PM, Keith Medcalf <[email protected]> > wrote: > > > Not an answer to what is wrong with your call to get the metadata, > > however, the information is all available from SQL. > > > > Data Catalog Views: > > > > drop view SysIndexColumns; > > drop view SysIndexes; > > drop view SysColumns; > > drop view SysObjects; > > > > create view if not exists SysObjects > > as > > select type as ObjectType, > > name as ObjectName > > from sqlite_master > > where type in ('table', 'view', 'index'); > > > > create view if not exists SysColumns > > as > > select ObjectType, > > ObjectName, > > cid as ColumnID, > > name as ColumnName, > > type as Affinity, > > "notnull" as IsNotNull, > > dflt_value as DefaultValue, > > pk as IsPrimaryKey > > from SysObjects > > join pragma_table_info(ObjectName); > > > > create view if not exists SysIndexes > > as > > select ObjectType, > > ObjectName, > > name as IndexName, > > seq as IndexID, > > "unique" as IsUniqueIndex, > > origin as IndexOrigin, > > partial as IsPartialIndex > > from SysObjects > > join pragma_index_list(ObjectName); > > > > create view if not exists SysIndexColumns > > as > > select ObjectType, > > ObjectName, > > IndexName, > > seqno as IndexColumnSequence, > > cid as ColumnID, > > name as ColumnName, > > "desc" as IsDescendingOrder, > > coll as Collation, > > key as IsPartOfKey > > from SysIndexes > > join pragma_index_xinfo(IndexName); > > > > Then > > > > select O.* > > from SysColumns as O > > where ObjectType == 'table' > > and IsPrimaryKey == 1 > > and Affinity == 'integer' > > and not exists (select 1 > > from SysColumns as I > > where I.ObjectType == O.ObjectType > > and I.ObjectName == O.ObjectName > > and I.IsPrimaryKey > 1); > > > > will return all the 'INTEGER PRIMARY KEY' in the "main" database ... > > > > I do not know what it does for "without rowid" tables where there is a > > single field declared as "integer primary key", if you have some of those > > you will have to figure it out (and how to eliminate them if you want to > do > > so) yourself. > > > > --- > > The fact that there's a Highway to Hell but only a Stairway to Heaven > says > > a lot about anticipated traffic volume. > > > > >-----Original Message----- > > >From: sqlite-users [mailto:sqlite-users- > > >[email protected]] On Behalf Of Bart Smissaert > > >Sent: Sunday, 26 November, 2017 13:14 > > >To: SQLite mailing list > > >Subject: Re: [sqlite] How to use sqlite3_table_column_metadata? > > > > > >I am passing a pointer to UTF encoded strings and the passed table > > >and > > >column do exist. > > >There must be something wrong in my code. > > > > > >RBS > > > > > > > > > > > > > > >On Sun, Nov 26, 2017 at 7:56 PM, Clemens Ladisch <[email protected]> > > >wrote: > > > > > >> Bart Smissaert wrote: > > >> > Having some difficulty using sqlite3_table_column_metadata, used > > >from > > >> VB6. > > >> > > > >> > I keep getting a zero for the first output argument (should be > > >pointer > > >> to declared data type string) > > >> > and the return value of the function keeps giving 1. > > >> > > >> The documentation > > ><http://www.sqlite.org/c3ref/table_column_metadata.html> > > >> says: > > >> | The sqlite3_table_column_metadata() interface returns > > >SQLITE_ERROR and > > >> if the specified column does not exist. > > >> > > >> > Should the tbl_name and column_name also be encoded in UTF8? > > >> > > >> In SQLite, pretty much all strings are UTF-8. > > >> > > >> > > >> Regards, > > >> Clemens > > >> _______________________________________________ > > >> sqlite-users mailing list > > >> [email protected] > > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- > > >users > > >> > > >_______________________________________________ > > >sqlite-users mailing list > > >[email protected] > > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
