• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dynamic column selection in prepared statement?

 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Say I have two tables:
user_field_data, containing user_id, field_1, field_2, field_...
user_field_meta, containing field_id, field_title
So whatever has a field_id of 1 in the meta table corresponds to the field_1 column in the data table.
If I was building the query in java, it'd be something like...

My question is, is there a way to do this in one SQL SELECT statement? Ie, have sql dynamically build a column name?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34225
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How do you determine the fid in java?
 
Phil Chuang
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
let's assume that there is a fictional (but functional!) method/class called fieldIdLookupQuery.findIdForField (String) that gets the id from the database.
 
Wayne L Johnson
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't believe that you can do this with a prepared statement. The table names and column names can't be parameters. If you think about it, the purpose of using prepared statements is to create a pre-compiled SQL once that can be used many time. If you used a table name or column name in the SQL, the database engine would have to validate the SQL every time you use it to make sure the table and/or column name is valid. And that's essentially what happens when you use a statement.
So you have a couple of choices. Option 1: use a (regular) statement

Option 2: use a prepared statement

In the second example the query returns every column, but you pull out only the one you want.
[ March 29, 2004: Message edited by: Wayne L Johnson ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic