Trouble with INSERT

[M Burke]

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]

Use a PreparedStatement. In fact, it's a good idea to prefer PreparedStatements in general...

And it's easy to do:

String sql = "INSERT INTO Customer(Name, Description, Action) VALUES (?, ?, ?)";
PreparedStatement pstmt = null;
try {
    pstmt = con.prepareStatement(sql);
    pstmt.setString(1, name);
    pstmt.setString(2, desc);
    pstmt.setString(3, act);
    pstmt.executeUpdate();
} finally {
    if (pstmt != null)
        pstmt.close();
}

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]

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]

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]

Use String class indexOf() and replace()/replaceAll() to help you remove characters such as:

String newString = oldString.replaceAll("\n", "");

[Jeff Albertson]

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 ]