Insert doesn't insert

[longneckGoosie]

I don't know what's going on with an insert.

I am using Java to build many (11000 or so) SQL insert statments. I am looping through many ASCII comma delimited files pulling out the fields I need and putting them in the proper order.

The problem is this. In one of the string fields, single ticks occur as accent marks in some of the names, XI'AN, for example. Well when I build my SQL statment, I get:

Insert into <table_name> values (00, 00, 00, 'XI'AN', 00, 00)

Oracle thinks that since there is a ' after XI, that ends the string. So, it expects a comma. I get an error. How do I get around this? I can manipulate the ASCII files before I pump them in, if required. In fact I tried to change the ticks to %%039, but to no avail. I wind up with XI%%039AN in the table.

Any help here would be greatly appreciated.

 

[sedj]

You need to insert it like this (ie where there are one single quote, there needs to be two, to insert into SQL db) :

Insert into <table_name> values (00, 00, 00, 'XI''AN', 00, 00)

 

[sedj]

note, its two single quotes to insert just one - not a double quote character, if you see what I mean !

 

[venur]

Hi,

If you use the PreparedStatement then you will not have the single qote problem. Then the above insert statement will be some thing like

Sol 1:
String sql = &quot;INSERT INTO <table_name> VALUES(?)&quot;;
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1,&quot;XI'AN&quot;
pstmt.executeUpdate();

Sol 2:
Write a function which replaces the ' in a string.
thread269-657910

The best way is Sol 1. as you are using Oracle it accepts BatchUpdates, set of SQL statements assembled and then sent altogether to the database.

Cheers,
Venu