Re: Getting the table ID - Mailing list pgsql-general

Hi, guys,

On Tue, Jul 19, 2022 at 4:42 AM Walter Dörwald <walter@livinglogic.de> wrote:
>
> On 19 Jul 2022, at 5:10, Igor Korot wrote:
>
> Hi, guys,
>
> In the database theory each table is identified as "schema_name"."table_name".
>
> When I tried to look at how to get the table id inside the PostgreSQL,
> I saw that I needed to look at the pg_class table.
>
> SELECT oid FROM pg_class WHERE relname = "table_name";
>
> However that query will give a non-unique table id (see the first sentence).
>
> So how do I get the table id based on the "schema_name.table_name"?
>
> There is a pg_namespace table - is this where the schema should come from?
> If yes - how?
> Looking at that table I don't see any unique fields...
> Or is this something that is hidden?
>
> In fact I'm trying to run following query:
>
> SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
> c.relnamespace AND c.relname = ? AND nc.nspname = ?;
>
> from my ODBC based program, but it returns 0 rows on SQLFetch.
>
> I know PostgreSQL does not use '?' for query parameters
> but I thought that since its an ODBC everything should work.
>
> Nevertheless, all bindings were successful, but now rows are returned.
>
> Is this query correct?
>
> Thank you.
>
> That's more or less the same query that I am using:
>
> select
> r.oid as oid,
> n.nspname || '.' || r.relname as name
> from
> pg_catalog.pg_namespace n
> join
> pg_catalog.pg_class r on n.oid = r.relnamespace
> where
> (r.relkind = 'r') and
> (n.nspname not like 'pg_%') and
> (n.nspname != 'information_schema') and
> (n.nspname = 'email') and
> (r.relname = 'emailhistory')
>
> Maybe your problem has to to with uppercase/lowercase schema and/or table names?

Below is my C++ code based on the ODBC library:

[code]
SQLHSTMT stmt = 0;
SQLHDBC hdbc;
SQLLEN cbName, cbTableName = SQL_NTS, cbSchemaName = SQL_NTS;
long id;
int result = 0;
std::wstring query;
SQLWCHAR *qry = NULL, *tname = NULL, *sname = NULL;;
query = L"SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = ? AND nc.nspname = ?;";
qry = new SQLWCHAR[query.length() + 2];
tname = new SQLWCHAR[tableName.length() + 2];
sname = new SQLWCHAR[schemaName.length() + 2];
memset( tname, '\0', tableName.length() + 2 );
memset( sname, '\0', schemaName.length() + 2);
uc_to_str_cpy( sname, schemaName );
uc_to_str_cpy( tname, tableName );
memset( qry, '\0', query.length() + 2 );
uc_to_str_cpy( qry, query );
SQLRETURN retcode = SQLAllocHandle( SQL_HANDLE_DBC, m_env, &hdbc );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 0 );
result = 1;
}
else
{
SQLSMALLINT OutConnStrLen;
retcode = SQLDriverConnect( hdbc, NULL, m_connectString, SQL_NTS,
NULL, 0, &OutConnStrLen, SQL_DRIVER_NOPROMPT );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
{
auto dbName = new SQLWCHAR[pimpl->m_dbName.length() + 2];
memset( dbName, '\0', pimpl->m_dbName.length() + 2 );
uc_to_str_cpy( dbName, pimpl->m_dbName );
retcode = SQLSetConnectAttr( hdbc, SQL_ATTR_CURRENT_CATALOG, dbName, SQL_NTS );
delete[] dbName;
dbName = nullptr;
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2 );
result = 1;
retcode = SQLEndTran( SQL_HANDLE_DBC, m_hdbc, SQL_ROLLBACK );
}
else
{
retcode = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
{
retcode = SQLPrepare( stmt, qry, SQL_NTS );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
SQLSMALLINT dataType[2], decimalDigit[2], nullable[2];
SQLULEN parameterSize[2];
retcode = SQLDescribeParam( stmt, 1, &dataType[0], ¶meterSize[0],
&decimalDigit[0], &nullable[0] );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLBindParameter( stmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT,
dataType[0], parameterSize[0], decimalDigit[0], tname, 0, &cbTableName
);
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
}
if( !result )
{
retcode = SQLDescribeParam( stmt, 2, &dataType[1], ¶meterSize[1],
&decimalDigit[1], &nullable[1] );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLBindParameter( stmt, 2, SQL_PARAM_INPUT, SQL_C_DEFAULT,
dataType[1], parameterSize[1], decimalDigit[1], sname, 0,
&cbSchemaName );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
}
}
if( !result )
{
retcode = SQLExecute( stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLBindCol( stmt, 1, SQL_C_SLONG, &id, 100, &cbName );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLFetch( stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO &&
retcode != SQL_NO_DATA )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else if( retcode == SQL_NO_DATA )
tableId = 0;
}
}
}
}
}
}
}
}
delete[] qry;
qry = NULL;
delete[] tname;
tname = NULL;
delete[] sname;
sname = NULL;
if( stmt )
{
retcode = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
stmt = 0;
retcode = SQLDisconnect( hdbc );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
{
retcode = SQLFreeHandle( SQL_HANDLE_DBC, hdbc );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
hdbc = 0;
}
}
}
return result;
[/code]

Every single SQLXXX() call is successful (returns 0 - SQL_SUCCESS),
except SQLFetch(),
which returns 100 (SQL_NO_DATA).

Can you spot an error?

Thank you.

>
> Servus,
> Walter