From: Martin Handsteiner <martin.handsteiner@sibvisions.com>
To: Tom Lane <tgl@sss.pgh.pa.us>, "David G. Johnston"
	<david.g.johnston@gmail.com>
CC: "pgsql-jdbc@lists.postgresql.org" <pgsql-jdbc@lists.postgresql.org>
Subject: AW: stringtype=unspecified is null check problem
Thread-Topic: stringtype=unspecified is null check problem
Thread-Index: 
 AdklFfsFLHgEZ0o5QRejFv2odQbTIAABTL2AACCisKAAIHtkAAABkD8AAADq/oAAAo7CgAAKy3Mw
Date: Thu, 12 Jan 2023 08:32:29 +0000
Message-ID: 
 <VI1PR1001MB1423F7050B1979EAF610D2C8E8FD9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
References: 
 <VI1PR1001MB1423CDC3E737B4763436B730E8FF9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
 <CAKFQuwZA+9+FNovGXA6moj+sDzR6ShyzzP3H77m-05n3dXDv-w@mail.gmail.com>
 <VI1PR1001MB1423C002817FECB26B1AFB3DE8FC9@VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
 <CAKFQuwZrn0F9RyM=WL9Z=pioGX5pwH1+mbBe-jGNnjy_J2E+Jw@mail.gmail.com>
 <784915.1673487222@sss.pgh.pa.us>
 <CAKFQuwYtmLLkmjaKghYXPnBWwvUvsqhZR=9J8KDcLzavf8707w@mail.gmail.com>
 <865715.1673493193@sss.pgh.pa.us>
