Postgres Pro
Downloads
Home   >   mailing lists

Thread: [bug-report]SQLRowCount set affected row numbers is incorrect when use SQLBindParameter and SQLSetStmtAttr for SQL_ATTR_PARAMSET_SIZE

[bug-report]SQLRowCount set affected row numbers is incorrect when use SQLBindParameter and SQLSetStmtAttr for SQL_ATTR_PARAMSET_SIZE

From
Chang Lu
Date:
Hi,

I'm tring to insert and update a lot of records using  SQLBindParameter and SQLSetStmtAttr, but SQLRowsCount returned record number affected by ONLY the LAST SINGLE SQL STATEMENT.

Here is my code sequence.

1. 
status = SQLBindParameter(cda,
       KCPD_SCAST(KCPD_SQLUSMALLINT, parm_pos),
       KCPD_SCAST(KCPD_SQLSMALLINT, param_type),
       ftype,
        KCPD_SCAST(KCPD_SQLSMALLINT, mapped_sqltype),
        temp_column_size,
        sqltype == SQL_TYPE_TIMESTAMP ?  kcpd_odbc_date_scale : 0,  
        KCPD_RCAST(KCPD_SQLPOINTER, v.p_v),
        buflen,
        v.p_len);

2. 
status = SQLSetStmtAttr(cda,
         SQL_ATTR_PARAMSET_SIZE,
         KCPD_RCAST(void*, temp_iters), 
         SQL_NTS);

3.
status = SQLExecute(cda);

4.
SQLRowCount(cda, affected_num);

 I inserted 1000 records into the table, but affected_num set by SQLRowCount is 1

I searched the source code for this issue, and found in CC_send_query_append (connection.c:2062) 
image.png

My question is :
Is this line (connection.cpp : 2062) has a bug? I think it may use '+=' rather than '='.
lack of libpg.dll and headers, I cannot compile the source code and check my idea.
Attachment

Re: [bug-report]SQLRowCount set affected row numbers is incorrect when use SQLBindParameter and SQLSetStmtAttr for SQL_ATTR_PARAMSET_SIZE

From
Dave Cramer
Date:



On Thu, 18 Jul 2024 at 05:54, Chang Lu <luchang0624@gmail.com> wrote:
Hi,

I'm tring to insert and update a lot of records using  SQLBindParameter and SQLSetStmtAttr, but SQLRowsCount returned record number affected by ONLY the LAST SINGLE SQL STATEMENT.

Here is my code sequence.

1. 
status = SQLBindParameter(cda,
       KCPD_SCAST(KCPD_SQLUSMALLINT, parm_pos),
       KCPD_SCAST(KCPD_SQLSMALLINT, param_type),
       ftype,
        KCPD_SCAST(KCPD_SQLSMALLINT, mapped_sqltype),
        temp_column_size,
        sqltype == SQL_TYPE_TIMESTAMP ?  kcpd_odbc_date_scale : 0,  
        KCPD_RCAST(KCPD_SQLPOINTER, v.p_v),
        buflen,
        v.p_len);

2. 
status = SQLSetStmtAttr(cda,
         SQL_ATTR_PARAMSET_SIZE,
         KCPD_RCAST(void*, temp_iters), 
         SQL_NTS);

3.
status = SQLExecute(cda);

4.
SQLRowCount(cda, affected_num);

 I inserted 1000 records into the table, but affected_num set by SQLRowCount is 1

I searched the source code for this issue, and found in CC_send_query_append (connection.c:2062) 
image.png

My question is :
Is this line (connection.cpp : 2062) has a bug? I think it may use '+=' rather than '='.
lack of libpg.dll and headers, I cannot compile the source code and check my idea.

Good Morning Chang,

Thanks for the report. If you go to  postgresql-interfaces/psqlodbc (github.com) and file the issue we will be able to track it better 

Also since it will run tests for you there, you can test it with a Pull Request.

Cheers,

Dave
Attachment

RE: [bug-report]SQLRowCount set affected row numbers is incorrect when use SQLBindParameter and SQLSetStmtAttr for SQL_ATTR_PARAMSET_SIZE

From
"Wal, Jan Tjalling van der"
Date:

Hi Chang Lu,

 

I guess the behaviour is correct and as expected.

 

It seems that each of your updates affects one record.

And you receive back that correct count as number_affected for that (single) statement as just one.

 

To keep track of how many records have been successfully updated by the script/procedure, you probably need to add a step to sum the number_affected to get your final number of updated records.
It might worthwhile to log single values of number_affected that report back as zero.

 

Kind regards,                               Jan Tjalling van der Wal

 Wageningen Marine Reseach (WMR)  / formerly IMARES Institute for Marine Resources & Ecosystem Studies

Ankerpark 27, 1781 AG Den Helder       Postbus 57, 1780 AB Den Helder

Tel. +31 (0)317-4 87147 #                     GSM. +31 (0)626120915 (privé) #

# Ma+Di Vr 09:00-18:00, Wo XX, Do+Vr 09:00-18:00

Jan_Tjalling.vanderWal@wur.nl

From: Chang Lu <luchang0624@gmail.com>
Sent: Thursday, 18 July 2024 11:54
To: pgsql-odbc@lists.postgresql.org
Subject: [bug-report]SQLRowCount set affected row numbers is incorrect when use SQLBindParameter and SQLSetStmtAttr for SQL_ATTR_PARAMSET_SIZE

 

Hi,

 

I'm tring to insert and update a lot of records using  SQLBindParameter and SQLSetStmtAttr, but SQLRowsCount returned record number affected by ONLY the LAST SINGLE SQL STATEMENT.

 

Here is my code sequence.

 

1. 

status = SQLBindParameter(cda,
       KCPD_SCAST(KCPD_SQLUSMALLINT, parm_pos),
       KCPD_SCAST(KCPD_SQLSMALLINT, param_type),
       ftype,
        KCPD_SCAST(KCPD_SQLSMALLINT, mapped_sqltype),
        temp_column_size,
        sqltype == SQL_TYPE_TIMESTAMP ?  kcpd_odbc_date_scale : 0,  
        KCPD_RCAST(KCPD_SQLPOINTER, v.p_v),
        buflen,
        v.p_len);

 

2. 

status = SQLSetStmtAttr(cda,
         SQL_ATTR_PARAMSET_SIZE,
         KCPD_RCAST(void*, temp_iters), 
         SQL_NTS);

 

3.

status = SQLExecute(cda);

 

4.

SQLRowCount(cda, affected_num);

 

 I inserted 1000 records into the table, but affected_num set by SQLRowCount is 1

 

I searched the source code for this issue, and found in CC_send_query_append (connection.c:2062) 

My question is :

Is this line (connection.cpp : 2062) has a bug? I think it may use '+=' rather than '='.

lack of libpg.dll and headers, I cannot compile the source code and check my idea.

Attachment