Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Trouble with INSERT

 
M Burke
Ranch Hand
Posts: 406
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am writing a utility using JDBC to make scripts to import data from one db to another. But some of the text contains chars that mess up the SQL statement, specifically '\'', '\n', and '\r'; Right now, I am stripping them out of the row text. Can I wrap them somehow so I don't lose the data? I am using MS SQL2000.

An example...

INSERT INTO Customer
(Name, Description, [Action])
VALUES ('MyName', 'MyDesc', 'MyAction's')

Notice Action has an 's
[ December 15, 2005: Message edited by: M Burke ]
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use a PreparedStatement. In fact, it's a good idea to prefer PreparedStatements in general...

And it's easy to do:
Since you're probably inserting multiple rows, check out the addBatch() and executeBatch() methods -- batching it is not much harder.
[ December 15, 2005: Message edited by: Jeff Albrechtsen ]
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most JDBC implementations allow you to specify two quotes '' (not a double quote) to represent a single quote. The other characters though, such as line breaks has to be cut out though although some drivers may ignore it.
 
M Burke
Ranch Hand
Posts: 406
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Scott. The '' works. I'm still not sure what to do with \n \r.

Jeff, I am makeing a utility that makes .sql scripts. Your solution is better, but I want to move data betweem db's that are not connected in any way.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use String class indexOf() and replace()/replaceAll() to help you remove characters such as:

String newString = oldString.replaceAll("\n", "");
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by M Burke:

Jeff, I am makeing a utility that makes .sql scripts. Your solution is better, but I want to move data betweem db's that are not connected in any way.


Could you explain why PreparedStatements are less relevant when moving data *between* databases? (By the way, you may have problems moving dates or timestamps, and the same PreparedStatement approach solves that problem as well... And did I mention that PreparedStatement can be faster, too, because they can be precompiled...)
[ December 16, 2005: Message edited by: Jeff Albrechtsen ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic