public inbox for [email protected]help / color / mirror / Atom feed
Re: PQexecPrepared() question 10+ messages / 6 participants [nested] [flat]
* Re: PQexecPrepared() question @ 2025-12-19 00:10 Igor Korot <[email protected]> 0 siblings, 2 replies; 10+ messages in thread From: Igor Korot @ 2025-12-19 00:10 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Hi, David, On Thu, Dec 18, 2025 at 7:41 AM David G. Johnston <[email protected]> wrote: > > On Thu, Dec 18, 2025 at 8:20 AM Igor Korot <[email protected]> wrote: >> >> Imagine following scenario: >> >> I have 2 machines. One is running PG server on *nix. Second is my app on Windows. >> >> An application starts for the first time. >> >> What is “clientencoding in this case? > > > This day in age, probably UTF-8; which is what most servers are initialized using. If you aren't having issues with encoding I suggest you just take for granted that the defaults work in 99% of the cases. If you are having issues, share the details. My code: for( int i = 0; i < PQntuples( res ); i++ ) { std::wstring cat = m_pimpl->m_myconv.from_bytes( PQgetvalue( res, i, 0 ) ); std::wstring schema = m_pimpl->m_myconv.from_bytes( PQgetvalue( res, i, 1 ) ); std::wstring table = m_pimpl->m_myconv.from_bytes( PQgetvalue( res, i, 2 ) ); char *table_owner = PQgetvalue( res, i, 3 ); pimpl.m_tableDefinitions[cat].push_back( TableDefinition( cat, schema, table ) ); count++; paramValues = schema + L"." + table; params[0] = new char[paramValues.length() + 2]; memset( params[0], '\0', paramValues.length() + 2 ); std::wcstombs( params[0], paramValues.c_str(), paramValues.length() ); params[1] = new char[table.length() + 2]; memset( params[1], '\0', table.length() + 2 ); std::wcstombs( params[1], table.c_str(), table.length() ); params[2] = new char[schema.length() + 2]; memset( params[2], '\0', schema.length() + 2 ); std::wcstombs( params[2], schema.c_str(), schema.length() + 2 ); paramFormat[0] = paramFormat[1] = paramFormat[2] = 0; paramLength[0] = paramValues.length(); paramLength[1] = table.length(); paramLength[2] = schema.length(); res8 = PQexecPrepared( m_db, "set_table_prop", 3, params, paramLength, paramFormat, 0 ); if( PQresultStatus( res8 ) != PGRES_COMMAND_OK ) { std::wstring err = m_pimpl->m_myconv.from_bytes( PQerrorMessage( m_db ) ); errorMsg.push_back( L"Error executing query: " + err ); result = 1; } PQclear( res8 ); delete[] params[0]; params[0] = nullptr; delete[] params[1]; params[1] = nullptr; delete[] params[2]; params[2] = nullptr; } And ths s what I have n the DB: table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action ---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+- -----------------------+--------------------+----------+--------------- draft | public | abcß | BASE TABLE | | | | | | YES | NO | Using my setup above and assumng my Win locale is en_US.UTF8 I can successfullly retreve that record but the insertion fails with Invalid byte sequence for parameter $1 in UTF8 Thank you. > > David J. > ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-21 05:47 David G. Johnston <[email protected]> parent: Igor Korot <[email protected]> 1 sibling, 2 replies; 10+ messages in thread From: David G. Johnston @ 2025-12-21 05:47 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Thursday, December 18, 2025, Igor Korot <[email protected]> wrote: > > Using my setup above and assumng my Win locale is en_US.UTF8 This seems like an incorrect assumption. The server is expecting UTF8 yet you are passing it data that is not valid UTF8 bytes. I infer thus your Windows encoding is thus not UTF8. Suggest you make an even smaller, simpler, and self-contained reproducer and include how you are executing the code. You’ve not provided sufficient detail for someone else to debug your problem. David J. ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-21 07:01 Igor Korot <[email protected]> parent: David G. Johnston <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Igor Korot @ 2025-12-21 07:01 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Below is my select statement: SELECT t.table_catalog AS catalog, t.table_schema AS schema, t.table_name AS table, u.usename AS owner, c.oid AS table_id FROM information_schema.tables t, pg_catalog.pg_class c, pg_catalog.pg_user u WHERE t.table_name = c.relname AND c.relowner = usesysid AND (t.table_type = 'BASE TABLE' OR t.table_type = 'VIEW' OR t.table_type = 'LOCAL TEMPORARY') ORDER BY table_name; and INSERT statement: query7 = L"INSERT INTO \"abcattbl\" VALUES( 0, $1, (SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid = c.relnamespace AND c.relname = $2 AND nc.nspname = $3), \'\', 8, 400, \'N\', \'N\', 0, 1, 0, \'MS Sans Serif\', 8, 400, \'N\', \'N\', 0, 1, 0, \'MS Sans Serif\', 8, 400, \'N\', \'N\', 0, 1, 0, \'MS Sans Serif\', \'\' ) ON CONFLICT DO NOTHING;"; Thank you On Sat, Dec 20, 2025 at 9:47 PM David G. Johnston <[email protected]> wrote: > > > > On Thursday, December 18, 2025, Igor Korot <[email protected]> wrote: >> >> >> Using my setup above and assumng my Win locale is en_US.UTF8 > > > This seems like an incorrect assumption. The server is expecting UTF8 yet you are passing it data that is not valid UTF8 bytes. I infer thus your Windows encoding is thus not UTF8. > > Suggest you make an even smaller, simpler, and self-contained reproducer and include how you are executing the code. You’ve not provided sufficient detail for someone else to debug your problem. > > David J. > ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-21 10:10 Peter J. Holzer <[email protected]> parent: Igor Korot <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Peter J. Holzer @ 2025-12-21 10:10 UTC (permalink / raw) To: [email protected] On 2025-12-18 18:10:45 -0600, Igor Korot wrote: > std::wcstombs( params[1], table.c_str(), table.length() ); I assume that the C++ function std::wcstombs() is just a wrapper around the C function wcstombs(). According the the C standard: | The behavior of the multibyte character functions is affected by the | LC_CTYPE category of the current locale. So you need to know what your current locale is. You can find out (somewhat unintuitively) by calling setlocale(). > Invalid byte sequence for parameter $1 in UTF8 So your locale is probably not using UTF8. hjp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | [email protected] | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-21 11:56 Igor Korot <[email protected]> parent: David G. Johnston <[email protected]> 1 sibling, 1 reply; 10+ messages in thread From: Igor Korot @ 2025-12-21 11:56 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Hi, David, Attached is a script I ran on a server (from psql). The offending line is: CREATE TABLE abcß(id integer primary key, name text); Script ran successfully. Using the ODBC driver everything works. I didn't change anything in the driver setup. Same setup - Windows client connecting to Linux server. Any other info needed - please do tell. Thank you. On Sat, Dec 20, 2025 at 11:47 PM David G. Johnston <[email protected]> wrote: > > > > On Thursday, December 18, 2025, Igor Korot <[email protected]> wrote: >> >> >> Using my setup above and assumng my Win locale is en_US.UTF8 > > > This seems like an incorrect assumption. The server is expecting UTF8 yet you are passing it data that is not valid UTF8 bytes. I infer thus your Windows encoding is thus not UTF8. > > Suggest you make an even smaller, simpler, and self-contained reproducer and include how you are executing the code. You’ve not provided sufficient detail for someone else to debug your problem. > > David J. > Attachments: [application/sql] postgresql draft script.sql (7.0K, 2-postgresql%20draft%20script.sql) download ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-21 15:34 Adrian Klaver <[email protected]> parent: Igor Korot <[email protected]> 0 siblings, 2 replies; 10+ messages in thread From: Adrian Klaver @ 2025-12-21 15:34 UTC (permalink / raw) To: Igor Korot <[email protected]>; David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 12/21/25 03:56, Igor Korot wrote: > Hi, David, > > Attached is a script I ran on a server (from psql). > > The offending line is: > > CREATE TABLE abcß(id integer primary key, name text); > > Script ran successfully. > > Using the ODBC driver everything works. I didn't change anything in > the driver setup. > Same setup - Windows client connecting to Linux server. It is not the same setup, in your original question you are hand writing code not running a script through the ODBC driver. Best bet is the ODBC driver is picking up the client(Windows) encoding and passing it to the server. Crank up the logging on both ends, the ODBC driver and the Postgres server and see what is hitting the server when you use the driver. > > Any other info needed - please do tell. > > Thank you. > > > On Sat, Dec 20, 2025 at 11:47 PM David G. Johnston > <[email protected]> wrote: -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-21 16:58 Igor Korot <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Igor Korot @ 2025-12-21 16:58 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: David G. Johnston <[email protected]>; Laurenz Albe <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Hi, So what is the best way to make it work? Thank you. On Sun, Dec 21, 2025 at 7:34 AM Adrian Klaver <[email protected]> wrote: > On 12/21/25 03:56, Igor Korot wrote: > > Hi, David, > > > > Attached is a script I ran on a server (from psql). > > > > The offending line is: > > > > CREATE TABLE abcß(id integer primary key, name text); > > > > Script ran successfully. > > > > Using the ODBC driver everything works. I didn't change anything in > > the driver setup. > > Same setup - Windows client connecting to Linux server. > > It is not the same setup, in your original question you are hand writing > code not running a script through the ODBC driver. Best bet is the ODBC > driver is picking up the client(Windows) encoding and passing it to the > server. Crank up the logging on both ends, the ODBC driver and the > Postgres server and see what is hitting the server when you use the driver. > > > > > Any other info needed - please do tell. > > > > Thank you. > > > > > > On Sat, Dec 20, 2025 at 11:47 PM David G. Johnston > > <[email protected]> wrote: > > -- > Adrian Klaver > [email protected] > ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-21 17:17 Tom Lane <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 1 reply; 10+ messages in thread From: Tom Lane @ 2025-12-21 17:17 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Igor Korot <[email protected]>; David G. Johnston <[email protected]>; Laurenz Albe <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Adrian Klaver <[email protected]> writes: > It is not the same setup, in your original question you are hand writing > code not running a script through the ODBC driver. Best bet is the ODBC > driver is picking up the client(Windows) encoding and passing it to the > server. It might be the other way around. psql absolutely will try to infer an encoding from its environment, but perhaps the ODBC driver does not, or does it differently. In any case, "SHOW client_encoding" in both the working and non-working contexts would yield useful information. regards, tom lane ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-21 19:44 Igor Korot <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Igor Korot @ 2025-12-21 19:44 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; David G. Johnston <[email protected]>; Laurenz Albe <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Hi, Tom, On Sun, Dec 21, 2025 at 9:17 AM Tom Lane <[email protected]> wrote: > > Adrian Klaver <[email protected]> writes: > > It is not the same setup, in your original question you are hand writing > > code not running a script through the ODBC driver. Best bet is the ODBC > > driver is picking up the client(Windows) encoding and passing it to the > > server. > > It might be the other way around. psql absolutely will try to infer > an encoding from its environment, but perhaps the ODBC driver does > not, or does it differently. > > In any case, "SHOW client_encoding" in both the working and > non-working contexts would yield useful information. Adding following code: SQLHSTMT stmt; SQLWCHAR value[25]; ret = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &stmt ); ret = SQLExecDirect( stmt, L"SHOW client_encoding", SQL_NTS ); ret = SQLBindCol( stmt, 1, SQL_C_WCHAR, &value, 25, 0 ); ret = SQLFetch( stmt ); ret = SQLFreeHandle( SQL_HANDLE_STMT, stmt ); the value of the "value" variable is still "UTF8". Thank you. > > regards, tom lane ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PQexecPrepared() question @ 2025-12-22 08:18 Laurenz Albe <[email protected]> parent: Igor Korot <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Laurenz Albe @ 2025-12-22 08:18 UTC (permalink / raw) To: Igor Korot <[email protected]>; Tom Lane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Sun, 2025-12-21 at 11:44 -0800, Igor Korot wrote: > Adding following code: > > SQLHSTMT stmt; > SQLWCHAR value[25]; > ret = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &stmt ); > ret = SQLExecDirect( stmt, L"SHOW client_encoding", SQL_NTS ); > ret = SQLBindCol( stmt, 1, SQL_C_WCHAR, &value, 25, 0 ); > ret = SQLFetch( stmt ); > ret = SQLFreeHandle( SQL_HANDLE_STMT, stmt ); > > the value of the "value" variable is still "UTF8". The ODBC driver comes in a "unicode" and an "ANSI" flavor. If you are using the "unicode" driver, it will always set the client encoding to UTF8. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-12-22 08:18 UTC | newest] Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-12-19 00:10 Re: PQexecPrepared() question Igor Korot <[email protected]> 2025-12-21 05:47 ` David G. Johnston <[email protected]> 2025-12-21 07:01 ` Igor Korot <[email protected]> 2025-12-21 11:56 ` Igor Korot <[email protected]> 2025-12-21 15:34 ` Adrian Klaver <[email protected]> 2025-12-21 16:58 ` Igor Korot <[email protected]> 2025-12-21 17:17 ` Tom Lane <[email protected]> 2025-12-21 19:44 ` Igor Korot <[email protected]> 2025-12-22 08:18 ` Laurenz Albe <[email protected]> 2025-12-21 10:10 ` Peter J. Holzer <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox