CODEWITHSUNDEEP
javasql
Yakhoob
Yakhoob

Reputation: 569

Numeric constant not valid in SQL

I'm having a list object and im sending to SQL query as 

UPDATE <table name> SET status= 'CLOSED' WHERE dlno IN ("+StringUtils.join(deals, ',')+") ";

My deals object is as follows

1549886508071HELMPFN

How can I pass a string here 

[SQL0103] Numeric constant 1549886508071HELMPFN not valid.

Upvotes: 0

Views: 1506

Answers (2)

EvanM
EvanM

Reputation: 687

Using bind variables will solve your quote problem and make your code safe against SQL injection:

    List<String> deals = ImmutableList.of("abc", "123", "def");
    StringBuilder questionMarks = new StringBuilder("?");
    for (int i=1;i<deals.size();i++) {
        questionMarks.append(",?");
    }
    Connection conn = ...; // presumably, you already have this
    PreparedStatement stmt = conn.prepareStatement(
            "UPDATE my_table SET status= 'CLOSED' WHERE dlno IN (" + questionMarks + ")");
    for (int i=1;i<=deals.size();i++) { // note these are 1-indexed, not 0-indexed
        stmt.setString(i, deals.get(i-1));
    }
    stmt.executeUpdate();

Basically, you'll generate a query that says UPDATE my_table SET status = 'CLOSED' WHERE dlno IN (?,?) (the number of question marks corresponding to the number of parameters), and then you'll update them with stmt.setString. And then you can execute your update.

Also note you'll need to deal with SQLExceptions and closing the statement. Removed here for clarity.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

String constants need to be enclosed in single quotes. In your case this would look like:

UPDATE <table name>
    SET status = 'CLOSED'
    WHERE dlno IN ('"+StringUtils.join(deals, "', '")  +"') ";

Upvotes: 1

Related Questions