Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
Clear All
new posts
Previous template Next
    #1

    Timestamp to datetime conversion problem with Oracle

    Hello,

    I am testing SmartGWTEE and created a simple ListGrid and DynamicForm to view and edit a table from a relational database, in our case Oracle 10g. One field is defined in the datasource definition as "datetime" and the corresponding column in the Oracle database is of type "timestamp". When loading the data from the database the client hangs but I can see in the console that a list with X results could be fetched. The data is not being displayed. If the timestamp values in the database are null values everything loads. Saving a datetime through the dynamic form then succeeds and the correct date and time is being saved to the database. After reloading the form the previously saved date can again not be displayed. I tried different Oracle JDBC drivers and tested it with the type "date" where everything works fine.

    The conversion timestamp --> datetime seems to fail.

    Has anyone experienced that problem and can give me information on how to fix that? How can I display Timestamp values from an Oracle 10g database in a DynamicForm?

    Thank you.

    I am using SmartGWTEE 1.2.1
    Last edited by s.hagedorn; 7 Sep 2009, 07:04.
    Tags: None
    #2
    Hello,

    let me add more details to the case:

    Database: Oracle 10g
    JDBC Driver: ojdbc6
    Java: 1.6
    SmartGWTEE: 1.2.1
    SmartClient: 7.1

    Database table which is being queried:

    Code:
    PS_NAME : NUMBER(8,20)
NAME : VARCHAR2(50 BYTE)
CDATE : TIMESTAMP(6)
    Sample data within table:

    Code:
    PS_NAME | NAME | CDATE
0       | abc  | 29.09.09 00:00:00,000000000
1       | def  | null
    The datasource definition name.ds.xml:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
<DataSource  
    ID="name"  
    serverType="sql"  
    tableName="name"  
    titleField="name"  
>  
    <fields>  
        <field name="ps_name"      type="integer" 	hidden="true"      length="28" primaryKey="true" required="true"/>  
        <field name="name"    		type="text"     title="Name"        length="50"       required="true"/>  
        <field name="cdate" 	type="datetime"     title="Created" />  
    </fields>  
</DataSource>
    When the Oracle database is queried this is the result in the console:

    Code:
    INFO  SQLDriver - [builtinApplication.name_fetch] Executing SQL query on 'Oracle': SELECT COUNT(*) FROM name WHERE ('1'='1')
DEBUG SQLDataSource - [builtinApplication.name_fetch] Using SQL Limit query
DEBUG SQLDataSource - [builtinApplication.name_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT * FROM (SELECT /*+ FIRST_ROWS(75) */ a.*, rownum myrownum FROM (SELECT name.ps_name, name.cdate, name.name FROM name WHERE ('1'='1')) a where rownum <=76) WHERE myrownum >= 1
DEBUG PoolableSQLConnectionFactory - [builtinApplication.name_fetch] Returning pooled Connection
INFO  DSResponse - [builtinApplication.name_fetch] DSResponse: List with 2 items
    So the two data sets could be fetched from the database. In the DynamicForm though the output is as follows (only for the field "cdate"):

    Code:
    [object Object]
    The date and time from the Timestamp data is not being displayed. Saving a date through the DynamicForm though works! The date and time cannot be displayed and edited.

    I hope this helps and if anyone has a clue on how to solve this problem I'd be glad.

    Regards,
    S. Hagedorn

    Comment

      #3
      Oracle datetime &amp; timestamp

      Did you ever get any resolution to this? I am running into the exact same problem.

      I changed the builtin example to look at my oracle database. The column in the database table is defined as:

      AUDIT_DATETIME TIMESTAMP(6)

      And the visual builder generated a datasource file for me, containing:
      <field name="AUDIT_DATETIME" type="datetime"></field>

      My server log shows the data being retrieved and sent back to the browser, and I can see the response object thru firebug...but it doesn't display any records. If I remove the timestamp/datetime column & mapping, everything works fine.

      Does the builtin functionality not work well with oracle?

      Comment

        #4
        Oracle is the most commonly encountered database on our many consulting projects, so yes the SQL driver works very well with Oracle.

        Can you show your server and client-side logs for this interaction as a starting point (see the FAQ for other important items to share).

        Comment

          #5
          OK here are the logs.

          Jetty log:

          Code:
          00:00:00.000 [INFO] 304 - GET /Minder2.html?gwt.codesvr=10.60.7.37:9997 (127.0.0.1)
00:00:00.062 [INFO] 304 - GET /Minder2.css (127.0.0.1)
00:00:00.109 [INFO] 304 - GET /minder2/minder2.nocache.js (127.0.0.1)
00:00:00.390 [INFO] 200 - GET /sc/DataSourceLoader?dataSource=AIRLINES (127.0.0.1) 683 bytes
00:00:26.859  [INFO] 200 - POST /minder2/sc/IDACall?isc_rpc=1&isc_v=7.1-Evaluation&isc_xhr=1&isc_tnum=0 (127.0.0.1) 411 bytes
          Firefox console

          Request:
          Code:
          http://localhost:8888/minder2/sc/IDACall?isc_rpc=1&isc_v=7.1-Evaluation&isc_xhr=1&isc_tnum=0&_transaction=%3Ctransaction%20xmlns%3Axsi%3D%22http%3A%2F%2Fwww.w3.org%2F2000%2F10%2FXMLSchema-instance%22%20xsi%3Atype%3D%22xsd%3AObject%22%3E%3CtransactionNum%20xsi%3Atype%3D%22xsd%3Along%22%3E0%3C%2FtransactionNum%3E%3Coperations%20xsi%3Atype%3D%22xsd%3AList%22%3E%3Celem%20xsi%3Atype%3D%22xsd%3AObject%22%3E%3Ccriteria%20xsi%3Atype%3D%22xsd%3AObject%22%3E%3C%2Fcriteria%3E%3CoperationConfig%20xsi%3Atype%3D%22xsd%3AObject%22%3E%3CdataSource%3EAIRLINES%3C%2FdataSource%3E%3CoperationType%3Efetch%3C%2FoperationType%3E%3CtextMatchStyle%3Eexact%3C%2FtextMatchStyle%3E%3C%2FoperationConfig%3E%3CstartRow%20xsi%3Atype%3D%22xsd%3Along%22%3E0%3C%2FstartRow%3E%3CendRow%20xsi%3Atype%3D%22xsd%3Along%22%3E75%3C%2FendRow%3E%3CcomponentId%3Eisc_OID_0%3C%2FcomponentId%3E%3CappID%3EbuiltinApplication%3C%2FappID%3E%3Coperation%3EAIRLINES_fetch%3C%2Foperation%3E%3ColdValues%20xsi%3Atype%3D%22xsd%3AObject%22%3E%3C%2FoldValues%3E%3C%2Felem%3E%3C%2Foperations%3E%3C%2Ftransaction%3E&protocolVersion=1.0
          Response body:
          Code:
          //isc_RPCResponseStart-->[
    {
        endRow:7,
        totalRows:7,
        isDSResponse:true,
        invalidateCache:false,
        status:0,
        startRow:0,
        data:[
            {
                NAME:"Air Canada",
                VERSION:0,
                ID:1,
                AUDIT_USERID:"sys",
                MYROWNUM:1,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"AC"
            },
            {
                NAME:"British Airways",
                VERSION:0,
                ID:2,
                AUDIT_USERID:"sys",
                MYROWNUM:2,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"BA"
            },
            // (5 more items here)
        ]
    }
]//isc_RPCResponseEnd
          Server Sysout:

          Code:
               [java] ISC: Configuring log4j from: file:/C:/dev/minder2/war/WEB-INF/classes/log4j.isc.config.xml
     [java] === 2009-12-16 09:12:55,642 [main] INFO  ISCInit - Isomorphic SmartClient Framework - Initializing
     [java] === 2009-12-16 09:12:55,673 [main] INFO  ConfigLoader - Attempting to load framework.properties from CLASSPATH
     [java] === 2009-12-16 09:12:55,813 [main] INFO  ConfigLoader - Successfully loaded framework.properties from CLASSPATH at location: jar:file:/C:/dev/minder2/war/WEB-INF/lib/isomorphic_core_rpc.jar!/framework.properties
     [java] === 2009-12-16 09:12:55,813 [main] INFO  ConfigLoader - Attempting to load project.properties from CLASSPATH
     [java] === 2009-12-16 09:12:55,813 [main] INFO  ConfigLoader - Unable to locate project.properties in CLASSPATH
     [java] === 2009-12-16 09:12:55,829 [main] INFO  ConfigLoader - Successfully loaded isc_interfaces.properties from CLASSPATH at location: jar:file:/C:/dev/minder2/war/WEB-INF/lib/isomorphic_core_rpc.jar!/isc_interfaces.properties
     [java] === 2009-12-16 09:12:55,829 [main] INFO  ConfigLoader - Attempting to load server.properties from CLASSPATH
     [java] === 2009-12-16 09:12:55,845 [main] INFO  ConfigLoader - Successfully loaded server.properties from CLASSPATH at location: file:/C:/dev/minder2/war/WEB-INF/classes/server.properties
     [java] === 2009-12-16 09:12:55,845 [main] INFO  Logger - Logging system started.
     [java] === 2009-12-16 09:12:55,860 [main] INFO  ISCInit - Isomorphic SmartClient Framework (7.1-Evaluation/Enterprise Deployment 2009-08-23) - Initialization Complete
     [java] === 2009-12-16 09:12:55,860 [main] DEBUG ISCInit - Verifying webroot: C:\dev\minder2\war
     [java] === 2009-12-16 09:12:55,860 [main] INFO  ISCInit - Auto-detected webRoot - using: C:\dev\minder2\war
     [java] log4j:WARN No appenders could be found for logger (org.apache.jasper.compiler.JspRuntimeContext).
     [java] log4j:WARN Please initialize the log4j system properly.
     [java] === 2009-12-16 09:12:56,470 [main] INFO  PreCache - Isomorphic PreCache servlet loading
     [java] === 2009-12-16 09:12:56,532 [main] INFO  PoolManager - SmartClient pooling disabled for 'DataSource' objects
     [java] === 2009-12-16 09:12:56,704 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\DataSource.ds.xml: 16ms
     [java] === 2009-12-16 09:12:56,735 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\builtinTypes.xml: 0ms
     [java] === 2009-12-16 09:12:56,860 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\DataSourceField.ds.xml: 15ms
     [java] === 2009-12-16 09:12:56,891 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\Validator.ds.xml: 0ms
     [java] === 2009-12-16 09:12:56,907 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\ValueMap.ds.xml: 0ms
     [java] === 2009-12-16 09:12:56,938 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\InstantDataApp.ds.xml: 15ms
     [java] === 2009-12-16 09:12:57,032 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\Application.ds.xml: 16ms
     [java] === 2009-12-16 09:12:57,032 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\IDAPage.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,063 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\IDAUserType.ds.xml: 15ms
     [java] === 2009-12-16 09:12:57,079 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\IDAOperation.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,157 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\Canvas.ds.xml: 16ms
     [java] === 2009-12-16 09:12:57,266 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\MethodDeclaration.ds.xml: 15ms
     [java] === 2009-12-16 09:12:57,282 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\Img.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,313 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\StatefulCanvas.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,345 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\Button.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,360 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\ListViewer.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,423 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\ListGrid.ds.xml: 47ms
     [java] === 2009-12-16 09:12:57,501 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\DynamicForm.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,657 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\FormItem.ds.xml: 16ms
     [java] === 2009-12-16 09:12:57,704 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\DetailViewer.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,735 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\DetailViewerField.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,735 [main] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\isomorphicXML.ds.xml: 0ms
     [java] === 2009-12-16 09:12:57,751 [main] INFO  PreCache - Isomorphic PreCache complete (1281ms)
     [java] === 2009-12-16 09:13:04,376 [l0-2] INFO  RequestContext - URL: '/sc/DataSourceLoader', User-Agent: 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5': Moz (Gecko) with Accept-Encoding header
     [java] === 2009-12-16 09:13:04,423 [l0-2] DEBUG XML - Parsed XML from C:\dev\minder2\war\ds\AIRLINES.ds.xml: 0ms
     [java] === 2009-12-16 09:13:29,110 [l0-2] INFO  RequestContext - URL: '/minder2/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5': Moz (Gecko) with Accept-Encoding header
     [java] === 2009-12-16 09:13:29,188 [l0-2] DEBUG XML - Parsed XML from (in memory stream): 0ms
     [java] === 2009-12-16 09:13:29,204 [l0-2] DEBUG XML - Parsed XML from C:\dev\minder2\war\minder2\sc\system\schema\List.ds.xml: 16ms
     [java] === 2009-12-16 09:13:29,204 [l0-2] DEBUG RPCManager - Processing 1 requests.
     [java] === 2009-12-16 09:13:29,235 [l0-2] DEBUG RPCManager - Request #1 (DSRequest) payload: {
     [java]     criteria:{},
     [java]     operationConfig:{dataSource:"AIRLINES", operationType:"fetch", textMatchStyle:"exact"},
     [java]     startRow:0,
     [java]     endRow:75,
     [java]     componentId:"isc_OID_0",
     [java]     appID:"builtinApplication",
     [java]     operation:"AIRLINES_fetch",
     [java]     oldValues:{}
     [java] }
     [java] === 2009-12-16 09:13:29,251 [l0-2] INFO  IDACall - Performing 1 operation(s)
     [java] === 2009-12-16 09:13:29,282 [l0-2] DEBUG AppBase - [builtinApplication.AIRLINES_fetch] No userTypes defined, allowing anyone access to all operations for this application
     [java] === 2009-12-16 09:13:29,282 [l0-2] DEBUG AppBase - [builtinApplication.AIRLINES_fetch] No public zero-argument method named '_AIRLINES_fetch' found, performing generic datasource operation
     [java] === 2009-12-16 09:13:29,282 [l0-2] INFO  SQLDataSource - [builtinApplication.AIRLINES_fetch] Performing fetch operation with
     [java] 	criteria: {}	values: {}
     [java] === 2009-12-16 09:13:29,298 [l0-2] INFO  SQLWhereClause - [builtinApplication.AIRLINES_fetch] empty condition
     [java] === 2009-12-16 09:13:29,563 [l0-2] DEBUG SQLDataSource - [builtinApplication.AIRLINES_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
     [java] === 2009-12-16 09:13:30,016 [l0-2] DEBUG PoolableSQLConnectionFactory - [builtinApplication.AIRLINES_fetch] Initializing SQL config for 'Oracle' from system config - using DataSource:  oracle.jdbc.pool.OracleDataSource
     [java] setProperties: couldn't set:
     [java] {context:"No such property"}
     [java] === 2009-12-16 09:13:30,751 [l0-2] DEBUG PoolableSQLConnectionFactory - [builtinApplication.AIRLINES_fetch] Returning pooled Connection
     [java] === 2009-12-16 09:13:30,860 [l0-2] INFO  SQLDriver - [builtinApplication.AIRLINES_fetch] Executing SQL query on 'Oracle': SELECT COUNT(*) FROM AIRLINES WHERE ('1'='1')
     [java] === 2009-12-16 09:13:30,891 [l0-2] DEBUG SQLDataSource - [builtinApplication.AIRLINES_fetch] Using SQL Limit query
     [java] === 2009-12-16 09:13:30,891 [l0-2] DEBUG SQLDataSource - [builtinApplication.AIRLINES_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT * FROM (SELECT /*+ FIRST_ROWS(75) */ a.*, rownum myrownum FROM (SELECT AIRLINES.NAME, AIRLINES.VERSION, AIRLINES.ID, AIRLINES.AUDIT_USERID, AIRLINES.AUDIT_DATETIME, AIRLINES.CODE FROM AIRLINES WHERE ('1'='1')) a where rownum <=76) WHERE myrownum >= 1
     [java] === 2009-12-16 09:13:31,063 [l0-2] DEBUG PoolableSQLConnectionFactory - [builtinApplication.AIRLINES_fetch] Returning pooled Connection
     [java] === 2009-12-16 09:13:31,110 [l0-2] INFO  DSResponse - [builtinApplication.AIRLINES_fetch] DSResponse: List with 7 items
     [java] === 2009-12-16 09:13:31,110 [l0-2] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
     [java] === 2009-12-16 09:13:31,110 [l0-2] DEBUG RPCManager - non-DMI response, dropExtraFields: false
     [java] === 2009-12-16 09:13:31,157 [l0-2] INFO  Compression - /minder2/sc/IDACall: 2706 -> 411 bytes
          Developer Console log messages:

          Code:
          09:41:17.259:INFO:Log:initialized
09:41:17.311:WARN:Log:NOTE: Firebug is enabled. Firebug greatly slows the performance of applications that make heavy use of JavaScript. Isomorphic highly recommends Firebug for troubleshooting, but Firebug and other development tools should be disabled when assessing the real-world performance of SmartClient applications.
09:41:23.278:WARN:RecordEditor:isc_OID_0filterEditor:ListGrid initialized with this.fields attribute set to an array containing fields which are already being displayed in another ListGrid instance. To reuse standard field configuration across multiple ListGrids, use listGrid.defaultFields rather than assigning directly to listGrid.fields.
09:41:24.185:INFO:Log:isc.Page is loaded
10:26:20.385:XRP1:WARN:RPCManager:Error evaling structured RPC response: SyntaxError: unterminated string literal response text: [
    {
        endRow:7,
        totalRows:7,
        isDSResponse:true,
        invalidateCache:false,
        status:0,
        startRow:0,
        data:[
            {
                NAME:"Air Canada",
                VERSION:0,
                ID:1,
                AUDIT_USERID:"sys",
                MYROWNUM:1,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"AC"
            },
            {
                NAME:"British Airways",
                VERSION:0,
                ID:2,
                AUDIT_USERID:"sys",
                MYROWNUM:2,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"BA"
            },
            {
                NAME:"WestJet",
                VERSION:0,
                ID:3,
                AUDIT_USERID:"sys",
                MYROWNUM:3,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"WJ"
            },
            {
                NAME:"American Airlines",
                VERSION:0,
                ID:4,
                AUDIT_USERID:"sys",
                MYROWNUM:4,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"AA"
            },
            {
                NAME:"Southwest Airlines",
                VERSION:0,
                ID:5,
                AUDIT_USERID:"sys",
                MYROWNUM:5,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"SA"
            },
            {
                NAME:"Continental",
                VERSION:0,
                ID:6,
                AUDIT_USERID:"sys",
                MYROWNUM:6,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"CO"
            },
            {
                NAME:"United Airlines",
                VERSION:0,
                ID:7,
                AUDIT_USERID:"sys",
                MYROWNUM:7,
                AUDIT_DATETIME:{
                    bytes:"xm
.",
                    length:7,
                    stream:{}
                },
                CODE:"UA"
            }
        ]
    }
]
10:26:20.482:XRP1:WARN:Log:TypeError: _5 is undefined

          AIRLINES.ds.xml:

          Code:
          <DataSource 
	ID="AIRLINES"
	tableName="AIRLINES"
	serverType="sql"
>
	<fields>
		<field primaryKey="true" name="ID" type="sequence" title="Id"></field>
		<field name="AUDIT_DATETIME" type="datetime" title="Audit Datetime"></field>
		<field name="AUDIT_USERID" length="20" type="text" title="Audit Userid"></field>
		<field name="CODE" length="8" type="text" title="Code"></field>
		<field name="NAME" length="40" type="text" title="Name"></field>
		<field name="VERSION" type="number" title="Version"></field>
	</fields>
</DataSource>

          Versions:

          Firefox 3.5.5
          SmartGWTEE 1.2.1
          Oracle 10g
          Oracle JDBC driver 10.2.0.1.0

          Comment

            #6
            OK, JDBC appears to be inexplicably providing a Reader or similar Java object for the datetime column instead of the expected java.sql.Timestamp or similar Date object.

            The fact that this object is being returned is basically a JDBC driver issue or similar. This is all outside of SmartGWT code and doesn't happen with the (many) Oracle configurations we've tested. You might be able to find a JDBC setting that resolves this issue by Googling.

            You can also work around this issue by subclassing the SQLDataSource, overriding execute(), and modifying the default DSResponse to convert whatever Java object is coming from JDBC into a java.sql.Timestamp or similar object. But we'd recommend a little Googling about this JDBC issue first.

            Comment

              #7
              hi..
              Now only am learning about sql.So,i will try to solve your resolution..
              ...
              data entry india

              Comment

              Previous template Next
              Working...
              X