Re: How to fix psqlODBC driver regression to get error message details

Hi Andrus,

Could you please try the test drivers on Windows 12.01.0100 at
https://winpg.jp/~inoue/psqlodbc/index.html
<https://link.getmailspring.com/link/827528D4-F338-402D-B793-8695C80712C1(at)getmailspring(dot)com/0?redirect=https%3A%2F%2Flink.getmailspring.com%2Flink%2F59D870E8-5ACB-407C-9724-BFF629FF2DD7%40getmailspring.com%2F1%3Fredirect%3Dhttps%253A%252F%252Fwinpg.jp%252F~inoue%252Fpsqlodbc%252Findex.html%26recipient%3DaC1pbm91ZUBkcmVhbS5lbWFpbC5uZS5qcA%253D%253D&recipient=aC1pbm91ZUBkcmVhbS5lbWFpbC5uZS5qcA%3D%3D>
?
You can turn on the *Display Optional Error Message* option
using ODBC Data Source Adminirator (Advanced Options page 2).

regards,
Hiroshi Inoue

On 2020/02/27 15:23, Andrus wrote:
> Hi!
>
> Yes.
> I have done this earlier for previous issue where error message was
> always the same after moving to libpq and also approx 10 years ago for
> another issue.
>
> In pgsql odbc source code file connection.c: line 866
>
> contains:
>
> errprimary = PQresultErrorField(pgres, PG_DIAG_MESSAGE_PRIMARY);
>
> which gets only primary error message.
>
> To get error message detail,  PG_DIAG_MESSAGE_DETAIL should used
> according
> to https://www.postgresql.org/docs/current/libpq-exec.html.
> PG_DIAG_MESSAGE_DETAIL  is not used in pgsql-odbc source code.
>
> I have large number of schemas all containing same table names.
> Error message and even error message detail show only table name
> without schema name.
> This makes difficult to diagnore errors.
>
> According to docs there is parameter
>
> PG_DIAG_SCHEMA_NAME
>
> If the error was associated with a specific database object, the name
> of the schema containing that object, if any.
>
> Can you add this also to error message if this provides schema name or
> error.
>
> Also following fields would be useful in error message:
>
> PG_DIAG_MESSAGE_HINT
> PG_DIAG_STATEMENT_POSITION
> PG_DIAG_INTERNAL_QUERY
> PG_DIAG_CONTEXT
> PG_DIAG_COLUMN_NAME
> PG_DIAG_DATATYPE_NAME
>
> Can you provide a way to get them also in error message.
>
> Andrus.
>
> -----Algsõnum----- From: Inoue, Hiroshi
> Sent: Thursday, February 27, 2020 4:43 AM
> To: Andrus
> Cc: pgsql-odbc(at)postgresql(dot)org
> Subject: Re: How to fix psqlODBC driver regression to get error
> message details
>
> Hi Andrus,
>
> Could you try the test driver if I provide a test driver on Windows?
>
> regards,
> Hiroshi Inoue
>
> On 2020/02/23 7:43, Andrus wrote:
>> Hi!
>>
>> I'm looking for a way to fix psqlODBC driver regression.
>>
>> Starting at psqlODBC 09.05.0100 when psqlODBC driver uses libpq for all
>> operations (earlier versions used libpg only for authentication) ODBC
>> client
>> does not show error message details.
>>
>> For example, users got only generic error message like
>>
>>    Connectivity error: ERROR: insert or update on table "mytable"
>> violates
>>    foreign key constraint  "mytable_myfield_fkey
>>
>> Error message details like
>>
>>    Key (myfield)=(mykeyvalue) is not present in table "mymastertable".;
>>
>> are no more returned.
>>
>> How to detailed error message or at least only bad key value
>> "mykeyvalue"
>> which is returned in error message details ?
>>
>> Some ideas:
>>
>> 1. First versions of libpq based ODBC drivers returned same error
>> message "no resource found" for all errors. Hiroshi has fixed it it
>> later versions. Maybe psqlODBC code can fixed to restore pre-libpq
>> behaviour.
>>
>> 2. Maybe analyzing odbc logs from pre and after 09.05.0100 drivers
>> may provide solution. I can provide ODBC log files.
>>
>> 3. psqlODBC allows to pass parameters to libpq from connection
>> string. Maybe some libpq parameter can fix this.
>>
>> 4.  Maybe some Postgres query, postgres extension  or direct odbc or
>> libpq call can used to return last error message details like Windows
>> API GetLastError() or Unix global errno.
>>
>> 5. Maybe it is possible to to create method which returns error
>> message detals from postgres log file.
>>
>> Postgres 12.2 and latest psqlODBC driver 12.01.0000 are used.
>> psqlODBC is called from Visual FoxPro
>>
>> Andrus.
>>
>> Posted also in
>>
>> https://stackoverflow.com/questions/60357505/how-to-fix-psqlodbc-driver-regression-to-get-error-message-details
>>
>>
>> and in pgsql-general mailist list.
>