Direct mapping Double to NUMBER(0,0) problem. - TopLink/JPA

My database table field is a NUMBER(0,0). That is, it has 0 precision and 0 scale. According to the Oracle SQL Reference this is how a floating point number can be specified.
Now Oracle JDBC thin driver maps this to a java.math.BigDecimal which is good. Reading 5.6 from the database yields a BigDecimal equal to 5.6.
In TopLink 9.0.3.1 I've mapped it as DirectToFieldMapping to a Double. It stores OK on INSERTs. However when I read the object using TopLink the Double attribute has it's fractional portion truncated. So that 5.6 becomes 5.0. Why is that?
Client Environment:
TopLink 9.0.3.1
Windows 2000
Oracle JDBC 9.2.0.1.0
Database Environment:
Oracle8i Enterprise Edition Release 8.1.6.1.0
Red Hat Linux release 6.1

REPOST 

My database table field is a NUMBER(0,0). That is, it has 0 precision and 0 scale. According to the Oracle SQL Reference this is how a floating point number can be specified.
Now Oracle JDBC thin driver maps this to a java.math.BigDecimal which is good. Reading 5.6 from the database yields a BigDecimal equal to 5.6.
In TopLink 9.0.3.1 I've mapped it as DirectToFieldMapping to a Double. It stores OK on INSERTs. However when I read the object using TopLink the Double attribute has it's fractional portion truncated. So that 5.6 becomes 5.0. Why is that?
Client Environment:
TopLink 9.0.3.1
Windows 2000
Oracle JDBC 9.2.0.1.0
Database Environment:
Oracle8i Enterprise Edition Release 8.1.6.1.0
Red Hat Linux release 6.1 That IS strange. What kind of Mapping are you using for the attribute which holds the Number? It might be a configuration problem because I'm not seeing any truncation when I use a DirectToFieldMapping to map a Double attribute to a column with NUMBER(0,0). I'm not using the same version of Oracle as you but that shouldn't make any difference. JIM

Related

I can't get text column from PostgreSQL database via dg4odbc

Hi guys,I have a database link to a PostgresPlus database using dg4odbc. When selecting text type columns, I can't get the data of text type columns.But in isql,I can get the data of text columns. Postgres Database TableCREATE TABLE edbuser.tc(  id integer,  t1 text,  t2 text,  t3 character varying(20),  t4 text,  cmt character varying(20)) Query within isql and Prostgres idt1t2t3t4cmt1abcdtest12aabbccddtest2 Executing SQL from Oracle to PostgreSQL Database idt3cmt1ctest12cctest2These text type columns are disappeared Initialization File HS_FDS_TRACE_LEVEL = 255HS_FDS_CONNECT_INFO = EnterprisedbHS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.soHS_LANGUAGE=american_america.al32utf8HS_NLS_NCHAR = UCS2HS_FDS_SQLLEN_INTERPRETATION=64set ODBCINI = /etc/odbc.ini
Hi,  What is the name and version of the Postgres ODBC driver you are using ?  If it is the V8 driver from Postgres then this will cause the problem and you should be using the latest v9 driver.If you are already using a V9 driver then we need to know the following -- version of DG4ODBC- version of RDBMS being used- Postgres version- a gateway 255 level trace from a select. To do this, in the gateway init<sid>.ora file set -HS_FDS_TRACE_LEVEL=255- start a new SQLPLUS session- run the select- end the session.- trace will be in $GTW_ORACLE_HOME/hs/log on Unix/Linux or $GTW_ORACLE_HOME/hs/trace on Windows.Either paste the trace here or upload to somewhere offline where it can be accessed.Regards,Mike
Hi, 1.version of DG4ODBC →ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped2.version of RDBMS being used→10.2.0.13.Postgres version→EnterpriseDB 9.3.5.18  I upload the trace file.http://www.filedropper.com/edbagt20396  Thank you.
Hi,  Thanks for the trace. It shows the DG4ODBC is version is 11.2.0.1.0 .It also shows that the text columns are converted to WLONGVARCHAR so it looks like you may be hitting a conversion problem as shown in the documentation -Oracle® Database Gateway for ODBCUser’s Guide11g Release 2 (11.2)E12070-03In Data Type Conversion on page A-2 it is stated:SQL_WLONGVARCHAR is mapped to an Oracle LONG if Oracle DB Character Set = Unicode. Otherwise, it is not supportedWhat is the character set of your Oracle 10.2.01 RDBMS ?A couple of other points -- 11.2 gateways are only certified with RDBMS 10.2.0.3 with patch 5965763 applied and with10.2.0.4 and 10.2.0.5 which include 5965763 applied so you should upgrade your RDBMS.- the latest 11g DG4ODBC available is 11.2.0.4 which is available from My Oracle Support as -Patch 13390677: 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVERYou should download and install that version as it has various fixes over the earlier version.Regards,Mike
Hi,My Oracle 10.2.0.1 RDBMS is big5.How could I to convert the SQL_WLONGVARCHAR to SQL_LONGVARCHAR?Does there have any method to covert it? Thank you.
Hi, It is the ODBC driver that converts the Postgres datatype to an ODBC datatype and it is this that is passed to the gateway to handle.  The gateway has no control over the datatypes passed to it and cannot change how that datatype is converted to an Oracle datatype.If you can get an ODBC trace this will show the ODBC datatypes to which the Postgres columns are converted by the driver.The documentation referred to earlier - Oracle® Database Gateway for ODBCUser’s Guide11g Release 2 (11.2)E12070-03details how DG4ODBC converts the ODBC datatypes to Oracle datatypes in the section -Data Type Conversion - page A-2.Regards,Mike

