Forums Register Login

prepared statement to update table with hundreds of columns

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
Indy,
Do you know about P6 Spy?

Good Luck,
Avi.
+Pie Number of slices to send: Send
"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
+Pie Number of slices to send: Send
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.)
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
I really think if you are updating using a large SQL statement, you should use a stored procedure if possible - more efficient and safer ...
Think of how dumb the average person is. Mathematically, half of them are EVEN DUMBER. Smart tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 1665 times.
Similar Threads
getGeneratedKeys() + batchUpdate
how to reduce time in deleting records
Callable statement ...?
Interchanging Database columns as rows while displaying in a JTable
Allowing unchanged values in mySQL update PreparedStatement
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 12:11:02.