Re[2]: [JBoss-user] Oracle 8i and auto-increment

[Alexey L. <al...@jb...>]

Perhaps, it is the result of casting the type retured by the driver to
the type you use for the column.
I don't know what type is returned by the driver.
So, you have two choices:
- adjust column's type to match the driver's;
- patch or write-your-own create command.

alex

Thursday, May 22, 2003, 8:17:57 PM, Richard Stack wrote:

RS> Checking the source for JDBCPkSqlCreateCommand.java, I only need to 
RS> change the attribute name to "pk-sql" rather than "sequence". But now 
RS> I'm getting a ClassCastException. Any idea what's wrong?

RS> Richard

RS> <entity-command name="oracle-sequence" 
RS> class="org.jboss.ejb.plugins.cmp.jdbc.JDBCPkSqlCreateCommand">
RS>                 <attribute name="pk-sql">SELECT theSequence.NEXTVAL FROM 
RS> SYS.DUAL</attribute>
RS>         </entity-command>


RS> 2003-05-22 12:57:03,076 DEBUG 
RS> [org.jboss.ejb.plugins.cmp.jdbc.JDBCPkSqlCreateCommand.Customer] 
RS> Executing SQL: SELECT theSequence.NEXTVAL FROM SYS.DUAL
RS> 2003-05-22 12:57:03,086 DEBUG 
RS> [org.jboss.ejb.plugins.cmp.jdbc.JDBCPkSqlCreateCommand.Customer] Create: 
RS> pk=4
RS> 2003-05-22 12:57:03,096 DEBUG 
RS> [org.jboss.ejb.plugins.cmp.jdbc.JDBCPkSqlCreateCommand.Customer] 
RS> Executing SQL: SELECT COUNT(*) FROM CUSTOMER WHERE CUSTOMER_ID=?
RS> 2003-05-22 12:57:03,096 ERROR 
RS> [org.jboss.ejb.plugins.cmp.jdbc.JDBCPkSqlCreateCommand.Customer] Error 
RS> checking if entity exists
RS> java.lang.ClassCastException
RS>         at 
RS> oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:1971)
RS>         at 
RS> oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2052)
RS>         at 
RS> org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:607)
RS>         at

