Horton
Menu

Thread

  1. PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-17T03:04:21Z

    Hi, ALL,
If I want to have strings (aka char *) as parameters fr the query,
should I send UTF-8 strings or something else?

Thank you.

  2. Re: PQexecPrepared() question

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-17T03:08:37Z

    Igor Korot <ikorot01@gmail.com> writes:
> If I want to have strings (aka char *) as parameters fr the query,
> should I send UTF-8 strings or something else?

They should be in whatever is selected as the client_encoding
on your connection.  That goes for query strings too, and whatever
other text a client might send.

			regards, tom lane

  3. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-17T05:53:58Z

    Thx, Tom..

Is there some default value for client_encoding?

Thank you.

On Tue, Dec 16, 2025 at 7:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Igor Korot <ikorot01@gmail.com> writes:
> > If I want to have strings (aka char *) as parameters fr the query,
> > should I send UTF-8 strings or something else?
>
> They should be in whatever is selected as the client_encoding
> on your connection.  That goes for query strings too, and whatever
> other text a client might send.
>
>                         regards, tom lane

  4. Re: PQexecPrepared() question

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-12-17T07:32:21Z

    On Tue, 2025-12-16 at 21:53 -0800, Igor Korot wrote:
> Is there some default value for client_encoding?

The default value for a client connection is whatever the parameter
"client_encoding" was set to in the PostgreSQL server configuration.

But that value can be overridden in several ways:

- explicitly by setting "client_encoding" in the connect string

- with the SQL statement SET by the client

- if the client has "client_encoding=auto", by the encoding set in
  the client's environment

- by setting the PGCLIENTENCODING environment variable

Yours,
Laurenz Albe

  5. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-17T17:38:38Z

    Thank you.


On Tue, Dec 16, 2025 at 11:32 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

> On Tue, 2025-12-16 at 21:53 -0800, Igor Korot wrote:
> > Is there some default value for client_encoding?
>
> The default value for a client connection is whatever the parameter
> "client_encoding" was set to in the PostgreSQL server configuration.
>
> But that value can be overridden in several ways:
>
> - explicitly by setting "client_encoding" in the connect string
>
> - with the SQL statement SET by the client
>
> - if the client has "client_encoding=auto", by the encoding set in
>   the client's environment
>
> - by setting the PGCLIENTENCODING environment variable
>
> Yours,
> Laurenz Albe
>

  6. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-18T15:20:12Z

    Hi,

On Tue, Dec 16, 2025 at 11:32 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

> On Tue, 2025-12-16 at 21:53 -0800, Igor Korot wrote:
> > Is there some default value for client_encoding?
>
> The default value for a client connection is whatever the parameter
> "client_encoding" was set to in the PostgreSQL server configuration.


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?

Thank you.


>
> But that value can be overridden in several ways:
>
> - explicitly by setting "client_encoding" in the connect string
>
> - with the SQL statement SET by the client
>
> - if the client has "client_encoding=auto", by the encoding set in
>   the client's environment
>
> - by setting the PGCLIENTENCODING environment variable
>
> Yours,
> Laurenz Albe
>

  7. Re: PQexecPrepared() question

    David G. Johnston <david.g.johnston@gmail.com> — 2025-12-18T15:40:31Z

    On Thu, Dec 18, 2025 at 8:20 AM Igor Korot <ikorot01@gmail.com> 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.

David J.

  8. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-19T00:10:45Z

    Hi, David,

On Thu, Dec 18, 2025 at 7:41 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Thu, Dec 18, 2025 at 8:20 AM Igor Korot <ikorot01@gmail.com> 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.
>

  9. Re: PQexecPrepared() question

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-12-19T07:43:06Z

    On Thu, 2025-12-18 at 07:20 -0800, Igor Korot 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?

If I read the code correctly:

- if PGCLIENTENCODING is set in the environment of the client executable, that

- otherwise, if "client_encoding" is set on the server, that

- otherwise, SQL_ASCII

Yours,
Laurenz Albe

  10. Fwd: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-19T18:49:26Z

    ---------- Forwarded message ---------
From: Igor Korot <ikorot01@gmail.com>
Date: Fri, Dec 19, 2025 at 9:17 AM
Subject: Re: PQexecPrepared() question
To: Laurenz Albe <laurenz.albe@cybertec.at>


Hi, Lauren’s,

On Thu, Dec 18, 2025 at 11:43 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

