5 Replies Latest reply on May 13, 2013 2:00 PM by Hudi

    jdbc input error

    Hudi

      I'm getting this error when executing a select * from a filemaker table.

       


      An error occurred when executing the SQL command:

      Select * FROM Table

      [FileMaker][FileMaker JDBC] For input string: "?": ? [SQL State=22003]

       

       

      Execution time: 1.67s

       

       

      1 statement(s) failed.

       


       

      It works great when I select a field that I know has valid data. There is obviously some field somewhere in the table with a value (?) that jdbc is not able to read. Is this something standard when setting up a jdbc connection and does it mean that I must go through each field and ensure that they return no question marks?

       

      Sure hope not

       

      Thanks

        • 1. Re: jdbc input error
          taylorsharpe

          I've run into this type of problem when I made fields in FileMaker using characters that are illegal in most SQL engines.  Can you give us a list of the fields in the table?

          • 2. Re: jdbc input error
            Hudi

            Taylor,

             

            Thanks for the reply.

            In the error message not talking about an error in the field values? Thats what I thought.

             

            I cannot show a list of the fields but I can tell you that I've gone through each one and they all fit to a naming convention specified by the mysql DBA. That is fieldname_fieldname_fieldname.

             

            The only thing I am seeing now that you mention it, is that some field names end with a number. fieldname_fieldname_1 , fieldname_fieldname_2 etc.

             

            Could that be an issue?

             

            Thanks!

            • 3. Re: jdbc input error
              taylorsharpe

              Numbers are OK to end fields. 

               

              Try this from inside of FileMaker (Dataview or a script): 

               

              ExecuteSQL ( “SELECT FieldName FROM FileMaker_Fields WHERE TableName=[Inset Table Name] ORDER BY FieldName” ; “” ; “”  )

               

              I don't know your table name, so I just insert it where I put [Insert Table Name].  This shoud list all of your field names as they should be seen by the JDBC client.  From your JDBC client, try a select statement only specifying some of the fields instead of * and see what happens. 

              • 4. Re: jdbc input error
                Hudi

                I did this and fount that the errors are being thrown by number fields that in some instances contains "?".

                 

                Is there a way to set the calculation to "" if one of the referenced fields are empty?

                 

                ie. MyField = table::field1 / table::field2

                 

                if table::field2 is empty it returns a ? and the entire jdbc query does not go through.

                 

                do I need to rewrite every calc to filter out the ? and enter a "" or 0 instead?

                 

                 

                 

                Many Thanks   

                • 5. Re: jdbc input error
                  Hudi

                  So, the plot thickens...

                   

                  I got rid of all the field values that were causing the sql query to fail.

                   

                  it ran for awhile and showed this:

                   

                  FROM Facebook_ROllup

                  java.lang.NullPointerException

                  java.lang.NullPointerException

                            at com.filemaker.jdbc.FMSQLException.<init>(Unknown Source)

                            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.executeQuery(Unknown Source)

                            at com.filemaker.jdbc1.CommonJ1Statement.executeQuery(Unknown Source)

                            at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:129)

                            at workbench.sql.StatementRunner.runStatement(StatementRunner.java:419)

                            at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3024)

                            at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:1983)

                            at workbench.gui.sql.SqlPanel$13.run(SqlPanel.java:1940)

                   

                   

                   

                   

                  Execution time: 8m 31s

                   

                   

                  1 statement(s) failed.

                   

                  Where should I be looking?

                   

                  if anyone has any ideas.

                   

                  Thanks