Thread: psqlODBC suggestions
Several weeks ago, I sent a message to this list (included below).
I would appreciate it if someone on this list would acknowledge
that the message was received.
Thank you,
Robert Willis
From: Robert Willis
Sent: Monday, July 03, 2023 1:20 PM
To: pgsql-odbc@postgresql.org
Subject: ODBC driver changes
To Whom It May Concern:
Please consider making the following changes to the psqlODBC driver.
Items 1 through 3 (the VARBIT and JSON-related changes) should be
non-controversial – I just think someone forgot to put them in the code.
- In pgtypes.h, please add these definitions:
#define PG_TYPE_JSON 114
#define PG_TYPE_VARBIT 1562
You might also consider adding this if it is correct:
#define PG_TYPE_JSONB 3802
- In info.c, add this line immediately after the line containing the
only occurrence of PG_TYPE_BIT and right before the “break” line in the file:
case PG_TYPE_VARBIT:
- In columninfo.c, add these lines immediately after the line containing the
only occurrence of PG_TYPE_TIME_WITH_TMZONE and right before the “break”
line in the file:
case PG_TYPE_BIT:
case PG_TYPE_VARBIT:
- In info.c, in the function PGAPI_ProcedureColumns() at line 5121 (in the version
15.00 sources) the following code is added to the query to restrict the answer
to executable items:
appendPQExpBuffer(&proc_query,
" has_function_privilege(p.oid, 'EXECUTE')");
I suggest changing this to the following In order to truly make the function return info about actual procedures:
appendPQExpBuffer(&proc_query,
" p.prokind = 'p' and"
" has_function_privilege(p.oid, 'EXECUTE')");
- In configure, line 5016 (in the version 15.00 sources) has this:
CPPFLAGS="$CPPFLAGS -I$with_libpq/include - I$with_libpq/include/postgresql/internal"
I think that really ought to be this:
CPPFLAGS="$CPPFLAGS -I$with_libpq/include -I$with_libpq/include/internal"
- In connection.c, I think you should make this change. The comment explains my reasoning.
Change these lines from:
appendPQExpBufferStr(&query_buf, query);
if (appendq)
{
appendPQExpBuffer(&query_buf, ";%s", appendq);
}
if (query_rollback)
{
appendPQExpBuffer(&query_buf, ";%s %s", rlscmd, per_query_svp);
}
to:
appendPQExpBufferStr(&query_buf, query);
/* prepend newline too, in case user SQL ends in a */
/* dash-dash comment without a final newline character. Otherwise, */
/* the appended SQL statement gets treated as part of the comment. */
if (appendq)
{
appendPQExpBuffer(&query_buf, "\n;%s", appendq);
}
if (query_rollback)
{
appendPQExpBuffer(&query_buf, "\n;%s %s", rlscmd, per_query_svp);
}
- In odbcapi30.c, in SQLEndTran(), I think it should return SQL_ERROR if the connection is NOT in auto-commit mode.
Thus, in the SQL_HANDLE_DBC case inside that function, these two lines:
CC_clear_error((ConnectionClass *) Handle);
ret = PGAPI_Transact(SQL_NULL_HENV, Handle, CompletionType);
should be:
if (((ConnectionClass *)Handle)->__error_number == CONNECTION_SERVER_REPORTED_SEVERITY_FATAL
&& (CC_loves_visible_trans( (ConnectionClass *)Handle )) )
ret = SQL_ERROR;
else
{
CC_clear_error((ConnectionClass *) Handle);
ret = PGAPI_Transact(SQL_NULL_HENV, Handle, CompletionType);
}
I would be interested in getting your opinion on my suggestions, and hope the changes merit inclusion in the next release.
Sincerely,
Robert Willis
NOTICE from Ab Initio: This email (including any attachments) may contain information that is subject to confidentiality obligations or is legally privileged, and sender does not waive confidentiality or privilege. If received in error, please notify the sender, delete this email, and make no further use, disclosure, or distribution.
Several weeks ago, I sent a message to this list (included below).
I would appreciate it if someone on this list would acknowledge
that the message was received.
Thank you,
Robert Willis
From: Robert Willis
Sent: Monday, July 03, 2023 1:20 PM
To: pgsql-odbc@postgresql.org
Subject: ODBC driver changes
To Whom It May Concern:
Please consider making the following changes to the psqlODBC driver.
Items 1 through 3 (the VARBIT and JSON-related changes) should be
non-controversial – I just think someone forgot to put them in the code.
- In pgtypes.h, please add these definitions:
#define PG_TYPE_JSON 114
#define PG_TYPE_VARBIT 1562
You might also consider adding this if it is correct:
#define PG_TYPE_JSONB 3802
- In info.c, add this line immediately after the line containing the
only occurrence of PG_TYPE_BIT and right before the “break” line in the file:
case PG_TYPE_VARBIT:
- In columninfo.c, add these lines immediately after the line containing the
only occurrence of PG_TYPE_TIME_WITH_TMZONE and right before the “break”
line in the file:
case PG_TYPE_BIT:
case PG_TYPE_VARBIT:
- In info.c, in the function PGAPI_ProcedureColumns() at line 5121 (in the version
15.00 sources) the following code is added to the query to restrict the answer
to executable items:
appendPQExpBuffer(&proc_query,
" has_function_privilege(p.oid, 'EXECUTE')");
I suggest changing this to the following In order to truly make the function return info about actual procedures:
appendPQExpBuffer(&proc_query,
" p.prokind = 'p' and"
" has_function_privilege(p.oid, 'EXECUTE')");
- In configure, line 5016 (in the version 15.00 sources) has this:
CPPFLAGS="$CPPFLAGS -I$with_libpq/include - I$with_libpq/include/postgresql/internal"
I think that really ought to be this:
CPPFLAGS="$CPPFLAGS -I$with_libpq/include -I$with_libpq/include/internal"
- In connection.c, I think you should make this change. The comment explains my reasoning.
Change these lines from:
appendPQExpBufferStr(&query_buf, query);
if (appendq)
{
appendPQExpBuffer(&query_buf, ";%s", appendq);
}
if (query_rollback)
{
appendPQExpBuffer(&query_buf, ";%s %s", rlscmd, per_query_svp);
}
to:
appendPQExpBufferStr(&query_buf, query);
/* prepend newline too, in case user SQL ends in a */
/* dash-dash comment without a final newline character. Otherwise, */
/* the appended SQL statement gets treated as part of the comment. */
if (appendq)
{
appendPQExpBuffer(&query_buf, "\n;%s", appendq);
}
if (query_rollback)
{
appendPQExpBuffer(&query_buf, "\n;%s %s", rlscmd, per_query_svp);
}
- In odbcapi30.c, in SQLEndTran(), I think it should return SQL_ERROR if the connection is NOT in auto-commit mode.
Thus, in the SQL_HANDLE_DBC case inside that function, these two lines:
CC_clear_error((ConnectionClass *) Handle);
ret = PGAPI_Transact(SQL_NULL_HENV, Handle, CompletionType);
should be:
if (((ConnectionClass *)Handle)->__error_number == CONNECTION_SERVER_REPORTED_SEVERITY_FATAL
&& (CC_loves_visible_trans( (ConnectionClass *)Handle )) )
ret = SQL_ERROR;
else
{
CC_clear_error((ConnectionClass *) Handle);
ret = PGAPI_Transact(SQL_NULL_HENV, Handle, CompletionType);
}
I would be interested in getting your opinion on my suggestions, and hope the changes merit inclusion in the next release.
Sincerely,
Robert Willis
NOTICE from Ab Initio: This email (including any attachments) may contain information that is subject to confidentiality obligations or is legally privileged, and sender does not waive confidentiality or privilege. If received in error, please notify the sender, delete this email, and make no further use, disclosure, or distribution.
Disclaimer: Please note that I am NOT a maintainer for the psqlODBC driver. I have been subscribed to this list for a few years now and I am just trying to help out here. Of course my info below could be completely wrong 😊.
Although I have seen some bug reports and patches come through this list, I’m not sure if this is the official place for such things. It could be that this ODBC driver is just like all the rest of Postgres and it is expected that patches go through the normal Postgres channels. Have a look here:
https://wiki.postgresql.org/wiki/Submitting_a_Patch
Another article explaining the process:
https://www.timescale.com/blog/how-and-why-to-become-a-postgresql-contributor/
Something else worth noting is that this discussion list is listed as only “Discussion of PostgreSQL’s ODBC interface.” That may also point to this being the wrong forum.
https://www.postgresql.org/list/pgsql-odbc/
Maybe one of the maintainers can respond and clarify for us?
Jon
From: Robert Willis <rwillis@abinitio.com>
Date: Monday, July 31, 2023 at 5:45 AM
To: pgsql-odbc@postgresql.org <pgsql-odbc@postgresql.org>
Subject: psqlODBC suggestions
Several weeks ago, I sent a message to this list (included below).
I would appreciate it if someone on this list would acknowledge
that the message was received.
Thank you,
Robert Willis
From: Robert Willis
Sent: Monday, July 03, 2023 1:20 PM
To: pgsql-odbc@postgresql.org
Subject: ODBC driver changes
To Whom It May Concern:
Please consider making the following changes to the psqlODBC driver.
Items 1 through 3 (the VARBIT and JSON-related changes) should be
non-controversial – I just think someone forgot to put them in the code.
- In pgtypes.h, please add these definitions:
#define PG_TYPE_JSON 114
#define PG_TYPE_VARBIT 1562
You might also consider adding this if it is correct:
#define PG_TYPE_JSONB 3802
- In info.c, add this line immediately after the line containing the
only occurrence of PG_TYPE_BIT and right before the “break” line in the file:
case PG_TYPE_VARBIT:
- In columninfo.c, add these lines immediately after the line containing the
only occurrence of PG_TYPE_TIME_WITH_TMZONE and right before the “break”
line in the file:
case PG_TYPE_BIT:
case PG_TYPE_VARBIT:
- In info.c, in the function PGAPI_ProcedureColumns() at line 5121 (in the version
15.00 sources) the following code is added to the query to restrict the answer
to executable items:
appendPQExpBuffer(&proc_query,
" has_function_privilege(p.oid, 'EXECUTE')");
I suggest changing this to the following In order to truly make the function return info about actual procedures:
appendPQExpBuffer(&proc_query,
" p.prokind = 'p' and"
" has_function_privilege(p.oid, 'EXECUTE')");
- In configure, line 5016 (in the version 15.00 sources) has this:
CPPFLAGS="$CPPFLAGS -I$with_libpq/include - I$with_libpq/include/postgresql/internal"
I think that really ought to be this:
CPPFLAGS="$CPPFLAGS -I$with_libpq/include -I$with_libpq/include/internal"
- In connection.c, I think you should make this change. The comment explains my reasoning.
Change these lines from:
appendPQExpBufferStr(&query_buf, query);
if (appendq)
{
appendPQExpBuffer(&query_buf, ";%s", appendq);
}
if (query_rollback)
{
appendPQExpBuffer(&query_buf, ";%s %s", rlscmd, per_query_svp);
}
to:
appendPQExpBufferStr(&query_buf, query);
/* prepend newline too, in case user SQL ends in a */
/* dash-dash comment without a final newline character. Otherwise, */
/* the appended SQL statement gets treated as part of the comment. */
if (appendq)
{
appendPQExpBuffer(&query_buf, "\n;%s", appendq);
}
if (query_rollback)
{
appendPQExpBuffer(&query_buf, "\n;%s %s", rlscmd, per_query_svp);
}
- In odbcapi30.c, in SQLEndTran(), I think it should return SQL_ERROR if the connection is NOT in auto-commit mode.
Thus, in the SQL_HANDLE_DBC case inside that function, these two lines:
CC_clear_error((ConnectionClass *) Handle);
ret = PGAPI_Transact(SQL_NULL_HENV, Handle, CompletionType);
should be:
if (((ConnectionClass *)Handle)->__error_number == CONNECTION_SERVER_REPORTED_SEVERITY_FATAL
&& (CC_loves_visible_trans( (ConnectionClass *)Handle )) )
ret = SQL_ERROR;
else
{
CC_clear_error((ConnectionClass *) Handle);
ret = PGAPI_Transact(SQL_NULL_HENV, Handle, CompletionType);
}
I would be interested in getting your opinion on my suggestions, and hope the changes merit inclusion in the next release.
Sincerely,
Robert Willis
NOTICE from Ab Initio: This email (including any attachments) may contain information that is subject to confidentiality obligations or is legally privileged, and sender does not waive confidentiality or privilege. If received in error, please notify the sender, delete this email, and make no further use, disclosure, or distribution.
Dave, Jon,
thanks for responding. I was on vacation but am back now.
I’ll have a look at the various URLs you provided Jon, to see if there is a better
place to respond. But according to https://odbc.postgresql.org (which was the first
link I got when I searched for “psqlodbc”) it says:
psqlODBC is the official PostgreSQL ODBC Driver.
and in the “PsqlODBC Development” section it says:
psqlODBC is developed and supported through the pgsql-odbc@postgresql.org mailing list.
Also note that the README.txt in the docs subdirectory of the psqlodbc distribution also
points one to the odbc.postgresql.org site.
Perhaps the documentation could be updated to suggest a better mailing list (or other
method) for questions/bug-fixes, etc.?
Robert
From: Jon Raiford
Sent: Monday, July 31, 2023 8:50 AM
To: Robert Willis <rwillis@abinitio.com>; pgsql-odbc@postgresql.org
Subject: Re: psqlODBC suggestions
Disclaimer: Please note that I am NOT a maintainer for the psqlODBC driver. I have been subscribed to this list for a few years now and I am just trying to help out here. Of course my info below could be completely wrong 😊.
Although I have seen some bug reports and patches come through this list, I’m not sure if this is the official place for such things. It could be that this ODBC driver is just like all the rest of Postgres and it is expected that patches go through the normal Postgres channels. Have a look here:
https://wiki.postgresql.org/wiki/Submitting_a_Patch
Another article explaining the process:
https://www.timescale.com/blog/how-and-why-to-become-a-postgresql-contributor/
Something else worth noting is that this discussion list is listed as only “Discussion of PostgreSQL’s ODBC interface.” That may also point to this being the wrong forum.
https://www.postgresql.org/list/pgsql-odbc/
Maybe one of the maintainers can respond and clarify for us?
Jon
From: Robert Willis <rwillis@abinitio.com>
Date: Monday, July 31, 2023 at 5:45 AM
To: pgsql-odbc@postgresql.org <pgsql-odbc@postgresql.org>
Subject: psqlODBC suggestions
Several weeks ago, I sent a message to this list (included below).
I would appreciate it if someone on this list would acknowledge
that the message was received.
Thank you,
Robert Willis
From: Robert Willis
Sent: Monday, July 03, 2023 1:20 PM
To: pgsql-odbc@postgresql.org
Subject: ODBC driver changes
To Whom It May Concern:
Please consider making the following changes to the psqlODBC driver.
Items 1 through 3 (the VARBIT and JSON-related changes) should be
non-controversial – I just think someone forgot to put them in the code.
- In pgtypes.h, please add these definitions:
#define PG_TYPE_JSON 114
#define PG_TYPE_VARBIT 1562
You might also consider adding this if it is correct:
#define PG_TYPE_JSONB 3802
- In info.c, add this line immediately after the line containing the
only occurrence of PG_TYPE_BIT and right before the “break” line in the file:
case PG_TYPE_VARBIT:
- In columninfo.c, add these lines immediately after the line containing the
only occurrence of PG_TYPE_TIME_WITH_TMZONE and right before the “break”
line in the file:
case PG_TYPE_BIT:
case PG_TYPE_VARBIT:
- In info.c, in the function PGAPI_ProcedureColumns() at line 5121 (in the version
15.00 sources) the following code is added to the query to restrict the answer
to executable items:
appendPQExpBuffer(&proc_query,
" has_function_privilege(p.oid, 'EXECUTE')");
I suggest changing this to the following In order to truly make the function return info about actual procedures:
appendPQExpBuffer(&proc_query,
" p.prokind = 'p' and"
" has_function_privilege(p.oid, 'EXECUTE')");
- In configure, line 5016 (in the version 15.00 sources) has this:
CPPFLAGS="$CPPFLAGS -I$with_libpq/include - I$with_libpq/include/postgresql/internal"
I think that really ought to be this:
CPPFLAGS="$CPPFLAGS -I$with_libpq/include -I$with_libpq/include/internal"
- In connection.c, I think you should make this change. The comment explains my reasoning.
Change these lines from:
appendPQExpBufferStr(&query_buf, query);
if (appendq)
{
appendPQExpBuffer(&query_buf, ";%s", appendq);
}
if (query_rollback)
{
appendPQExpBuffer(&query_buf, ";%s %s", rlscmd, per_query_svp);
}
to:
appendPQExpBufferStr(&query_buf, query);
/* prepend newline too, in case user SQL ends in a */
/* dash-dash comment without a final newline character. Otherwise, */
/* the appended SQL statement gets treated as part of the comment. */
if (appendq)
{
appendPQExpBuffer(&query_buf, "\n;%s", appendq);
}
if (query_rollback)
{
appendPQExpBuffer(&query_buf, "\n;%s %s", rlscmd, per_query_svp);
}
- In odbcapi30.c, in SQLEndTran(), I think it should return SQL_ERROR if the connection is NOT in auto-commit mode.
Thus, in the SQL_HANDLE_DBC case inside that function, these two lines:
CC_clear_error((ConnectionClass *) Handle);
ret = PGAPI_Transact(SQL_NULL_HENV, Handle, CompletionType);
should be:
if (((ConnectionClass *)Handle)->__error_number == CONNECTION_SERVER_REPORTED_SEVERITY_FATAL
&& (CC_loves_visible_trans( (ConnectionClass *)Handle )) )
ret = SQL_ERROR;
else
{
CC_clear_error((ConnectionClass *) Handle);
ret = PGAPI_Transact(SQL_NULL_HENV, Handle, CompletionType);
}
I would be interested in getting your opinion on my suggestions, and hope the changes merit inclusion in the next release.
Sincerely,
Robert Willis
NOTICE from Ab Initio: This email (including any attachments) may contain information that is subject to confidentiality obligations or is legally privileged, and sender does not waive confidentiality or privilege. If received in error, please notify the sender, delete this email, and make no further use, disclosure, or distribution.
NOTICE from Ab Initio: This email (including any attachments) may contain information that is subject to confidentiality obligations or is legally privileged, and sender does not waive confidentiality or privilege. If received in error, please notify the sender, delete this email, and make no further use, disclosure, or distribution.
On Thu, Aug 10, 2023 at 3:25 PM Robert Willis <rwillis@abinitio.com> wrote: > Dave, Jon, > > thanks for responding. I was on vacation but am back now. > > > > I’ll have a look at the various URLs you provided Jon, to see if there is a better > > place to respond. But according to https://odbc.postgresql.org (which was the first > The links that Jon posted were geared toward "core" development of the server itself, and don't apply to odbc development. > link I got when I searched for “psqlodbc”) it says: > > psqlODBC is the official PostgreSQL ODBC Driver. > > and in the “PsqlODBC Development” section it says: > > psqlODBC is developed and supported through the pgsql-odbc@postgresql.org mailing list. > > Also note that the README.txt in the docs subdirectory of the psqlodbc distribution also > > points one to the odbc.postgresql.org site. > > > > Perhaps the documentation could be updated to suggest a better mailing list (or other > > method) for questions/bug-fixes, etc.? > The issue isn't with the odbc documentation; it is pointing to the right places; the issue is that we don't have dedicated attention on odbc maintenance, so these requests take time to get processed. Robert Treat https://xzilla.net
Robert > The issue isn't with the odbc documentation; it is pointing to the > right places; the issue is that we don't have dedicated attention on > odbc maintenance, so these requests take time to get processed. > > Robert Treat > https://xzilla.net I'm hoping that means that in fact, this WAS the right mailing list to which to mail my suggestions, and that someone will look them over and possibly incorporate them in a future release. That's all I am hoping for. Robert Willis NOTICE from Ab Initio: This email (including any attachments) may contain information that is subject to confidentialityobligations or is legally privileged, and sender does not waive confidentiality or privilege. If receivedin error, please notify the sender, delete this email, and make no further use, disclosure, or distribution.
I’m also happy to hear that this is the official place to post fixes (at least I know now). I do wish there was an official git repository that we could use to issue pull requests. That would probably make consuming fixes like this easier. There are a couple GitHub repositories but nothing that is seems to be official.
Jon
From: Robert Willis <rwillis@abinitio.com>
Date: Wednesday, August 16, 2023 at 5:06 PM
To: Robert Treat <rob@xzilla.net>
Cc: Jon Raiford <raiford@labware.com>, Dave Cramer <davecramer@postgres.rocks>, pgsql-odbc@postgresql.org <pgsql-odbc@postgresql.org>
Subject: RE: psqlODBC suggestions
Robert
> The issue isn't with the odbc documentation; it is pointing to the
> right places; the issue is that we don't have dedicated attention on
> odbc maintenance, so these requests take time to get processed.
>
> Robert Treat
> https://xzilla.net
I'm hoping that means that in fact, this WAS the right mailing list to
which to mail my suggestions, and that someone will look them over
and possibly incorporate them in a future release. That's all I am
hoping for.
Robert Willis
NOTICE from Ab Initio: This email (including any attachments) may contain information that is subject to confidentiality obligations or is legally privileged, and sender does not waive confidentiality or privilege. If received in error, please notify the sender, delete this email, and make no further use, disclosure, or distribution.