db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John I. Moore, Jr." <softmo...@att.net>
Subject RE: Using Identity Columns with or without Sequences derby 10.8.1.2
Date Mon, 01 Oct 2012 13:54:22 GMT
Matt,
 
I use generated IDs a lot in my applications.  My implementation looks something like the
following:
 
create table X
  (
    x_id int generated always as identity (start with 1000),
    ...
 
    constraint x_pk primary key (x_id)
  );
 
In order to get access to the generated ID, my code looks something like the following:
 
ConnectionFactory connFactory = ConnectionFactory.getInstance();
conn = connFactory.getConnection();
stmt = conn.prepareStatement(insertQuery, PreparedStatement.RETURN_GENERATED_KEYS);
...  // set prepared statement parameters
stmt.executeUpdate();
 
// get id from the database
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next())
  { 
    int xId = rs.getInt(1);
    ...
  }
 
_________________________________________
 
John I. Moore, Jr.
SoftMoore Consulting
 
email:  jmoore@softmoore.com
web:    www.softmoore.com
cell:   843-906-7887
 
From: Malte.Kempff@de.equens.com [mailto:Malte.Kempff@de.equens.com] 
Sent: Monday, October 01, 2012 9:27 AM
To: derby-user@db.apache.org
Subject: Using Identity Columns with or without Sequences derby 10.8.1.2
 
Hi,
I'd like to use an identity-column And I am not quite sure how to receive the used number,
if I used
default.
Actually I need the particular identity-value for the entries of other tables, since they
are
supposed to reference it.
 
Am I forced to give those identities for my own like with a sequence-number, or can I somwhow
get it
by some "magic" statement?
--------------------------------
here my particular case:
CREATE TABLE "APP"."INPUTFILES" 
                                (
                                  INPUTFILE_ID   int generated always as identity
                                  ,"NAME"        VARCHAR(512) NOT NULL
                                  ,"TMPST_IN"    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                                );
 
CREATE TABLE "APP"."OUTPUTFILES"
                                (
                                  OUTPUTFILE_ID int generated always as identity
                                  ,INPUTFILE_ID     int
                                  ,"NAME"        VARCHAR(512) NOT NULL
                                  ,"TMPST_IN"    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                                );
 
 
ALTER TABLE "APP"."INPUTFILES" ADD CONSTRAINT "PK_INPUTFILE_ID" PRIMARY KEY ("INPUTFILE_ID");
 
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "PK_OUTPUTFILE_ID" PRIMARY KEY ("OUTPUTFILE_ID");
ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "FK_INPUTFILE_ID" FOREIGN KEY ("INPUTFILE_ID")
REFERENCES "APP"."INPUTFILES" ("INPUTFILE_ID") ON DELETE CASCADE ON UPDATE RESTRICT;
---------------
Am I right that I need to use generated by default as identity instead of int generated always
as
identity 
And that there is no way to get the identity value if generated by derby?
Malte Kempff
 

Mime
View raw message