Subject: psqlODBC suggestions

From: Robert Willis <[email-masked]>
Date: Wed, 26 Jul 2023 15:21:07 +0000

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: [email-masked]
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.

  1.  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

  1.  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:

  1.  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:

  1.  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')");

  1.  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"

  1.  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);

                }

  1.  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.

---

From: Dave Cramer <[email-masked]>
Date: Mon, 31 Jul 2023 06:22:38 -0600

Seems to make sense.

Someone should respond to this.
Dave Cramer
www.postgres.rocks

On Mon, 31 Jul 2023 at 03:37, Robert Willis <[email-masked]> wrote:

> *From:* Robert Willis
> *Sent:* Monday, July 03, 2023 1:20 PM
> *To:* [email-masked]
> *Subject:* ODBC driver changes
>
>
>
> To Whom It May Concern:
>
>
>
>                                                                 "
> 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:
>
>                                                                  "
> p.prokind = 'p' and"
>                                                                 "
> has_function_privilege(p.oid, 'EXECUTE')");
>
>
>
>                                 CPPFLAGS="$CPPFLAGS -I$with_libpq/include
> -  I$with_libpq/include/postgresql/internal"
>
>    1. In odbcapi30.c, in  SQLEndTran(),   I think it should return
>    SQL_ERROR if the connection is NOT in auto-commit mode.
>
>                 if (((ConnectionClass *)Handle)->__error_number ==
> CONNECTION_SERVER_REPORTED_SEVERITY_FATAL
>
>                                                     &&
> (CC_loves_visible_trans( (ConnectionClass *)Handle  )) )
>
>                   ret = SQL_ERROR;
>
>                                 else
>
>                   {
>
> 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. *
>

---

From: Jon Raiford <[email-masked]>
Date: Mon, 31 Jul 2023 12:49:54 +0000

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 <[email-masked]>
Date: Monday, July 31, 2023 at 5:45 AM
To: [email-masked] <[email-masked]>
Subject: psqlODBC suggestions
Several weeks ago,  I sent a message to this list  (included below).

Thank you,

Robert Willis

From: Robert Willis

To Whom It May Concern:

line in the file:

                case PG_TYPE_BIT:

                case PG_TYPE_VARBIT:

Change these lines from:

                if (appendq)

                {

                }

                if (query_rollback)

                {

                }

to:

                if (appendq)

                {

                }

                if (query_rollback)

                {

                }

should be:

                  ret = SQL_ERROR;
                                else

                  {

                  }

Sincerely,

Robert Willis

---

From: Robert Willis <[email-masked]>
Date: Tue, 8 Aug 2023 17:47:22 +0000

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 [email-masked]<http://www.postgresql.org/list/pgsql-odbc/> 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 <[email-masked]>; [email-masked]
Subject: Re: psqlODBC suggestions

Jon

From: Robert Willis <[email-masked]<mailto:[email-masked]>>
Date: Monday, July 31, 2023 at 5:45 AM
To: [email-masked]<mailto:[email-masked]> <[email-masked]<mailto:[email-masked]>>
Subject: psqlODBC suggestions
Several weeks ago,  I sent a message to this list  (included below).

Thank you,

Robert Willis

From: Robert Willis
Sent: Monday, July 03, 2023 1:20 PM
To: [email-masked]<mailto:[email-masked]>
Subject: ODBC driver changes

To Whom It May Concern:

line in the file:

                case PG_TYPE_BIT:

                case PG_TYPE_VARBIT:

Change these lines from:

                if (appendq)

                {

                }

                if (query_rollback)

                {

                }

to:

                if (appendq)

                {

                }

                if (query_rollback)

                {

                }

should be:

                  ret = SQL_ERROR;
                                else

                  {

                  }

Sincerely,

Robert Willis

---

From: Robert Treat <[email-masked]>
Date: Sat, 12 Aug 2023 13:59:21 -0400

> 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.

>                 psqlODBC is developed and supported through the [email-masked] mailing list.
>

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

---

From: Robert Willis <[email-masked]>
Date: Wed, 16 Aug 2023 21:06:33 +0000

Robert

> odbc maintenance, so these requests take time to get processed.

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

---

From: Jon Raiford <[email-masked]>
Date: Thu, 17 Aug 2023 17:37:45 +0000

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 <[email-masked]>
Date: Wednesday, August 16, 2023 at 5:06 PM
To: Robert Treat <[email-masked]>
Cc: Jon Raiford <[email-masked]>, Dave Cramer <[email-masked]>, [email-masked] <[email-masked]>
Subject: RE: psqlODBC suggestions
Robert

> https://xzilla.net<https://xzilla.net>

Robert Willis