Обсуждение: Bug in SQLRowCount ?

(2013/01/17 18:31), j.letzel@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

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

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

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

Am 17.01.2013 18:03, schrieb Heikki Linnakangas:
> 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
>

Hello,

I has also face same issue. I am just curious to know, how are you using
SQL_ATTR_PARAM_STATUS_PTR?
I have tried to use that in my code and I was incrementing the count based
on the status "SQL_PARAM_SUCCESS" i.e. 0.
However, I found that when I send let's say 3 rows in batch and there is no
row to delete, the status was still "SQL_PARAM_SUCCESS". When I called
"SQLRowCount" it was returning me 0.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-odbc-f2186591.html

Jaydip wrote:
> I has also face same issue. I am just curious to know, how are you using
> SQL_ATTR_PARAM_STATUS_PTR?

Please note that the mail you were replying to is over five years old.
It is unlikely that the original author still reads this list.

> I have tried to use that in my code and I was incrementing the count based
> on the status "SQL_PARAM_SUCCESS" i.e. 0.
> However, I found that when I send let's say 3 rows in batch and there is no
> row to delete, the status was still "SQL_PARAM_SUCCESS". When I called
> "SQLRowCount" it was returning me 0.

The SQL_ATTR_PARAM_STATUS_PTR array returns whether a statement resulted in
an error.  But a WHERE clause that matches zero rows is not considered an
error by the SQL standard.

To find out how many rows were deleted, you _must_ call SQLRowCount.
For a SQL_PARC_BATCH driver, you have to get each row count separately by
calling SQLRowCount/SQLMoreResults in a loop, as documented here:
<https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/multiple-results>


Regards,
Clemens