Re: [ODBC] Bug in PostgreSQL ODBC - in names with dollar - Mailing list pgsql-odbc

Hi,

 

I’m sorry to bother you again, but I didn’t see any updates in the mailing list lately.

 

Unfortunately this problem with the dollar signs in names is quite sensitive to us (we’re porting a set of products to PostgreSQL), so we’d really like to know, even approximately, if the fix will be in the official odbc release and the time frame.

 

Any information on that would be greatly appreciated.

 

Thank you.

 

Georgy Pruss.

 

 

From: Georgy Pruss [mailto:gpruss@ksf.kiev.ua]
Sent: Wednesday, August 30, 2017 17:02
To: 'Inoue, Hiroshi' <h-inoue@dream.email.ne.jp>
Cc: pgsql-odbc@postgresql.org
Subject: RE: [ODBC] Bug in PostgreSQL ODBC - in names with dollar

 

Thank you very much.

 

It works on Windows.

 

I hope it’ll be the same on Linux.

 

Can I ask when it’s in the official release of ODBC?

 

Thank you.

 

Georgy Pruss

 

 

 

From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Inoue, Hiroshi
Sent: Wednesday, August 30, 2017 01:39
To: Georgy Pruss <gpruss@ksf.kiev.ua>
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Bug in PostgreSQL ODBC - in names with dollar

 

Hi Georgy,

Could you try the git at git.postgresql.org/git/postgresql.git ?
(web page https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=summary ).

Or could you please try the test drivers 9.6.0415 on Windows at
  http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/ ?

regards,
Hiroshi Inoue
.

On 2017/08/30 2:49, Georgy Pruss wrote:

Hi,

 

 

I’m sorry, I really tried to register at pgsql-odbc list at postgresql.org, using different email addresses, but it didn’t’ work.

 

I saw that you may deal with this problem.

 

 

When UseServerSidePrepare is set to 1 (which is default, I guess), any names with $ in them cause error:

 

“The # of binded parameters < the # of parameter markers”

 

It’s both on Windows and Linux.

 

It seems that PG tries to use un-paired ‘$’ symbols as parameter marks for the server-side prepare/execute feature, even if there’s no PREPARE or EXECUTE statements.

 

I believe, PG should not parse any statements for $-parameters outside PREPARE/EXECUTE.

 

 

Simple ODBC program:

 

 

$ cat test_odbc.c

// gcc -I unixODBC-2.3.4/include -I unixODBC-2.3.4 -L libs -lodbc test_odbc.c -o test_odbc

// test_odbc 0|1

 

#include <stdio.h>

#include <time.h>

#include <sql.h>

#include <sqlext.h>

 

int main(int ac, char* av[])

{

  SQLHENV env;

  SQLHDBC dbc;

  SQLRETURN ret;

  SQLCHAR outstr[1024];

  SQLSMALLINT outstrlen;

  SQLHSTMT stmt;

  if( ac!=2 || av[1][1]!='\0' || !(av[1][0]=='0' || av[1][0]=='1')) return 1;

  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);

  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

  SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

  char conn[512];

  sprintf( conn, "DSN=PGDriver;UseServerSidePrepare=%s", av[1] );

  printf( "Connection: %s\n", conn );

  ret = SQLDriverConnect(dbc, NULL, conn, SQL_NTS,

       outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);

  if(!SQL_SUCCEEDED(ret))

  {

    fprintf(stderr, "Failed to connect\n");

  }

  else

  {

    printf("Returned connection string was:\n\t%s\n", outstr);

    char sql[512];

    sprintf( sql, "create table T$_x_%u (f integer)", (int)time(0) );

    printf( "Exec: %s\n", sql );

    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

    ret = SQLExecDirect(stmt, sql, SQL_NTS);

    if(SQL_SUCCEEDED(ret))

      printf("ok\n");

    else

      printf("error\n");

    SQLFreeHandle(SQL_HANDLE_STMT,stmt);

    SQLDisconnect(dbc);

  }

  SQLFreeHandle(SQL_HANDLE_DBC, dbc);

  SQLFreeHandle(SQL_HANDLE_ENV, env);

  return 0;

}

 

 

$ test_odbc 0

Connection: DSN=PGDriver;UseServerSidePrepare=0

Returned connection string was:

        DSN=PGDriver;DATABASE=…;SERVER=…;PORT=5432;UID=…;PWD=…;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;=Fetch=100;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;ExtraSysTablePrefixes=;LFConversion=0;UpdatableCursors=1;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=0;LowerCaseIdentifier=0;

Exec: create table T$_x_1504022913 (f integer)

ok

 

 

$ test_odbc 1

Connection: DSN=PGDriver;UseServerSidePrepare=1

Returned connection string was:

        DSN=PGDriver;DATABASE=…;SERVER=…;PORT=5432;UID=…;PWD=…;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;=Fetch=100;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;ExtraSysTablePrefixes=;LFConversion=0;UpdatableCursors=1;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=1;UseServerSidePrepare=1;LowerCaseIdentifier=0;

Exec: create table T$_x_1504022916 (f integer)

error

 

 

Actually, probably any statement with ‘$’ in table/field name causes this error.

 

 

PGDriver - /usr/pgsql-9.6/lib/psqlodbcw.so

Driver Info

DBMS Name: PostgreSQL

DBMS Version: 9.6.3

PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

 

 

This is probably the same error as in https://www.postgresql.org/message-id/09ce3d91ce364987964726aa50a16155%40neosys.si

 

 

Thank you.

 

 

Georgy Pruss.