Hi, guys, On Tue, Jul 19, 2022 at 4:42 AM Walter Dörwald <walter@xxxxxxxxxxxxxx> 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