Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Constructing prepareStatement efficiently

 
James Merchny
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to add a record to a database that has about 30 fields. As there are text, I've got to enclose the field contents in '.

conn.prepareStatement("INSERT INTO Table1(Field1, Field2) VALUES('"+data1+"','"+data2+"') ");

I have the feeling there's got to be aneater way....?

Fields are text datatypes
JDBC/ODBC bridge.
MS-Access is the underlying database.
JDK 1.6
NetBeans 6.5
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35266
383
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
James,
Welcome to JavaRanch!

You should be using:


This approach lets the database reuse the execution plan.
 
James Merchny
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sure...and I am doing that. But to wrap a bit of text in quotes thirty times just makes me wonder if there is not a better way.

Also, trying to debug the SQL statement is a challenge. I cannot find a way to examine the "updated" statement just prior to execution, other than drilling down through a coupld of layers of watches.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
James Merchny wrote:Sure...and I am doing that. But to wrap a bit of text in quotes thirty times just makes me wonder if there is not a better way.

Where in Jeanne's example do you see text being wrapped in quotes at all?
 
James Merchny
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sorry... didn't explain myself there...

I have only managed to get this to work by doing this:

I prepare text2 by this:

text2 = "'" + data + "'";

so that the stmt.setString(2, text2) part will work.

It may be a quirk of MS-Access, but if the text is not enclosed in single quotes, it gives a SQL Sytanx error.




 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can't confirm that this is true for MS Access, but I'll take your word for it. How about a method that adds the single quotes for you? It meets your requirement of not wrapping 30 text strings in quotes.



or, if your data is in some kind of collection already:

 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
are you sure?

You have to use question marks as placeholders *without single quotes, even for string values*.


setString() will not work if you use ext2 = "'" + data + "'"

it will work if you use ext2 = ?
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
James Merchny wrote:Sure...and I am doing that. But to wrap a bit of text in quotes thirty times just makes me wonder if there is not a better way.

Also, trying to debug the SQL statement is a challenge. I cannot find a way to examine the "updated" statement just prior to execution, other than drilling down through a coupld of layers of watches.


Yep. I hate that. A long time ago I found a library that could parse the prepared statement and show you the actual SQL you end up with.
Obviously, it is only for debugging as it kind of defeats the purpose of prepared statements.

I posted on my blog about it a long time ago, here it is:
http://www.phpsolvent.com/wordpress/?p=198

(note that my version of the library is no longer available)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic