Re: Postgre driver(9.06.04.00) connected to Amazon redshift, is returning wrong count in SQLRowCount

Hi Hiroshi Inoue,

Thanks for replying.
Can you please share your simple test program.
I will try it at my end too because with my program it is giving me
issues.
I will try to build a simple test program too using my odbctest utility
which I can share with you.

Thanks & Regards
Asif G. Shaikh

From: "Inoue, Hiroshi" <h-inoue(at)dream(dot)email(dot)ne(dot)jp>
To: Asif Shaikh <asif(dot)shaikh(at)in(dot)ibm(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Date: 08/17/2017 09:14 AM
Subject: Re: [ODBC] Postgre driver(9.06.04.00) connected to Amazon
redshift, is returning wrong count in SQLRowCount

Hi,

On 2017/08/16 17:40, Asif Shaikh wrote:
Hi,

I am Asif from IBM and need an urgent help on following.

We are facing an issue with postgre driver(9.06.04.00) connecting to
Amazon Redshift database.
We are executing the following query
SELECT DISTINCT customer_id INTO <New_Table_NAME>FROM
<Existing_Table_Name>.

The query gets executed properly and the table also gets created.
The problem here is that postgre driver returns wrong count(affected
number of rows) for this query as output paramter in SQLRowCount.
The sequence of driver calls are as follows
1. SQLDriverConnect - to connect to driver
2. SQLGetInfo (hDBC, SQL_ODBC_API_CONFORMANCE, (PTR)&confLevel,
sizeof(confLevel), NULL); - To get the conformance level of database.
3. SQLGetInfo(hDBC, SQL_CURSOR_COMMIT_BEHAVIOR, (PTR)&cursorCB,
sizeof(cursorCB), NULL); - To get the commit behavior for database.
4. SQLGetInfo(hDBC, SQL_TXN_CAPABLE, (PTR)&txnSupport, sizeof
(txnSupport), NULL); - To get the transaction support information for
database.
5. SQLGetInfo(hDBC, SQL_MAX_CONCURRENT_ACTIVITIES,
(PTR)&iMaxConcurrent, sizeof(iMaxConcurrent), NULL); - To get the maximum
concurrent statement information

The output of above 4 commands is as below(mentioned in bold). These may
not be important for this question but mentioning it just in case if any
output has any relation with the issue we are facing.
Server <DSN NAME> conforms to LEVEL 1.
Server's cursor commit behavior: PRESERVE
Transactions supported: ALL
Maximum number of concurrent statements: 0
6.
6. Finally the following query is executed using SQLExecDirect
SELECT DISTINCT customer_id INTO <New_Table_NAME> FROM
<Existing_Table_Name>.
7. After executing this query, we check SQLNumResultCols(hStmt,
&nResultCols); In this case, the result count is 0 which is expected as
its not a normal select query.
8. Finally we call SQLRowCountAPI which doesn't modify the rowcount
variable passed to it as output parameter whereas the same variable gets
the correct value stored for other databases which supports such statement
e.g. SQL server or even for update/insert this works fine.

I get an expected result here in a simple test program.

regards,
Hiroshi Inoue

9.
Because of this wrong count, our product is facing some blocking issues
and it is delaying our release.
The above driver calls sequence is from our general tool for odbc
connection test using which also we could reproduce the issue.

Our applications including the odbc connection tester are 64 bit
applications and we are facing this issue on Windows, RHEL and AIX
Please help us rectify the issue asap.

Thanks & Regards
Asif G. Shaikh