Error in the native query

Question

Im new to sql and im facing a problem with the below native query

 public void saveOfflineBatchDetails(BigInteger user_id) {

    em.createNativeQuery("INSERT INTO rst_offline_transaction_batch (created_date , user_id)" +
            "VALUES('?1', ?2)")
            .setParameter(1, new java.util.Date())
            .setParameter(2, user_id)
            .executeUpdate();
}

It doesn't pass the values to the database. the created date should be the today's date and time. Can anyone tell me whats wrong in this query. Thanks a lot

paxdiablo · Accepted Answer · 2011-08-19T05:00:20.090

2

You generally don't include the single quotes for parameterised queries. Try:

VALUES(?1, ?2)

(without the quotes around the ?1). Otherwise it's hard (for both readers and parsers) to tell whether you wanted parameter 1 or the literal value ?1 to be inserted.

You should also check the return value from executeUpdate() to see if it thinks it affected any rows. This will probably give you zero but it's worth checking anyway.

And, finally, I think dates require special handling as per:

setParameter(1, new java.util.Date(), TemporalType.DATE);

This is because the Java Date object is not a date at all but a timestamp - you need to ensure you select the correct temporal object type so that the right value is placed in the query.

So, in short, something like:

int affected = em.createNativeQuery(
    "INSERT INTO rst_offline_transaction_batch (" +
    "    created_date," +     // ?1
    "    user_id" +           // ?2
    ") VALUES (?1,?2)"
    )
    .setParameter(1, new java.util.Date(), TemporalType.DATE)
    .setParameter(2, user_id)
    .executeUpdate();
// Check affected.

edited Aug 19 '11 at 05:00

answered Aug 19 '11 at 04:40

paxdiablo

854,327
234
1,573
1,953

I tried but still it doesn't add the vales to the DB.I add single quotations cos its date. – user421607 Aug 19 '11 at 04:44
@user421607: You don't need single quotes in a prepared statement, because the database engine will add them for you. – nikc.org Aug 19 '11 at 04:56
@paxdiablo: I don't think the type hinting is necessary, as the setParameter-function is overloaded for different types of values. – nikc.org Aug 19 '11 at 04:58
@nikc, true, but there's no two-argument overload for a date and, in any case, Java's dates are not dates, they're timestamps, so you're likely to end up with a parameter like `2011-01-02 15:36:74` rather than a usable date. In any case, I haven't checked my answer (I don't have a JEE environment handy at home), I may be completely wrong :-) My suggestion is for the OP to give it a shot. – paxdiablo Aug 19 '11 at 05:05
@paxdiablo: How is that not a usable date? But I see your point and stand corrected. – nikc.org Aug 19 '11 at 05:07

Error in the native query

1 Answers1