Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: strange problem using DATE fields from coldfusion/MX (jdbc)

Re: strange problem using DATE fields from coldfusion/MX (jdbc)

From: Yoram Ayalon <yoram.ayalon_at_structuredweb.com>
Date: 19 Jul 2006 07:04:13 -0700
Message-ID: <1153317853.690488.110980@m79g2000cwm.googlegroups.com> 





Joe,



I hope you can help me, since I am stuck between Oracle and Adobe, each
blaming the other...



I tried to add the parameters to the connection string, the obvious way
was Data Sources section of the ColdFusion, which is where you define
all the parameters to the JDBC driver. they have a section calls
"connection string", under "advanced settings",  which was empty, and I
added the string you specified. no luck. I noticed that after some
time, either a reset of ColdFusion or a reboot, these settings get
erased.



these are the settings on the machine as reported by coldFusion. in the
summary of the data source you don't see the connection string.



I am no Java expert. I see no other place where to configure the JDBC
connection and add to it the connection parameter.



the JDBC driver we are using is the oracle 10g, ojdbc14.jar



Java and JVM




Java virtual machine path  C:/CFusionMX7/runtime/jre
Initial memory size  MB


Maximum memory size  512 MB


Class path


-Dcoldfusion.classPath={application.home}/../../classes,{application.home}/../lib/updates,{application.home}/../lib/,{application.home}/../gateway/lib/,{application.home}/../wwwroot/WEB-INF/cfform/jars,D:\oracle\ora92\jdbc\lib\10_ojdbc14.jar



JVM arguments  -server -Dsun.io.useCanonCaches=false
-XX:MaxPermSize=128m -Dcoldfusion.rootDir={application.home}/../
-Dcoldfusion.libPath={application.home}/../lib




oracle_develop    (the data source we are trying to configure)




CF data source name  oracle_develop


Description


Driver


JDBC URL  jdbc:oracle:thin:@dbdevelop:1521:FDEV12
Username  devuser


Login timeout  30 seconds


Long text buffer size  64000


Timeout  1200 seconds


Maintain connections  Yes


Interval  420 seconds


Restricted SQL operations


Disable connections  No



JVM details




Java Version  1.4.2_09


Java Vendor  Sun Microsystems Inc.


Java Vendor URL  http://java.sun.com/


Java Home  C:\CFusionMX7\runtime\jre


Java File Encoding  Cp1252


Java Default Locale  en_US


File Separator  \


Path Separator  ;


Line Separator  Chr(13)


User Name  Administrator


User Home  C:\Documents and Settings\Administrator.STRUCTURED
User Dir  C:\CFusionMX7\runtime\bin


Java VM Specification Version  1.0


Java VM Specification Vendor  Sun Microsystems Inc.
Java VM Specification Name  Java Virtual Machine Specification
Java VM Version  1.4.2_09-b05


Java VM Vendor  Sun Microsystems Inc.


Java VM Name  Java HotSpot(TM) Server VM
Java Specification Version  1.4


Java Specification Vendor  Sun Microsystems Inc.
Java Specification Name  Java Platform API Specification
Java Class Version  48.0


CF Server Java Class Path