> On Thu, 2025-12-18 at 07:20 -0800, Igor Korot 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?
>
> If I read the code correctly:
>
> - if PGCLIENTENCODING is set in the environment of the client executable,
> that


No it is not.


>
> - otherwise, if "client_encoding" is set on the server, that


I guess I is.
But what is the purpose of setting client encoding on the server? It is for
the client

Thank you.


>
> - otherwise, SQL_ASCII
>
> Yours,
> Laurenz Albe
>

  11. Re: Fwd: PQexecPrepared() question

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-19T18:52:14Z

    Igor Korot <ikorot01@gmail.com> writes:
> But what is the purpose of setting client encoding on the server? It is for
> the client

No, it's to tell the server what encoding to transmit to the client
(as well as what encoding strings coming from the client are in).

			regards, tom lane

  12. Re: Fwd: PQexecPrepared() question

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-12-19T19:12:37Z

    On Fri, Dec 19, 2025 at 1:52 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Igor Korot <ikorot01@gmail.com> writes:
> > But what is the purpose of setting client encoding on the server? It is
> for
> > the client
>
> No, it's to tell the server what encoding to transmit to the client
> (as well as what encoding strings coming from the client are in).
>

If there's an encoding mismatch between the server and client, does
"something" (like a PQ driver) convert text in the data stream from the
source encoding to the target encoding?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

  13. Re: Fwd: PQexecPrepared() question

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-19T19:18:15Z

    Ron Johnson <ronljohnsonjr@gmail.com> writes:
> If there's an encoding mismatch between the server and client, does
> "something" (like a PQ driver) convert text in the data stream from the
> source encoding to the target encoding?

The server does that itself.

			regards, tom lane

  14. Re: Fwd: PQexecPrepared() question

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-12-19T21:21:11Z

    On 12/19/25 11:12, Ron Johnson wrote:
> On Fri, Dec 19, 2025 at 1:52 PM Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     Igor Korot <ikorot01@gmail.com <mailto:ikorot01@gmail.com>> writes:
>      > But what is the purpose of setting client encoding on the server?
>     It is for
>      > the client
> 
>     No, it's to tell the server what encoding to transmit to the client
>     (as well as what encoding strings coming from the client are in).
> 
> 
> If there's an encoding mismatch between the server and client, does 
> "something" (like a PQ driver) convert text in the data stream from the 
> source encoding to the target encoding?

It is explained here:

https://www.postgresql.org/docs/current/multibyte.html#MULTIBYTE-AUTOMATIC-CONVERSION
> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!


-- 
Adrian Klaver
adrian.klaver@aklaver.com

  15. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-20T04:10:12Z

    Hi, ALL,

On Fri, Dec 19, 2025 at 9:17 AM Igor Korot <ikorot01@gmail.com> wrote:

> Hi, Lauren’s,
>
> On Thu, Dec 18, 2025 at 11:43 PM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>
>> On Thu, 2025-12-18 at 07:20 -0800, Igor Korot 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?
>>
>> If I read the code correctly:
>>
>> - if PGCLIENTENCODING is set in the environment of the client executable,
>> that
>
>
> No it is not.
>

>
>>
>> - otherwise, if "client_encoding" is set on the server, that
>
>
I just checked the postgres.conf.

This file does not have any client_encoding.



> I guess I is.
> But what is the purpose of setting client encoding on the server? It is
> for the client
>
> Thank you.
>
>
>>
>> - otherwise, SQL_ASCII
>
>
Which means that this is an encoding that will be used.

But then I don’t understand anything.

The code I posted above worked fine on SELECT, but INSERT failed.

If the SQL_ASCII is the encoding used both operations should fail. Or both
succeeds.

Could someone explain what happened?

Thank you.



>>
>> Yours,
>> Laurenz Albe
>>
>

  16. Re: PQexecPrepared() question

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-12-20T06:24:47Z

    On Fri, 2025-12-19 at 20:10 -0800, Igor Korot wrote:
> > > > What is “clientencoding in this case?
> > > 
> > > - if PGCLIENTENCODING is set in the environment of the client executable, that
> > 
> > No it is not.
> > 
> > > - otherwise, if "client_encoding" is set on the server, that
> 
> I just checked the postgres.conf.
> 
> This file does not have any client_encoding.
> 
> > > - otherwise, SQL_ASCII
> 
> Which means that this is an encoding that will be used.

You can verify that with the SQL statement "SHOW client_encoding"
in your sample program.

