From: Hime Haieto <himehaieto@gmail.com>
To: pgsql-odbc@postgresql.org
Subject: handling of parametre types for prepared statements
Message-ID: <075c30be-6254-c03e-e551-31018e0b664e@gmail.com>
Date: Sun, 17 Sep 2023 23:13:44 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/075c30be-6254-c03e-e551-31018e0b664e%40gmail.com>
Precedence: bulk

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;