Thread: SQLTables does not retrieve everything

Hi,
I am trying to write an application where I need to know the list of
"ALL" tables and views.

However, it looks like the code:

[code]
            ret = SQLTables( m_hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0 );
            if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
            {
                GetErrorMessage( errorMsg, 1 );
                result = 1;
            }
[/code]

only retrieves the tables from the current database/catalog and "public" schema.

There is no tables/view on the INFORMATION_SCHEMA and all the system tables.

Do I have to drop it and use the actual query for that?

Because in MS SQL that code retrieves everything...

I am currently testing with PG9.1 on OSX 10.8 with Xcode 5.

Thank you for any pointers.

Forgot to add:

I'm using iODBC DM with the ODBC driver downloaded from the postgres web site.

Thank you.
On Fri, Nov 30, 2018 at 12:12 AM Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi,
> I am trying to write an application where I need to know the list of
> "ALL" tables and views.
>
> However, it looks like the code:
>
> [code]
>             ret = SQLTables( m_hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0 );
>             if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
>             {
>                 GetErrorMessage( errorMsg, 1 );
>                 result = 1;
>             }
> [/code]
>
> only retrieves the tables from the current database/catalog and "public" schema.
>
> There is no tables/view on the INFORMATION_SCHEMA and all the system tables.
>
> Do I have to drop it and use the actual query for that?
>
> Because in MS SQL that code retrieves everything...
>
> I am currently testing with PG9.1 on OSX 10.8 with Xcode 5.
>
> Thank you for any pointers.

Igor Korot wrote:
>     ret = SQLTables( m_hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0 );
>
> only retrieves the tables from the current database/catalog and "public" schema.
> There is no tables/view on the INFORMATION_SCHEMA and all the system tables.

System tables are hidden by default, unless you specify them explicitly
in the TableType parameter.

The "Show System Tables" data source configuration option can change
this behaviour.


Regards,
Clemens

Or maybe there is a way to set it using SQLSetOption()?

Thank you.

On Sun, Dec 2, 2018 at 12:58 AM Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, Clemens,
>
> Igor Korot wrote:
> >> ret = SQLTables( m_hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0 );
> >>
> >> only retrieves the tables from the current database/catalog and "public" schema.
> >> There is no tables/view on the INFORMATION_SCHEMA and all the system tables.
> >
> >System tables are hidden by default, unless you specify them explicitly
> >in the TableType parameter.
> >
> >The "Show System Tables" data source configuration option can change
> >this behaviour.
>
> I presume I should add:
>
> ShowSystemTable = true
>
> to odbc.ini. Am I right? Or that needs to be spaced out?
>
> On the un-related note, I didn't receive you reply to the list.
> Could you please check my subscription to the list?
>
> Thank you.
>
>
> Regards,
> Clemens

Hi, Clemens,

Igor Korot wrote:
>> ret = SQLTables( m_hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0 );
>>
>> only retrieves the tables from the current database/catalog and "public" schema.
>> There is no tables/view on the INFORMATION_SCHEMA and all the system tables.
>
>System tables are hidden by default, unless you specify them explicitly
>in the TableType parameter.
>
>The "Show System Tables" data source configuration option can change
>this behaviour.

I presume I should add:

ShowSystemTable = true

to odbc.ini. Am I right? Or that needs to be spaced out?

On the un-related note, I didn't receive you reply to the list.
Could you please check my subscription to the list?

Thank you.


Regards,
Clemens

Igor Korot wrote:
>>>> ret = SQLTables( m_hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0 );
>>>>
>>>> only retrieves the tables from the current database/catalog and "public" schema.
>>>> There is no tables/view on the INFORMATION_SCHEMA and all the system tables.
>>>
>>> System tables are hidden by default, unless you specify them explicitly
>>> in the TableType parameter.

E.g.:

  const char *all_types = "SYSTEM TABLE,TABLE,VIEW,FOREIGN TABLE,MATVIEW";
  SQLTables(..., all_types, strlen(all_types));


Regards,
Clemens