How to use sqlite3_table_column_metadata?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use sqlite3_table_column_metadata?

Bart Smissaert
In reply to this post by Clemens Ladisch
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use sqlite3_table_column_metadata?

Keith Medcalf
In reply to this post by Bart Smissaert
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-
>[hidden email]] 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 <[hidden email]>
>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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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-
> >[hidden email]] 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 <[hidden email]>
> >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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use sqlite3_table_column_metadata?

petern
In reply to this post by Keith Medcalf
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 <[hidden email]> 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-
> >[hidden email]] 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 <[hidden email]>
> >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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use sqlite3_table_column_metadata?

Keith Medcalf

On Sunday, 26 November, 2017 18:00, petern <[hidden email]> wrote:

>First off,  Affinity (aka pragma table_info.type) column is case
>collated, so the LIKE operator should be used:

Yes, but it is always lowercase.  Just as the ObjectType (sqlite_master.type) is always lowercase.

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

Sometimes.  Many languages require "==" for comparisons, so sometimes one gets it the habit.  One can also use IS which will mean that NULL IS NULL returns True (1) whereas NULL == NULL (or NULL = NULL) will return False (0).  In cases where only one of the operands is NULL there is no difference between =, ==, and IS ...

The version below puts a collate nocase against each text column ... and each view should be flattened nicely to boot ...

drop view if exists SysIndexColumns;
drop view if exists SysIndexes;
drop view if exists SysColumns;
drop view if exists SysObjects;

create view if not exists SysObjects
as
select ObjectType collate nocase,
       ObjectName collate nocase
  from (
        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 collate nocase,
       ObjectName collate nocase,
       ColumnID collate nocase,
       ColumnName collate nocase,
       Affinity collate nocase,
       IsNotNull,
       DefaultValue,
       IsPrimaryKey
from (
        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 collate nocase,
       ObjectName collate nocase,
       IndexName collate nocase,
       IndexID,
       IsUniqueIndex collate nocase,
       IndexOrigin collate nocase,
       IsPartialIndex
  from (
        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 collate nocase,
       ObjectName collate nocase,
       IndexName collate nocase,
       IndexColumnSequence,
       ColumnID,
       ColumnName collate nocase,
       IsDescendingOrder,
       Collation collate nocase,
       IsPartOfKey
  from (
        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)
       );




>On Sun, Nov 26, 2017 at 1:41 PM, Keith Medcalf <[hidden email]>
>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-
>> >[hidden email]] 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
><[hidden email]>
>> >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
>> >> [hidden email]
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> >users
>> >>
>> >_______________________________________________
>> >sqlite-users mailing list
>> >[hidden email]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use sqlite3_table_column_metadata?

Bart Smissaert
In reply to this post by petern
> 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 <[hidden email]>
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 <[hidden email]>
> 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-
> > >[hidden email]] 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 <[hidden email]>
> > >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
> > >> [hidden email]
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> > >users
> > >>
> > >_______________________________________________
> > >sqlite-users mailing list
> > >[hidden email]
> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use sqlite3_table_column_metadata?

Bart Smissaert
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 <[hidden email]>
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 <[hidden email]>
> 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 <[hidden email]>
>> 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-
>> > >[hidden email]] 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 <[hidden email]>
>> > >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
>> > >> [hidden email]
>> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> > >users
>> > >>
>> > >_______________________________________________
>> > >sqlite-users mailing list
>> > >[hidden email]
>> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> >
>> >
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use sqlite3_table_column_metadata?

petern
Hi Bart.  Questions like that can be answered by looking at the body of the
function in your copy of the source code.  In this case, the call will
accept null column name and then later set default values for fields like
so:

if( pCol ){
    zDataType = sqlite3ColumnType(pCol,0);
    zCollSeq = pCol->zColl;
    notnull = pCol->notNull!=0;
    primarykey  = (pCol->colFlags & COLFLAG_PRIMKEY)!=0;
    autoinc = pTab->iPKey==iCol && (pTab->tabFlags & TF_Autoincrement)!=0;
  }else{
    zDataType = "INTEGER";
    primarykey = 1;
  }

That else block is originating your phantom values without specifying a
column name but I don't think there is any cosmic significance.  Earlier in
the same function body there are also some comments:

/* Find the column for which info is requested */
  if( zColumnName==0 ){
    /* Query for existance of table only */
  }else{

Be thankful for comments!

Peter

On Mon, Nov 27, 2017 at 5:32 PM, Bart Smissaert <[hidden email]>
wrote:

> 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 <[hidden email]
> >
> 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 <[hidden email]>
> > 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 <[hidden email]>
> >> 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-
> >> > >[hidden email]] 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 <[hidden email]
> >
> >> > >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
> >> > >> [hidden email]
> >> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >> > >users
> >> > >>
> >> > >_______________________________________________
> >> > >sqlite-users mailing list
> >> > >[hidden email]
> >> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> >
> >> >
> >> > _______________________________________________
> >> > sqlite-users mailing list
> >> > [hidden email]
> >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to use sqlite3_table_column_metadata?

Clemens Ladisch
In reply to this post by Bart Smissaert
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 <http://www.sqlite.org/c3ref/table_column_metadata.html>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users