Re: 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/ 
<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 
> <http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiY4NWFkOWEwNjFkMTgzOGRiYj01OUE1NzQ2NV8yMTIyOF8xMzEzN18xJiZkZTU4YWZhNmRjMzY5YTk9MTIyMyYmdXJsPWh0dHBzJTNBJTJGJTJGd3d3JTJFcG9zdGdyZXNxbCUyRW9yZyUyRm1lc3NhZ2UtaWQlMkYwOWNlM2Q5MWNlMzY0OTg3OTY0NzI2YWE1MGExNjE1NSU0MG5lb3N5cyUyRXNp>
>
> Thank you.
>
> Georgy Pruss.
>