In-Reply-To: <865715.1673493193@sss.pgh.pa.us>
Accept-Language: de-AT, en-US
Content-Language: de-DE
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?TE69fBKQwMNP+Ul3mJ/J3ix6jj/BxoBwrOppZbIXi1rg/jUUCOvdqDtXUAnG?=
 =?us-ascii?Q?xHAwZkF07rQcl+Ftekc34UF/m+8SUMrQCzJhF4sIayqrDiyz2hHYsGqbUJ4D?=
 =?us-ascii?Q?h/PyL4MKaxYNUePwulaOmfbY94R29WeDCXN9PrLNegY2AHMKWUIR6vwPmdJK?=
 =?us-ascii?Q?4HzW5cCHlQey1LKxhCZmh271Gs+bRh9oHg+lcR8fgCnJPzxLgWu4EaPJq7bP?=
 =?us-ascii?Q?tcRs3fYi5FG+vur1tT7I+yJWUtEvU0a5bHWsYUJdwT1b6729uGLvbgMjrLsw?=
 =?us-ascii?Q?IVhwYEke4CvPPMfWuWRA/NXB2FRBs5Ka10+7MOdqmG+VTiCnd4rAmfJ1/Prd?=
 =?us-ascii?Q?WQu2jp+S+4nsJZDIhduhsHzEAE6IvuZEWilP6kd1wTv5EDQpSk7M/MkRy/iR?=
 =?us-ascii?Q?un+q0OwoUKBXEFrpRRHIaZfGByoem2UnLKNK9KdllEmi+EzRr5votp0IjNSn?=
 =?us-ascii?Q?WI/YaiKRghGfgYp0PGKBQfKWAh7JYU8RIw27AvW50MSML5tW4/OseztqeTQG?=
 =?us-ascii?Q?lgkYIBPzZ+4IyVmqInU+VNJ78vCiYTLglYINm9qzVrsCMCjKfFbjhA56AMVi?=
 =?us-ascii?Q?wlMlPHX7XvjRc482O7U+akh99fFIDUDUAQZqqr4/uYkKDjFgGmzx/1B/UQa7?=
 =?us-ascii?Q?WAFfAh97NfV2uGiUezp7NyZkea66P+lUdN7a827eixkYupbwvKEOmcRA71dK?=
 =?us-ascii?Q?65puPloryYZ4jW5+XDMvz4VYLXLflk8bKG/Ndr7QfoGNj4KqiSxKq/ytcEfK?=
 =?us-ascii?Q?C7MPeHkFvDytglq9/IwSir88vB6oVDueGUpEpMaDQjuMH+yn3nsEfb4GGPjh?=
 =?us-ascii?Q?YhkBgiErNw0Cb7OzAuZoQUrRB+jNE59FYfOi5y/l5K2g/9buD50S8bZdsspo?=
 =?us-ascii?Q?1HSD1Uw4Git2QZFyONqobXGUpZDJpauYLWtTHypJOn+n7easCRbMXo6CgceS?=
 =?us-ascii?Q?DXeCgrM4UPZAAQ7mW2h4vWFsfTF2fBnUfkbx+ZkM5/hTCR16zwfQ2CNAqNu8?=
 =?us-ascii?Q?WbM4FlqzcmTwDzVvxALG5z0bKeEuZmsXauHx7pZiVRVKP9Ml8XxgvdvKA6hw?=
 =?us-ascii?Q?rwzyZInO6b3BpiNi0sbWwl7sl+PO+/ac4arLD/dEpT6Nv5k9MeNlBAIjoqfE?=
 =?us-ascii?Q?iYo/Nfu8qdd0syYeiW9zF3QZcvk7tfdRI+7HXRWhkkSgo2u+SOBgB6bUDWOK?=
 =?us-ascii?Q?rplD3NKK7ebkj9mNGRCF5haNck/B6WJR3v9HzlM/6gFHwEY6EHDvxUiLjY1w?=
 =?us-ascii?Q?9oYAgRp3qhjihtXrDCsIwC0ZprtB3QaVxpUDT6fIcf+Iy0s+HNvQxWC/vTFH?=
 =?us-ascii?Q?RStWpr53BVF+mmf7xhsenisAr2Sg3tSso7veMuDK63094vYmYBJgRKjoC6mX?=
 =?us-ascii?Q?Nmla+s7eRWCGTQb2LwjmLNrVrG8WF31+cVWgIy2yd50JzBJ/uK72nfTVi1XI?=
 =?us-ascii?Q?JEDUzWAdT2ufSfNsVA5udfEVBLbD+z89PZ3kchkKNcmhhObChbfY+r42jQgx?=
 =?us-ascii?Q?XLcsnqoer53Ha5YfTfG+eYOyGF6fjoTQbX5dYsU/eADznJW8jcVUPEF66JeY?=
 =?us-ascii?Q?WAi60wmz6r9Z+g9kXLRL/XKVmuWWCcBC0Hl+a3H6bZv3h42o21gy05/PLcHJ?=
 =?us-ascii?Q?Jut+9nif95SiBpZV/CO473c=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: sibvisions.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: 
 VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 c3febf3e-8127-412c-e42f-08daf4778ae2
X-MS-Exchange-CrossTenant-originalarrivaltime: 12 Jan 2023 08:32:29.4444
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: beb1b6aa-9998-4674-9056-cbc4fe8c7cad
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 owRTx05uBkEGTcCHu7HzifMOWmIsKa99Qld8QoZxuOy0giqDEcVvSjYFiM5OS9o9GbB6zxI00fN1pOjw8ytdRr6abMUZ257iNSfCu2YRoBomgXIrKr+VSl7YHqEDzS+z
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AS4PR10MB5576
List-Id: <pgsql-jdbc.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-jdbc@lists.postgresql.org>
List-Owner: <mailto:pgsql-jdbc-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-jdbc>
Archived-At: 
 <https://www.postgresql.org/message-id/VI1PR1001MB1423F7050B1979EAF610D2C8E8FD9%40VI1PR1001MB1423.EURPRD10.PROD.OUTLOOK.COM>
Precedence: bulk

Thanks a lot, I think, this will help a lot of (java) users, and makes the =
usage of PostgreSQL DB a way better!

I had yesterday an idea for a solution on java side. As we have already a p=
reprocessing with a jdbc wrapper, for function and procedure calls (in/out =
parameters are not working with postgres jdbc), I'll added preprocessing fo=
r the parameters.

I'll search the statement for all question marks (?) that are not inside a =
comment (-- \n, /* */), not in quotes (" ") and not in strings (' '), check=
 the parameter, if it is null, and replace the ? by null, and reduce the pa=
rameter list.

I can link the code if you want, as it is Apache License 2.0.