RS> Richard Stack wrote:
>> OK, I get it now, thanks.
>> 
>> Richard
>> 
>> Jeremy Boynes wrote:
>> 
>>> A sequence is not really a pseudo-column, it is a separate construct 
>>> in the
>>> database. NEXTVAL is a method on that construct.
>>>
>>> In your INSERT statement, you are calling the NEXTVAL method to get 
>>> the next
>>> value, which then becomes a data value inserted in the table.
>>>
>>> Another way to write this (in PL/SQL) is:
>>> DECLARE NUMBER(10) a
>>> SELECT cust_seq.NEXTVAL INTO a FROM SYS.DUAL
>>> INSERT INTO customer VALUES(a, 'Tom', 'Smith')
>>>
>>> The JDBCPkSqlCreateCommand does just: it issues a query first and then 
>>> uses
>>> the result from the query to insert into the table. In the 
>>> entity-commnd you
>>> specify the sql to execute, something like
>>>
>>>   <entity-command name="oracle-sequence"
>>>         class="org.jboss.ejb.plugins.cmp.jdbc.JDBCPkSqlCreateCommand">
>>>     <attribute name="sequence">SELECT cust_seq.NEXTVAL FROM
>>> SYS.DUAL</attribute>
>>>   </entity-command>
>>>
>>> This needs to SQL statements (the SELECT, the INSERT) which is 
>>> inefficient.
>>> In 3.2.2 we introduced the JDBCOracleCreateCommand which does an 
>>> single SQL
>>> statement:
>>>   INSERT INTO customer (cust_id, first, last)
>>>     VALUES(cust_seq.NEXTVAL, 'Tim', 'Smith')
>>>     RETURNING cust_id INTO ?
>>>
>>> As sequences are not associated with a table, then JBoss will not 
>>> create or
>>> drop them for you.
>>>
>>> Cheers
>>> Jeremy
>>>
>>>
>>>> -----Original Message-----
>>>> From: jbo...@li...
>>>> [mailto:jbo...@li...]On Behalf Of Richard
>>>> Stack
>>>> Sent: Wednesday, May 21, 2003 3:24 PM
>>>> To: jbo...@li...
>>>> Subject: Re: [JBoss-user] Oracle 8i and auto-increment
>>>>
>>>>
>>>> Thanks to Peter, Alex and Jeremy for the responses. I am using 3.2.1,
>>>> but I'm not clear what you mean by PkSQL command. I understand a
>>>> SEQUENCE in Oracle is a pseudocolumn and can be created like:
>>>>
>>>> CREATE SEQUENCE cust_seq
>>>> INCREMENT BY 1
>>>> START WITH 1
>>>> NOMAXVALUE
>>>> NOCYCLE
>>>> CACHE 10;
>>>>
>>>> and can be accessed like:
>>>> INSERT INTO customer VALUES(cust_seq.nextval, 'Tom', 'Smith' ....);
>>>>
>>>> I am having JBoss create and remove the tables. Can I have it create and
>>>>   remove the sequences as well?
>>>>
>>>> Richard
>>>>
>>>>
>>>>
>>>> Jeremy Boynes wrote:
>>>>
>>>>> This is in 3.2.2beta - for 3.2.1 you need to use a PkSQL command to
>>>>> pre-fetch the sequence value.
>>>>>
>>>>> Jeremy
>>>>>
>>>>>
>>>>>
>>>>>> -----Original Message-----
>>>>>> From: jbo...@li...
>>>>>> [mailto:jbo...@li...]On Behalf Of Alexey
>>>>>> Loubyansky
>>>>>> Sent: Wednesday, May 21, 2003 9:35 AM
>>>>>> To: Richard Stack
>>>>>> Subject: Re: [JBoss-user] Oracle 8i and auto-increment
>>>>>>
>>>>>>
>>>>>> Add to jbosscmp-jdbc.xml:
>>>>>>     <!-- this command requires the specified sequence to have
>>>>>> been created -->
>>>>>>     <entity-command name="oracle-sequence"
>>>>>>
>>>>>> class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand">
>>>>>>        <attribute name="sequence">TEST_SEQUENCE</attribute>
>>>>>>     </entity-command>
>>>>>>
>>>>>> It's a child of <entity> element. AFAIK, class attribute is optional.
>>>>>>
>>>>>> alex
>>>>>>
>>>>>> Wednesday, May 21, 2003, 6:48:31 PM, Richard Stack wrote:
>>>>>>
>>>>>> RS> I'm trying to get auto-increment of the primary key to work with
>>>>>> RS> JBoss3.2 and Oracle 8i.
>>>>>>
>>>>>> RS> In jbosscmp-jdbc.xml I've added auto-increment to primary key 
>>>>>> field
>>>>>>
>>>>>> RS> <cmp-field>
>>>>>> RS>     <field-name>id</field-name>
>>>>>> RS>     <column-name>CUSTOMER_ID</column-name>
>>>>>> RS>     <jdbc-type>INTEGER</jdbc-type>
>>>>>> RS>     <sql-type>NUMBER(10)</sql-type>
>>>>>> RS>     <auto-increment/>
>>>>>> RS> </cmp-field>
>>>>>>
>>>>>> RS> What else has to be added to get this to work?
>>>>>>
>>>>>> RS> Richard
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> RS> -------------------------------------------------------
>>>>>> RS> This SF.net email is sponsored by: ObjectStore.
>>>>>> RS> If flattening out C++ or Java code to make your application fit 
>>>>>> in a
>>>>>> RS> relational database is painful, don't do it! Check out 
>>>>>> ObjectStore.
>>>>>> RS> Now part of Progress Software.