From derby-user-return-14654-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Oct 1 13:54:56 2012 Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 0D343D1E2 for ; Mon, 1 Oct 2012 13:54:56 +0000 (UTC) Received: (qmail 86594 invoked by uid 500); 1 Oct 2012 13:54:55 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 86573 invoked by uid 500); 1 Oct 2012 13:54:55 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 86565 invoked by uid 99); 1 Oct 2012 13:54:55 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 01 Oct 2012 13:54:55 +0000 X-ASF-Spam-Status: No, hits=3.3 required=5.0 tests=FILL_THIS_FORM_FRAUD_PHISH,HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_NEUTRAL,T_FILL_THIS_FORM_SHORT,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: neutral (athena.apache.org: local policy) Received: from [98.139.91.196] (HELO nm9-vm0.bullet.mail.sp2.yahoo.com) (98.139.91.196) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 01 Oct 2012 13:54:49 +0000 Received: from [98.139.91.67] by nm9.bullet.mail.sp2.yahoo.com with NNFMP; 01 Oct 2012 13:54:28 -0000 Received: from [98.139.44.81] by tm7.bullet.mail.sp2.yahoo.com with NNFMP; 01 Oct 2012 13:54:27 -0000 Received: from [127.0.0.1] by omp1018.access.mail.sp2.yahoo.com with NNFMP; 01 Oct 2012 13:54:27 -0000 X-Yahoo-Newman-Id: 941422.88917.bm@omp1018.access.mail.sp2.yahoo.com Received: (qmail 68666 invoked from network); 1 Oct 2012 13:54:27 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=att.net; s=s1024; t=1349099666; bh=lZ/5xcDEiSs8DKH6f7TW18/MqbZnZWhuQF5lTi/T4sQ=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:Reply-To:From:To:References:In-Reply-To:Subject:Date:Organization:Message-ID:MIME-Version:Content-Type:X-Mailer:Content-Language:Thread-Index; b=HSzPQyfEIwIc1vTlx9JgghU0hVk2QyCgg9SQXAE62vskgnPpz7QYj2DPt2TBZHftSSn4o6PDlO6f75KfYlEvHM0jxEoZoEzzI3SOI56/7BDKE4MYTqzljDPumcBbAVjLa9PciWFq5RJo9rVPDzmgN7KkY4Yu7MP1jJT8opHMN4s= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: w3U9hEEVM1lzJVu5duQnJ5ItVRPZahZEewvra_4kakVLmNY EHoLWfCuvjHciTbnSXaO9.KmXsZCUbgheH6RLcEdcMYnpaEuGCnAxkiyxdAM rBjVP8RFvMJYGHqQb9PIQsu43FsKabOSdBTq_Y1.BdeeGcIosSnFW.3NyVGB 80DbGiKJAm_kxEp7AYN9BKDmXrCsDi85ATUrONICSbFAW1xLrQr_dUhumi1U SjtsA3rvvNA8VnBC8vIuQvNJu1IpokIQPWw0mU_wdeA8INA34w37fdFWLO5c XSxU0niW0fCjumq7eP3lDaGZl7esfvZSgVSxPp.9RzLxsgp0zYIgVAUOtOiy M2T_LAV0C2U0x0DNcvUkZmq4U2ofEjjE2rJlN_j3ytBk19fCUEuojRVFgTXm Y4KZClnS_zIXjlxTPszlxICtWtrNBQxEKnhhzbDtUlm62gVWEArrKTacm1A_ Sj7MjlL5P74x5PONPY63fEfdYd3x90VlHUAFw_sa54uNm0223fR.UZoWnZ1s o4HD6QvWmYhu3PMJa4g-- X-Yahoo-SMTP: EWoIHdqswBAurM1L7vJFxs9_vvoZorONjVmVZtmC Received: from KeyLargo (softmoore@155.225.248.251 with login) by smtp104.sbc.mail.ne1.yahoo.com with SMTP; 01 Oct 2012 13:54:26 +0000 UTC Reply-To: From: "John I. Moore, Jr." To: "'Derby Discussion'" References: <944A1108FF717B43A3F9AA883583DDB2025248D7@EVS01.intern.interpay.nl> In-Reply-To: <944A1108FF717B43A3F9AA883583DDB2025248D7@EVS01.intern.interpay.nl> Subject: RE: Using Identity Columns with or without Sequences derby 10.8.1.2 Date: Mon, 1 Oct 2012 09:54:22 -0400 Organization: SoftMoore Consulting Message-ID: <000c01cd9fdc$43af5d00$cb0e1700$@net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000D_01CD9FBA.BC9DBD00" X-Mailer: Microsoft Office Outlook 12.0 Content-Language: en-us Thread-Index: Ac2f2GSUYPTRFQ9AQWqEuefDtzmTJgAAuVsg X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. ------=_NextPart_000_000D_01CD9FBA.BC9DBD00 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_000D_01CD9FBA.BC9DBD00 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D = ConnectionFactory.getInstance();

conn =3D = connFactory.getConnection();

stmt =3D conn.prepareStatement(insertQuery, = PreparedStatement.RETURN_GENERATED_KEYS);

...  // = set prepared statement parameters

stmt.executeUpdate();

 

// get id from the database

ResultSet rs =3D = stmt.getGeneratedKeys();

if = (rs.next())

  {

    int xId =3D = 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"

           = ;            =          = (
           &n= bsp;           &nb= sp;          = INPUTFILE_ID   int generated always = as = identity
         =             &= nbsp;            = ,"NAME"        VARCHAR(512) = NOT = NULL
           = ;            =            = ,"TMPST_IN"    TIMESTAMP NOT NULL DEFAULT = CURRENT_TIMESTAMP
        &nbs= p;            = ;           = );

 

CREATE TABLE = "APP"."OUTPUTFILES"

           = ;            =          (

           = ;            =            = OUTPUTFILE_ID int generated always as = identity
          &= nbsp;           &n= bsp;           = ,INPUTFILE_ID     int
    = ;            =             &= nbsp;     = ,"NAME"        VARCHAR(512) = NOT = NULL
           = ;            =            = ,"TMPST_IN"    TIMESTAMP NOT NULL DEFAULT = CURRENT_TIMESTAMP
        &nbs= p;            = ;           = );

 

 

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");

<= div>

ALTER TABLE = "APP"."OUTPUTFILES" ADD CONSTRAINT = "FK_INPUTFILE_ID" FOREIGN KEY ("INPUTFILE_ID") = REFERENCES "APP"."INPUTFILES" = ("INPUTFILE_ID") ON DELETE CASCADE ON UPDATE = RESTRICT;

---------------<= /o:p>

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

 

------=_NextPart_000_000D_01CD9FBA.BC9DBD00--