RE: psqlODBC suggestions

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

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

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

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

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

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

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

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

                }

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