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

Processing large fields

 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I'm writing JDBC translation code to copy data from DB 1 - > DB 2.

The source database has several multi-line fields with commas, quotes, etc., which mess up any attempt at using SQL INSERT INTO TABLE Values(value1, value2, ....) - resulting in SQL Syntax errors.

So, what's the best way to handle this more complicated situation?

Question: So for each of the complicated multi-line fields, do I need to pre-process them, possibly with a RegEx, to remove quotes, commas and other problem characters or is there a better INSERT mechanism for this case I don't know about?

Thanks,

mike
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The first question I like to ask: why are you copying data from db1 to db2 using JDBC? Why not use the export/import (or copy data) function of the tool set of your database.

If you really need JDBC, you should definitely use prepared statements. Then you have one INSERT statement for all rows and only the parameters will be different for each row.Using this approach you don't have to worry about removing/escaping quotes. The JDBC driver will take care of this cumbersome task. More info about prepared statements can be found here.

Hope it helps!
Kind regards,
Roel
 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:The first question I like to ask: why are you copying data from db1 to db2 using JDBC? Why not use the export/import (or copy data) function of the tool set of your database.

If you really need JDBC, you should definitely use prepared statements. Then you have one INSERT statement for all rows and only the parameters will be different for each row.Using this approach you don't have to worry about removing/escaping quotes. The JDBC driver will take care of this cumbersome task. More info about prepared statements can be found here.

Hope it helps!
Kind regards,
Roel


Yeah, I was looking at changing it to a PreparedStatement after my posting. Thanks for that suggestion as well. It worked!!!

I'm using JDBC since I only have access to the data files themselves, but not the application that can open them. Plus, the export doesn't work with all types of fields making your good export/import suggestion not workable.

Thanks again!

mike
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike London wrote:Yeah, I was looking at changing it to a PreparedStatement after my posting. Thanks for that suggestion as well. It worked!!!

Glad to hear your solution worked and it was exactly the same as my suggestion. You'll know what they say about great minds
 
Mike London
Ranch Hand
Posts: 1227
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Mike London wrote:Yeah, I was looking at changing it to a PreparedStatement after my posting. Thanks for that suggestion as well. It worked!!!

Glad to hear your solution worked and it was exactly the same as my suggestion. You'll know what they say about great minds


Cool thanks!

- mike
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic