I have just been bitten by issue described in SO question Binding int64 (SQL_BIGINT) as query parameter causes error during execution in Oracle 10g ODBC.
由于查询参数在Oracle 10g ODBC执行过程中会导致错误，所以问题绑定int64 (SQL_BIGINT)中所描述的问题让我感到困扰。
I'm porting a C/C++ application using ODBC 2 from SQL Server to Oracle. For numeric fields exceeding NUMBER(9) it uses __int64 datatype which is bound to queries as SQL_C_SBIGINT. Apparently such binding is not supported by Oracle ODBC. I must now do an application wide conversion to another method. Since I don't have much time---it's an unexpected issue---I would rather use proved solution, not trial and error.
我正在将使用ODBC 2的C/ c++应用程序从SQL服务器移植到Oracle。对于超过数字(9)的数字字段，它使用__int64数据类型，该数据类型绑定到查询SQL_C_SBIGINT。显然，Oracle ODBC不支持这种绑定。我现在必须对另一种方法进行应用程序范围的转换。因为我没有太多的时间---这是一个意想不到的问题---我宁愿使用证明的解决方案，而不是反复试验。
What datatype should be used to bind as e.g. NUMBER(15) in Oracle? Is there documented recommended solution? What are you using? Any suggestions?
应该使用什么数据类型来绑定，例如Oracle中的NUMBER(15) ?是否有文档推荐的解决方案?你使用什么?有什么建议吗?
I'm especially interested in solutions that do not require any additional conversions. I can easily provide and consume numbers in form of __int64 or char* (normal non-exponential form without thousands separator or decimal point). Any other format requires additional conversion on my part.
我对不需要任何额外转换的解决方案特别感兴趣。我可以很容易地以__int64或char*的形式提供和使用数字(正常的非指数形式，没有千位分隔符或十进制点)。任何其他格式都需要我进行额外的转换。
What I have tried so far:
到目前为止我所尝试的:
SQL_C_CHAR
SQL_C_CHAR
Looks like it's going to work for me. I was worried about variability of number format. But in my use case it doesn't seem to matter. Apparently only fraction point character changes with system language settings.
看起来对我有用。我担心数字格式的可变性。但在我的用例中，这似乎并不重要。显然只有分数点字符随系统语言设置而改变。
And I don't see why I should use explicit cast (e.g. TO_NUMERIC) in SQL INSERT or UPDATE command. Everything works fine when I bind parameter with SQL_C_CHAR as C type and SQL_NUMERIC (with proper precision and scale) as SQL type. I couldn't reproduce any data corruption effect.
我不明白为什么我应该在SQL INSERT或UPDATE命令中使用显式转换(例如TO_NUMERIC)。当我将参数与SQL_C_CHAR作为C类型绑定时，并且将SQL_NUMERIC(具有适当的精度和比例)绑定为SQL类型时，一切都很正常。我无法再现任何数据损坏的效果。
SQL_NUMERIC_STRUCT
SQL_NUMERIC_STRUCT
I've noticed SQL_NUMERIC_STRUCT added with ODBC 3.0 and decided to give it a try. I am disappointed.
我注意到使用ODBC 3.0添加了SQL_NUMERIC_STRUCT，并决定尝试一下。我很失望。
In my situation it is enough, as the application doesn't really use fractional numbers. But as a general solution... Simply, I don't get it. I mean, I finally understood how it is supposed to be used. What I don't get is: why anyone would introduce new struct of this kind and then make it work this way.
在我的情况下，这就足够了，因为应用程序实际上不使用小数。但作为通解……很简单，我不明白。我的意思是，我终于明白应该怎么使用它了。我不明白的是:为什么有人会引入这种新的结构体，然后让它以这种方式工作。
SQL_NUMERIC_STRUCT has all the needed fields to represent any NUMERIC (or NUMBER, or DECIMAL) value with it's precision and scale. Only they are not used.
SQL_NUMERIC_STRUCT拥有所有需要的字段来表示任何数字(或数字，或十进制)值，并具有其精度和可伸缩性。只是它们没有被使用。
When reading, ODBC sets precision of the number (based on precision of the column; except that Oracle returns bigger precision, e.g. 20 for NUMBER(15)). But if your column has fractional part (scale > 0) it is by default truncated. To read number with proper scale you need to set precision and scale yourself with SQLSetDescField call before fetching data.
读取时，ODBC设置数字精度(基于列的精度);除了Oracle返回更大的精度，例如数字(15)的20。但是如果您的列具有小数部分(scale > 0)，则默认为截断。要以适当的比例读取数字，需要在获取数据之前设置精度并使用SQLSetDescField调用对自己进行缩放。
When writing, Oracle thankfully respects scale contained in SQL_NUMERIC_STRUCT. But ODBC spec doesn't mandate it and MS SQL Server ignores this value. So, back to SQLSetDescField again.
在编写时，Oracle感谢地考虑SQL_NUMERIC_STRUCT中包含的比例。但是ODBC规范没有强制要求它，而MS SQL Server忽略了这个值。回到SQLSetDescField。
See HOWTO: Retrieving Numeric Data with SQL_NUMERIC_STRUCT and INF: How to Use SQL_C_NUMERIC Data Type with Numeric Data for more information.
参见HOWTO:使用SQL_NUMERIC_STRUCT和INF检索数字数据:如何使用SQL_C_NUMERIC数据类型和数字数据来获取更多信息。
Why ODBC doesn't fully use its own SQL_NUMERIC_STRUCT? I don't know. It looks like it works but I think it's just too much work.
为什么ODBC没有充分使用它自己的SQL_NUMERIC_STRUCT?我不知道。这看起来是可行的，但我觉得工作量太大了。
I guess I'll use SQL_C_CHAR.
我想我会用SQL_C_CHAR。
1 个解决方案
#1
2
My personal preference is to make the bind variables character strings (VARCHAR2), and let Oracle do the conversion from character to it's own internal storage format. It's easy enough (in C) to get data values represented as null terminated strings, in an acceptable format.
我个人的偏好是使绑定变量字符串(VARCHAR2)，并让Oracle从字符转换为它自己的内部存储格式。(在C语言中)很容易以可接受的格式将数据值表示为空终止字符串。
So, instead of writing SQL like this:
所以，与其这样写SQL:
SET MY_NUMBER_COL = :b1
, MY_DATE_COL = :b2
I write the SQL like this:
我这样写SQL:
SET MY_NUMBER_COL = TO_NUMBER( :b1 )
, MY_DATE_COL = TO_DATE( :b2 , 'YYYY-MM-DD HH24:MI:SS')
and supply character strings as the bind variables.
并提供字符字符串作为绑定变量。
There are a couple of advantages to this approach.
这种方法有几个优点。
One is that works around the issues and bugs one encounters with binding other data types.
一种方法是解决绑定其他数据类型时遇到的问题和bug。
Another advantage is that bind values are easier to decipher on an Oracle event 10046 trace.
另一个优点是绑定值更容易在Oracle事件10046跟踪上进行解密。
Also, an EXPLAIN PLAN (I believe) expects all bind variables to be VARCHAR2, so that means the statement being explained is slightly different than the actual statement being executed (due to the implicit data conversions when the datatypes of the bind arguments in the actual statement are not VARCHAR2.)
另外，一个解释计划(我认为)期望所有绑定变量都是VARCHAR2，因此，这意味着所解释的语句与实际执行的语句略有不同(由于在实际语句中绑定参数的数据类型不是VARCHAR2时，隐式数据转换)。
And (less important) when I'm testing of the statement in TOAD, it's easier just to be able to type in strings in the input boxes, and not have to muck with changing the datatype in a dropdown list box.
而且(不太重要)当我在TOAD中测试语句时，只需在输入框中键入字符串，而不必在下拉列表框中更改数据类型，就更容易了。
I also let the buitin TO_NUMBER and TO_DATE functions validate the data. (In earlier versions of Oracle at least, I encountered issues with binding a DATE value directly, and it bypassed (at least some of) the validity checking, and allowed invalid date values to be stored in the database.
我还让buitin TO_NUMBER和TO_DATE函数验证数据。(至少在Oracle的早期版本中，我遇到了直接绑定日期值的问题，它绕过了有效性检查，并允许将无效的日期值存储在数据库中。
This is just a personal preference, based on past experience. I use this same approach with Perl DBD.
这只是个人偏好，基于过去的经验。我对Perl DBD使用了相同的方法。
I wonder what Tom Kyte (asktom.oracle.com) has to say about this topic?
我想知道Tom Kyte (asktom.oracle.com)对这个话题有什么看法?
#1
2
My personal preference is to make the bind variables character strings (VARCHAR2), and let Oracle do the conversion from character to it's own internal storage format. It's easy enough (in C) to get data values represented as null terminated strings, in an acceptable format.
我个人的偏好是使绑定变量字符串(VARCHAR2)，并让Oracle从字符转换为它自己的内部存储格式。(在C语言中)很容易以可接受的格式将数据值表示为空终止字符串。
So, instead of writing SQL like this:
所以，与其这样写SQL:
SET MY_NUMBER_COL = :b1
, MY_DATE_COL = :b2
I write the SQL like this:
我这样写SQL:
SET MY_NUMBER_COL = TO_NUMBER( :b1 )
, MY_DATE_COL = TO_DATE( :b2 , 'YYYY-MM-DD HH24:MI:SS')
and supply character strings as the bind variables.
并提供字符字符串作为绑定变量。
There are a couple of advantages to this approach.
这种方法有几个优点。
One is that works around the issues and bugs one encounters with binding other data types.
一种方法是解决绑定其他数据类型时遇到的问题和bug。
Another advantage is that bind values are easier to decipher on an Oracle event 10046 trace.
另一个优点是绑定值更容易在Oracle事件10046跟踪上进行解密。
Also, an EXPLAIN PLAN (I believe) expects all bind variables to be VARCHAR2, so that means the statement being explained is slightly different than the actual statement being executed (due to the implicit data conversions when the datatypes of the bind arguments in the actual statement are not VARCHAR2.)
另外，一个解释计划(我认为)期望所有绑定变量都是VARCHAR2，因此，这意味着所解释的语句与实际执行的语句略有不同(由于在实际语句中绑定参数的数据类型不是VARCHAR2时，隐式数据转换)。
And (less important) when I'm testing of the statement in TOAD, it's easier just to be able to type in strings in the input boxes, and not have to muck with changing the datatype in a dropdown list box.
而且(不太重要)当我在TOAD中测试语句时，只需在输入框中键入字符串，而不必在下拉列表框中更改数据类型，就更容易了。
I also let the buitin TO_NUMBER and TO_DATE functions validate the data. (In earlier versions of Oracle at least, I encountered issues with binding a DATE value directly, and it bypassed (at least some of) the validity checking, and allowed invalid date values to be stored in the database.
我还让buitin TO_NUMBER和TO_DATE函数验证数据。(至少在Oracle的早期版本中，我遇到了直接绑定日期值的问题，它绕过了有效性检查，并允许将无效的日期值存储在数据库中。
This is just a personal preference, based on past experience. I use this same approach with Perl DBD.
这只是个人偏好，基于过去的经验。我对Perl DBD使用了相同的方法。
I wonder what Tom Kyte (asktom.oracle.com) has to say about this topic?
我想知道Tom Kyte (asktom.oracle.com)对这个话题有什么看法?