3 Replies Latest reply on Jan 3, 2011 8:48 AM by IwaoAVE!

    Using JDBC to access data in a Column whose values are defined by a Value List

    StevenChall

      Title

      Using JDBC to access data in a Column whose values are defined by a Value List

      Post

      I've written an app that extracts data from a FileMaker Pro 11 database via JDBC.  A typical query (in Processing, built on Java) looks like this (and is working just fine):

        ResultSet rs = db.query("SELECT \"Document Origin Location\", \"Document Destination\" " +                      "FROM \"Correspondence Details\" " + "WHERE \"Document Origin Location\" LIKE '" + origin + "%' "); 

      However, some of the columns in the database tables have entries whose types are defined by Value Lists and whose values are selected from popup menus.  For example, let's say there's a Value List whose name is "Type of Good", whose Source is Custom Values, and whose Values are "painting", "sketch", "sculpture", and "plate".  When, from within FileMaker Pro 11 I look at the table containing a column of these kinds of values, the column title is "::Type good".  When I try to access that column from within my code,  I always get the error message

      "com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0007/(1:76): The column named "<xxx>" does not exist in any table in the column reference's scope."

      whether "<xxx>" is actually (embedded as part of the full quoted SQL query string constant)

      \"::Type good\" or

      \"good\" or

      \"Type good\" or

      \"Type 'good'\" or

      \"'::Type good'\" or

      \"Type of good\" or

      \"::Type of good\" or

      \"::good\" or 

      good

      .

      ::good gives me 

      "com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:78): There is an error in the syntax of the query."

      and '::good' of course just gives me back the string constant "::good".

      Can I access this column programmatically via JDBC?  If so, what do I call it in the SQL string?  If I get back an integer -- say 2 instead of "sketch" or 3 instead of "sculpture" that's ok -- but I need to find out the values in several of those Value List--defined columns.  Thank you for any help you may be able to provide.

      -Steve

      P.S. Code for the full function is provided below including commented-out failed attempts.  Please note that the database is given to me by an external client and I'm unable to make changes to it.  Thanks again.

      void countGoodsFrom(String origin)

      {  

      initConnectionArray();


      ResultSet rs = db.query("SELECT \"Goods_Services _ Origin\", \"Goods_Services _ Destination\"" + 

      //                           ", \"::Type good\"" +  

      //                          ", \"good\"" +  

      //                           ", \"Type good\"" +  

      //                          ", \"Type 'good'\"" +  

      //                           ", \"'::Type good'\"" +  

      //                           ", \"Type of good\"" +  

      //                          ", \"::Type of good\"" +  

      //                           ", \"::good\"" +  

      //                           ", ::good" +  

      //                           ", '::good'" +  

      //                           ", good" +                             

      " FROM \"IB Documents\" " +                            

      "WHERE \"Goods_Services _ Origin City\" LIKE '" + origin + "%' ");   

      try

      {    

      while (rs.next())    

      {      

      String dbOrigin = rs.getString(1);

      String dest = rs.getString(2);

      String type = rs.getString(3);

      println(dbOrigin + " -> " + dest + "; type: " + type);

      for (int c = 0; c < cityCount; c++)

      {

      if (cityTable.getString(c, 0).equals(dest))

      {

      (numConnections[c])++;        

      }

      }

      }

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

      }

        • 1. Re: Using JDBC to access data in a Column whose values are defined by a Value List
          philmodjunk

          I'm not familiar enough with the Java to comment on your query syntax, but will comment on "::fieldName".

          You'll find that no field in your FileMaker database has that name. The :: is telling you that this field is from a related table (table occurrence actually) instead of from the table occurrence listed in Show Recors From in Layout Setup... for the layout where you saw this field. It's fully qualified name will be TableOccurrenceName::FieldName. You can find out the name of the relevant table occurrence in FileMaker by entering layout mode and double clicking the field. The name in the drop down list at the top of your dialog box will be the table occurrence name.

          (Table occurrences are the boxes found in Manage | Database | Relationships.)

          • 2. Re: Using JDBC to access data in a Column whose values are defined by a Value List
            StevenChall

            Thanks, Phil.  Yes, I see what you're saying.  However, the JDBC connection is still not recognizing the fully qualified name when I insert it into the SQL query (It's \"Documents Line Items::Type good\" for this particular field). It's possible that JDBC doesn't support the TableOccurrenceName::FieldName syntax, or that I've missed some minor syntactic detail -- I was previously flummoxed for several hour by an undiscovered distinction between single and double quotes (the former required for items in a column, the latter for the column name).  Your explanation has made the FileMaker-specific aspects much clearer, however. Thanks again.

            -Steve

            • 3. Re: Using JDBC to access data in a Column whose values are defined by a Value List
              IwaoAVE!

              I think you need to join tables as the 'Type good' column is defined in the 'Documents Line Items' table.
              It would look something like below.

              SELECT 
                "IB Documents"."Goods_Services _ Origin",
                "IB Documents"."Goods_Services _ Destination",
                "Documents Line Items"."Type good"
              FROM "IB Documents"
              LEFT JOIN "Documents Line Items" ON "IB Documents"."xxx" = "Documents Line Items"."yyy"
              WHERE "Goods_Services _ Origin City" LIKE 'querystring%'
              

              // Iwao