You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Rick Bonnett <rb...@mesca.com> on 2014/01/08 20:45:31 UTC

Selecting against postgres sequence

I’m trying to directly fetch the nextval form a postgres sequence using Cayenne, and am baffled but why I am getting nothing back.

I’m using SqlTemplate to build my query, since the modeler does;t seem to let me model a sequence DB object directly.

Code snippets:

1) using performQuery
SQLTemplate query = new SQLTemplate(EdiProfile.class,"select nextval('live.edi_control_number_sequence') as seqNum");
List response = ORMContext.performQuery(query);

2) using performGenericQuery

SQLTemplate query = new SQLTemplate(EdiProfile.class,"select nextval('live.edi_control_number_sequence') as seqNum");
QueryResponse response = ORMContext.performGenericQuery(query);

In both cases I am getting back null from the query.

Here’s what the log shows:

INFO: Detected and installed adapter: org.apache.cayenne.dba.postgres.PostgresAdapter
INFO: --- transaction started.
INFO: select nextval('live.edi_control_number_sequence') as seqNum
INFO: === returned 1 row. - took 10 ms.
INFO: +++ transaction committed.


And in fact if I go out and look in the postgres database, the sequence is getting hit and incremented.

Is this something that just won;t work with Cayenne (in which case I suppose I’ll have to fall back to using JDBC directly).

Thanks

Re: Selecting against postgres sequence

Posted by Rick Bonnett <rb...@mesca.com>.
Thanks Mike, that did the trick

On Jan 8, 2014, at 3:13 PM, Mike Kienenberger <mk...@gmail.com> wrote:

> Well, it's trying to convert your returned sequence result into an
> EdiProfile object.  Is that compatible?   It might make more sense to
> read the sequence as a data row -- setFetchingDataRows(true);
> 
> Here's code I used to fetch a sequence number (from an old Cayenne 2.0
> project, so some things may be a little different now):
> 
>    private Integer fetchDocumentNumberAutoIncrement() {
>        String defaultSql = "SELECT #result('max(DOCUMENT_NUMBER)+1'
> 'long' 'ID') FROM ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT";
>        String oracleSql = "SELECT
> #result('SEQ_AuthDocDocumentNumber.NextVal' 'long' 'ID') FROM DUAL";
> 
>        SQLTemplate rawSelect = new SQLTemplate(getClass(), defaultSql);
>        rawSelect.setTemplate(OracleAdapter.class.getName(), oracleSql);
>        rawSelect.setTemplate(Oracle11Adapter.class.getName(), oracleSql);
>        rawSelect.setFetchingDataRows(true);
> 
>        List list = getDataContext().performQuery(rawSelect);
>        Map row = (Map)list.get(0);
> 
>        Number autoincrementID = (Number)row.get("ID");
> 
>        return new Integer(autoincrementID.intValue());
>    }
> 
> 
> On Wed, Jan 8, 2014 at 2:45 PM, Rick Bonnett <rb...@mesca.com> wrote:
>> I’m trying to directly fetch the nextval form a postgres sequence using Cayenne, and am baffled but why I am getting nothing back.
>> 
>> I’m using SqlTemplate to build my query, since the modeler does;t seem to let me model a sequence DB object directly.
>> 
>> Code snippets:
>> 
>> 1) using performQuery
>> SQLTemplate query = new SQLTemplate(EdiProfile.class,"select nextval('live.edi_control_number_sequence') as seqNum");
>> List response = ORMContext.performQuery(query);
>> 
>> 2) using performGenericQuery
>> 
>> SQLTemplate query = new SQLTemplate(EdiProfile.class,"select nextval('live.edi_control_number_sequence') as seqNum");
>> QueryResponse response = ORMContext.performGenericQuery(query);
>> 
>> In both cases I am getting back null from the query.
>> 
>> Here’s what the log shows:
>> 
>> INFO: Detected and installed adapter: org.apache.cayenne.dba.postgres.PostgresAdapter
>> INFO: --- transaction started.
>> INFO: select nextval('live.edi_control_number_sequence') as seqNum
>> INFO: === returned 1 row. - took 10 ms.
>> INFO: +++ transaction committed.
>> 
>> 
>> And in fact if I go out and look in the postgres database, the sequence is getting hit and incremented.
>> 
>> Is this something that just won;t work with Cayenne (in which case I suppose I’ll have to fall back to using JDBC directly).
>> 
>> Thanks
>>

Re: Selecting against postgres sequence

Posted by Mike Kienenberger <mk...@gmail.com>.
Well, it's trying to convert your returned sequence result into an
EdiProfile object.  Is that compatible?   It might make more sense to
read the sequence as a data row -- setFetchingDataRows(true);

Here's code I used to fetch a sequence number (from an old Cayenne 2.0
project, so some things may be a little different now):

    private Integer fetchDocumentNumberAutoIncrement() {
        String defaultSql = "SELECT #result('max(DOCUMENT_NUMBER)+1'
'long' 'ID') FROM ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT";
        String oracleSql = "SELECT
#result('SEQ_AuthDocDocumentNumber.NextVal' 'long' 'ID') FROM DUAL";

        SQLTemplate rawSelect = new SQLTemplate(getClass(), defaultSql);
        rawSelect.setTemplate(OracleAdapter.class.getName(), oracleSql);
        rawSelect.setTemplate(Oracle11Adapter.class.getName(), oracleSql);
        rawSelect.setFetchingDataRows(true);

        List list = getDataContext().performQuery(rawSelect);
        Map row = (Map)list.get(0);

        Number autoincrementID = (Number)row.get("ID");

        return new Integer(autoincrementID.intValue());
    }


On Wed, Jan 8, 2014 at 2:45 PM, Rick Bonnett <rb...@mesca.com> wrote:
> I’m trying to directly fetch the nextval form a postgres sequence using Cayenne, and am baffled but why I am getting nothing back.
>
> I’m using SqlTemplate to build my query, since the modeler does;t seem to let me model a sequence DB object directly.
>
> Code snippets:
>
> 1) using performQuery
> SQLTemplate query = new SQLTemplate(EdiProfile.class,"select nextval('live.edi_control_number_sequence') as seqNum");
> List response = ORMContext.performQuery(query);
>
> 2) using performGenericQuery
>
> SQLTemplate query = new SQLTemplate(EdiProfile.class,"select nextval('live.edi_control_number_sequence') as seqNum");
> QueryResponse response = ORMContext.performGenericQuery(query);
>
> In both cases I am getting back null from the query.
>
> Here’s what the log shows:
>
> INFO: Detected and installed adapter: org.apache.cayenne.dba.postgres.PostgresAdapter
> INFO: --- transaction started.
> INFO: select nextval('live.edi_control_number_sequence') as seqNum
> INFO: === returned 1 row. - took 10 ms.
> INFO: +++ transaction committed.
>
>
> And in fact if I go out and look in the postgres database, the sequence is getting hit and incremented.
>
> Is this something that just won;t work with Cayenne (in which case I suppose I’ll have to fall back to using JDBC directly).
>
> Thanks
>