> But then I don’t understand anything.
> 
> The code I posted above worked fine on SELECT, but INSERT failed.
> 
> If the SQL_ASCII is the encoding used both operations should fail. Or both succeeds.
> 
> Could someone explain what happened?

SQL_ASCII as client encoding means that no conversion will take place.

Still, the database encoding (I suspect UTF8) will govern what can be stored
in the database.  Anything that is not valid UTF-8 will be rejected.

A SELECT will never cause an error - the client will just receive data
in UTF-8.

Yours,
Laurenz Albe

  17. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-20T07:04:03Z

    Hi, Lauren’z,


On Fri, Dec 19, 2025 at 10:24 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

> On Fri, 2025-12-19 at 20:10 -0800, Igor Korot wrote:
> > > > > What is “clientencoding in this case?
> > > >
> > > > - if PGCLIENTENCODING is set in the environment of the client
> executable, that
> > >
> > > No it is not.
> > >
> > > > - otherwise, if "client_encoding" is set on the server, that
> >
> > I just checked the postgres.conf.
> >
> > This file does not have any client_encoding.
> >
> > > > - otherwise, SQL_ASCII
> >
> > Which means that this is an encoding that will be used.
>
> You can verify that with the SQL statement "SHOW client_encoding"
> in your sample program.



Thx, will check.


>
> > But then I don’t understand anything.
> >
> > The code I posted above worked fine on SELECT, but INSERT failed.
> >
> > If the SQL_ASCII is the encoding used both operations should fail. Or
> both succeeds.
> >
> > Could someone explain what happened?
>
> SQL_ASCII as client encoding means that no conversion will take place.
>
> Still, the database encoding (I suspect UTF8) will govern what can be
> stored
> in the database.  Anything that is not valid UTF-8 will be rejected.


Rejected how?


>
> A SELECT will never cause an error - the client will just receive data
> in UTF-8.


And then what?

I’ll check the encoding and report back..

Thank you.


>
> Yours,
> Laurenz Albe
>

  18. Re: PQexecPrepared() question

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-12-20T17:02:13Z

    On Fri, 2025-12-19 at 23:04 -0800, Igor Korot wrote:
> > SQL_ASCII as client encoding means that no conversion will take place.
> > 
> > Still, the database encoding (I suspect UTF8) will govern what can be stored
> > in the database.  Anything that is not valid UTF-8 will be rejected.
> 
> Rejected how?

As you experienced: by throwing an error.

> > A SELECT will never cause an error - the client will just receive data
> > in UTF-8.
> 
> And then what?

The *client application* will receive UTF-8 data.

Yours,
Laurenz Albe

  19. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-21T05:33:23Z

    Hi, Laurenz,

On Fri, Dec 19, 2025 at 10:24 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Fri, 2025-12-19 at 20:10 -0800, Igor Korot wrote:
> > > > > What is “clientencoding in this case?
> > > >
> > > > - if PGCLIENTENCODING is set in the environment of the client executable, that
> > >
> > > No it is not.
> > >
> > > > - otherwise, if "client_encoding" is set on the server, that
> >
> > I just checked the postgres.conf.
> >
> > This file does not have any client_encoding.
> >
> > > > - otherwise, SQL_ASCII
> >
> > Which means that this is an encoding that will be used.
>
> You can verify that with the SQL statement "SHOW client_encoding"
> in your sample program.

I added the following code in my app:

    res = PQexec( m_db, "SHOW client_encoding" );
    auto value = PQgetvalue( res, 0, 0 );
    PQclear( res );

and the value of the "value" variable is "UTF8".
>
> > But then I don’t understand anything.
> >
> > The code I posted above worked fine on SELECT, but INSERT failed.
> >
> > If the SQL_ASCII is the encoding used both operations should fail. Or both succeeds.
> >
> > Could someone explain what happened?
>
> SQL_ASCII as client encoding means that no conversion will take place.
>
> Still, the database encoding (I suspect UTF8) will govern what can be stored
> in the database.  Anything that is not valid UTF-8 will be rejected.
>
> A SELECT will never cause an error - the client will just receive data
> in UTF-8.

The exact error message is:

ERROR: invalid byte sequence for encoding UTF8: 0xdf
CONTEXT: unnamed portal parameter $1

on the INSERT.

Thank you.

>
> Yours,
> Laurenz Albe

  20. Re: PQexecPrepared() question

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-21T05:39:23Z

    Igor Korot <ikorot01@gmail.com> writes:
