Re: 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 *SQLRowCount**API 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