Two Laptop Bag*
The moose likes JDBC and the fly likes How to use Bind Variable with FROM and UPDATE clauses in PreparedStatement??? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to use Bind Variable with FROM and UPDATE clauses in PreparedStatement???" Watch "How to use Bind Variable with FROM and UPDATE clauses in PreparedStatement???" New topic
Author

How to use Bind Variable with FROM and UPDATE clauses in PreparedStatement???

vdammala vkumar
Greenhorn

Joined: Jan 16, 2012
Posts: 6


Hi Guys,

I would like to parameterize the Table field names in my SQL query as given below :

SELECT ? FROM USERS WHERE ROLE = ?

param1 - USER_NAME
param2 - ADMIN

How do we run this query using JDBC PreparedStatement in java?

Please help me...

Thanks,
Vijay
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

Go to the JDBC tutorial page.
Search for "PreparedStatement ".


No more Blub for me, thank you, Vicar.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 36508
    
  16
. . . and welcome to the Ranch
Patryk Sosinski
Greenhorn

Joined: Sep 10, 2011
Posts: 18
I think you cannot set table name as parameter in PreparedStatement.

Edit: Sorry i read Your question wrong. Still you cannot set column name as parameter in PreparedStatement
vdammala vkumar
Greenhorn

Joined: Jan 16, 2012
Posts: 6
thanks for your response patryk, i am getting the below error :
java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

however I am looking for some workaround for this problem.
Patryk Sosinski
Greenhorn

Joined: Sep 10, 2011
Posts: 18
vdammala vkumar wrote:thanks for your response patryk, i am getting the below error :
java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

however I am looking for some workaround for this problem.


If You are using java it is quite simple. If You got column name as String just create query String and use it to create PreparedStatement.


Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19543
    
  16

Just make sure you validate the column name, or you have a huge security risk (SQL injection).


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
vdammala vkumar
Greenhorn

Joined: Jan 16, 2012
Posts: 6
Guys - i want to make it dynamic , current system is having static params as suggested by Patryk , i have to replace them with the query params using '?'(or any other way).

-So basically i never know what are the fields i need to retrieve from Table in compile time, it may change from job to job.
Patryk Sosinski
Greenhorn

Joined: Sep 10, 2011
Posts: 18
vdammala vkumar wrote:Guys - i want to make it dynamic , current system is having static params as suggested by Patryk , i have to replace them with the query params using '?'(or any other way).

-So basically i never know what are the fields i need to retrieve from Table in compile time, it may change from job to job.


It is dynamic. You dont really have to know the column_name parameter. You can pass diferent column_name in each iteration of a loop. Assume You got table with all column_names called column_list_table, you could write something like this (pseudocode):

chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1479
    
  11

Couple of thoughts:

  • Make sure you check the column names, as Rob says, because this could cause security problems. Also, be careful which columns you reveal e.g. you might not want to include PASSWORD in your list of columns.
  • Changing the SQL every time you execute it like this means your DB may not be able to re-use the parsed SQL from previous executions, so performance may not be so good (depends on other factors as well of course).
  • Does it really need to be "dynamic"? I often encounter situations where people are convinced they need infinite flexibilty (sometimes at great cost), when really they only need to switch between a few known options. You might find you only need a couple of alternative SQL statements to cover all the required options.
  • vdammala vkumar
    Greenhorn

    Joined: Jan 16, 2012
    Posts: 6
    Hi ,

    when i say dynamic , i wanted it to be dynamic in terms of query execution in memory not programmatically.
    Actual issue is here :

    • The queries use literal values(conastants) rather than bind variables. This impacts performance as what’s called a “hard parse” must be performed for each one which is expensive, but more importantly it fills up the area of Oracle’s memory known as the shared pool, which causes fragmentation of the shared pool and in extreme cases can cause any SQL to crash due to an “unable to allocate space in shared pool” error.

    I am trying to fix this issue with minimal changes by using wild char '?' in prepared statement.

    I hope i am very clear about my problem now.

    Let me know the possible workaround..

    Thanks,
    Vijay
    Martin Vajsar
    Sheriff

    Joined: Aug 22, 2010
    Posts: 3436
        
      47

    Using PreparedStatement and bind variables is the right way to do what you need then. However, table names and column names cannot be replaced by a bind variable in Oracle; if two statements differ in column or table name, they cannot share a cursor and therefore each of them will occupy space in shared pool. There is no way around it.

    Replacing literal constants with bind variables should be enough to save the shared pool. If it isn't, you'll have to look at all the different SQL statements you're generating and think about ways of how to combine several similar commands into one. For example, let's say you have three queries that differ only in a list of columns they return from the database. You could replace them by one query that would select all of the columns you need at once, therefore using one instead of three distinct SQL statements. Sometimes a more ingenious way could be found to merge statements together, but in any case this is by no means an easy task and I doubt you could save a lot of resources meaningfully this way.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: How to use Bind Variable with FROM and UPDATE clauses in PreparedStatement???
     
    Similar Threads
    how to create tablespace in java dbms
    PreparedStatements
    prepared statement and IN clause
    Question on closing ResultSets
    How to get Query from PreparedStatement