"No ScalarType registered" error with setParameter

[Thibault Meyer]

Hi,

I would like to use a "raw" SQL query with Ebean 10.3.2 but I'm facing a strange error (No ScalarType registered). When I copy/past the SQL query directly on PostgreSQL, it working.

The Java code

                final SqlQuery sqlQuery = Ebean.createSqlQuery(
                    "SELECT COUNT(1) AS tc "
                        + "FROM payment_order as po LEFT JOIN tag ON tag.id = po.tag_id "
                        + "WHERE tag.id = ? AND po.status NOT IN(?) "
                        + "AND po.created_at >= (NOW() - INTERVAL '" + cleanedPeriod + "')"
                );
                sqlQuery.setParameter(1, this.tag.getId());
                sqlQuery.setParameter(
                    2,
                    Arrays.asList(
                        OrderStatus.CANCELED.getValue(),
                        OrderStatus.EXPIRED.getValue(),
                        OrderStatus.REFUSED.getValue()
                    )
                );
                final SqlRow sqlRow = sqlQuery.findUnique();
                return sqlRow.getInteger("tc");

Output

Query threw SQLException:No ScalarType registered for class java.util.Arrays$ArrayList

Query was:SELECT COUNT(1) AS tc FROM payment_order as po LEFT JOIN tag ON tag.id = po.tag_id WHERE tag.id = ? AND po.status NOT IN(?) AND po.created_at >= (NOW() - INTERVAL '10 MINUTES')

Sincerely.

[Rob Bygrave]

The SQL has 1 bind value for the IN clause ... and then it binds a List of 3 values.

Instead the SQL needs to be:

po.status NOT IN (?, ?, ?)

... with the bind being:

sqlQuery.setParameter(1, this.tag.getId());
sqlQuery.setParameter(2, OrderStatus.CANCELED.getValue())
sqlQuery.setParameter(3, OrderStatus.EXPIRED.getValue())
sqlQuery.setParameter(4, OrderStatus.REFUSED.getValue())

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Thibault Meyer]

Thanks Rob,

To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.