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

Hi,

Attached is a sample test program.

regards,
Hiroshi Inoue

On 2017/08/17 12:54, Asif Shaikh wrote:
> 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