oracle odbc驱动不支持BIGINT,需使用其他类型代替
来源:互联网 发布:c语言经典编程题 编辑:程序博客网 时间:2024/06/05 07:36
一:oracle官方文档
Features Not Supported
Oracle ODBC Driver does not support the following ODBC 3.0 features:
Interval data types
SQL_C_UBIGINT
andSQL_C_SBIGINT
C data type identifiersShared connections
Shared environments
The
SQL_LOGIN_TIMEOUT
attribute of SQLSetConnectAttrThe expired password option
二:参考文档
https://stackoverflow.com/questions/3258457/what-datatype-should-i-bind-as-query-parameter-to-use-with-number15-column-in
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.
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.
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?
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.
What I have tried so far:
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_NUMERIC_STRUCT
I've noticed SQL_NUMERIC_STRUCT added with ODBC 3.0 and decided to give it a try. I am disappointed.
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.
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.
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.
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.
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.
I guess I'll use SQL_C_CHAR.
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.
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?
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.
What I have tried so far:
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_NUMERIC_STRUCT
I've noticed SQL_NUMERIC_STRUCT added with ODBC 3.0 and decided to give it a try. I am disappointed.
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.
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.
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.
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.
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.
I guess I'll use SQL_C_CHAR.
三:解决办法
使用SQL_C_CHAR来替换
SQL_C_SBIGINT
char cText[38];
SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,38,0,(SQLPOINTER)cText,0,NULL);
strcpy(cText,"123456789123456789")
SQLLEN Ind1;
SQLBindCol(hstmt,1,SQL_CHAR,cText,38,&Ind1);
long ltmp = atol(cText);
阅读全文
0 0
- oracle odbc驱动不支持BIGINT,需使用其他类型代替
- Oracle ODBC 驱动项目
- ORACLE ODBC驱动安装
- sqlalchemy使用SQLite时BIGINT不支持自增
- MySql ODBC驱动的使用
- ODBC、JDBC和四种驱动类型
- ODBC驱动程序不支持所需的属性
- ODBC 驱动程序不支持所需的属性
- 在windows下安装oracle odbc驱动
- Oracle ODBC driver 驱动 官方 下载
- Oracle Instant Client ODBC驱动安装步骤
- Oracle的32位ODBC驱动
- php使用odbc访问oracle
- Mysql bigint类型 BUG
- VC中,使用ODBC 驱动连接Mysql
- oracle不支持的字符集,nvarchar2类型
- jsp/java代码中用jdbc驱动代替jdbc-odbc桥连接SQL Server数据库
- oracle odbc 驱动安装(不安装oracle客户端)
- java多线程处理导入数据拆分List集合,同步处理插入数据
- Android studio如何将本地变更文件移changelist
- Eclipse打开报错“java was started but returned exit code=13”
- (一) Hyperledger Fabric在CentOS 7.2 64位系统的开发环境搭建
- 图像识别与处理之Opencv——Mat表达式的运算(矩阵的一些运算)
- oracle odbc驱动不支持BIGINT,需使用其他类型代替
- Linux CentOS 7×64 & JDK 1.8.0_11 安装与配置
- 题目:输出张三李四的姓名,年龄,地址,性别以及体重
- 124. Binary Tree Maximum Path Sum
- 安卓API指南之Intent 和 Intent 过滤器
- apicloud div 滚动到顶部
- iOS中根据视图生成图片,裁剪图片
- 封装的基类activity
- python 读写csv