aspose file tools*
The moose likes JDBC and the fly likes Only for JDBC tallents Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Only for JDBC tallents" Watch "Only for JDBC tallents" New topic
Author

Only for JDBC tallents

Sasi Kumar
Greenhorn

Joined: Jan 18, 2002
Posts: 15
If you give a easy solution for this problem, then you are very good in JDBC.
The following SQL query should be executed only
through PreparedStatement. You should not use
Statement.
Query is
select employees from employeemast where
employee_id in (1,2,3,4,5)
This you have to give in prepared statement as
select employees from employeemast where
employee_id in (?)
The values (1,2,3,4,5) will change dynamically in
a for loop.
if you say
preparedStatementOb.setString(1,stringValue)
here stringValue = "1,2,3,4,5"
This setSting will wrap a single quotes in
the query ie, query will build by java like following.
select employees from employeemast where
employee_id in ('1,2,3,4,5')
which will cause invalid number error. since
employee_id is a number in database.
You should not dynamically build ? in the query
like following
select employees from employeemast where
employee_id in (?,?,?,?,?)
There should be only one Question mark.
If you find a solution then u are great.
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
well I guess you could send an array to a proc, thus:
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("TESTARRAY", conn);

ARRAY newArray = new ARRAY(desc, conn, s);
CallableStatement cstmt = conn.prepareCall("{ call test.test (?) }");
cstmt.setArray(1, newArray);

Personally I think it's ok to build the query dynamically (so you have a variable number of '?' in the query). In fact thats exactly what I use in my app

You might try asking on dbasupport.com there are plenty of smart(er) people there who can help you out.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

unfortunately the PreparedStatement was not made to be used in this way. Even if you find a workaround to make it execute, you will most likely lose all advantages of using PreparedStatements( statement caching cannot be done as the number of parameters is different on each execution, type checking has to be done on every execution, etc. ) So even though you may accomplish fooling the db into using a PreparedStatement in this way, it accomplishes nothing. Your better choice for dynamic queries is a Statement which loops to append the IN values.
and just because you happen to slip it by one driver, does not mean that it will work for another.
Jamie
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
I guess it depends on the application.
The benefit is that strings don't need to be escaped, which is a big prob with statements. I'm happy to take a performance hit.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Only for JDBC tallents