public inbox for [email protected]  

help / color / mirror / Atom feed

From: Hime Haieto <[email protected]>
To: [email protected]
Subject: handling of parametre types for prepared statements
Date: Sun, 17 Sep 2023 23:13:44 -0400
Message-ID: <[email protected]> (raw)

Hello everyone, I came across an issue where the data types for
parametres of my prepared statements were not properly determined at the
time of statement preparation, and that led to me investigating the
matter a bit more closely.  It seems as though the psqlODBC driver
relies solely on inference to determine and set the types for positional
parametres when preparing a statement, and sometimes this can fail,
leading to a parametre getting the OID 0 and being treated as text.

This can lead to errors later when binding variables prior to a given
execution of the statement, and has no particularly satisfactory
solution that I've found, save for taking the hit for reparsing and not
using prepared statements in the first place.  However, when looking
into the matter, I noticed this text from the ODBC API reference:

"Depending on the capabilities of the driver and data source, parameter
information (such as data types) might be checked when the statement is
prepared (if all parameters have been bound) or when it is executed (if
all parameters have not been bound)."

It seems as though the current (16.0) version of the driver does not do
either of these, as if I attempt to bind all the parametres with their
explicit type information prior to calling SQLPrepare, it still relies
on the insufficient inferencing, and under no circumstances I've found
will it defer this determination to its (first?) execution.

The latter option seems as though it *might* not even be possible within
the limits of libpq (save for deferring the actual statement preparation
to the first execution, which might potentially be dubious), but the
former should absolutely be doable.  Thus, I have to wonder if there's a
reason for why psqlODBC does not follow the specification's guidance as
stated above, or if it's simply a matter of nobody having implemented it
as such and could just be awaiting a patch?

For reference, here is a simple query that can be problematic for an
inference-only approach:

with record as (values (5)) insert into mytable(someintvalue) select *
from record;

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: handling of parametre types for prepared statements
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox