Hypersonic SQL IDENTITY

[mozheyko_d]

I have hsqldb tables
create cached table organization(
code integer not null identity primary key,
name varchar( 256)
);

create cached table employee(
code integer not null identity primary key,
firstname varchar( 50),
lastname varchar( 50),
organization integer,
foreign key ( organization) references organization( code)
);

and CMP 2 Beans with relationship employee -> organization

when i call

Organization orgBean = organizationHome.create( 'The Dummies, Inc.');
Employee emlBean = create( 'Nick', 'Dummy', orgBean);

in tables inserting:

ORGANIZATION
CODE NAME
===================================
0 The Dummies, Inc.

EMPLOYEE

CODE FIRSTNAME LASTNAME ORGANIZATION
=======================================================
0 Nick Dummy null



How i can take idenity(database-engine-generated) value of primary key ?

[aloubyansky]

This is currently not supported for Hypersonic. Though, you can use other ways to generate primary keys.
For Hypersonic it's UUIDKeyGenerator
<unknown-pk>
<unknown-pk-class>java.lang.String</unknown-pk-class>
<column-name>genid</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</unknown-pk>
<entity-command name="key-generator"
class="org.jboss.ejb.plugins.cmp.jdbc.JDBCKeyGeneratorCreateCommand">
UUIDKeyGeneratorFactory
</entity-command>

and, if it supports JDBC3.0, get-generated-keys entity command.
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>genid</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INTEGER(11)</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="get-generated-keys"
class="org.jboss.ejb.plugins.cmp.jdbc.jdbc3.JDBCGetGeneratedKeysCreateCommand"/>

[mozheyko_d]

<jbosscmp-jdbc>

java:/DefaultDS
<datasource-mapping>Hypersonic SQL</datasource-mapping>
<create-table>true</create-table>
<remove-table>false</remove-table>
<pk-constraint>true</pk-constraint>
<preferred-relation-mapping>foreign-key</preferred-relation-mapping>


<enterprise-beans>

....
<cmp-field>
<field-name>code</field-name>
<column-name>code</column-name>
<unknown-pk>
<unknown-pk-class>java.lang.String</unknown-pk-class>
<column-name>code</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</unknown-pk>
<entity-command name="key-generator"
class="org.jboss.ejb.plagins.cmp.jdbc.JDBCKeyGeneratorCreateCommand">
UUIDKeyGeneratorFactory
</entity-command>
</cmp-field>
....

</jbosscmp-jdbc>

Can i write this tags if i use Hypersonic SQL ?

[aloubyansky]

Yes, of course. It doesn't depend on the db you use. But I guess the JBoss version you use doesn't support it.
These configurations are available only in Branch_3_2 (JBoss-3.2.0beta2 currently) and upper.

[mozheyko_d]

Where i can download it ( Branch_3_2) ?

[aloubyansky]

Currently, it's available only in CVS.

[mozheyko_d]

I install jboss-3.2.0beta.

Where i can write "unknown-pk"

process error. Line: 50. Error message: Element type "unknown-pk" must be declared.

2002-10-08 10:48:09,524 ERROR process error. Line: 51. Error message: Element type "unknown-pk-class" must be declared. 2002-10-08 10:48:09,528 ERROR [org.jboss.metadata.XmlFileLoader] jbosscmp-jdbc.xml process error. Line: 57. Error message: Element type "entity-command" must be declared. 2002-10-08 10:48:09,532 ERROR [org.jboss.metadata.XmlFileLoader] XmlFileLoader: File file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/70.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml process error. Line: 58. Error message: Element type "attribute" must be declared. 2002-10-08 10:48:09,537 ERROR [org.jboss.metadata.XmlFileLoader] XmlFileLoader: File file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/70.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml process error. Line: 73. Error message: The content of element type "entity" must match "(ejb-name,(datasource,datasource-mapping)?,create-table?,remove-table?,read-only?,read-time-out?,row-locking?,pk-constraint?,read-ahead?,list-cache-max?,fetch-size?,table-name?,cmp-field*,load-groups?,eager-load-group?,lazy-load-groups?,query*)". 2002-10-08 10:48:09,562 ERROR [org.jboss.ejb.EntityContainer] Starting failed

[mozheyko_d]

jboss-3.2.0beta, HypersonicSQL

<jbosscmp-jdbc>
...................
<cmp-field>
<field-name>code</field-name>
<column-name>code</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</cmp-field>

<unknown-pk>
<unknown-pk-class>java.lang.String</unknown-pk-class>
<column-name>code</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</unknown-pk>

<entity-command name="key-generator" class="org.jboss.ejb.plagins.cmp.jdbc.JDBCKeyGeneratorCreateCommand">
UUIDKeyGeneratorFactory
</entity-command>
...................

