• 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

Constructing prepareStatement efficiently

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
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
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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
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

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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.




 
Ranch Hand
Posts: 820
IntelliJ IDE VI Editor Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:

 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 VI Editor Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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)
 
But how did the elephant get like that? What did you do? I think all we can do now is read this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic