Re: [ODBC] Postgre driver(9.06.04.00) connected to Amazon redshift, isreturning wrong count in SQLRowCount - Mailing list pgsql-odbc
| From | Asif Shaikh |
|---|---|
| Subject | Re: [ODBC] Postgre driver(9.06.04.00) connected to Amazon redshift, isreturning wrong count in SQLRowCount |
| Date | |
| Msg-id | OF5E3345B1.A8DCC8D0-ON0025817F.00434555-6525817F.0043A38C@notes.na.collabserv.com Whole thread Raw |
| In response to | Re: [ODBC] Postgre driver(9.06.04.00) connected to Amazon redshift,is returning wrong count in SQLRowCount ("Inoue, Hiroshi" <h-inoue@dream.email.ne.jp>) |
| List | pgsql-odbc |
Hi Hiroshi Inoue,
I see the same error with the code you sent.
Did you build your application for 64 bit and used 64 bit postgre driver(9.06.04.00) ?
Thanks & Regards
Asif G. Shaikh
From: "Inoue, Hiroshi" <h-inoue@dream.email.ne.jp>
To: Asif Shaikh <asif.shaikh@in.ibm.com>
Cc: pgsql-odbc@postgresql.org
Date: 08/17/2017 03:21 PM
Subject: Re: [ODBC] 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@dream.email.ne.jp>
To: Asif Shaikh <asif.shaikh@in.ibm.com>
Cc: pgsql-odbc@postgresql.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[attachment "simple_t.c" deleted by Asif Shaikh/India/IBM]
I see the same error with the code you sent.
Did you build your application for 64 bit and used 64 bit postgre driver(9.06.04.00) ?
Thanks & Regards
Asif G. Shaikh
From: "Inoue, Hiroshi" <h-inoue@dream.email.ne.jp>
To: Asif Shaikh <asif.shaikh@in.ibm.com>
Cc: pgsql-odbc@postgresql.org
Date: 08/17/2017 03:21 PM
Subject: Re: [ODBC] 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@dream.email.ne.jp>
To: Asif Shaikh <asif.shaikh@in.ibm.com>
Cc: pgsql-odbc@postgresql.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[attachment "simple_t.c" deleted by Asif Shaikh/India/IBM]
pgsql-odbc by date:
Previous
From: "Inoue, Hiroshi"Date:
Subject: Re: [ODBC] Postgre driver(9.06.04.00) connected to Amazon redshift,is returning wrong count in SQLRowCount
Next
From: "Asif Shaikh"Date:
Subject: Re: [ODBC] Postgre driver(9.06.04.00) connected to Amazon redshift, isreturning wrong count in SQLRowCount