Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

Only for JDBC tallents

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic