From derby-user-return-7387-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Aug 10 12:16:45 2007 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 70534 invoked from network); 10 Aug 2007 12:16:45 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 10 Aug 2007 12:16:45 -0000 Received: (qmail 21224 invoked by uid 500); 10 Aug 2007 12:16:42 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 21204 invoked by uid 500); 10 Aug 2007 12:16:42 -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 21191 invoked by uid 99); 10 Aug 2007 12:16:42 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 10 Aug 2007 05:16:42 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [192.18.1.36] (HELO gmp-ea-fw-1.sun.com) (192.18.1.36) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 10 Aug 2007 12:16:36 +0000 Received: from d1-emea-10.sun.com (d1-emea-10.sun.com [192.18.2.120]) by gmp-ea-fw-1.sun.com (8.13.6+Sun/8.12.9) with ESMTP id l7ACGEjY029665 for ; Fri, 10 Aug 2007 12:16:14 GMT Received: from conversion-daemon.d1-emea-10.sun.com by d1-emea-10.sun.com (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) id <0JMK00C014MRNQ00@d1-emea-10.sun.com> (original mail from Kristian.Waagan@Sun.COM) for derby-user@db.apache.org; Fri, 10 Aug 2007 13:16:14 +0100 (BST) Received: from [129.159.112.188] by d1-emea-10.sun.com (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPSA id <0JMK0065F4R1SN5K@d1-emea-10.sun.com> for derby-user@db.apache.org; Fri, 10 Aug 2007 13:16:14 +0100 (BST) Date: Fri, 10 Aug 2007 14:16:13 +0200 From: Kristian Waagan Subject: Re: Insert into Table (identity,CLOB) impossible ?? how to ? In-reply-to: <2EBA26CF7C861640AAD5CEB1218D60A1AC9B8E@egwbeex001.esko-graphics.com> Sender: Kristian.Waagan@Sun.COM To: Derby Discussion Message-id: <46BC570D.4040102@Sun.com> Organization: Sun Microsystems Inc. MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7BIT References: <2EBA26CF7C861640AAD5CEB1218D60A1AC9B8E@egwbeex001.esko-graphics.com> User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; no-NO; rv:1.8.1.3) Gecko/20070419 Thunderbird/2.0.0.0 Mnenhy/0.7.5.0 X-Virus-Checked: Checked by ClamAV on apache.org Van Vlierberghe, Steven wrote: > Use case: > MyTable (id identity, data CLOB) > insert some bytes into the table and return the id of the record > > Problem: > 1. I need the generated key (identity), so I cannot use a PreparedStatement > beause with Derby, one can only get the generated key via > Statement.execute (sql, RETURN_GENERATED_KEYS); Hi Steven, That is not correct. You can use a PreparedStatement by using Connection.prepareCall(String sql, int generatedKeys). After you have executed it, you can obtain a resultset with the autogenerated key by using PreparedStatement.getGeneratedKeys() (the method is actually inherited from Statement). Note that Derby is a bit limited regarding auto-generated keys. I don't think you can use the prepare-methods taking an array as input for the keys. Also, not sure about this, but isn't it currently only possible to have one auto-generated key column per table? regards, -- Kristian > > 2. Hence, as a PreparedStatement cannot be used, a Statement must be used > Now, of course, I do not want (or even can) to form a String for an SQL > insert statement in which the CLOB is included . > > 3. Euh, that brings me , in order to insert an CLOB, to create a record > first, get the generated key and then update that record with the clob data > > So, in 2 phases > first: id = DoInsert (); > second: DoUpdate (id,data); > Tried this as well, > if one does: stm.execute ("insert into MyTable") > or stm.execute ("insert into MyTable () values ()") > dont' go : syntax errors > > So, how to insert some bytes into a table with an identity column and > have the identity value as return value ?? > > Any solution welcome > Thanks > > steven > svv@esko.com >