1 Reply Latest reply on Aug 2, 2007 5:58 AM by Robert Hirst

    SQLAssembler: Handling dates

    Robert Hirst Level 1
      I'm just wondering if anyone has had any luck using date/time fields with SQLAssembler, as I'm having a few problems.

      My update query declaration looks like this:

      <update-item>

      <sql>UPDATE Timesheet SET person=#person#, <sniiiiip, lots of fields>, date=#date# WHERE id=#_PREV.id# </sql>

      </update-item>


      I'm editing the data from the DataService with an item editor, which looks something like this:

      <mx:itemEditor>

      <mx:Component><mx:DateField formatString="DD/MM/YYYY"/></mx:Component>

      </mx:itemEditor>

      I'm using an actionscript-class declaration in the SQLAssembler config, and it that class the var named "date" is of type Date.

      My database is an Access MDB connecting with sun.jdbc.odbc.JdbcOdbcDriver.

      If I remove the part which sets the date field (date=#date#) from my update query, everything works perfectly.

      I have LCDS set to show all debug messages, and this is the error shown when I exit the date field after changing it:

      When I set editorDataField="selectedDate" I get the following error:

      [Flex] 16:19:50.468 [DEBUG] [Service.Data.SQL] SQL Assembler jdbc exception on operation=update-item error=java.sql.SQLException: SQL Exception : Unknown SQL Type for PreparedStatement.setObject (SQL Type=1111

      And if I set editorDataField="text" (which appears to cause the format string to be applied according to the rtmp message intercepts), then I get this:

      [Flex] 16:27:23.500 [DEBUG] [Service.Data.SQL] SQL Assembler jdbc exception on operation=update-item error=java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

      And if I then add single quotes around the date field in the update query ( date='#date#' ), I get this:

      [Flex] 16:32:11.156 [DEBUG] [Service.Data.SQL] SQL Assembler jdbc exception on operation=update-item error=java.sql.SQLException: Unexpected exception : 7

      I've tried various format strings which I think Access should handle, but they all result in the unexpected exception error. I've also tried changing the data type on the actionscript class to String but it still gives the same results.

      What would be really handy would be if I could see the exact query which is being run against the database to try and determine what is actually being entered when the error occurs.

      I've tried this logging jdbc driver, but so far I couldn't get it to work with the Access driver and the trouble shooting documentation is very sparse.

      If anyone has any clues on either how I can see the actual update query SQLAssembler is running or what I need to do to enable editable date fields I'd be very grateful.

      Thanks,

      Robert
        • 1. SQLAssembler: Handling dates
          Robert Hirst Level 1
          I've now tried with 2 other databases:

          MySQL works perfectly.

          MSSQL fails with the error "The Java type java.util.Date is not a supported type."

          So this might be a question of whether a specific JDBC driver can map Java's date object to a field correctly.


          EDIT:

          I tried experimenting with another JDBC driver to connect to MSSQL: jTDS

          It failed with a very similar error message, but it can be tweaked to map the JAVA_OBJECT type to datetime, but there may be side-effects of this change (e.g. another type without mapping available defined my now cause a class cast exception rather than resulting in an sql exception being raised).

          In most situations storing the date in a timestamp would be more appropriate than a datetime field, but as the SQLAssembler appears to be unable to map Timestamp to anything but ByteArray this solution doesn't seem feasible here. If SQLAssembler were to map to java. sql.Date instead of java. util.Date, this would also be undesirable as the java.sql version does not include the time.

          If anyone is interested in the tweaks you need to make to the jTDS driver then post here and I'll put the diff up, but really it feels "a bit dodgy" to map JAVA_OBJECT jdbc fields to date and probably not something you'd want to do in production for the sake of reliability (this is just for a small internal project at my company).

          Now I just need to work out how best to enable people to edit a time as well as the date in the DateField...