6 Replies Latest reply on Jun 28, 2010 11:49 AM by durniak

    FM 11 + JDBC updateString produces "No primary keys"

    warrenn

      Summary

      FM 11 + JDBC updateString produces "No primary keys"

      Description of the issue

      Version: 11.0v1 (02-04-2010) I'm having a problem with JDBC in FM10 and I thought I'd try out FM11 JDBC to see if it helped.  Unfortunately, I'm running into an issue when I try to update a cell.  I can connect and query a record, but when I update the cell I get this error:com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] No primary keys        at com.filemaker.jdbc2.CommonJ2ResultSet.isUpdateable(Unknown Source)        at com.filemaker.jdbc2.CommonJ2ResultSet.updateValue(Unknown Source)        at com.filemaker.jdbc2.CommonJ2ResultSet.updateString(Unknown Source)        at com.filemaker.jdbc2.CommonJ2ResultSet.updateString(Unknown Source)My code worked okay with FM10.  I did have to change the driver and URL, but otherwise the code is the same.. 

        • 1. Re: FM 11 + JDBC updateString produces "No primary keys"
          warrenn

          Here is a test program to demonstrate the issue:

          import java.sql.*;

          /********************************************
            Test to show this exception with JDBC in FM 11:

             com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] No primary keys
                  at com.filemaker.jdbc2.CommonJ2ResultSet.isUpdateable(Unknown Source)
                  at com.filemaker.jdbc2.CommonJ2ResultSet.updateValue(Unknown Source)
                  at com.filemaker.jdbc2.CommonJ2ResultSet.updateString(Unknown Source)
                  at com.filemaker.jdbc2.CommonJ2ResultSet.updateString(Unknown Source)


            Setup:

              1. Create a database called JdbcNumberBug with the following fields:

            CustId  Text
           Payment Number
           Comment Text

              2.  Add this record to the database:

           CustId  1
           Payment 100
           Comment Orig Comment

              3.  Compile and run this program.  You should see the above
           exception when it tries to update the comment field.

          If this test program is changed to use the sequelink drivers and jar, and is
          pointed at a FM 10 server, it works successfully.
          */

          public class PrimaryKeyError {

               static String SEQDRIVER = "com.ddtek.jdbc.sequelink.SequeLinkDriver";
               static String SEQURL = "jdbc:sequelink://127.0.0.1:2399";

               static String FMDRIVER = "com.filemaker.jdbc.Driver";
               static String FMURL = "jdbc:filemaker://127.0.0.1:2399/JdbcNumberBug";

               static String DRIVER = FMDRIVER;
               static String URL = FMURL;

          public static void main(String [] args)
          {

           try {

           // REGISTER DRIVER
               Driver d = (Driver)Class.forName(DRIVER).newInstance();
           
           // GET CONNECTION
           Connection con;
               con = DriverManager.getConnection(URL,"Admin","");
           
           // GET CONNECTION WARNINGS
           SQLWarning warning = null;
               warning = con.getWarnings();
           
               while (warning != null) {
                   System.out.println("Warning: "+warning);
                   warning = warning.getNextWarning();
               }
           
            // CREATE STATEMENT
           Statement stmt;
               stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
           
            // EXECUTE QUERY
           ResultSet results;
               results = stmt.executeQuery("Select CustId,Comment from JdbcNumberBug");
           
           // GET NEXT ROW
                   boolean ret;
               ret = results.next();
           
           // GET RESULT STRING
           String rst ;
                rst = results.getString("Comment");
               System.out.println("Comment is " + rst);
           
               results.updateString("Comment", "Updated from jdbc");
           
           // UPDATE ROW
               results.updateRow();
           
           
            stmt.close();
            con.close();

           } catch (Exception e) {
            e.printStackTrace();
           }

           

           


          }
          }

           

           





















          • 2. Re: FM 11 + JDBC updateString produces "No primary keys"
            TSGal

            warrenn:

             

            Thank you for your post.

             

            I have forwarded your posts to our Development and Software Quality Assurance (Testing) departments for review.  To help them, what machine and OS are you running?  Also, you mentioned "I did have to change the driver and URL, but otherwise the code is the same."  To be clear, this is the driver supplied with FileMaker Pro 11, and not a different driver.  Correct?

             

            TSGal

            FileMaker, Inc.

            • 3. Re: FM 11 + JDBC updateString produces "No primary keys"
              TSGal

              warrenn:

               

              I apologize for the late reply.

               

              Our Testing department was able to replicate the problem.  The workaround is set one of the fields as the primary key.  For example, set custid as the primary key.

               

              TSGal

              FileMaker, Inc.

              • 4. Re: FM 11 + JDBC updateString produces "No primary keys"
                durniak

                I just tried using FM11 as a JDBC datasource, with Servoy as the front end, and got the same error message: "no primary keys"

                 

                is this a known FileMaker driver limitation, or possibly a Servoy problem?

                 

                ( Servoy claims to support all databases for which a "JDBC compliant" driver is available )

                 

                 

                also, when you say "The workaround is to set one of the fields as the primary key",  how can this be done when FileMaker itself is the datasource?

                 

                thanks

                • 5. Re: FM 11 + JDBC updateString produces "No primary keys"
                  TSGal

                  durniak:

                   

                  Thank you for your post.

                   

                  This is definitely a FileMaker driver limitation.  You can set a primary key with SQL like CREATE TABLE T1 (CustID Numeric PRIMARY KEY, ...     or, in FileMaker Pro, set the field Options, click the Validation tab, "Validate data in this field: Always", and checkmark the options "Not empty" and "Unique value".

                   

                  TSGal

                  FileMaker, Inc.

                  • 6. Re: FM 11 + JDBC updateString produces "No primary keys"
                    durniak

                    thanks!  that did it.   In FileMaker, I just need to define one field as the "Primary Key",  that is validated unique, and not empty

                     

                    and then the JDBC driver does work.   I can now display a FileMaker 11 table in Servoy 5.1

                     

                    we have solutions in both, so this helps

                     

                    greg