PostgreSQL RETURNING

[akos.k...@digitaz.rs]

Hi!

I have a complex INSERT query which works fine. So far it was enough to return only the primary key after INSERT but my requirements have changed. Now, instead of returning the primary key I have to return the contents of a specific column. In PostgreSQL it looks like:

    INSERT INTO a(c_1, c_2) VALUES (:c1, :c2) RETURNING c_2

However when I'm trying to execute the above query through JDBI I'm getting the following exception:

    java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.String

My query is defined like:

    @SqlUpdate

    public abstract String createWorkOrder(@BindBean Activity a);

query is stored in a separate .sql.stg file.

Any help is appreciated.

[Tom Davis]

Don't return a String from createWorkOrder when you really want an Integer. That's what's being complained about.

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

[akos.k...@digitaz.rs]

That's right.

The problem is that I want to return a String after the INSERT instead of an Integer.

(I my example the type of column c_2 is text).

[akos.k...@digitaz.rs]

Any idea how can I return a String after a successful insert?

I can do that with a plain PostgreSQL query but jDBI keeps returning the:

java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.String

exception.

[Steven Schlansker]

Think of an UPDATE … RETURNING instead as a SELECT with side effects, e.g. you would use @SqlQuery instead of @SqlUpdate.

What is the code you are using?

[akos.k...@digitaz.rs]

@SqlUpdate

public abstract String create(@BindBean Activity a);

was the original code which produced the

Tests in error: 

  createOrder(...): java.lang.Integer cannot be cast to java.lang.String

exception. I tried to change @SqlUpdate to @SqlSelect which produced the following error:

Tests in error: 

  createOrder(...): Query did not have a result set, perhaps you meant update? [statement:"createWorkOrder", located:"LOCK TABLE activity IN ACCESS EXCLUSIVE MODE\;

Which is actually true, I have a lock at the beginning of my query, so the whole thing looks like:

LOCK TABLE activity IN ACCESS EXCLUSIVE MODE\;

WITH ref_constant as (....)

INSERT INTO a(c_1, c_2) VALUES (:c1, :c2) RETURNING c_2

The query is stored in a separate .sql.stg file.

I tried to remove the LOCK statement from the beginning of the query and jDBI returned the expected result string from RETURNING. So it definitely works without the LOCK.

I guess the solution would be to create separate queries to execute the LOCK and the INSERT statements:

@SqlUpdate

public abstract void lock()

@SqlQuery

public abstract String create()

[Tom Davis]

akos.k...@digitaz.rs writes:

> @SqlUpdate
> public abstract String create(@BindBean Activity a);
>
> was the original code which produced the
>
> Tests in error:
> createOrder(...): java.lang.Integer cannot be cast to java.lang.String
>
> exception. I tried to change @SqlUpdate to @SqlSelect which produced the
> following error:
>
> Tests in error:
> createOrder(...): Query did not have a result set, perhaps you meant
> update? [statement:"createWorkOrder", located:"LOCK TABLE activity IN
> ACCESS EXCLUSIVE MODE\;
>
> Which is actually true, I have a lock at the beginning of my query, so the
> whole thing looks like:
>
> LOCK TABLE activity IN ACCESS EXCLUSIVE MODE\;
> WITH ref_constant as (....)
> INSERT INTO a(c_1, c_2) VALUES (:c1, :c2) RETURNING c_2
>
> The query is stored in a separate .sql.stg file.
>
> I tried to remove the LOCK statement from the beginning of the query and
> jDBI returned the expected result string from RETURNING. So it definitely
> works without the LOCK.
> I guess the solution would be to create separate queries to execute the LOCK
> and the INSERT statements:

Doing it this way does nothing to guarantee that your lock and insert
take place in the same transaction (IIRC, they are guaranteed not to),
use the same connection, etc. Depending on your DBMS, this means the
lock will likely do nothing, opening you up to some very annoying bugs.
For instance, starting a transaction in MySQL implicitly releases any
table locks, meaning your lock will just silently release regardless of
any later "UNLOCK" calls (MySQL logic is always "just silently do
something random"). Postgres has no "UNLOCK"; locks are automatically
released at the end of a transaction, etc.

Something like this should be done directly using a Handle so you can
control the transaction, such as via the Transactional "mixin" methods
(begin(), etc.) or by writing a callable that uses inTransaction().

IIRC annotated DBI methods don't support multiple statements,
period. This is either a feature or a pain in the ass, depending on your
perspective.

>
>
> @SqlUpdate
> public abstract void lock()
>
> @SqlQuery
> public abstract String create()
>
>
>
> On Monday, September 9, 2013 7:35:12 PM UTC+2, Steven Schlansker wrote:
>>
>> Think of an UPDATE … RETURNING instead as a SELECT with side effects, e.g.
>> you would use @SqlQuery instead of @SqlUpdate.
>>
>> What is the code you are using?
>>

>> On Sep 9, 2013, at 1:27 AM, akos.k...@digitaz.rs <javascript:> wrote:
>>
>> > Any idea how can I return a String after a successful insert?
>> >
>> > I can do that with a plain PostgreSQL query but jDBI keeps returning
>> the:
>> >
>> > java.lang.ClassCastException: java.lang.Integer cannot be cast to
>> java.lang.String
>> >
>> > exception.
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> Groups "jDBI" group.
>> > To unsubscribe from this group and stop receiving emails from it, send

>> an email to jdbi+uns...@googlegroups.com <javascript:>.

[akos.k...@digitaz.rs]

We ended up controlling the transactions manually using Handle.

Thank you for your time.