> I added the following code in my app:
>     res = PQexec( m_db, "SHOW client_encoding" );
>     auto value = PQgetvalue( res, 0, 0 );
>     PQclear( res );
> and the value of the "value" variable is "UTF8".

Okay ...

> The exact error message is:
> ERROR: invalid byte sequence for encoding UTF8: 0xdf
> CONTEXT: unnamed portal parameter $1
> on the INSERT.

client_encoding governs both the encoding that the server will
send, and the encoding that it expects to receive.  You are
sending a parameter string that is not valid UTF8.

			regards, tom lane

  21. Re: PQexecPrepared() question

    David G. Johnston <david.g.johnston@gmail.com> — 2025-12-21T05:47:51Z

    On Thursday, December 18, 2025, Igor Korot <ikorot01@gmail.com> 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.

  22. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-21T07:01:38Z

    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
<david.g.johnston@gmail.com> wrote:
>
>
>
> On Thursday, December 18, 2025, Igor Korot <ikorot01@gmail.com> 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.
>

  23. Re: PQexecPrepared() question

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-12-21T10:10:58Z

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

  24. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-21T11:56:39Z

    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
<david.g.johnston@gmail.com> wrote:
>
>
>
> On Thursday, December 18, 2025, Igor Korot <ikorot01@gmail.com> 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.
>

  25. Re: PQexecPrepared() question

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-12-21T15:34:39Z

    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
> <david.g.johnston@gmail.com> wrote:

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  26. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-21T16:58:07Z

    Hi,

So what is the best way to make it work?

Thank you.


On Sun, Dec 21, 2025 at 7:34 AM Adrian Klaver <adrian.klaver@aklaver.com>
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
> > <david.g.johnston@gmail.com> wrote:
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

  27. Re: PQexecPrepared() question

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-21T17:17:16Z

    Adrian Klaver <adrian.klaver@aklaver.com> 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

  28. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-21T19:44:49Z

    Hi, Tom,

On Sun, Dec 21, 2025 at 9:17 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Adrian Klaver <adrian.klaver@aklaver.com> 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

  29. Re: PQexecPrepared() question

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-12-21T21:19:25Z

    On 2025-12-21 00:39:23 -0500, Tom Lane wrote:
> Igor Korot <ikorot01@gmail.com> writes:
> > I added the following code in my app:
> >     res = PQexec( m_db, "SHOW client_encoding" );
> >     auto value = PQgetvalue( res, 0, 0 );
> >     PQclear( res );
> > and the value of the "value" variable is "UTF8".
> 
> Okay ...
> 
> > The exact error message is:
> > ERROR: invalid byte sequence for encoding UTF8: 0xdf
> > CONTEXT: unnamed portal parameter $1
> > on the INSERT.
> 
> client_encoding governs both the encoding that the server will
> send, and the encoding that it expects to receive.  You are
> sending a parameter string that is not valid UTF8.

As I see it, Igor has two options:

1) Send UTF8. wcstombs is the wrong tool for this, since it uses the
   current locale which is obviously not using UTF-8. I don't program in
   C++, but I'm pretty sure there is a portable way to encode a string
   in UTF-8.
2) Figure out which encoding his locale is using and then set
   client_encoding appropriately. The first step to do that is to find
   the current locale.
   Igor, add
        #include <ctype.h>
        ...
        char *current_locale = setlocale(LC_CTYPE, "");
   to your code. What's the value of current_locale?

I think 1) is the better strategy, but them I've been a UTF-8 fan-boy
for over 30 years ;-).

        hjp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

  30. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-22T01:30:38Z

    Hi,

On Sun, Dec 21, 2025 at 1:19 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2025-12-21 00:39:23 -0500, Tom Lane wrote:
> > Igor Korot <ikorot01@gmail.com> writes:
> > > I added the following code in my app:
> > >     res = PQexec( m_db, "SHOW client_encoding" );
> > >     auto value = PQgetvalue( res, 0, 0 );
> > >     PQclear( res );
> > > and the value of the "value" variable is "UTF8".
> >
> > Okay ...
> >
> > > The exact error message is:
> > > ERROR: invalid byte sequence for encoding UTF8: 0xdf
> > > CONTEXT: unnamed portal parameter $1
> > > on the INSERT.
> >
> > client_encoding governs both the encoding that the server will
> > send, and the encoding that it expects to receive.  You are
> > sending a parameter string that is not valid UTF8.
>
> As I see it, Igor has two options:
>
> 1) Send UTF8. wcstombs is the wrong tool for this, since it uses the
>    current locale which is obviously not using UTF-8. I don't program in
>    C++, but I'm pretty sure there is a portable way to encode a string
>    in UTF-8.
> 2) Figure out which encoding his locale is using and then set
>    client_encoding appropriately. The first step to do that is to find
>    the current locale.
>    Igor, add
>         #include <ctype.h>
>         ...
>         char *current_locale = setlocale(LC_CTYPE, "");
>    to your code. What's the value of current_locale?
>
> I think 1) is the better strategy, but them I've been a UTF-8 fan-boy
> for over 30 years ;-).

