From derby-user-return-14481-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed Jul 18 00:23:39 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 40E02D51F for ; Wed, 18 Jul 2012 00:23:39 +0000 (UTC) Received: (qmail 79130 invoked by uid 500); 18 Jul 2012 00:23:38 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 79084 invoked by uid 500); 18 Jul 2012 00:23:38 -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 79076 invoked by uid 99); 18 Jul 2012 00:23:38 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 18 Jul 2012 00:23:38 +0000 X-ASF-Spam-Status: No, hits=-2.8 required=5.0 tests=FSL_RCVD_USER,HTML_MESSAGE,RCVD_IN_DNSWL_HI,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of hauckma@us.ibm.com designates 32.97.110.158 as permitted sender) Received: from [32.97.110.158] (HELO e37.co.us.ibm.com) (32.97.110.158) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 18 Jul 2012 00:23:27 +0000 Received: from /spool/local by e37.co.us.ibm.com with IBM ESMTP SMTP Gateway: Authorized Use Only! Violators will be prosecuted for from ; Tue, 17 Jul 2012 18:23:06 -0600 Received: from d03dlp03.boulder.ibm.com (9.17.202.179) by e37.co.us.ibm.com (192.168.1.137) with IBM ESMTP SMTP Gateway: Authorized Use Only! Violators will be prosecuted; Tue, 17 Jul 2012 18:21:59 -0600 Received: from d03relay02.boulder.ibm.com (d03relay02.boulder.ibm.com [9.17.195.227]) by d03dlp03.boulder.ibm.com (Postfix) with ESMTP id C8A8719D804E for ; Wed, 18 Jul 2012 00:21:56 +0000 (WET) Received: from d03av02.boulder.ibm.com (d03av02.boulder.ibm.com [9.17.195.168]) by d03relay02.boulder.ibm.com (8.13.8/8.13.8/NCO v10.0) with ESMTP id q6I0LwKZ156188 for ; Tue, 17 Jul 2012 18:21:58 -0600 Received: from d03av02.boulder.ibm.com (loopback [127.0.0.1]) by d03av02.boulder.ibm.com (8.14.4/8.13.1/NCO v10.0 AVout) with ESMTP id q6I0Lw13002939 for ; Tue, 17 Jul 2012 18:21:58 -0600 Received: from d03mc113.boulder.ibm.com (d03mc113.boulder.ibm.com [9.63.40.97]) by d03av02.boulder.ibm.com (8.14.4/8.13.1/NCO v10.0 AVin) with ESMTP id q6I0LwoD002632 for ; Tue, 17 Jul 2012 18:21:58 -0600 Subject: Updating Autoincrement values X-KeepSent: A6169D86:471A3D9D-88257A3F:00018EB8; type=4; name=$KeepSent To: derby-user@db.apache.org X-Mailer: Lotus Notes Release 8.5.3FP1 March 07, 2012 Message-ID: From: Matthew Hauck Date: Tue, 17 Jul 2012 17:21:51 -0700 X-MIMETrack: Serialize by Router on D03MC113/03/M/IBM(Release 8.5.3 ZX853HP5|January 12, 2012) at 07/17/2012 18:21:57 MIME-Version: 1.0 Content-type: multipart/alternative; Boundary="0__=07BBF0ACDF9208288f9e8a93df938690918c07BBF0ACDF920828" Content-Disposition: inline X-Content-Scanned: Fidelis XPS MAILER x-cbid: 12071800-7408-0000-0000-000006D8A575 --0__=07BBF0ACDF9208288f9e8a93df938690918c07BBF0ACDF920828 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable I have an old database that I need to import into apache derby. This se= emed to be a pretty easy thing to do, except when I realized that my autogenerated ID column is stuck thinking the next autoincrement value = is 1. i.e. since my import statements from the old database included IDs (which are important for foreign key associations), I do not allow (and= cannot allow) derby to autogenerate them. So, I need to find some way t= o tell derby to update the next autogenerate value to the one more than t= he maximum of the ID in each of my tables. So, I looked up tableid from sys.systables, and matched it with referen= ceid from sys.syscolumns, and was going to change autoincrementvalue to be t= his number, then got the following error: java.sql.SQLSyntaxErrorException:= 'SYS.SYSCOLUMNS' is a system table. Users are not allowed to modify th= e contents of this table. Boo. Is there another way to do this? This seems like a pretty common t= hing to do, but can't seem to find the standard way to do it... -- Matt Hauck= --0__=07BBF0ACDF9208288f9e8a93df938690918c07BBF0ACDF920828 Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable

I have an old database that I n= eed to import into apache derby. This seemed to be a pretty easy thing = to do, except when I realized that my autogenerated ID column is stuck = thinking the next autoincrement value is 1. i.e. since my import statem= ents from the old database included IDs (which are important for foreig= n key associations), I do not allow (and cannot allow) derby to autogen= erate them. So, I need to find some way to tell derby to update the nex= t autogenerate value to the one more than the maximum of the ID in each= of my tables.

So, I looked up tableid from sys.s= ystables, and matched it with referenceid from sys.syscolumns, and was = going to change autoincrementvalue to be this number, then got the foll= owing error: java.sql.SQLSyntaxErrorException: 'SYS.SYSCOLUMNS' is a sy= stem table.  Users are not allowed to modify the contents of this = table.

Boo. Is there another way to do th= is? This seems like a pretty common thing to do, but can't seem to find= the standard way to do it...

--
Matt Hauck= --0__=07BBF0ACDF9208288f9e8a93df938690918c07BBF0ACDF920828--