Thread: BUG #5180: How to get only User created tables by using SQLTables() in ODBC

The following bug has been logged online:

Bug reference:      5180
Logged by:          Jitendra Lenka
Email address:      jitendra.lenka@csqldb.com
PostgreSQL version: 8.3.3
Operating system:   Fedora10
Description:        How to get only User created tables by using SQLTables()
in ODBC
Details:

Hi,

I created a table named "t1" in postgres. Used SQLTables() function in ODBC
to retrieve the table "t1" only. But it retrieves many System and Metadata
tables which bothers me.

Follow below to get all system tables along with user created table "t1".

sql_features
sql_implementation_info
sql_languages
sql_packages
sql_parts
sql_sizing
sql_sizing_profiles
t1

Can I get only user created table ?

If anybody find it good please report it in my mail ID.

Your reply is highly solicited.

Please find the code:
--------------------
retValue=SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, (SQLCHAR*) "TABLE",
SQL_NTS);

while (SQL_SUCCEEDED(retValue = SQLFetch(hstmt))) {
        SQLUSMALLINT i;
        i=3;
        if(i<=columns){
            SQLINTEGER indicator;
            char buf[512];
            retValue=SQLGetData(hstmt, i, SQL_C_CHAR,
                         buf, sizeof(buf), &indicator);
            if (SQL_SUCCEEDED(retValue)) {
                 if (indicator == SQL_NULL_DATA) strcpy(buf, "NULL");
                 else{
                         printf("table:%s\n",buf);

                  }
             }
          }
      }

2009/11/12 Jitendra Lenka <jitendra.lenka@csqldb.com>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A05180
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Jitendra Lenka
> Email address: =C2=A0 =C2=A0 =C2=A0jitendra.lenka@csqldb.com
> PostgreSQL version: 8.3.3
> Operating system: =C2=A0 Fedora10
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0How to get only User created tabl=
es by using SQLTables()
> in ODBC
> Details:
>
> Hi,
>
> I created a table named "t1" in postgres. Used SQLTables() function in OD=
BC
> to retrieve the table "t1" only. But it retrieves many System and Metadata
> tables which bothers me.
>
> Follow below to get all system tables along with user created table "t1".
>
> sql_features
> sql_implementation_info
> sql_languages
> sql_packages
> sql_parts
> sql_sizing
> sql_sizing_profiles
> t1
>
> Can I get only user created table ?

Hello,

look on information_schema, please.

postgres=3D# \dt
No relations found.
postgres=3D# create table omega(a integer);
CREATE TABLE
postgres=3D# \x

postgres=3D# SELECT *
                       FROM information_schema.tables
                     WHERE table_schema NOT IN
('pg_catalog','information_schema');
-[ RECORD 1 ]----------------+-----------
table_catalog                | postgres
table_schema                 | public
table_name                   | omega
table_type                   | BASE TABLE
self_referencing_column_name |
reference_generation         |
user_defined_type_catalog    |
user_defined_type_schema     |
user_defined_type_name       |
is_insertable_into           | YES
is_typed                     | NO
commit_action

Regards
Pavel Stehule

>
> If anybody find it good please report it in my mail ID.
>
> Your reply is highly solicited.
>
> Please find the code:
> --------------------
> retValue=3DSQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, (SQLCHAR*) "TABLE",
> SQL_NTS);
>
> while (SQL_SUCCEEDED(retValue =3D SQLFetch(hstmt))) {
> =C2=A0 =C2=A0 =C2=A0 =C2=A0SQLUSMALLINT i;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0i=3D3;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0if(i<=3Dcolumns){
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SQLINTEGER indicator;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0char buf[512];
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0retValue=3DSQLGetData(hstmt, i, =
SQL_C_CHAR,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 buf, sizeof(buf), &indicator);
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (SQL_SUCCEEDED(retValue)) {
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 if (indicator =3D=
=3D SQL_NULL_DATA) strcpy(buf, "NULL");
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 else{
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 printf("table:%s\n",buf);
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0}
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 }
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0}
> =C2=A0 =C2=A0 =C2=A0}
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Hi,

> Please find the code:
> --------------------
> retValue=SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, (SQLCHAR*) "TABLE",
> SQL_NTS);
>

You should pass the schemaname to the above call to restrict the
result set to a specific schema. For example:

SQLTables(hstmt, NULL, 0, (SQLCHAR *)"public", SQL_NTS, NULL, 0,
(SQLCHAR*) "TABLE", SQL_NTS);

Regards,
Nikhils
--
http://www.enterprisedb.com