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
SQLDriverConnect - to connect to driver
SQLGetInfo (hDBC, SQL_ODBC_API_CONFORMANCE, (PTR)&confLevel, sizeof(confLevel), NULL); - To get the conformance level of database.
SQLGetInfo(hDBC, SQL_CURSOR_COMMIT_BEHAVIOR, (PTR)&cursorCB, sizeof(cursorCB), NULL); - To get the commit behavior for database.
SQLGetInfo(hDBC, SQL_TXN_CAPABLE, (PTR)&txnSupport, sizeof(txnSupport), NULL); - To get the transaction support information for database.
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
Finally the following query is executed using SQLExecDirect SELECT DISTINCT customer_id INTO <New_Table_NAME> FROM <Existing_Table_Name>.
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.
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
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.
