• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

prepared statement to update table with hundreds of columns

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi anyone

I need to update table with huge number of columns. I am using PreparedStatement to help me with that. Debugging for any kind of SQLException is consuming valuable time. I need help on
1. How do I get the string (for debugging purposes) that the preparedstatement is generating for me.
2. Is there any better solution for updating table with huge number of column data other than using PreparedStatement?

I am looking for a solution that will change to
thank you,

Indy
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ind,
Welcome to JavaRanch!

1) There isn't a method that returns the String. The best you can do is print it out before setting the prepared statement and filling in the variables. I suggest an incremental approach to troubleshooting this. Start by updating one column. Once that works add ten more, then ten more, etc. That way you can isolate the problem.
2) Prepared statements are the best way to go for updating data.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Indy,
Do you know about P6 Spy?

Good Luck,
Avi.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"Ind Dev",

We're pleased to have you here with us on the Ranch, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender
 
Indhar Devanth
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank you Jeanne for your advice. I have tried doing that too, executing 10 columns at a time, and that was taking too much time too. If there is no better soluction than prepared statement then for now I will stop looking for an alternative to ps. So now I am looking for a way to catch the SQL string the preparedStatement is generating. I thank Avi for suggesting p6spy. I am looking at this, and trying to use it. Hope I get better results. Thank you guys for your help.....

-Indu
(ps: Bear, while registering I was told that someone had already used my user name, and told that I need to change my name and user name, which I did. Now I tried to change user name and it got approved. I apologise for messing up my own name.)
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Indy,
Thanks for updating the display name.

I was actually suggesting starting with 10 columns and building up the statement, not splitting it into separate queries. It's more a debugging technique. By the time you reach production, everything is done in one prepared statement.
 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I really think if you are updating using a large SQL statement, you should use a stored procedure if possible - more efficient and safer ...
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic