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!

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();  }

}

Outcomes