;C:/CFusionMX7/runtime/../lib/ant-launcher.jar;
C:/CFusionMX7/runtime/../lib/ant.jar;
C:/CFusionMX7/runtime/../lib/axis.jar;
C:/CFusionMX7/runtime/../lib/backport-util-concurrent.jar;
C:/CFusionMX7/runtime/../lib/bcel.jar;
C:/CFusionMX7/runtime/../lib/cdo.jar;
C:/CFusionMX7/runtime/../lib/cdohost.jar;
C:/CFusionMX7/runtime/../lib/cf4was.jar;
C:/CFusionMX7/runtime/../lib/cf4was_ae.jar;
C:/CFusionMX7/runtime/../lib/cfmx-ssl.jar;
C:/CFusionMX7/runtime/../lib/cfusion.jar;
C:/CFusionMX7/runtime/../lib/commons-beanutils-1.5.jar;
C:/CFusionMX7/runtime/../lib/commons-collections-2.1.jar;
C:/CFusionMX7/runtime/../lib/commons-digester-1.3.jar;
C:/CFusionMX7/runtime/../lib/commons-digester-1.7.jar;
C:/CFusionMX7/runtime/../lib/commons-discovery-0.2.jar;
C:/CFusionMX7/runtime/../lib/commons-discovery.jar;
C:/CFusionMX7/runtime/../lib/commons-logging-1.0.2.jar;
C:/CFusionMX7/runtime/../lib/commons-logging-api-1.0.2.jar;
C:/CFusionMX7/runtime/../lib/commons-net-1.2.2.jar;
C:/CFusionMX7/runtime/../lib/crystal.jar;
C:/CFusionMX7/runtime/../lib/flashgateway.jar;
C:/CFusionMX7/runtime/../lib/flashremoting_update.jar;
C:/CFusionMX7/runtime/../lib/flex-assemblerservice.jar;
C:/CFusionMX7/runtime/../lib/flex-messaging-common.jar;
C:/CFusionMX7/runtime/../lib/flex-messaging-opt.jar;
C:/CFusionMX7/runtime/../lib/flex-messaging-req.jar;
C:/CFusionMX7/runtime/../lib/flex-messaging.jar;
C:/CFusionMX7/runtime/../lib/httpclient.jar;
C:/CFusionMX7/runtime/../lib/ib61patch.jar;
C:/CFusionMX7/runtime/../lib/ib6addonpatch.jar;
C:/CFusionMX7/runtime/../lib/ib6core.jar;
C:/CFusionMX7/runtime/../lib/ib6swing.jar;
C:/CFusionMX7/runtime/../lib/ib6util.jar;
C:/CFusionMX7/runtime/../lib/im.jar;
C:/CFusionMX7/runtime/../lib/iText.jar;
C:/CFusionMX7/runtime/../lib/iTextAsian.jar;
C:/CFusionMX7/runtime/../lib/izmado.jar;
C:/CFusionMX7/runtime/../lib/jakarta-oro-2.0.6.jar;
C:/CFusionMX7/runtime/../lib/java2wsdl.jar;
C:/CFusionMX7/runtime/../lib/jaxrpc.jar;
C:/CFusionMX7/runtime/../lib/jdom.jar;
C:/CFusionMX7/runtime/../lib/jeb.jar;
C:/CFusionMX7/runtime/../lib/jintegra.jar;
C:/CFusionMX7/runtime/../lib/ldap.jar;
C:/CFusionMX7/runtime/../lib/ldapbp.jar;
C:/CFusionMX7/runtime/../lib/log4j.jar;
C:/CFusionMX7/runtime/../lib/macromedia_drivers.jar;
C:/CFusionMX7/runtime/../lib/mail.jar;
C:/CFusionMX7/runtime/../lib/msapps.jar;
C:/CFusionMX7/runtime/../lib/pbclient42RE.jar;
C:/CFusionMX7/runtime/../lib/pbembedded42RE.jar;
C:/CFusionMX7/runtime/../lib/pbserver42RE.jar;
C:/CFusionMX7/runtime/../lib/pbtools42RE.jar;
C:/CFusionMX7/runtime/../lib/poi-2.5.1-final-20040804.jar;
C:/CFusionMX7/runtime/../lib/poi-contrib-2.5.1-final-20040804.jar;
C:/CFusionMX7/runtime/../lib/ri_generic.jar;
C:/CFusionMX7/runtime/../lib/saaj.jar;
C:/CFusionMX7/runtime/../lib/smack.jar;
C:/CFusionMX7/runtime/../lib/smpp.jar;
C:/CFusionMX7/runtime/../lib/STComm.jar;
C:/CFusionMX7/runtime/../lib/tools.jar;
C:/CFusionMX7/runtime/../lib/tt-bytecode.jar;
C:/CFusionMX7/runtime/../lib/vadmin.jar;
C:/CFusionMX7/runtime/../lib/verity.jar;
C:/CFusionMX7/runtime/../lib/vparametric.jar;
C:/CFusionMX7/runtime/../lib/vsearch.jar;
C:/CFusionMX7/runtime/../lib/wc50.jar;
C:/CFusionMX7/runtime/../lib/webchartsJava2D.jar;
C:/CFusionMX7/runtime/../lib/wsdl2java.jar;
C:/CFusionMX7/runtime/../lib/wsdl4j-1.5.1.jar;
C:/CFusionMX7/runtime/../lib/wsdl4j.jar;
C:/CFusionMX7/runtime/../lib/xalan.jar;
C:/CFusionMX7/runtime/../lib/xercesImpl.jar;
C:/CFusionMX7/runtime/../lib/xml-apis.jar;
C:/CFusionMX7/runtime/../lib/;
C:/CFusionMX7/runtime/../gateway/lib/examples.jar;
C:/CFusionMX7/runtime/../gateway/lib/flex-messaging-gateway.jar;
C:/CFusionMX7/runtime/../gateway/lib/;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/batik-awt-util.jar;
 C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/batik-css.jar;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/batik-ext.jar;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/batik-transcoder.jar;
 C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/batik-util.jar;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/commons-discovery.jar;

C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/commons-logging.jar;
 C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/concurrent.jar;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/flex.jar;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/jakarta-oro-2.0.7.jar;
 C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/jcert.jar;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/jnet.jar;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/jsse.jar;
C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/oscache.jar;


C:/CFusionMX7/runtime/../wwwroot/WEB-INF/cfform/jars/;
D:/oracle/ora92/jdbc/lib/10_ojdbc14.jar;
Java Class Path  C:\CFusionMX7\runtime\servers\lib;

C:\CFusionMX7\runtime\servers\lib\jrun-patch.jar;
C:\CFusionMX7\runtime\..\lib\macromedia_drivers.jar;
C:\CFusionMX7\runtime\lib\cfmx_mbean.jar;
C:\CFusionMX7\runtime\lib;
C:\CFusionMX7\runtime\lib\cfmx_mbean.jar;
C:\CFusionMX7\runtime\lib\instutil.jar;
C:\CFusionMX7\runtime\lib\java2wsdl.jar;
C:\CFusionMX7\runtime\lib\jrun-ant-tasks.jar;
C:\CFusionMX7\runtime\lib\jrun-xdoclet.jar;
C:\CFusionMX7\runtime\lib\jrun.jar;
C:\CFusionMX7\runtime\lib\jspc.jar;
C:\CFusionMX7\runtime\lib\migrate.jar;
C:\CFusionMX7\runtime\lib\oem-xdoclet.jar;
C:\CFusionMX7\runtime\lib\sniffer.jar;
C:\CFusionMX7\runtime\lib\webservices.jar;
C:\CFusionMX7\runtime\lib\wsconfig.jar;
C:\CFusionMX7\runtime\lib\wsdl2java.jar;
C:\CFusionMX7\runtime\lib\xmlscript.jar;
C:\CFusionMX7\runtime\lib\jrun.jar




Java Ext Dirs  C:\CFusionMX7\runtime\jre\lib\ext




joeNOSPAM_at_BEA.com wrote:


> Yoram Ayalon wrote:

> > existing coldfusion 5 code, using ODBC connections, can SELECT and

> > UPDATE oracle (9.2) DATE fields, without any problems.

> >

> > when checking same functionality with the coldFusion MX, which uses

> > jdbc, we are using the 10g thin client (ojdbc14.jar),  you cannot

> > SELECT or UPDATE the time component of the field. everything's is read

> > as on midnight.

> >

> > any clue ?


>




> Yes. Oracle made a mistake in their current JDBC drivers, mapping a

> DATE column to a java.sql.Date. A java.sql.Date is just that, having

> no time component, so the driver merrily discards the time portion of

> your data (if you call getObject()). If you call getTimestamp() you get

> all the data. Their weak excuse is that they map their new TIMESTAMP

> column type to a java.sql.Timestamp. That is of course irrelevant. If

> Oracle had 10 column types, all of which stored some time+date value,

> they should all map to a java.sql.Timestamp.

>    That being said, there is a driver property you should add to your

> connection parameters, which will revert this behavior:


>




> oracle.jdbc.V8Compatible=true

> 

> Add that and let me know...

> Joe Weinstein at BEA Systems

Received on Wed Jul 19 2006 - 09:04:13 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US