DESC TABLE <table> does seem to word with ODBC
A user wanting to extract table structure (columns, keys, indexes) from an IDS 7.3 database over ODBC found that "DESC TABLE" gave a syntax error. Replies explained that DESC TABLE is not an Informix or standard SQL statement; instead, use the ODBC metadata functions (e.g. SQLPrimaryKeys), or copy the SQL/system-catalog queries from Art Kagel's dbschema replacement 'myschema' (utils2_ak in the IIUG repository), notably print_columns.ec and print_indexes.ec, which show how to read sysindexes/sysindices correctly. The poster accepted this and planned to query sysindexes directly.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, I am in need to extract the database structure using an ODBC connection to my IDS 7.3 database. When i try to execute the SQL commande "desc table mytable" the system does recognise the commande and state a syntax error. How can i get the structure of the base (table, fields and keys) Thanks in advance, Fred
Fred (au boulot) wrote: > I am in need to extract the database structure using an ODBC connection to > my IDS 7.3 database. When i try to execute the SQL commande "desc table > mytable" the system does recognise the commande and state a syntax error. That's because it isn't a command that the Informix database servers recognize, partly because it is not a part of standard SQL. > How can i get the structure of the base (table, fields and keys) By using the metadata enquiry functions provided in the ODBC API. There are a lot of them. I don't have the manual at hand, but SQLPrimaryKey() is a plausble possibility. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
On Mon, 29 Mar 2004 08:02:48 -0500, Fred \\(au boulot\\) wrote:
DESC TABLE is not an IDS or standard SQL command but specific to some
implementations. If you get my dbschema replacement utility, or any of
several script based schema utilities available in the IIUG Software
Repository, in there you will find all of the SQL you need to determine your
database's schema. Myschema is part of the package utils2_ak.
Art S. Kagel
PS - While I appreciate the reasons, most folk will not send you a reply if
you use a no-spam address. This one's written so, I'll be nice.
> Hello,
>
> I am in need to extract the database structure using an ODBC connection to
> my IDS 7.3 database. When i try to execute the SQL commande "desc table
> mytable" the system does recognise the commande and state a syntax error.
>
> How can i get the structure of the base (table, fields and keys)
>
> Thanks in advance,
>
> Fred
Thanks for your answer ... and thank about the advice, i removed the no_spam
:-)
As i am not a confirmed Informix administrator or even developper (the
database was not installed by our company ... i am able only to do
import/export data using ODBC to use in our "home made" softwares) i dont
even know how to use the solution you provided :-)
The information i needed from the DESC command was the description on the
keys and indexes. I will try to extract this information from the sysindexes
table :-)
-------------------------------------------------------------
Fr'd'ri LACROIX
"Gestion des Besoins en Informatique"
France
-------------------------------------------------------------
"Art S. Kagel" <kagel@bloomberg.net> a 'crit dans le message de
news:pan.2004.03.29.15.58.37.248246.1445@bloomberg.net...
> On Mon, 29 Mar 2004 08:02:48 -0500, Fred \\(au boulot\\) wrote:
>
> DESC TABLE is not an IDS or standard SQL command but specific to some
> implementations. If you get my dbschema replacement utility, or any of
> several script based schema utilities available in the IIUG Software
> Repository, in there you will find all of the SQL you need to determine
your
> database's schema. Myschema is part of the package utils2_ak.
>
> Art S. Kagel
>
> PS - While I appreciate the reasons, most folk will not send you a reply
if
> you use a no-spam address. This one's written so, I'll be nice.
>
> > Hello,
> >
> > I am in need to extract the database structure using an ODBC connection
to
> > my IDS 7.3 database. When i try to execute the SQL commande "desc table
> > mytable" the system does recognise the commande and state a syntax
error.
> >
> > How can i get the structure of the base (table, fields and keys)
> >
> > Thanks in advance,
> >
> > Fred
Thanks for your answer; ------------------------------------------------------------- Fr'd'ri LACROIX "Gestion des Besoins en Informatique" France ------------------------------------------------------------- "Jonathan Leffler" <jleffler@earthlink.net> a 'crit dans le message de news:y6X9c.5444$Dv2.904@newsread2.news.pas.earthlink.net... > Fred (au boulot) wrote: > > I am in need to extract the database structure using an ODBC connection to > > my IDS 7.3 database. When i try to execute the SQL commande "desc table > > mytable" the system does recognise the commande and state a syntax error. > > That's because it isn't a command that the Informix database servers > recognize, partly because it is not a part of standard SQL. > > > How can i get the structure of the base (table, fields and keys) > > By using the metadata enquiry functions provided in the ODBC API. > There are a lot of them. I don't have the manual at hand, but > SQLPrimaryKey() is a plausble possibility. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ >
On Tue, 30 Mar 2004 02:22:06 -0500, Fred \\(au boulot\\) wrote: > Thanks for your answer ... and thank about the advice, i removed the no_spam > :-) > > As i am not a confirmed Informix administrator or even developper (the > database was not installed by our company ... i am able only to do > import/export data using ODBC to use in our "home made" softwares) i dont > even know how to use the solution you provided :-) My only point is if you look at the SQL and the data interpretation code in the myschema source, file print_columns.ec and print_indexes.ec, you will see how to fetch the information you need about columns and indexes and how to interpret the information in sysindices (in 9.xx or sysindexes in 7.xx). Especially when it comes to non-std index types and functional keys it's not straight forward. > The information i needed from the DESC command was the description on the > keys and indexes. I will try to extract this information from the sysindexes > table :-) <SNIP> Art S. Kagel
> >"Art S. Kagel" <kagel@bloomberg.net> a �crit dans le message de >news:pan.2004.03.29.15.58.37.248246.1445@bloomberg.net... >> >> PS - While I appreciate the reasons, most folk will not send you a reply >if >> you use a no-spam address. This one's written so, I'll be nice. >> What is so bad in using "no-spam" block? I have a lot of problems when I open my e-mail adress in NG. nebojsa ------------------------------------ Remove spam block (DELETE_) to reply