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
