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

[vdammala vkumar]

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]

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

[Campbell Ritchie]

. . . and welcome to the Ranch

[Patryk Sosinski]

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]

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]

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.

String column_name = dynamic_user_name;

String query = "SELECT " + column_name + " FROM USERS WHERE ROLE = ?";

con.prepareStatement(query);

[Rob Spoor]

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

[vdammala vkumar]

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]

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

String query = "SELECT column_name from column_list_table" //GET ALL COLUMN NAMES FROM A TABLE

stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
     String column_name = rs.getString(1); // GET SINGLE DYNAMIMC COLUMN NAME
       
     String query = "SELECT " + column_name + " FROM USERS WHERE ROLE = ?";  
  
     con.prepareStatement(query);  
            
     .....//rest of code set ROLE parameter etc.

[chris webster]

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]

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 Vashko]

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.