5 Replies Latest reply on Dec 15, 2014 5:48 PM by IwaoAVE!

    JDBC Timestamp Insert Issues

    JamesEly

      Title

      JDBC Timestamp Insert Issues

      Your post

      Hello Everyone,

      This is my first post into this forum. I've been tasked with writing a small Java program that inserts records into a FM 13 database server and I'm stumped with getting the correct format for a timestamp type field. I've scoured the internet and read the ODBC/JDBC Developer's Guide, yet everything I try gives me an error. I am hoping someone can see what I'm missing and point me in the right direction. Here's the details so far.

      I'm testing in Eclipse in debug mode currently. My version of JRE is JDK 1.8.0_20 and I am running the FM13.0v2_xDBC driver for Java.

      When I insert a record into the database, I've tried the following in the VALUES ( ) portion of the query, and none of them have worked:

      'firstval', TIMESTAMP 'YYYY-MM-DD HH:mm:ss', 'nextval'
      'firstval', {ts 'YYYY-MM-DD HH:mm:ss'}, 'nextval'
      'firstval', {ts 'YYYY-MM-DD HH:mm:ss.SSSS'}, 'nextval'
      'firstval', {ts'YYYY-MM-DD HH:mm:ss'}, 'nextval' // No space between ts and '
      'firstval', {ts'YYYY-MM-DD HH:mm:ss.SSSS'}, 'nextval' // No space between ts and '

      I've tried without seconds on the datetime, with seconds and milliseconds, etc. nothing works. The format in the field itself when viewed in Filemaker Pro is MM/DD/YYYY HH:mm:ss TZ.

      Every attempt to insert yields the following error stack:

      com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:56): There is an error in the syntax of the query.
          at com.filemaker.jdbc.FM_API.getColumnDefinitions(Unknown Source)
          at com.filemaker.jdbc.FM_API.execDirect(Unknown Source)
          at com.filemaker.jdbc.FM_API.execDirect(Unknown Source)
          at com.filemaker.jdbc1.CommonJ1Statement.execDirect(Unknown Source)
          at com.filemaker.jdbc1.CommonJ1Statement.executeUpdate(Unknown Source)
          at com.filemaker.jdbc1.CommonJ1Statement.executeUpdate(Unknown Source)
          at com.marvel.FM13Import.DBInterface.insertRecord(DBInterface.java:50)
          at com.marvel.FM13Import.FM13Import.main(FM13Import.java:167)

      The query is built as a string that is passed to the DBInterface class and executed as an executeUpdate because I am not reading any content back. It's pure inserts. I am not doing transactional SQL statement prepares.

      Please help. I can provide any other information needed but I am stumped as to why this isn't working for me.

      Thanks

       

       

       

       

        • 1. Re: JDBC Timestamp Insert Issues
          IwaoAVE!

          The below code worked as expected without an error.
          f1 and f2 are text fields and timestampfld is a timestamp field.
          I would suggest you to run the code and see if it works.

          import java.sql.*;
          import java.io.*;
          
          public class InsertTimestamp {
            public static void main(String[] args) {
              try {
                Class.forName("com.filemaker.jdbc.Driver");
                String url = "jdbc:filemaker://127.0.0.1:2399/SqlTest";
                Connection con = DriverManager.getConnection(url, "admin", "");
          
                String sql = "insert into tbl1 (f1, timestampfld, f2) values ('firstval', TIMESTAMP '2014-11-20 10:00:00', 'nextval')";
                Statement stmt = con.createStatement();
                int res = stmt.executeUpdate(sql);
                System.out.println(res + " rows inserted.");
                stmt.close();
                con.close();
              } catch (Exception e) {
                e.printStackTrace();
              }
            }
          }
          


          My environment:
          - Mac OS X 10.9.5
          - Java 1.8.0_20
          - FileMaker Pro Advanced 13.0v4
          - JDBC Driver: FM13.0v5_xDBC_13.2.14

          • 2. Re: JDBC Timestamp Insert Issues
            JamesEly

            I finally had a chance to test this code. It didn't work for me either. I assume at this point that means there's something wrong with the FM DB server?

            Thanks

            • 3. Re: JDBC Timestamp Insert Issues
              IwaoAVE!

              First of all, make sure to upgrade your FMS and the JDBC driver version to the latest.

              As it's a syntax error, the cause should simply be a formality of the statement.
              Most likely, your insert statement contains some sql reserved word as a column name.
              Try enclosing table/column names with double quotes:

              String sql = "insert into \"tbl1\" (\"f1\", \"timestampfld\", \"f2\") values ...

              And I didn't realize this, but the error message indicates the error position.
              For example, FQL0001/(1:56) means that the cause of the error is at the character 56 in the statement.

              Regards,
              Iwao

              • 4. Re: JDBC Timestamp Insert Issues
                JamesEly

                I finally solved the problem and there were 3 issues with the database I was provided to interface with. First, there were two columns named timestamp and size. Once I changed them from reserved words to something else, that fixed a lot of the issues. Lastly, the database I was provided was an FM Pro 7 that was converted to v12 and served from a v13 server. I recreated the database structure in FM Pro 13 and then uploaded that to the server. Everything stared working.  Thanks to IWao for your help.

                • 5. Re: JDBC Timestamp Insert Issues
                  IwaoAVE!

                  Glad to hear that you could solve the problem :-)
                  And thank you for the detailed follow-up!

                  // Iwao