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