Derby autogenereted keys

[Thoralf Rickert <th...@cadooz.de>]

Hi,

I couldn't find any questions/answers nor a tutorial about my current problem, so I'm not sure, if this is a user bug or a real problem. But I hope, you have an answer for me.

I want to use Derby 10.4 and Torque 3.3 to implement an embedded database into a small GUI application. I'm new to Derby so that I'm not sure, where my problem really is.

I've defined a table:

<table name="report">
  <column name="id" required="true" type="BIGINT" primaryKey="true" autoIncrement="true"/>
  <column name="filename" required="true" type="VARCHAR" size="1024"/>

  <unique>
    <unique-column name="filename"/>
  </unique>
</table>
The important point is the "autoIncrement". The generated SQL code does'nt contain any informations about this.

CREATE TABLE report
(
    id BIGINT NOT NULL,
    filename VARCHAR(1024) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE (filename)
);
This means - no autogenerated keys. I don't want to use IDBroker if the database is able to generate the keys. Actually I found out, that Derby is able to generate autogenerated keys with "GENERATED ALWASY AS IDENTITY". But Torque seems not not use this feature. When I run this SQL statement manually...

CREATE TABLE report
(
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
    filename VARCHAR(1024) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE (filename)
);
The table is generated as I wanted. But then the next problem is, that I cannot INSERT any values via Torque because it tries to save/set an ID value. I'll get an SQLSyntaxException "Attempt to modify an identity column 'ID'" from Derby.

I could define a default value "DEFAULT" for the primary key as Derby wanted and use GENERATED BY DEFAULT but I cannot setup a default String value "default" for a long field (compile error).

Any ideas?


Thanks
Thoralf

AW: Derby autogenereted keys

[Thoralf Rickert <th...@cadooz.de>]

Oh, I didn't checked that. I only added the idMethod attribute to <database>, because I need it in every table.


> -----Ursprüngliche Nachricht-----
> Von: Thomas Vandahl [mailto:tv@apache.org]
> Gesendet: Sonntag, 25. Mai 2008 13:16
> An: Apache Torque Users List
> Betreff: Re: Derby autogenereted keys
>
> Thoralf Rickert wrote:
> > I've defined a table:
> >
> > <table name="report">
> >   <column name="id" required="true" type="BIGINT" primaryKey="true"
> > autoIncrement="true"/>
>
> Does <table name="report" idMethod="native"> change something?
>
> Bye, Thomas.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>

Re: Derby autogenereted keys

[Thomas Vandahl <tv...@apache.org>]

Thoralf Rickert wrote:
> I've defined a table:
>  
> <table name="report">
>   <column name="id" required="true" type="BIGINT" primaryKey="true" 
> autoIncrement="true"/>

Does <table name="report" idMethod="native"> change something?

Bye, Thomas.


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

RE: Derby autogenereted keys

[Greg Monroe <Gr...@DukeCE.com>]

Just add the attribute:

 

    defaultIdMethod="native"

 

to the <database ...> tag.  This will create the SQL needed for
autoincrement fields.

 

Greg

 

 

From: Thoralf Rickert [mailto:thoralf.rickert@cadooz.de] 
Sent: Thursday, May 22, 2008 9:35 AM
To: 'Apache Torque Users List'
Subject: Derby autogenereted keys

 

Hi,

 

I couldn't find any questions/answers nor a tutorial about my current
problem, so I'm not sure, if this is a user bug or a real problem. But I
hope, you have an answer for me.

 

I want to use Derby 10.4 and Torque 3.3 to implement an embedded
database into a small GUI application. I'm new to Derby so that I'm not
sure, where my problem really is.

 

I've defined a table:

 

<table name="report">
  <column name="id" required="true" type="BIGINT" primaryKey="true"
autoIncrement="true"/>
  <column name="filename" required="true" type="VARCHAR" size="1024"/>
    
  <unique>
    <unique-column name="filename"/>
  </unique>
</table>

The important point is the "autoIncrement". The generated SQL code
does'nt contain any informations about this.

 

CREATE TABLE report
(
    id BIGINT NOT NULL,
    filename VARCHAR(1024) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE (filename)

);

This means - no autogenerated keys. I don't want to use IDBroker if the
database is able to generate the keys. Actually I found out, that Derby
is able to generate autogenerated keys with "GENERATED ALWASY AS
IDENTITY". But Torque seems not not use this feature. When I run this
SQL statement manually...

 

CREATE TABLE report
(
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
    filename VARCHAR(1024) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE (filename)

);

The table is generated as I wanted. But then the next problem is, that I
cannot INSERT any values via Torque because it tries to save/set an ID
value. I'll get an SQLSyntaxException "Attempt to modify an identity
column 'ID'" from Derby.

 

I could define a default value "DEFAULT" for the primary key as Derby
wanted and use GENERATED BY DEFAULT but I cannot setup a default String
value "default" for a long field (compile error).

 

Any ideas?

 

 

Thanks

Thoralf

 



DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.