New Bug ? Always return number(38) as float (ODBC 11.1.0.7.20)

Hi,
In last release of ODBC, the driver always returns float (SQL_FLOAT) when the field is of type "number(38)". The option "bind number as float" (in workaround tab of ODBC configuration) is not working, there is no change when checked or not!
Do you have an workaround ?
Installed version : Oracle 11g ODAC (11.1.0.7.20) (version needed for Windows 7 support).
Thanks
Regards,
Patrick 
Same problem on Windows Vista or Windows XP after upgrading the ODBC driver.... The type returned by ODBC change between driver version !

JDK 1.7 JDBC driver for 11g

I was looking at the following link to find out the JDBC driver for 11g, but couldn't find any for JDK 1.7. Is there any other location where I can find the driver or is it not yet supported?Oracle Database 11g Release 2 JDBC Driver Downloads</title><meta name="Title" content="Oracle Da…
It's on the Oracle 12 page - about halfway down the list of links - ojdbc7.jarhttp://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html 
Thanks for your response. But I am not using 12c database. I have 11g database and wanted to use JDK1.7 JDBC driver for the same without upgrading the DB.
Thanks for your response. But I am not using 12c database. I have 11g database and wanted to use JDK1.7 JDBC driver for the same without upgrading the DB.I don't know what that means 'use JDK1.7 JDBC driver for the same'. You said you wanted to download the JDBC driver that supports JDK1.7 and it is on that page I gave you the link to. What does that have to do with what DB version you wanted to use it with? The jar file is used for code executing external to the database. Oracle 11g database installation comes with both ojdbc5.jar and ojdbc6.jar but the version of Java loaded INTO the DB is version 1.5 and you can't upgrade it or modify it. Oracle 12g DB includes both ojdbc6.jar and ojdbc7.jar and is the first version of the DB to support multiple Java versions.Just for info if others are interested see the 'Multiple JDK Support' section of the Java Developer's Guidehttp://docs.oracle.com/cd/E16655_01/java.121/e17658/chone.htm#sthref96
Starting from Oracle Database 12c Release 1 (12.1), Oracle JVM provides support for multiple JDK versions, including the latest JDK version. Typically, the supported versions are a default version and the next higher version. Oracle Database 12c Release 1 (12.1) supports JDK 6 and JDK 7, where JDK 6 is the default JDK version. So, if you do not set the JDK version explicitly, the JDK version in effect is JDK 6.
 
