One strange thing I noticed with this:

If I pass a valid connection  and  valid table name but null for all other
input parameters I get:

return value:              0
data type:                 INTEGER
collation:                   BINARY
not null:                     0
part of primary key:  1
auto-increment:        0

How does that work?

RBS

On Mon, Nov 27, 2017 at 10:13 AM, Bart Smissaert <bart.smissa...@gmail.com>
wrote:

> > 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