> 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 <peter.nichvolo...@gmail.com>
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 <kmedc...@dessus.com>
> 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-
> > >boun...@mailinglists.sqlite.org] 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 <clem...@ladisch.de>
> > >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
> > >> sqlite-users@mailinglists.sqlite.org
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> > >users
> > >>
> > >_______________________________________________
> > >sqlite-users mailing list
> > >sqlite-users@mailinglists.sqlite.org
> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to