At any given point of time, only one JDK version is in effect for the Database. You can switch from one JDK version to another depending on your needs.
What I mean is that I have a setup of 11g DB, external Java program using JDK1.6 and ojdbc6.jar. I am planning to upgrade my JDK to 1.7 for the external Java program, but couldn't find any corresponding driver for 11g DB. I saw that the link you provided lists ojdbc7.jar but that is listed under 12c DB drivers and am concerned about its compatibility with 11g DB.
What I mean is that I have a setup of 11g DB, external Java program using JDK1.6 and ojdbc6.jar. I am planning to upgrade my JDK to 1.7 for the external Java program, but couldn't find any corresponding driver for 11g DB. I saw that the link you provided lists ojdbc7.jar but that is listed under 12c DB drivers and am concerned about its compatibility with 11g DB.You don't need to change the JDBC jar file. See the Oracle JDBC FAQ 'Which JDBC drivers support which versions of Oracle database'. The page hasn't been updated yet for the new driver.http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html
Thanks again for your response. The link actually confirms my concern that 11g JDBC drivers are not available for JDK 1.7, under "Which JDBC drivers support which versions of Javasoft's JDK?". But since it is an old page, I will need a confirmation, may be I think better to contact Oracle support for this. Even though as you suggested I can continue using the same drivers but there are some new features of JDBC introduced in JDK 1.7, that I need to make sure not to use.
Yes they are - Oracle drivers are backwards compatible so you can use the 12g drivers on Java 7 to connect to your 11g database, much like you can use OJDBC6 to connect to an old 10g database.
 The link actually confirms my concern that 11g JDBC drivers are not available for JDK 1.7The FAQ I posted is what shows the database and JDK versions supported by the different drivers. As that page shows you can use a 'JDBC 9.2.0' driver to talk to an 11.2.0 database if you needed to for some reason.But since it is an old page, I will need a confirmation, may be I think better to contact Oracle support for this.By all means contact Oracle support. But perhaps you missed it when I said this:The page hasn't been updated yet for the new driver.The new driver was just released earlier this year and Oracle just hasn't updated that FAQ yet.Even though as you suggested I can continue using the same drivers but there are some new features of JDBC introduced in JDK 1.7, that I need to make sure not to use.The functionality to do that has been built into every Java compiler for many years now. Just use the 'source' and/or 'target' command-line parameters.
E:\>javac
Usage: javac <options> <source files>
where possible options include:
. . .
  -source <release>          Provide source compatibility with specified release
  -target <release>          Generate class files for specific VM version
