Thread: Postgres inserts null value for parameterized statement

Postgres inserts null value for parameterized statement

From

kishoramballi

Date:

18 October 2009, 03:38:09

Hi,

I have created table using following sql statement through psql tool in test
database using
create table t1(f1 int);
table is created.
Now I am using an odbc script to insert 5 records. But it inserts 6 records
NULL value being the first one inserted.
the code snippet is as below.

...
...
retVal = SQLDriverConnect(dbHdl, NULL, (SQLCHAR*)"psql",
SQL_NTS,outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
SQLSetConnectAttr(dbHdl, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
int f1 = 0;
ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?);" ,
SQL_NTS);
ret = SQLBindParameter (stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER,0,0,&f1,0,NULL);
int i,count=0;
// EXECUTE THE STATEMENT
for(i=0;i<5;i++)
{
f1++;
ret = SQLExecute(stmt);
ret = SQLTransact(env,dbc,SQL_COMMIT);
count++;
}
...
...

I am using unixodb driver and my odbc.ini file reads as follows.

$ vi ~/.odbc.ini

[psql]
Description = CP resources db
Driver = postgres
Trace = No
TraceFile = sql.log
Database = test
Servername = localhost
UserName = kishoramballi
Password = kishoramballi
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
ExtraSysTablePrefixes =

$ vi /etc/odbcinst.ini

[ODBC]
Trace = yes
TraceFile = /tmp/sql.log

[postgres]
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
Description = Connector Driver DSN
SERVER = localhost
Database = test
ReadOnly = No

[PostgreSQL]
Description = PostgreSQL ODBC driver for Linux
Driver = /usr/lib/libodbcpsql.so.1
Setup = /usr/lib/libodbcpsqlS.so.1
ExtraSysTablePrefixes =
UsageCount = 1


Am I missing something or have I added some thing more than required for
this problem to occur.

Please help
thanks
--
View this message in context:
http://www.nabble.com/Postgres-inserts-null-value-for-parameterized-statement-tp25896531p25896531.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

Re: Postgres inserts null value for parameterized statement

From

Hiroshi Inoue

Date:

19 October 2009, 04:01:32

kishoramballi wrote:
> Hi,
>
> I have created table using following sql statement through psql tool in test
> database using
> create table t1(f1 int);
> table is created.
> Now I am using an odbc script to insert 5 records. But it inserts 6 records
> NULL value being the first one inserted.
> the code snippet is as below.
>
> ...
> ...
> retVal = SQLDriverConnect(dbHdl, NULL, (SQLCHAR*)"psql",
> SQL_NTS,outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
> SQLSetConnectAttr(dbHdl, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0);
> int f1 = 0;
> ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?);" ,
> SQL_NTS);
> ret = SQLBindParameter (stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
> SQL_INTEGER,0,0,&f1,0,NULL);
> int i,count=0;
> // EXECUTE THE STATEMENT
> for(i=0;i<5;i++)
> {
> f1++;
> ret = SQLExecute(stmt);
> ret = SQLTransact(env,dbc,SQL_COMMIT);
> count++;
> }
> ...
> ...
>
> I am using unixodb driver and my odbc.ini file reads as follows.
>
> $ vi ~/.odbc.ini
>
> [psql]
> Description = CP resources db
> Driver = postgres
> Trace = No
> TraceFile = sql.log
> Database = test
> Servername = localhost
> UserName = kishoramballi
> Password = kishoramballi
> Port = 5432
> Protocol = 6.4

Please set
   Protocol = 7.4

and set one of the follwoing.

1)
   UseServerSidePrepare = 1
2)
   DisallowPremature = 1
3)
   Parse = 1

regards,
Hiroshi Inoue