Current C++ Application using ODBC API calling Oracle Client 10g (all versions), 11gR1 (v18.104.22.168) returns a LONG (4-byte integer) for "SELECT COUNT(*) from tablename"
Change Oracle client to 11gR1 (v22.214.171.124) or higher (11.2.x.y etc) and that same statement returns a DOUBLE (8-byte floating value equivalent to oracle NUMBER data type).
Since the backend DB was not changed in the testing (same version), there must be a client side setting (I presume) that has changed the default behaviour of this Aggregate SQL function to return a double rather than a long. I'm hoping to find such a setting that I can either set programmatically through an ODBC API call, or in the Oracle Client configuration itself.
I even tried using SQL CAST to make it an INT SELECT CAST(COUNT(*) AS INT) FROM tablename but that still returns a DOUBLE (8-byte floating NUMBER).
Note: given that I use ODBC, I've written alot of generic C++ code supporting the return value as LONG, as that is how it's been for 10 years via ODBC. I'd like to maintain that if possible without having to write ORACLE specific code within my applications.
Thanks, I will try it.
However, I'm looking for a solution in the client configuration so I don't have to change the > 100 different occurrences within my application where I use SELECT COUNT.
Plus using NUMBER(8,0) or NUMBER(12,0) is specific to Oracle, and since I have an ODBC Interface, which doesn't have code for specific backend database types, using that on SQL Server won't work. This would mean specific checks throughout the code for each database type that requires a different SELECT COUNT statement.
Edited by: wehnerr on Sep 7, 2012 3:00 PM
That change made it work, but I fear making the change over 100 times in my code to be specific to Oracle client or anything else.
I still hope there is an Oracle Client Configuration change that would help this, or perhaps something programmatic that I can set once ODBC Environment is acquired, that would force the ODBC SQL AGGREGATE function COUNT to return LONG (4-byte integer) rather than whatever else it is defaulting to.
Thanks for the suggestion.
That's alot if counting!!!
If these count values are something you can store in a LOOKUP table and refresh on a schedule, like each night or whatever,
then you can likely reduce front end code overburden by having one routine to fetch the row counts for a given table.
Understood. These 'COUNT' routines do it on various tables. It is a database with over 250 tables, and counts are intentionally dynamic and have very little consequence in the overall scheme of the user experience.
Can anyone help me with this?
There MUST be a method in the Oracle Client API that allows a setting to return all of these calls as LONG like they used to do.
RE: MAX, MIN, COUNT etc. when performed on a Long Integer / Number(10) column. I understand doing this on a Double would produce a double.