Maybe all I need is something like this:

convert(char *dest, const std::wstring src)
{
    const wchar_t *temp = src.c_str();
    while( *dest )
    {
        dest++;
    }
    while( *temp )
    {
        *dest = *temp;
        dest++;
        temp++;
    }
    *dest++ = '\0';
    *dest = '\0';
}

?

Thank you.



>
>         hjp
>
> --
>    _  | Peter J. Holzer    | Story must make more sense than reality.
> |_|_) |                    |
> | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"

  31. Re: PQexecPrepared() question

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-12-22T08:18:57Z

    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

  32. Re: PQexecPrepared() question

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-12-22T09:55:16Z

    On 2025-12-21 17:30:38 -0800, Igor Korot wrote:
> On Sun, Dec 21, 2025 at 1:19 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> > As I see it, Igor has two options:
> >
> > 1) Send UTF8. wcstombs is the wrong tool for this, since it uses the
> >    current locale which is obviously not using UTF-8. I don't program in
> >    C++, but I'm pretty sure there is a portable way to encode a string
> >    in UTF-8.
[...]
> > I think 1) is the better strategy, but them I've been a UTF-8 fan-boy
> > for over 30 years ;-).
> 
> Maybe all I need is something like this:
> 
> convert(char *dest, const std::wstring src)
> {
>     const wchar_t *temp = src.c_str();
>     while( *dest )
>     {
>         dest++;
>     }
>     while( *temp )
>     {
>         *dest = *temp;
>         dest++;
>         temp++;
>     }
>     *dest++ = '\0';
>     *dest = '\0';
> }
> 
> ?

I hope your compiler complains loudly about that code. You can't just
assign a wchar_t to a char and expect it to do anything sensible.

A short search points to std::wstring_convert, but that has been
deprecated in C++17, and it's not obvious to me what the replacement is.

There is always ICU, of course, but that feels like cracking a nut with
a sledgehammer. 

Personally, I might roll my own if an hour of googling doesn't turn up
anything promising (UTF-8 encoding is quite simple), but that shouldn't
be necessary.


        hjp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

  33. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-22T23:07:52Z

    Hi, Peter,

On Mon, Dec 22, 2025 at 1:55 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2025-12-21 17:30:38 -0800, Igor Korot wrote:
> > On Sun, Dec 21, 2025 at 1:19 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> > > As I see it, Igor has two options:
> > >
> > > 1) Send UTF8. wcstombs is the wrong tool for this, since it uses the
> > >    current locale which is obviously not using UTF-8. I don't program in
> > >    C++, but I'm pretty sure there is a portable way to encode a string
> > >    in UTF-8.
> [...]
> > > I think 1) is the better strategy, but them I've been a UTF-8 fan-boy
> > > for over 30 years ;-).
> >
> > Maybe all I need is something like this:
> >
> > convert(char *dest, const std::wstring src)
> > {
> >     const wchar_t *temp = src.c_str();
> >     while( *dest )
> >     {
> >         dest++;
> >     }
> >     while( *temp )
> >     {
> >         *dest = *temp;
> >         dest++;
> >         temp++;
> >     }
> >     *dest++ = '\0';
> >     *dest = '\0';
> > }
> >
> > ?
>
> I hope your compiler complains loudly about that code. You can't just
> assign a wchar_t to a char and expect it to do anything sensible.

At least MSVC 2017 did not.
However, trying didn't solve it. Same error.

>
> A short search points to std::wstring_convert, but that has been
> deprecated in C++17, and it's not obvious to me what the replacement is.
>
> There is always ICU, of course, but that feels like cracking a nut with
> a sledgehammer.

I think ICU IS a replacement for wstring_comvert.
Will check it.

