AnsweredAssumed Answered

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

Question asked by StevenChall on Dec 30, 2010
Latest reply on Jan 3, 2011 by IwaoAVE!


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


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 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.


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)



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 + "%' ");   



while (    


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))






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