DevHeads.net

error with $ in names again

Hi,

I think there’s still a bug in parsing names with dollars.

Linux RHEL66

UseServerSidePrepare=0:

create table T$001 (…) ok

create table T_$001 (…) ok

UseServerSidePrepare=1:

create table T$001 (…) ok

create table T_$001 (…) error

Here is session log for that:

$ test_odbc 0 'T$001'

Connection: DSN=PGDriver;UseServerSidePrepare=0

Returned connection string was: …;UseDeclareFetch=0;…;Parse=0;…;UseServerSidePrepare=1;LowerCaseIdentifier=0;

Exec: create table T$001 (i integer)

ok

$ test_odbc 1 'T$001'

Connection: DSN=PGDriver;UseServerSidePrepare=1

Returned connection string was: …

Exec: create table T$001 (i integer)

ok

$ test_odbc 0 'T_$001'

Connection: DSN=PGDriver;UseServerSidePrepare=0

Returned connection string was: …

Exec: create table T_$001 (i integer)

ok

$ test_odbc 1 'T_$001'

Connection: DSN=PGDriver;UseServerSidePrepare=1

Returned connection string was: …

Exec: create table T_$001 (i integer)

Error

ODBC error: "Parameters exist but IPD isn't set. Please call SQLDescribeParam()"

I guess $n parameters should not be allowed/treated in any ODBC statements except PREPARE/EXECUTE at all, right?

In ODBC statements (and in any case not in table names) it’s ‘?’, right?

(I know, internally ‘?’ are replaced by ‘$n’, maybe it’s too much for pure ‘$’ sign – literal strings, parameters for PREPARE, internal parameters for ODBC…)

- - - - - - - - - - - - - - - - - - - - - - - - - - - -

$ 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 'tablename'

#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!=3 || av[1][1]!='\0' || !(av[1][0]=='0' || av[1][0]=='1') )

return printf( "Syntax: test_odbc 0|1 'tablename'\n" ), 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 %s (i integer)", av[2] ); // <-------------

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

sprintf( sql, "drop table if exists %s", av[2] );

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

SQLExecDirect(stmt, sql, SQL_NTS);

SQLFreeHandle(SQL_HANDLE_STMT,stmt);

SQLDisconnect(dbc);

}

SQLFreeHandle(SQL_HANDLE_DBC, dbc);

SQLFreeHandle(SQL_HANDLE_ENV, env);

return 0;

}

$ cat $ODBCINI

[ODBC Data Sources]

PGDriver = PG ODBC Data Source

[PGDriver]

Driver = /usr/pgsql-9.6/lib/psqlodbcw.so

Servername = <….>

Port = 5432

Database = <….>

Username = <….>

Password = <….>

$ rpm -qa |grep postgresql

postgresql96-server-9.6.5-1PGDG.rhel6.x86_64

postgresql96-9.6.5-1PGDG.rhel6.x86_64

postgresql96-libs-9.6.5-1PGDG.rhel6.x86_64

postgresql96-odbc-09.06.0500-1PGDG.rhel6.x86_64

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.6 (Santiago)

- - - - - - - - - - - - - - - - - - - - - - - - - - - -

Regards

Georgy Pruss

Comments

Re: error with $ in names again

By Inoue, Hiroshi at 10/11/2017 - 09:08

Hi Gregory,

On 2017/10/11 19:44, Georgy Pruss wrote:
Thanks for the report.
I would examine the issue.

regards,
Hiroshi Inoue

Re: error with $ in names again

By Inoue, Hiroshi at 10/13/2017 - 08:52

Hi Georgy,

On 2017/10/11 23:08, Inoue, Hiroshi wrote:
I found a cause and committed the fix to git.
We would make a new release soon.

regards,
Hiroshi Inoue

Re: error with $ in names again

By Georgy Pruss at 10/30/2017 - 08:31

Hi,

Any news on new driver for 9.6 on Linux?

I guess everybody must be busy with 10.0, but still, maybe some progress…

Thank you!

G.P.

Cc: <a href="mailto:pgsql- ... at postgresql dot org">pgsql- ... at postgresql dot org</a>

Hi Georgy,

Hi Gregory,

Hi,

I think there’s still a bug in parsing names with dollars.

Linux RHEL66

UseServerSidePrepare=0:

create table T$001 (…) ok

create table T_$001 (…) ok

UseServerSidePrepare=1:

create table T$001 (…) ok

create table T_$001 (…) error

Thanks for the report.
I would examine the issue.

I found a cause and committed the fix to git.
We would make a new release soon.

regards,
Hiroshi Inoue

regards,
Hiroshi Inoue

Here is session log for that:

$ test_odbc 0 'T$001'

Connection: DSN=PGDriver;UseServerSidePrepare=0

Returned connection string was: …;UseDeclareFetch=0;…;Parse=0;…;UseServerSidePrepare=1;LowerCaseIdentifier=0;

Exec: create table T$001 (i integer)

ok

$ test_odbc 1 'T$001'

Connection: DSN=PGDriver;UseServerSidePrepare=1

Returned connection string was: …

Exec: create table T$001 (i integer)

ok

$ test_odbc 0 'T_$001'

Connection: DSN=PGDriver;UseServerSidePrepare=0

Returned connection string was: …

Exec: create table T_$001 (i integer)

ok

$ test_odbc 1 'T_$001'

Connection: DSN=PGDriver;UseServerSidePrepare=1

Returned connection string was: …

Exec: create table T_$001 (i integer)

Error

ODBC error: "Parameters exist but IPD isn't set. Please call SQLDescribeParam()"

I guess $n parameters should not be allowed/treated in any ODBC statements except PREPARE/EXECUTE at all, right?

In ODBC statements (and in any case not in table names) it’s ‘?’, right?

(I know, internally ‘?’ are replaced by ‘$n’, maybe it’s too much for pure ‘$’ sign – literal strings, parameters for PREPARE, internal parameters for ODBC…)

- - - - - - - - - - - - - - - - - - - - - - - - - - - -

$ 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 'tablename'

#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!=3 || av[1][1]!='\0' || !(av[1][0]=='0' || av[1][0]=='1') )

return printf( "Syntax: test_odbc 0|1 'tablename'\n" ), 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 %s (i integer)", av[2] ); // <-------------

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

sprintf( sql, "drop table if exists %s", av[2] );

SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

SQLExecDirect(stmt, sql, SQL_NTS);

SQLFreeHandle(SQL_HANDLE_STMT,stmt);

SQLDisconnect(dbc);

}

SQLFreeHandle(SQL_HANDLE_DBC, dbc);

SQLFreeHandle(SQL_HANDLE_ENV, env);

return 0;

}

$ cat $ODBCINI

[ODBC Data Sources]

PGDriver = PG ODBC Data Source

[PGDriver]

Driver = /usr/pgsql-9.6/lib/psqlodbcw.so

Servername = <….>

Port = 5432

Database = <….>

Username = <….>

Password = <….>

$ rpm -qa |grep postgresql

postgresql96-server-9.6.5-1PGDG.rhel6.x86_64

postgresql96-9.6.5-1PGDG.rhel6.x86_64

postgresql96-libs-9.6.5-1PGDG.rhel6.x86_64

postgresql96-odbc-09.06.0500-1PGDG.rhel6.x86_64

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.6 (Santiago)

- - - - - - - - - - - - - - - - - - - - - - - - - - - -

Regards

Georgy Pruss