</jbosscmp-jdbc>


WHERE IS MY ERROR ?

[aloubyansky]

You don't need to write cmp-field element for code.
code is unknown primary key (check the spec).
unknown-pk and entity-command elements look ok. I assume their parent is .
Also in ejb-jar.xml prim-key-class element must have java.lang.Object and no primkey-field.
ejbCreate method must return java.lang.Object and findByPrimaryKey must take a parameter of type java.lang.Object.

[mozheyko_d]

OK, it's work

what i need to write in ejbCreate

setCode( ????????????);

if i ignore this string generate error:

Try to insert null into not nullable column 'code'

[mozheyko_d]

OK, it's work

what i need to write in ejbCreate

setCode( ????????????);

if i ignore this string generate error:

Try to insert null into not nullable column 'code'

[aloubyansky]

It's unknown primary key column. It means it doesn't appear in cmp fields. It is generated by database/container and doesn't have accessors. No setCode(???), no getCode().

[mozheyko_d]

Hello Alex !
if i not use <cmp-field> tags for "code"
and not use <primkey-field>
jboss throws
Column not found create table the_table( name varchar(60), cast double, CONSTRAINT PRIMARY KEY())

that as I understand means a that it is impossible to create the table without a primary key

[aloubyansky]

If there are
- no methods setCode(String code) and String getCode() in bean's class and interfaces;
- no cmp-field in for code in ejb-jar.xml and jbosscmp-jdbc.xml;
- ejb-jar.xml is missing primkey-field;
- prim-key-class is java.lang.Object;
- ejbCreate returns java.lang.Object;
- findByPrimaryKey takes java.lang.Object as a parameter;

and entity element in jbosscmp-jdbc.xml contains:
<unknown-pk>
<unknown-pk-class>java.lang.String</unknown-pk-class>
<column-name>code</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</unknown-pk>
<entity-command name="key-generator" class="org.jboss.ejb.plagins.cmp.jdbc.JDBCKeyGeneratorCreateCommand">
UUIDKeyGeneratorFactory
</entity-command>

then it should work. If it still doesn't, please, provide a simple test-case.

[mozheyko_d]

Excuse Alex I before wrote <unknown-pk> and <entity-command> in <ejb-jar> instead of in <jbosscmp-jdbc> but after I have written in <jbosscmp-jdbc> arises that

2002-10-11 10:49:00,662 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.UserBean] Initializing CMP plugin for UserBean
2002-10-11 10:49:00,664 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.UserBean] Loading standardjbosscmp-jdbc.xml : file:/home/dm/programs/jboss-3.2.0beta/server/default/conf/standardjbosscmp-jdbc.xml
2002-10-11 10:49:00,757 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.UserBean] jar:file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/75.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml found. Overriding defaults
2002-10-11 10:49:00,841 ERROR [org.jboss.metadata.XmlFileLoader] XmlFileLoader: File file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/75.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml process error. Line: 86. Error message: Element type "unknown-pk" must be declared.
2002-10-11 10:49:00,846 ERROR [org.jboss.metadata.XmlFileLoader] XmlFileLoader: File file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/75.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml process error. Line: 87. Error message: Element type "unknown-pk-class" must be declared.
2002-10-11 10:49:00,853 ERROR [org.jboss.metadata.XmlFileLoader] XmlFileLoader: File file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/75.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml process error. Line: 92. Error message: Element type "entity-command" must be declared.
2002-10-11 10:49:00,861 ERROR [org.jboss.metadata.XmlFileLoader] XmlFileLoader: File file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/75.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml process error. Line: 93. Error message: Element type "attribute" must be declared.
2002-10-11 10:49:00,869 ERROR [org.jboss.metadata.XmlFileLoader] XmlFileLoader: File file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/75.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml process error. Line: 96. Error message: The content of element type "entity" must match "(ejb-name,(datasource,datasource-mapping)?,create-table?,remove-table?,read-only?,read-time-out?,row-locking?,pk-constraint?,read-ahead?,list-cache-max?,fetch-size?,table-name?,cmp-field*,load-groups?,eager-load-group?,lazy-load-groups?,query*)".
2002-10-11 10:49:00,878 ERROR [org.jboss.ejb.EntityContainer] Starting failed
org.jboss.deployment.DeploymentException: Invalid XML: file=file:/home/dm/programs/jboss-3.2.0beta/server/default/tmp/deploy/server/default/deploy/inetsalon.ear/75.inetsalon.ear-contents/inetsalon.jar!/META-INF/jbosscmp-jdbc.xml
at org.jboss.metadata.XmlFileLoader.getDocument(XmlFileLoader.java:262)

and .....