That 'source' parameter will ensure that your source code is compatible with the release you specify. The 'target' parameter will ensure that the class files will be the proper version for the release you specify.
Oracle JDBC drivers support all versions of the database that are supported at the time the driver is released. The 11g database was still supported when the 12c drivers were released so the 12c drivers support the 11g database. If you want to use JDK 7 you can either use the 11g ojdbc6.jar file which is supported with JDK 7 (see below), or you can use the 12c ojdbc7.jar file which supports 11g database. Oracle always recommends you use the most recent version of the driver so you should use 12c ojdbc7.jar. Going the other way, Oracle Database supports all versions of the JDBC drivers that are supported at the time the database is released. The 11g drivers were still supported when the 12c database was released so you can use the 11g drivers with the 12c database. Once again, Oracle always recommends you use the most recent version of the driver so while you can use 11g ojdbc6.jar with the 12c database Oracle recommends you use 12c ojdbc7.jar. Going across JDK versions is not always supported. Generally you cannot use ojdbcN.jar with JDK M if N > M. Oracle usually supports using ojdbcN.jar with JDK N+1, but not always. Mostly it has to do with when we get the resources to do the necessary testing. Oracle won't support a configuration we haven't tested. 11.2.0.3.0 ojdbc6.jar is supported with JDK 7. Frequently other combinations will work, they just aren't officially supported because they haven't been tested. If you have a problem with an unsupported combination we won't be able to help you. Douglas
dsurber/Douglas's answer is very helpful.  Thank you. I keep asking this question myself about which driver I should use because the download page is confusing.  If someone looks at these three links and they are running Oracle Database 11.2.0.3 with an app in WebLogic 12 on Java 7, which driver do you think they are going to download?
Oracle Database 12c Release 1 (12.1.0.1) drivers
Oracle Database 11g Release 2 (11.2.0.4), (11.2.0.3), (11.2.0.2.0), (11.2.0.1.0) drivers
Oracle Database 11g Release 1 (11.1.0.7), (11.1.0.6) drivers
 Most would chose the second one, but my understanding is that the first one is actually the recommended version.  If that is true, can the download page be updated to be more intuitive?  Here is a suggestion...
