Thread: JDBC prepared insert and X00 and SQL_ASCII

JDBC prepared insert and X00 and SQL_ASCII

From

gmail Vladimir Koković

Date:

16 March 2020, 19:04:35

Hi,

I don't know if this is a bug or the intended mode,
but since ODBC works and JDBC does not, I would ask why JDBC prepared insert does not work if ODBC prepared insert works
in case some varchar field contains 0x00 and DB is SQL_ASCII?

My environment:
[root @ vlada-home ~ 16:32:56] $ psql -h localhost -U vlada asoftdev-ispp-pprostor-beograd
psql (13devel)
Type "help" for help.
asoftdev-ispp-pprostor-beograd = # \ l asoftdev-ispp-pprostor-beograd
                                         List of databases
              Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------------------- + ------- + --------- - + ------------- + ------------- + -------------------
asoftdev-ispp-pprostor-belgrade | vlada | SQL_ASCII | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
asoftdev-ispp-pprostor-beograd = # select version ();
                                    version
-------------------------------------------------- ------------------------------
PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.2.0, 64-bit
(1 row)
asoftdev-ispp-pprostor-beograd = # \ q
[root @ vlada-home ~ 4:34:16 PM] $

cat /root/pglog/postgresql-2020-03-16_000000.log
---
2020-03-16 16:04:18 CET [unknown] 127.0.0.1 (39582) 36708 0 00000 LOG: connection received: host = 127.0.0.1 port = 39582
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: connection authorized: user = vlada database = asoftdev-ispp-pprostor-beograd
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.283 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.017 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: execute <unnamed>: SET extra_float_digits = 3
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.046 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.029 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.010 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.043 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.094 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.019 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: execute <unnamed>: BEGIN
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.201 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.041 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.023 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: execute <unnamed>: SET CLIENT_ENCODING TO SQL_ASCII
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 0.061 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 2.372 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: duration: 1.788 ms
2020-03-16 16:04:18 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: execute <unnamed>: select keyfield, record from public.ispp_group order by keyfield
...
2020-03-16 16:04:41 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 137187 22021 ERROR: invalid byte sequence for encoding "SQL_ASCII": 0x00
2020-03-16 16:04:41 CET asoftdev-ispp-pprostor-belgrade 127.0.0.1 (39582) 36708 137187 22021 STATEMENT: INSERT INTO Sekretariat_2019.ispp_promene VALUES ($1, $2, $3, $4, $5, $6, $7. $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31)
2020-03-16 16:05:15 CET asoftdev-ispp-pprostor-beograd 127.0.0.1 (39582) 36708 0 00000 LOG: disconnection: session time: 0: 00: 57.749 user = vlada database = asoftdev-ispp-pprostor- beograd host = 127.0.0.1 port = 39582

Don't ask me why DB is SQL_ASCII!

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 16, 2020

Re: JDBC prepared insert and X00 and SQL_ASCII

From

gmail Vladimir Koković

Date:

18 March 2020, 15:56:02

Hi,

After a thorough Java-Swig-libpq test, I can confirm that INSERT/SELECT is working properly:
1. server_encoding: SQL_ASCII
2. client_encoding: SQL_ASCII
3. INSERT / SELECT java string with x00

libpq, psql - everything is OK !

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 18, 2020

On 16.3.20. 17:04, gmail Vladimir Koković wrote:

Hi,

I don't know if this is a bug or the intended mode,
but since ODBC works and JDBC does not, I would ask why JDBC prepared insert does not work if ODBC prepared insert works
in case some varchar field contains 0x00 and DB is SQL_ASCII?

Re: JDBC prepared insert and X00 and SQL_ASCII

From

Dave Cramer

Date:

19 March 2020, 17:52:31

On Wed, 18 Mar 2020 at 08:56, gmail Vladimir Koković <vladimir.kokovic@gmail.com> wrote:

Hi,

After a thorough Java-Swig-libpq test, I can confirm that INSERT/SELECT is working properly:
1. server_encoding: SQL_ASCII
2. client_encoding: SQL_ASCII
3. INSERT / SELECT java string with x00

libpq, psql - everything is OK !

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 18, 2020
On 16.3.20. 17:04, gmail Vladimir Koković wrote:
Hi,

I don't know if this is a bug or the intended mode,
but since ODBC works and JDBC does not, I would ask why JDBC prepared insert does not work if ODBC prepared insert works
in case some varchar field contains 0x00 and DB is SQL_ASCII?

I responded on the github issue, but you cannot simply change the client encoding for the JDBC driver. This is not allowed even though there is a setting for allowClientEncodingChanges this is for specific purpose

When using the V3 protocol the driver monitors changes in certain server configuration parameters that should not be touched by end users. The client_encoding setting is set by the driver and should not be altered. If the driver detects a change it will abort the connection. There is one legitimate exception to this behaviour though, using the COPY command on a file residing on the server's filesystem. The only means of specifying the encoding of this file is by altering the client_encoding setting. The JDBC team considers this a failing of the COPY command and hopes to provide an alternate means of specifying the encoding in the future, but for now there is this URL parameter. Enable this only if you need to override the client encoding when doing a copy.

Dave Cramer