>
> Personally, I might roll my own if an hour of googling doesn't turn up
> anything promising (UTF-8 encoding is quite simple), but that shouldn't
> be necessary.
>
>
>         hjp
>
> --
>    _  | Peter J. Holzer    | Story must make more sense than reality.
> |_|_) |                    |
> | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"

  34. Re: PQexecPrepared() question

    Igor Korot <ikorot01@gmail.com> — 2025-12-25T01:28:23Z

    Hi, ALL,
My current code is below:

    std::wstring query1 = L"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;";
            std::wstring 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;";
        for( int i = 0; i < PQntuples( res ) && !result; 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 ) );
             std::wstring  table_owner = m_pimpl->m_myconv.from_bytes(
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 );
             auto temp = m_pimpl->m_myconv.to_bytes(
paramValues.c_str() ).c_str();
             params[0] = const_cast<char *>(
m_pimpl->m_myconv.to_bytes( paramValues.c_str() ).c_str() );
             params[1] = new char[table.length() + 2];
             memset( params[1], '\0', table.length() + 2 );
             params[1] = const_cast<char *>(
m_pimpl->m_myconv.to_bytes( table.c_str() ).c_str() );
             params[2] = new char[table_owner.length() + 2];
             memset( params[2], '\0', table_owner.length() + 2 );
             params[2] = const_cast<char *>(
m_pimpl->m_myconv.to_bytes( table_owner.c_str() ).c_str() );
             paramFormat[0] = paramFormat[1] = paramFormat[2] = 0;
             paramLength[0] = paramValues.length();
             paramLength[1] = table.length();
             paramLength[2] = table_owner.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;
         }

This code crashes on the PQexecParam.

I am completely puzzled.

Could anyone help?

Thank you.


On Mon, Dec 22, 2025 at 3:07 PM Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi, Peter,
>
> On Mon, Dec 22, 2025 at 1:55 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> >
> > On 2025-12-21 17:30:38 -0800, Igor Korot wrote:
> > > On Sun, Dec 21, 2025 at 1:19 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> > > > As I see it, Igor has two options:
> > > >
> > > > 1) Send UTF8. wcstombs is the wrong tool for this, since it uses the
> > > >    current locale which is obviously not using UTF-8. I don't program in
> > > >    C++, but I'm pretty sure there is a portable way to encode a string
> > > >    in UTF-8.
> > [...]
> > > > I think 1) is the better strategy, but them I've been a UTF-8 fan-boy
> > > > for over 30 years ;-).
> > >
> > > Maybe all I need is something like this:
> > >
> > > convert(char *dest, const std::wstring src)
> > > {
> > >     const wchar_t *temp = src.c_str();
> > >     while( *dest )
> > >     {
> > >         dest++;
> > >     }
> > >     while( *temp )
> > >     {
> > >         *dest = *temp;
> > >         dest++;
> > >         temp++;
> > >     }
> > >     *dest++ = '\0';
> > >     *dest = '\0';
> > > }
> > >
> > > ?
> >
> > I hope your compiler complains loudly about that code. You can't just
> > assign a wchar_t to a char and expect it to do anything sensible.
>
> At least MSVC 2017 did not.
> However, trying didn't solve it. Same error.
>
> >
> > A short search points to std::wstring_convert, but that has been
> > deprecated in C++17, and it's not obvious to me what the replacement is.
> >
> > There is always ICU, of course, but that feels like cracking a nut with
> > a sledgehammer.
>
> I think ICU IS a replacement for wstring_comvert.
> Will check it.
>
> >
> > Personally, I might roll my own if an hour of googling doesn't turn up
> > anything promising (UTF-8 encoding is quite simple), but that shouldn't
> > be necessary.
> >
> >
> >         hjp
> >
> > --
> >    _  | Peter J. Holzer    | Story must make more sense than reality.
> > |_|_) |                    |
> > | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> > __/   | http://www.hjp.at/ |       challenge!"

  35. Re: PQexecPrepared() question

    Laurenz Albe <laurenz.albe@cybertec.at> — 2025-12-25T09:32:08Z

    On Wed, 2025-12-24 at 17:28 -0800, Igor Korot wrote:
> My current code is below:
> 
>    [...]
>    res8 = PQexecPrepared( m_db, "set_table_prop", 3, params, paramLength, paramFormat, 0 );
>    [...]
> 
> This code crashes on the PQexecParam.

Your code does not contain PQexecParam.
It is also incomplete and doesn't contain where the statement is prepared,
nor does it contain the definition of the variables you are using.

I recommend using a debugger.

Yours,
Laurenz Albe