Subject: Re: PQexecPrepared() question

From: Igor Korot <[email-masked]>
Date: Thu, 18 Dec 2025 18:10:45 -0600

Hi, David,

On Thu, Dec 18, 2025 at 7:41 AM David G. Johnston
<[email-masked]> wrote:
>
> On Thu, Dec 18, 2025 at 8:20 AM Igor Korot <[email-masked]> 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.

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 20 Dec 2025 22:47:51 -0700

On Thursday, December 18, 2025, Igor Korot <[email-masked]> 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.

---

From: Igor Korot <[email-masked]>
Date: Sat, 20 Dec 2025 23:01:38 -0800

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-masked]> wrote:
> 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.
>

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 21 Dec 2025 11:10:58 +0100

>              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-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Igor Korot <[email-masked]>
Date: Sun, 21 Dec 2025 05:56:39 -0600

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.

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 21 Dec 2025 07:34:39 -0800

> 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.

-- 
Adrian Klaver
[email-masked]

---

From: Igor Korot <[email-masked]>
Date: Sun, 21 Dec 2025 08:58:07 -0800

Hi,

So what is the best way to make it work?

Thank you.

---

From: Tom Lane <[email-masked]>
Date: Sun, 21 Dec 2025 12:17:16 -0500

Adrian Klaver <[email-masked]> writes:
> 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

---

From: Igor Korot <[email-masked]>
Date: Sun, 21 Dec 2025 11:44:49 -0800

Hi, Tom,

> 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.

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 22 Dec 2025 09:18:57 +0100

> 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