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

    jdbc input error


      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



        • 1. Re: jdbc input error

          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



            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?



            • 3. Re: jdbc input error

              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

                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

                  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



                            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.