What data type should I bind as a query parameter for use with column NUMBER (15) in Oracle ODBC?

Question

I have just been bitten by the issue described in SO question Binding int64 (SQL_BIGINT), since the query parameter causes a run-time error in ODBC Oracle 10g .

I am migrating a C / C ++ application using ODBC 2 from SQL Server to Oracle. For numeric fields greater than NUMBER (9), it uses the __int64 data type, which is associated with queries as SQL_C_SBIGINT. Apparently this binding is not supported by Oracle ODBC. Now I have to do the conversion of the application to another method. Since I have little time, this is an unexpected problem. I would rather use a proven solution rather than a trial version and an error.

What type of data should be used for binding, for example. NUMBER (15) in Oracle? Is there a documented recommended solution? What are you using? Any suggestions?

I am particularly interested in solutions that do not require additional transformations. I can easily provide and consume numbers in the form of __int64 or char * (normal non-exponential form without thousands separator or decimal point). Any other format requires additional conversion on my part.

---

What I have tried so far:

SQL_C_CHAR

It looks like it will work for me. I was worried about the variability of the number format. But in my case it does not matter. Apparently, only the fraction point symbol changes with the system language settings.

And I do not understand why I should use an explicit conversion (e.g. TO_NUMERIC) in a SQL INSERT or UPDATE command. Everything works fine when I bind the parameter to SQL_C_CHAR as type C and SQL_NUMERIC (with the correct precision and scaling) as type SQL. I could not reproduce the effect of data corruption.

SQL_NUMERIC_STRUCT

I noticed that SQL_NUMERIC_STRUCT was added with ODBC 3.0 and decided to give it a try. I am disappointed.

In my situation, it’s enough, since the application doesn’t really use fractional numbers. But as a general solution ... Simple, I do not understand. I mean, I finally understood how it should be used. What I do not understand is why someone introduces a new structure of this kind, and then makes it work that way.

SQL_NUMERIC_STRUCT has all the necessary fields to represent any NUMERIC (or NUMBER or DECIMAL) values ​​with its precision and scale. Only they are not used.

When reading, ODBC sets the precision of the number (based on the precision of the column, except that Oracle returns greater precision, such as 20 for NUMBER (15)). But if your column has a fractional part (scale> 0), it is truncated by default. To read a number with an appropriate scale, you need to set the accuracy and scale yourself by calling SQLSetDescField before retrieving the data.

When writing, Oracle is grateful for the scale contained in SQL_NUMERIC_STRUCT. But the ODBC specification does not provide for this, and MS SQL Server ignores this value. So, back to SQLSetDescField again.

See HOWTO: Retrieving Numeric Data with SQL_NUMERIC_STRUCT and INF: How to Use the SQL_C_NUMERIC Data Type with Numeric Data for more information.

Why is ODBC not fully utilizing its own SQL_NUMERIC_STRUCT? I dont know. This seems to work, but I think it works too much.

---

I think I will use SQL_C_CHAR.

Answer 1

My personal preference is to make bind character variables (VARCHAR2), and let Oracle do the conversion from character to its own internal storage format. It's easy enough (in C) to get data values ​​presented as strings with zero completion in an acceptable format.

So, instead of writing SQL like this:

SET MY_NUMBER_COL = :b1 , MY_DATE_COL = :b2 

I am writing SQL as follows:

 SET MY_NUMBER_COL = TO_NUMBER( :b1 ) , MY_DATE_COL = TO_DATE( :b2 , 'YYYY-MM-DD HH24:MI:SS') 

and specify character strings as binding variables.

There are several advantages to this approach.

One of them concerns problems and errors which other types of data face.

Another advantage is that the binding values ​​are easier to decrypt on the Oracle event trace in protocol 10046.

In addition, PLUS EXPLAIN (I suppose) expects all bind variables to be VARCHAR2, so this means the statement being explained is slightly different from the actual statement being executed (due to implicit data conversions when the data types of the binding arguments in the actual expression are not VARCHAR2. )

And (less important), when I test the instruction in TOAD, it’s easier to just be able to enter lines in the input fields, and you don’t have to guess with the data type in the drop-down list.

I also allow the buitin TO_NUMBER and TO_DATE functions to validate the data. (In early versions of Oracle, at least I ran into problems binding the DATE value directly, and it circumvented (at least some) validation and allowed invalid date values ​​in the database.

This is only a personal preference based on past experience. I use the same approach with DBL Perl.

I wonder what Tom Keith (asktom.oracle.com) has to say about this topic?