• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

prepared statement to update table with hundreds of columns

 
Indhar Devanth
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • 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
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Indy,
Do you know about P6 Spy?

Good Luck,
Avi.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65342
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • 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
  • 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
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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.
 
Ben Keeping
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • 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 ...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic