Bug in SQLRowCount ?

j.le...@t-online.de

unread,

Jan 17, 2013, 4:31:05 AM1/17/13

to

Hello !

I maintain a backend in a commercial context. The backend provides access to MS SQLServer (via ODBC) and Oracle (via OCI). Now I want to make it ready for PostgreSQL via ODBC.

For performance reasons we use bulk inserts/updates.

This is done in this manner:

SQLAllocStmt

SQLPrepare // insert into kfztest.KP values (?,?,...,?)

SQBindParameter // for each column

...

SQLSetStmtAttr // SQL_ATTR_PARAMSET_SIZE to 523

SQLSetStmtAttr // SQL_ATTR_PARAM_STATUS_PTR to an array of SQLSMALLINT

SQLSetStmtAttr // SQL_ATTR_PARAMS_PROCESSED_PTR to an SQLULEN variable

SQLExecute // ends with SQL_SUCCESS

SQLRowCount // delivers 1 !!!!

SQLEndTrans // with COMMIT

When necessary I can provide a full ODBC trace.

All records were written into table.

Thanks in advance.

Regards

Johann Letzel

Hiroshi Inoue

unread,

Jan 17, 2013, 8:08:36 AM1/17/13

to

(2013/01/17 18:31), j.le...@t-online.de wrote:
> Hello !
>
> I maintain a backend in a commercial context. The backend provides
> access to MS SQLServer (via ODBC) and Oracle (via OCI). Now I want to
> make it ready for PostgreSQL via ODBC.
>
> For performance reasons we use bulk inserts/updates.
>
> This is done in this manner:
>
> SQLAllocStmt
>
> SQLPrepare // insert into kfztest.KP values (?,?,...,?)
>
> SQBindParameter // for each column
>
> ...
>
> SQLSetStmtAttr // SQL_ATTR_PARAMSET_SIZE to 523
>
> SQLSetStmtAttr // SQL_ATTR_PARAM_STATUS_PTR to an array of SQLSMALLINT
>
> SQLSetStmtAttr // SQL_ATTR_PARAMS_PROCESSED_PTR to an SQLULEN variable
>
> SQLExecute // ends with SQL_SUCCESS
>
> SQLRowCount // delivers 1 !!!!

The driver gives individual row counts for each parameter set.
SQLGetInfo(.., SQL_PARAM_ARRAY_ROW_COUNTS) returns SQL_PARC_BATCH.

regards,
Hiroshi Inoue

--
Sent via pgsql-odbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Johann Letzel

unread,

Jan 17, 2013, 9:05:41 AM1/17/13

to

Hi !

Tanks for the reply.

But according to the ODBC API SQLRowCount should retrieve the number of affected rows by the statement.

Why does PostgreSQL gives a 1 and MSSQL the number of inserted/updated rows ?

Regards

Johann

Hiroshi Inoue <in...@tpf.co.jp> schrieb:

Heikki Linnakangas

unread,

Jan 17, 2013, 12:03:01 PM1/17/13

to

On 17.01.2013 16:05, Johann Letzel wrote:
> But according to the ODBC API SQLRowCount should retrieve the number of affected rows by the statement.
>
> Why does PostgreSQL gives a 1 and MSSQL the number of inserted/updated rows ?

According to this:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms711818%28v=vs.85%29.aspx,
both behaviors are permitted. When the driver returns SQL_PARC_BATCH,
when you call SQLGetInfo(conn, SQL_PARAM_ARRAY_ROW_COUNTS, ... ), the
driver returns a separate row count for each parameter (PostgreSQL).
When it returns SQL_PARC_NO_BATCH, it returns a single row count that's
the sum of all parameters (MSSQL). In a portable application, you need
to call SQLGetInfo, and deal with both behaviors.

- Heikki

Johann Letzel

unread,

Jan 22, 2013, 1:02:39 AM1/22/13

to

Hi !

A big thanks for the tips :)

I solved it now by the use of SQL_ATTR_PARAMS_PROCESSED_PTR and
SQL_ATTR_PARAM_STATUS_PTR.

By the way: MSSQL also returns SQL_PARC_BATCH ;)

Regards

Johann