Oracle recommends using the latest driver compatible with your RDBMS and your program's java version:
JDBC 12.1.1 (for RDBMS 12.1.1, 11.2.0 ...(?)...
JDBC 11.2.0 (for RDBMS 11.2.0, 11.1,0, 10.2.0, 10.1.0, 9.2.0, 9.0.1)
JDBC 11.1.0 (for RDBMS 11.2.0, 11.1,0, 10.2.0, 10.1.0, 9.2.0, 9.0.1)
JDBC 10.2.0 (for RDBMS 11.2.0, 11.1,0, 10.2.0, 10.1.0, 9.2.0, 9.0.1, 8.1.7)
Bill
If that is true, can the download page be updated to be more intuitive? You would need to send any suggestions to Oracle. They are responsible for any and all web content. You may have noticed that the FAQ link I provided says it was last updated in 2009 which, as I mentioned earlier, explains why the new JDBC jar file isn't mentioned.
rp0428 wrote:
 
If that is true, can the download page be updated to be more intuitive?
You would need to send any suggestions to Oracle.
... possibly in this sub-forum: Downloads Issues
If you know what Oracle marketing is writing, this is reasonably clear.Oracle Database 12c Release 1 (12.1.0.1) driversOracle Database 11g Release 2 (11.2.0.4), (11.2.0.3), (11.2.0.2.0), (11.2.0.1.0) driversOracle Database 11g Release 1 (11.1.0.7), (11.1.0.6) driversWhat you have to understand is that "Oracle Database 12c Release 1" is the full official Oracle version number that describes a particular release of the JDBC drivers. It is not saying "use these drivers with Database 12c". The drivers are part of the database release. They don't have any version number independent of the database. So Oracle describes the driver version as the database version which included the drivers. Since the drivers are always released as part of a database release this makes some sense. The numbers in parens are the database patch release numbers. They refer to a particular patch release level of the database version. Yes, this is probably meaningful only to Oracle marketing but they are responsible for how things are presented on OTN. I'll bring this to the attention of our Product Manager but I don't know if even he has much control over it.
Hi dsurber Thanks a lot for all the information provided.But I am facing an issue over here since last 1 month, checked a lot forums, tried various alternatives, postponed the problem a lot times (due to other high priority tasks)& now this is on high priority. I seriously need your help, any sugessions are most welcome. Describing the problem...trying to connect to DB using oracle wallet from a java app..unix env..oracle installed - 11.2.0.4.I have tried the below jars for connectivity -1. Ojdbc6.jar (from Oracle Database 11g Release 2 JDBC Driver Downloads</title><meta name="Title" content="Oracle Da…)2. Ojdbc6.jar (from the one present in oracle installation folder - jdbc/lib)3. Ojdbc7.jar (from Oracle Database 12c Release 1 JDBC Driver Downloads) for 3 , getting the error : Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc12 in java.library.path  at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1860)  at java.lang.Runtime.loadLibrary0(Runtime.java:845)  at java.lang.System.loadLibrary(System.java:1084)  at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:4091)  at java.security.AccessController.doPrivileged(Native Method)  at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:4087)  at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:301)  at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)  at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:195)  at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:51)  at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)  at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:303)  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:230)  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:175)  at com.nordstrom.trickleEngine.cpp.dao.DataSource.getConnection(DataSource.java:247)  at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:202)  at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:350)  at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:262)  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101)  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)  at $Proxy23.getLastJobExecution(Unknown Source)  at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:85) for 1 & 2, getting the below error : Exception in thread "main" java.lang.UnsatisfiedLinkError: oracle.jdbc.driver.T2CConnection.getLibraryVersionNumber()I  at oracle.jdbc.driver.T2CConnection.getLibraryVersionNumber(Native Method)  at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:3561)  at java.security.AccessController.doPrivileged(Native Method)  at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:3556)  at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:269)  at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)  at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:165)  at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)  at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)  at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280)  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207)  at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:157)  at com.nordstrom.trickleEngine.cpp.dao.DataSource.getConnection(DataSource.java:247)  at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:202)  at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:350)  at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:262)  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101)  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)  at $Proxy23.getLastJobExecution(Unknown Source)  at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:85) The interesting part is it is working 100% fine with 11.2.0.1.0 & corresponsing jar.

ODBC Driver for Oracle Rdb Version 2 features for W2K

Help needed! We are stuck with the following problem:
We hook into a RDB Database (V6) with the ODBC Driver Version 2 which works fairly well.
However, this driver does not install with W2K.
The current version 3 driver does install but is useless because it does not handle data
in the formats DATE VMS and DOUBLE (due to inconsistent precisions).
The host application uses these formats for Timestamps (a precision of 1 second would be sufficient)
and currency date (2 decimal digits) and cannot be changed easily.
My opinion: Does nobody care about preserving customer investments based on functionality?
Question: Is there a driver for RDB around with the functionality of version 2 that works under W2K or WXP?
For Windows 2000 try installing version 2 for Windows 9x.

ODBC 9.02 Varchar2 Truncation

In Microsoft Access XP (2002) using the Oracle 9.02 ODBC driver, I am linked to a table that has a field defined as VARCHAR2(4000). When editing the field, any data with more than 2000 characters is getting truncated at 2000 characters; no error message.
The Oracle 8.01.07 ODBC driver works fine attaching to the same table.
On the 9.02 driver, the "Force Retrieval of Long Columns" is checked and I have tried it unchecked. Is there a way around this? Is it a bug? 
As a follow on to this, I have tried installing the 9.2.0.5.4 and 9.2.0.6.0 versions of the ODBC driver and now I cannot edit ANY data in this field. HELP!!!! 
More information; these issues only appear to happen when any of the 9.2 ODBC drivers are talking to an 8.1.7 database. I tried the same thing on an identical table in a 9.2 database and it worked lie a charm; all 4000 characters were able to be entered/copied/inserted.
Is there a compatibility switch for this driver to properly access 8i tables containing larger VARCHAR2(2000+) datatypes. The first 9.02 version of the ODBC driver seemed to work properly with up to VARCHAR2(2000) size fields; it truncated anything beyond that. In the latest 9.2.0.5.4 and 9.2.0.6.0 versions of the ODBC drivers, they seem to not like larger VARCHAR2() fields at all.
Basically as the versions of the 9.2.x ODBC drivers have become progressively less compatible with 8i databases. If there is some sort of compatibility mode that should set (i.e. SQL_WCHAR support) then that might explain it.
Any help on this would be GREATLY appreciated.

Categories

Resources