Re: [sqlite] How to use sqlite3_table_column_metadata?

[Clemens Ladisch]

Bart Smissaert wrote:
> If I pass a valid connection  and  valid table name but null for all other
> input parameters I get:
>
> return value:  0
>
> How does that work?

The documentation 
says:
| If the column-name parameter to sqlite3_table_column_metadata() is
| a NULL pointer, then this routine simply checks for the existence of
| the table and returns SQLITE_OK if the table exists and SQLITE_ERROR
| if it does not.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use sqlite3_table_column_metadata?

[petern]

t; 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,
> >> >cidas 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,
> >> >cidas ColumnID,
> >> >name   as ColumnName,
> >> >"desc" as IsDescendingOrder,
> >> >coll   as Collation,
> >> >keyas 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Bart Smissaert]

bjectName,
>> >IndexName,
>> >seqno  as IndexColumnSequence,
>> >cidas ColumnID,
>> >name   as ColumnName,
>> >"desc" as IsDescendingOrder,
>> >coll   as Collation,
>> >keyas 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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Bart Smissaert]

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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Keith Medcalf]

ique" 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,
>>cidas ColumnID,
>>name   as ColumnName,
>>"desc" as IsDescendingOrder,
>>coll   as Collation,
>>keyas 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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[petern]

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,
>cidas 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,
>cidas ColumnID,
>name   as ColumnName,
>"desc" as IsDescendingOrder,
>coll   as Collation,
>keyas 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.
> 

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Bart Smissaert]

Thanks, will try that out.
I don't think I actually need the sqlite3_table_column_metadata, but just
wanted to test it.

RBS

On Sun, Nov 26, 2017 at 9: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,
>cidas 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,
>cidas ColumnID,
>name   as ColumnName,
>"desc" as IsDescendingOrder,
>coll   as Collation,
>keyas 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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Keith Medcalf]

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,
   cidas 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,
   cidas ColumnID,
   name   as ColumnName,
   "desc" as IsDescendingOrder,
   coll   as Collation,
   keyas 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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Bart Smissaert]

All sorted now.
This was indeed a coding error and nil to do with SQLite.

RBS

On Sun, Nov 26, 2017 at 7:56 PM, Clemens Ladisch  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 
> 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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Bart Smissaert]

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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Clemens Ladisch]

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  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] How to use sqlite3_table_column_metadata?

[Bart Smissaert]

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.
I read in Jay Kreibich's book:

db_name
A logical database name, encoded in UTF-8.
The name may be main, temp, or a name given to ATTACH DATABASE.

tbl_name
A table name.

col_name
A column name.

Should the tbl_name and column_name also be encoded in UTF8?

Using SQLite 3.21.0


RBS
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users