Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 67815 invoked from network); 30 Mar 2007 17:30:42 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 30 Mar 2007 17:30:42 -0000 Received: (qmail 64644 invoked by uid 500); 30 Mar 2007 17:30:05 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 64620 invoked by uid 500); 30 Mar 2007 17:30:05 -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 64586 invoked by uid 99); 30 Mar 2007 17:30:05 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 30 Mar 2007 10:30:05 -0700 X-ASF-Spam-Status: No, hits=0.6 required=10.0 tests=UPPERCASE_50_75 X-Spam-Check-By: apache.org Received-SPF: neutral (herse.apache.org: local policy) Received: from [32.97.182.144] (HELO e4.ny.us.ibm.com) (32.97.182.144) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 30 Mar 2007 10:29:54 -0700 Received: from d01relay04.pok.ibm.com (d01relay04.pok.ibm.com [9.56.227.236]) by e4.ny.us.ibm.com (8.13.8/8.13.8) with ESMTP id l2UHTTVf023971 for ; Fri, 30 Mar 2007 13:29:29 -0400 Received: from d01av03.pok.ibm.com (d01av03.pok.ibm.com [9.56.224.217]) by d01relay04.pok.ibm.com (8.13.8/8.13.8/NCO v8.3) with ESMTP id l2UHTTeK222702 for ; Fri, 30 Mar 2007 13:29:29 -0400 Received: from d01av03.pok.ibm.com (loopback [127.0.0.1]) by d01av03.pok.ibm.com (8.12.11.20060308/8.13.3) with ESMTP id l2UHTTmp017590 for ; Fri, 30 Mar 2007 13:29:29 -0400 Received: from [127.0.0.1] (sig-9-76-215-127.mts.ibm.com [9.76.215.127]) by d01av03.pok.ibm.com (8.12.11.20060308/8.12.11) with ESMTP id l2UHTR0B017522 for ; Fri, 30 Mar 2007 13:29:28 -0400 Message-ID: <460D48DE.6010609@sbcglobal.net> Date: Fri, 30 Mar 2007 10:29:02 -0700 From: Mike Matrigali Reply-To: mikem_app@sbcglobal.net User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Derby Discussion Subject: Re: Alter Table ADD Column throws column number exception References: <2526D42D65F597428BFF422D6AB79E9E0108FD33@NA1000EXM01.na.ds.monsanto.com> In-Reply-To: <2526D42D65F597428BFF422D6AB79E9E0108FD33@NA1000EXM01.na.ds.monsanto.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org BALDWIN, ALAN J [AG-Contractor/1000] wrote: > Thanks, Mike for the reply. > > I don't think my client will allow me to mail out their database. It > has proprietary data in it. However, I pasted the DDL below. > > This particular alter table statement fails all the time. It started > out more complicated (timestamp NOT NULL, default value, etc...), but I > simplified it to narrow down the problem. > > We have 1100+ of these databases deployed to different clients via > webstart, and we've never seen this problem before. I'm fairly > confident the DDL is fine. > > As a side note, when I execute the same add column statement on the > LicenseStatus table, it tells me the column already exists, when in > fact, it does not. I was just doing that to experiment. I would probably run a consistency check on all the tables in db, it sounds like there are problems with multiple tables. Unfortunately the consistency check does a better job of checking data between user tables and indexes than on system catalog issues. > > > ALTER TABLE orderdetail > ADD COLUMN blah > varchar(15) > > Gives: > java.sql.SQLException: In a base table there was a mismatch between the > requested column number 16 and the maximum number of columns 20. > > ----------------------------------------------- > > ALTER TABLE licensestatus > ADD COLUMN blah > varchar(15) > > Gives: > java.sql.SQLException: Column 'BLAH' already exists in Table/View > 'LICENSESTATUS'. > > -------------------------------------------------------- > > ALTER TABLE farmmanager > ADD COLUMN blah > varchar(15) > > Gives: > Run successful > > > I'm executing these queries in Cloudscape Workbench, but the result is > the same if I execute them in java code. > > Thanks, > -Alan- > > > Here is the DDL: > ========================================== > > create table AFFILIATETYPE ( > AFFILIATETYPEID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > AFFILIATETYPENAME VARCHAR(10) not null, > constraint PK_AFFILIATETYPE primary key (AFFILIATETYPEID) > ); > > create table BASISTYPE ( > BASISTYPEID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > BASISTYPENAME VARCHAR(20) not null, > constraint PK_BASISTYPE primary key (BASISTYPEID) > ); > > create table CONTACT ( > CONTACTID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > LASTNAME VARCHAR(40), > FIRSTNAME VARCHAR(40), > BUSINESSNAME VARCHAR(50), > DESCRIPTION VARCHAR(80), > DIRECTIONS VARCHAR(256), > MIDDLEINITIAL VARCHAR(1), > STREETADDRESS VARCHAR(80) not null, > CITY VARCHAR(40) not null, > ZIPCODE VARCHAR(5) not null, > ZIPCODEEXTENSION VARCHAR(4), > HOMEPHONE VARCHAR(10), > OFFICEPHONE VARCHAR(10), > CELLPHONE VARCHAR(10), > FAX VARCHAR(10), > EMAILADDRESS VARCHAR(80), > COUNTY VARCHAR(30) not null, > STATECODE VARCHAR(2) not null, > constraint PK_CONTACT primary key (CONTACTID) > ); > > create table CROP ( > CROPID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > CROPNAME VARCHAR(20) not null, > constraint PK_CROP primary key (CROPID) > ); > > create table CUSTOMER ( > CUSTOMERID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > BUSINESSNAME VARCHAR(50) not null, > CUSTOMERCONTACTID INTEGER not null, > SPOUSENAME VARCHAR(80), > CUSTOMERNUMBER INTEGER, > IRDACCOUNTID VARCHAR(40), > ISAFFILIATEDEALER CHAR(1) not null, > ONMAILINGLIST CHAR(1) not null, > ISACTIVE CHAR(1) not null, > COMMENTS VARCHAR(256), > PAYTOMESSAGE VARCHAR(256), > MAILTOMESSAGE VARCHAR(128), > OTHERMESSAGE VARCHAR(128), > ISREPORTINGDEALER CHAR(1) not null, > HIDEBALANCEMESSAGE CHAR(1) not null, > PRICECOUNTYID INTEGER not null, > LICENSESTATUSID INTEGER not null, > DEALERID VARCHAR(40), > constraint PK_CUSTOMER primary key (CUSTOMERID) > ); > > create table CUSTOMERDELIVERYCONTACT ( > CUSTOMERID INTEGER not null, > CONTACTID INTEGER not null, > constraint PK_CUSTOMERDELIVERYCONTACT primary key (CUSTOMERID, > CONTACTID) > ); > > create table CUSTOMERDISCOUNTGROUP ( > DISCOUNTGROUPID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > CUSTOMERID INTEGER not null, > constraint PK_CUSTOMERDISCOUNTGROUP primary key (DISCOUNTGROUPID, > CUSTOMERID) > ); > > create table DATAEXCHANGELOG ( > EXCHANGETYPERESOURCEID VARCHAR(30) not null, > EXCHANGETIME TIMESTAMP not null, > constraint PK_DATAEXCHANGELOG primary key (EXCHANGETYPERESOURCEID) > ); > > create table DATAEXCHANGETYPE ( > EXCHANGETYPERESOURCEID VARCHAR(30) not null, > EXCHANGETYPENAME VARCHAR(40) not null, > constraint PK_DATAEXCHANGETYPE primary key (EXCHANGETYPERESOURCEID) > ); > > create table DEALER ( > IRDACCOUNTID VARCHAR(40) not null, > DEALERNAME VARCHAR(40), > SAPDEALERID VARCHAR(50) not null, > TERRITORYNUMBER VARCHAR(20) not null, > USEAFFILIATEDNAME CHAR(1) not null, > CANEDITDELIVERABLES CHAR(1) not null, > ICNUMBER INTEGER not null, > ISAFFILIATEDEALER CHAR(1) not null, > PAYTOMESSAGE VARCHAR(80) not null, > MAILTOMESSAGE VARCHAR(80) not null, > OTHERMESSAGE VARCHAR(80), > PAYMENTDUEDATE TIMESTAMP, > SALESORDERSDEFTOWHOLESALE CHAR(1) not null, > SHOWAFFILIATEASSHIPPER CHAR(1) not null, > EBID VARCHAR(40) not null, > SORTORDERID INTEGER not null, > DEALERCONTACTID INTEGER, > TECHSUPPORTCONTACTID INTEGER, > PRICECOUNTYID INTEGER, > AFFILIATETYPEID INTEGER, > MBSDIRDACCOUNTID VARCHAR(40), > constraint PK_DEALER primary key (IRDACCOUNTID) > ); > > create table DEALERINVENTORY ( > PRODUCTUPC VARCHAR(40) not null, > DEALERID VARCHAR(40) not null, > SEEDYEAR INTEGER not null, > REMAININGALLOCATION FLOAT, > DIRESTSTOCKORDER FLOAT, > REMAININGSTOCKORDER FLOAT, > SALESREPESTIMATEDALLOCATION FLOAT, > BEGINNINGBALANCE FLOAT, > CURRENTBALANCE FLOAT, > TRANSFERONORDER FLOAT, > constraint PK_DEALERINVENTORY primary key (PRODUCTUPC, SEEDYEAR, > DEALERID) > ); > > create table DEALERSERVICES ( > DEALERID VARCHAR(40) not null, > SERVICENUMBER INTEGER not null, > SERVICEDESCRIPTION VARCHAR(80) not null, > COMMENTS VARCHAR(80) not null, > constraint PK_DEALERSERVICES primary key (DEALERID, SERVICENUMBER) > ); > > create table DEALERTRANSACTION ( > TRANSACTIONID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > TRANSACTIONDATE TIMESTAMP not null, > TRANSACTIONTOFROM VARCHAR(80), > SEEDYEAR INTEGER not null, > PONUMBER VARCHAR(20), > PLANTCODE VARCHAR(20), > PLANTNAME VARCHAR(80), > SOLDTOID VARCHAR(20), > SOLDTOIDTYPE VARCHAR(20), > SOLDTONAME VARCHAR(80), > SHIPTOID VARCHAR(20), > SHIPTOIDTYPE VARCHAR(20), > SHIPTONAME VARCHAR(80), > SHIPFROMNAME VARCHAR(80), > BILLOFLADINGNUMBER INTEGER, > TRANSACTIONNUMBER VARCHAR(20) not null, > constraint PK_DEALERTRANSACTION primary key (TRANSACTIONID) > ); > > create table DELIVERYNOTIFICATION ( > INVENTORYTRANSACTIONID INTEGER not null, > ITEMNUMBER INTEGER not null, > LOTNUMBER VARCHAR(20), > QUANTITY FLOAT not null, > SEEDSIZE VARCHAR(20), > PRODUCTSAPID INTEGER, > SAPQUANTITY FLOAT, > SAPUNITOFMEASURE VARCHAR(20), > USERDATE TIMESTAMP, > LOCATION VARCHAR(80), > DAMAGEDQUANTITY FLOAT, > DELIVERYCOMMENT VARCHAR(80), > ISVOIDED CHAR(1) default 'N' not > null, > AFFILIATEIRDACCOUNTID VARCHAR(40), > PRODUCTUPC VARCHAR(40) not null, > SEEDYEAR INTEGER not null, > TRANSACTIONTYPEID INTEGER not null, > STATUSID INTEGER not null, > constraint PK_DELIVERYNOTIFICATION primary key > (INVENTORYTRANSACTIONID, ITEMNUMBER) > ); > > create table DELIVERYNOTIFICATIONSTATUS ( > STATUSID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > STATUSNAME VARCHAR(20) not null, > constraint PK_DELIVERYNOTIFICATIONSTATUS primary key (STATUSID) > ); > > create table DISCOUNT ( > DISCOUNTID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > DESCRIPTION VARCHAR(80) not null, > AMOUNT FLOAT not null, > DEFAULTONNEWINVOICE CHAR(1) not null, > DISCOUNTTYPEID INTEGER not null, > DISCOUNTCATEGORYID INTEGER not null, > BASISTYPEID INTEGER, > LASTEARLYPAYMENTDATE TIMESTAMP, > MINPURCHASEFORDISCOUNT FLOAT, > DISCOUNTCROPID INTEGER not null, > SEEDYEAR INTEGER not null, > ISACTIVE CHAR(1) default 'Y' not > null, > constraint PK_DISCOUNT primary key (DISCOUNTID) > ); > > create table DISCOUNTCATEGORY ( > DISCOUNTCATEGORYID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > DISCOUNTCATEGORYNAME VARCHAR(20) not null, > constraint PK_DISCOUNTCATEGORY primary key (DISCOUNTCATEGORYID) > ); > > create table DISCOUNTGROUP ( > DISCOUNTGROUPID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > DISCOUNTGROUPNAME VARCHAR(40) not null, > constraint PK_DISCOUNTGROUP primary key (DISCOUNTGROUPID) > ); > > create table DISCOUNTTYPE ( > DISCOUNTTYPEID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > DISCOUNTTYPENAME VARCHAR(20) not null, > constraint PK_DISCOUNTTYPE primary key (DISCOUNTTYPEID) > ); > > create table FARMMANAGER ( > FARMMANAGERID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > IRDACCOUNTID VARCHAR(40) not null, > STATECODE VARCHAR(2), > FARMMANAGERNAME VARCHAR(80) not null, > ADDRESS VARCHAR(80) not null, > CITY VARCHAR(20) not null, > ZIPCODE VARCHAR(5) not null, > ZIPCODEEXTENSION VARCHAR(4), > SAPID INTEGER not null, > ISACTIVE CHAR(1) not null, > ISPREPAY CHAR(1) not null, > constraint PK_FARMMANAGER primary key (FARMMANAGERID) > ); > > create table LICENSESTATUS ( > LICENSESTATUSID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > LICENSESTATUSNAME VARCHAR(30) not null, > constraint PK_LICENSESTATUS primary key (LICENSESTATUSID) > ); > > create table ORDERDETAIL ( > ORDERITEMNUMBER INTEGER not null, > ORDERID INTEGER not null, > PRODUCTUPC VARCHAR(40) not null, > SEEDYEAR INTEGER not null, > LOTNUMBER VARCHAR(40), > COMMENTS VARCHAR(80), > QUANTITY FLOAT not null, > UNITPRICE FLOAT not null, > DISCOUNTID INTEGER, > ISVOIDED CHAR(1) default 'N' not > null, > EXPECTEDDELIVERYDATE TIMESTAMP, > ORDERLINETYPEID INTEGER not null, > PROCESSORID INTEGER, > FIELDLOCATION VARCHAR(256), > ACRES FLOAT, > PROCESSORCONTRACTNBR VARCHAR(20), > CreatedDate TIMESTAMP NOT NULL default > timestamp(current_timestamp), > LastModifiedDate TIMESTAMP NOT NULL default > timestamp(current_timestamp), > LATITUDE FLOAT, > LONGITUDE FLOAT, > constraint PK_ORDERDETAIL primary key (ORDERITEMNUMBER, ORDERID) > ); > > create table ORDERDISCOUNT ( > DISCOUNTID INTEGER not null, > ORDERID INTEGER not null, > constraint PK_ORDERDISCOUNT primary key (DISCOUNTID, ORDERID) > ); > > create table ORDERLINEDELIVERY ( > ORDERITEMNUMBER INTEGER not null, > ORDERID INTEGER not null, > DELIVERYNUMBER INTEGER not null, > DELIVERYDATE TIMESTAMP not null, > DELIVERYQUANTITY FLOAT not null, > AFFILIATEIRDACCOUNTID VARCHAR(40), > constraint PK_ORDERLINEDELIVERY primary key (ORDERITEMNUMBER, > ORDERID, DELIVERYNUMBER) > ); > > create table ORDERLINETYPE ( > ORDERLINETYPEID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > ORDERLINETYPENAME VARCHAR(20) not null, > constraint PK_ORDERLINETYPE primary key (ORDERLINETYPEID) > ); > > create table ORDERPAYMENT ( > ORDERID INTEGER not null, > PAYMENTID INTEGER not null, > AMOUNTAPPLIED FLOAT not null, > constraint PK_ORDERPAYMENT primary key (ORDERID, PAYMENTID) > ); > > create table ORDERSHARE ( > CUSTOMERID INTEGER not null, > ORDERID INTEGER not null, > SHAREFRACTION FLOAT not null, > ISMAINCUSTOMER CHAR(1) not null, > constraint PK_ORDERSHARE primary key (CUSTOMERID, ORDERID) > ); > > create table PAYMENT ( > PAYMENTID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > CUSTOMERID INTEGER not null, > PAYMENTDATE TIMESTAMP not null, > TOTALPAYMENTAMOUNT FLOAT not null, > CHECKNUMBER VARCHAR(20) not null, > SEEDYEAR INTEGER not null, > COMMENTS VARCHAR(80), > constraint PK_PAYMENT primary key (PAYMENTID) > ); > > create table POSQUEUE ( > ORDERITEMNUMBER INTEGER not null, > ORDERID INTEGER not null, > EXCHANGETYPERESOURCEID VARCHAR(30) not null, > constraint PK_POSQUEUE primary key (ORDERITEMNUMBER, ORDERID, > EXCHANGETYPERESOURCEID) > ); > > create table POSTRANSACTIONHISTORY ( > TRANSACTIONKEY VARCHAR(12) not null, > EXCHANGETYPERESOURCEID VARCHAR(30) not null, > PRODUCTMOVEMENTTYPE VARCHAR(20) not null, > TRANSACTIONXML BLOB not null, > constraint PK_POSTRANSACTIONHISTORY primary key (TRANSACTIONKEY, > EXCHANGETYPERESOURCEID, PRODUCTMOVEMENTTYPE) > ); > > create table PRICE ( > PRODUCTUPC VARCHAR(40) not null, > PRICEAREANAME VARCHAR(10) not null, > SEEDYEAR INTEGER not null, > LISTPRICE FLOAT not null, > UNITPRICE FLOAT not null, > PRICEMODIFIEDDATE TIMESTAMP not null, > TECHROYALTYPRICE FLOAT, > constraint PK_PRICE primary key (PRODUCTUPC, PRICEAREANAME, SEEDYEAR) > ); > > create table PRICEAREA ( > PRICEAREANAME VARCHAR(10) not null, > DESCRIPTION VARCHAR(20) not null, > constraint PK_PRICEAREA primary key (PRICEAREANAME) > ); > > create table PRICECOUNTY ( > PRICECOUNTYID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > STATECODE VARCHAR(2) not null, > PRICEAREANAME VARCHAR(10) not null, > COUNTYNAME VARCHAR(30) not null, > constraint PK_PRICECOUNTY primary key (PRICECOUNTYID) > ); > > create table PRICETEMP ( > PRODUCTUPC VARCHAR(40) not null, > PRICEAREANAME VARCHAR(10) not null, > SEEDYEAR INTEGER not null, > LISTPRICE FLOAT, > UNITPRICE FLOAT, > PRICEMODIFIEDDATE TIMESTAMP, > TECHROYALTYPRICE FLOAT, > constraint PK_PRICETEMP primary key (PRODUCTUPC, PRICEAREANAME, > SEEDYEAR) > ); > > create table PRODUCT ( > PRODUCTUPC VARCHAR(40) not null, > SEEDYEAR INTEGER not null, > PRODUCTNAME VARCHAR(80), > ISBIOTECH CHAR(1) not null, > VARIETY VARCHAR(80) not null, > TRAIT VARCHAR(80) not null, > PRODUCTMODIFIEDDATE TIMESTAMP, > UNITOFMEASUREID INTEGER not null, > CROPID INTEGER not null, > BRANDNAME VARCHAR(40), > ISINSECTRESISTANTMATERIAL CHAR(1) not null, > NEEDSFIELDCOORDINATES CHAR(1) default 'N' not > null, > ISACTIVE CHAR(1) default 'Y' not > null, > PROCESSORCODE VARCHAR(10), > constraint PK_PRODUCT primary key (PRODUCTUPC, SEEDYEAR) > ); > > create table PRODUCTBRANDS ( > BRANDNAME VARCHAR(40) not null, > constraint PK_PRODUCTBRANDS primary key (BRANDNAME) > ); > > create table PRODUCTORDER ( > ORDERID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > INVOICEDATE TIMESTAMP, > CREATEDDATE TIMESTAMP not null, > LASTPRINTEDDATE TIMESTAMP, > LASTMODIFIEDDATE TIMESTAMP, > DESCRIPTION VARCHAR(80), > FARMMANAGERID INTEGER, > DELIVERYCUSTOMERID INTEGER, > DELIVERYCONTACTID INTEGER, > SELLERIRDACCOUNTID VARCHAR(40), > ISQUOTE CHAR(1) default 'N' not > null, > ISFARMFLEX CHAR(1) default 'N' not > null, > ISFLEXPLUS CHAR(1) default 'N' not > null, > ISWHOLESALE CHAR(1) default 'N' not > null, > ISDIRECTSHIP CHAR(1) not null, > USEDISCOUNTEDAMOUNT CHAR(1) not null, > NOTES VARCHAR(80), > ISFPOSSELECTED CHAR(1) not null, > ISVOIDED CHAR(1) default 'N' not > null, > FPOSTRANSMITDATE TIMESTAMP, > SEEDYEAR INTEGER not null, > SHOWDISCOUNTMESSAGE CHAR(1) not null, > CALCULATENETAVGCOST CHAR(1) not null, > FPOSAMOUNTDUE FLOAT, > constraint PK_PRODUCTORDER primary key (ORDERID) > ); > > create table RECONCILIATIONQUEUE ( > UNFINISHEDINVOICES BLOB not null > ); > > create table SEEDPROCESSOR ( > PROCESSORID INTEGER not null, > PROCESSORNAME VARCHAR(50) not null, > ISACTIVE CHAR(1) default 'Y' not > null, > constraint PK_SEEDPROCESSOR primary key (PROCESSORID) > ); > > create table SEEDPROCESSORROLE ( > SEEDPROCESSORROLEID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > PROCESSORID INTEGER not null, > PROCESSORCODE VARCHAR(10) not null, > ISACTIVE CHAR(1) default 'Y' not > null, > constraint PK_SEEDPROCESSORROLE primary key (SEEDPROCESSORROLEID), > constraint AK_SEEDPROCESSORROLE unique (PROCESSORID, PROCESSORCODE) > ); > > create table SELLER ( > SELLERID VARCHAR(40) not null, > SELLERNAME VARCHAR(80) not null, > ISACTIVE CHAR(1) not null, > constraint PK_SELLER primary key (SELLERID) > ); > > create table SORTORDER ( > SORTORDERID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > SORTORDERNAME VARCHAR(20) not null, > constraint PK_SORTORDER primary key (SORTORDERID) > ); > > create table STATE ( > STATECODE VARCHAR(2) not null, > ISCOUNTYREQUIREDFORPRICEAREA CHAR(1) not > null, > STATENAME VARCHAR(20) not null, > ISLANDDESCRREQUIREDFORRR CHAR(1) not null, > constraint PK_STATE primary key (STATECODE) > ); > > create table SUPPORTEDBRANDS ( > DEALERID VARCHAR(40) not null, > BRANDNAME VARCHAR(40) not null, > constraint PK_SUPPORTEDBRANDS primary key (DEALERID, BRANDNAME) > ); > > create table SYSTEMPARAMS ( > DEALERID VARCHAR(40) not null, > EOSLASTRUN INTEGER not null, > USEDISCOUNTEDUNITPRICE CHAR(1) not null, > RELEASEVERSION VARCHAR(20) not null, > LASTCONNECTION TIMESTAMP, > DISABLEAUTORECALCULATE CHAR(1) not null, > CURRENTSEEDYEAR INTEGER not null, > PRINTDUEDATEONINVOICE CHAR(1) not null, > INCLUDENETAVERAGECOST CHAR(1) not null, > DXOVERRIDECODE VARCHAR(10), > BALANCEDUETHRESHOLD FLOAT default 0 not > null, > LASTHANDHELDEXPORT TIMESTAMP, > HANDHELDIMPORTINPROGRESS CHAR(1) not null, > HASEXPORTED CHAR(1) default 'N' NOT > NULL, > constraint PK_SYSTEMPARAMS primary key (DEALERID) > ); > > create table TRANSACTIONTYPE ( > TRANSACTIONTYPEID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > TRANSACTIONTYPENAME VARCHAR(20) not null, > constraint PK_TRANSACTIONTYPE primary key (TRANSACTIONTYPEID) > ); > > create table UNITOFMEASURE ( > UNITOFMEASUREID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, > UNITOFMEASURECODE VARCHAR(2) not null, > UNITOFMEASURENAME VARCHAR(20) not null, > XMLSTANDARDUNITOFMEASURE VARCHAR(10) not null, > REPORTINGUNITOFMEASURE VARCHAR(20) not null, > constraint PK_UNITOFMEASURE primary key (UNITOFMEASUREID) > ); > > alter table CONTACT > add constraint FK_CONTACT_STATE foreign key (STATECODE) > references STATE (STATECODE); > > alter table CUSTOMER > add constraint FK_CUSTOMER_CONTACT foreign key (CUSTOMERCONTACTID) > references CONTACT (CONTACTID); > > alter table CUSTOMER > add constraint FK_CUSTOMER_DEALER foreign key (DEALERID) > references DEALER (IRDACCOUNTID); > > alter table CUSTOMER > add constraint FK_CUSTOMER_LICENSESTATUS foreign key > (LICENSESTATUSID) > references LICENSESTATUS (LICENSESTATUSID); > > alter table CUSTOMER > add constraint FK_CUSTOMER_PRICECOUNTY foreign key (PRICECOUNTYID) > references PRICECOUNTY (PRICECOUNTYID); > > alter table CUSTOMERDELIVERYCONTACT > add constraint FK_CUSTDELIVCONTACT_CONTACT foreign key (CONTACTID) > references CONTACT (CONTACTID); > > alter table CUSTOMERDELIVERYCONTACT > add constraint FK_CUSTDELIVCONTACT_CUSTOMER foreign key (CUSTOMERID) > references CUSTOMER (CUSTOMERID); > > alter table CUSTOMERDISCOUNTGROUP > add constraint FK_CUSTDISCGROUP_CUSTOMER foreign key (CUSTOMERID) > references CUSTOMER (CUSTOMERID); > > alter table CUSTOMERDISCOUNTGROUP > add constraint FK_CUSTDISCGROUP_DISCGROUP foreign key > (DISCOUNTGROUPID) > references DISCOUNTGROUP (DISCOUNTGROUPID); > > alter table DATAEXCHANGELOG > add constraint FK_DATAEXCHLOG_DATAEXCHTYPE foreign key > (EXCHANGETYPERESOURCEID) > references DATAEXCHANGETYPE (EXCHANGETYPERESOURCEID); > > alter table DEALER > add constraint FK_DEALER_AFFILIATETYPE foreign key (AFFILIATETYPEID) > references AFFILIATETYPE (AFFILIATETYPEID); > > alter table DEALER > add constraint FK_DEALER_CONTACT_DEALER foreign key (DEALERCONTACTID) > references CONTACT (CONTACTID); > > alter table DEALER > add constraint FK_DEALER_CONTACT_TECHSUPPORT foreign key > (TECHSUPPORTCONTACTID) > references CONTACT (CONTACTID); > > alter table DEALER > add constraint FK_DEALER_PRICECOUNTY foreign key (PRICECOUNTYID) > references PRICECOUNTY (PRICECOUNTYID); > > alter table DEALER > add constraint FK_DEALER_SORTORDER foreign key (SORTORDERID) > references SORTORDER (SORTORDERID); > > alter table DEALERINVENTORY > add constraint FK_DEALERINVENTORY_DEALER foreign key (DEALERID) > references DEALER (IRDACCOUNTID); > > alter table DEALERINVENTORY > add constraint FK_DEALERINVENTORY_PRODUCT foreign key (PRODUCTUPC, > SEEDYEAR) > references PRODUCT (PRODUCTUPC, SEEDYEAR); > > alter table DEALERSERVICES > add constraint FK_DEALERSERVICES_DEALER foreign key (DEALERID) > references DEALER (IRDACCOUNTID); > > alter table DELIVERYNOTIFICATION > add constraint FK_DELIVERYNOTIFICATION_DEALER foreign key > (AFFILIATEIRDACCOUNTID) > references DEALER (IRDACCOUNTID); > > alter table DELIVERYNOTIFICATION > add constraint FK_DELIVNOTIFICATION_PRODUCT foreign key (PRODUCTUPC, > SEEDYEAR) > references PRODUCT (PRODUCTUPC, SEEDYEAR); > > alter table DELIVERYNOTIFICATION > add constraint FK_DELIVERY_FK_DELIVN_TRANSACT foreign key > (TRANSACTIONTYPEID) > references TRANSACTIONTYPE (TRANSACTIONTYPEID); > > alter table DELIVERYNOTIFICATION > add constraint FK_DELIVNOTIF_DEALERTRANS foreign key > (INVENTORYTRANSACTIONID) > references DEALERTRANSACTION (TRANSACTIONID); > > alter table DELIVERYNOTIFICATION > add constraint FK_DELIVNOTIF_DELIVNOTIFSTATUS foreign key (STATUSID) > references DELIVERYNOTIFICATIONSTATUS (STATUSID); > > alter table DISCOUNT > add constraint FK_DISCOUNT_BASISTYPE foreign key (BASISTYPEID) > references BASISTYPE (BASISTYPEID); > > alter table DISCOUNT > add constraint FK_DISCOUNT_CROP foreign key (DISCOUNTCROPID) > references CROP (CROPID); > > alter table DISCOUNT > add constraint FK_DISCOUNT_DISCOUNTCATEGORY foreign key > (DISCOUNTCATEGORYID) > references DISCOUNTCATEGORY (DISCOUNTCATEGORYID); > > alter table DISCOUNT > add constraint FK_DISCOUNT_DISCOUNTTYPE foreign key (DISCOUNTTYPEID) > references DISCOUNTTYPE (DISCOUNTTYPEID); > > alter table FARMMANAGER > add constraint FK_FARMMANAGER_STATE foreign key (STATECODE) > references STATE (STATECODE); > > alter table ORDERDETAIL > add constraint FK_ORDERDETAIL_DISCOUNT foreign key (DISCOUNTID) > references DISCOUNT (DISCOUNTID); > > alter table ORDERDETAIL > add constraint FK_ORDERDETAIL_ORDERLINETYPE foreign key > (ORDERLINETYPEID) > references ORDERLINETYPE (ORDERLINETYPEID); > > alter table ORDERDETAIL > add constraint FK_ORDERDETAIL_PRODUCT foreign key (PRODUCTUPC, > SEEDYEAR) > references PRODUCT (PRODUCTUPC, SEEDYEAR); > > alter table ORDERDETAIL > add constraint FK_ORDERDETAIL_PRODUCTORDER foreign key (ORDERID) > references PRODUCTORDER (ORDERID); > > alter table ORDERDETAIL > add constraint FK_ORDERDETAIL_SEEDPROC foreign key (PROCESSORID) > references SEEDPROCESSORROLE (SEEDPROCESSORROLEID); > > alter table ORDERDISCOUNT > add constraint FK_ORDERDISCOUNT_DISCOUNT foreign key (DISCOUNTID) > references DISCOUNT (DISCOUNTID); > > alter table ORDERDISCOUNT > add constraint FK_ORDERDISCOUNT_PRODUCTORDER foreign key (ORDERID) > references PRODUCTORDER (ORDERID); > > alter table ORDERLINEDELIVERY > add constraint FK_ORDERLINEDELIVERY_DEALER foreign key > (AFFILIATEIRDACCOUNTID) > references DEALER (IRDACCOUNTID); > > alter table ORDERLINEDELIVERY > add constraint FK_ORDERLINEDELIV_ORDERDETAIL foreign key > (ORDERITEMNUMBER, ORDERID) > references ORDERDETAIL (ORDERITEMNUMBER, ORDERID); > > alter table ORDERPAYMENT > add constraint FK_ORDERPAYMENT_PAYMENT foreign key (PAYMENTID) > references PAYMENT (PAYMENTID); > > alter table ORDERPAYMENT > add constraint FK_ORDERPAYMENT_PRODUCTORDER foreign key (ORDERID) > references PRODUCTORDER (ORDERID); > > alter table ORDERSHARE > add constraint FK_ORDERSHARE_CUSTOMER foreign key (CUSTOMERID) > references CUSTOMER (CUSTOMERID); > > alter table ORDERSHARE > add constraint FK_ORDERSHARE_PRODUCTORDER foreign key (ORDERID) > references PRODUCTORDER (ORDERID); > > alter table PAYMENT > add constraint FK_PAYMENT_CUSTOMER foreign key (CUSTOMERID) > references CUSTOMER (CUSTOMERID); > > alter table POSQUEUE > add constraint FK_POSQUEUE_DATAEXCHTYPE foreign key > (EXCHANGETYPERESOURCEID) > references DATAEXCHANGETYPE (EXCHANGETYPERESOURCEID); > > alter table POSQUEUE > add constraint FK_POSQUEUE_ORDRDETAIL foreign key (ORDERITEMNUMBER, > ORDERID) > references ORDERDETAIL (ORDERITEMNUMBER, ORDERID); > > alter table POSTRANSACTIONHISTORY > add constraint FK_POSTRANSHIST_DATAEXCHTYP foreign key > (EXCHANGETYPERESOURCEID) > references DATAEXCHANGETYPE (EXCHANGETYPERESOURCEID); > > alter table PRICE > add constraint FK_PRICE_PRICEAREA foreign key (PRICEAREANAME) > references PRICEAREA (PRICEAREANAME); > > alter table PRICE > add constraint FK_PRICE_PRODUCT foreign key (PRODUCTUPC, SEEDYEAR) > references PRODUCT (PRODUCTUPC, SEEDYEAR); > > alter table PRICECOUNTY > add constraint FK_PRICECOUNTY_PRICEAREA foreign key (PRICEAREANAME) > references PRICEAREA (PRICEAREANAME); > > alter table PRICECOUNTY > add constraint FK_PRICECOUNTY_STATE foreign key (STATECODE) > references STATE (STATECODE); > > alter table PRODUCT > add constraint FK_PRODUCT_CROP foreign key (CROPID) > references CROP (CROPID); > > alter table PRODUCT > add constraint FK_PRODUCT_PRODBRANDS foreign key (BRANDNAME) > references PRODUCTBRANDS (BRANDNAME); > > alter table PRODUCT > add constraint FK_PRODUCT_UNITOFMEASURE foreign key (UNITOFMEASUREID) > references UNITOFMEASURE (UNITOFMEASUREID); > > alter table PRODUCTORDER > add constraint FK_PRODORDER_CUSTDELIVCONTACT foreign key > (DELIVERYCUSTOMERID, DELIVERYCONTACTID) > references CUSTOMERDELIVERYCONTACT (CUSTOMERID, CONTACTID); > > alter table PRODUCTORDER > add constraint FK_PRODORDER_FARMMGR foreign key (FARMMANAGERID) > references FARMMANAGER (FARMMANAGERID); > > alter table PRODUCTORDER > add constraint FK_PRODUCTORDER_SELLER foreign key > (SELLERIRDACCOUNTID) > references SELLER (SELLERID); > > alter table SEEDPROCESSORROLE > add constraint FK_SEEDPROCROLE_SEEDPROC foreign key (PROCESSORID) > references SEEDPROCESSOR (PROCESSORID); > > alter table SUPPORTEDBRANDS > add constraint FK_SUPPORTEDBRANDS_DEALER foreign key (DEALERID) > references DEALER (IRDACCOUNTID); > > alter table SUPPORTEDBRANDS > add constraint FK_SUPPORTEDBRANDS_PRODBRANDS foreign key (BRANDNAME) > references PRODUCTBRANDS (BRANDNAME); > > alter table SYSTEMPARAMS > add constraint FK_SYSTEMPARAMS_DEALER foreign key (DEALERID) > references DEALER (IRDACCOUNTID); > > ====================================================== > > > > > -----Original Message----- > From: Mike Matrigali [mailto:mikem_app@sbcglobal.net] > Sent: Thursday, March 29, 2007 4:30 PM > To: Derby Discussion > Subject: Re: Alter Table ADD Column throws column number exception > > can you provide a self contained repro for this. At least telling what > the current ddl of the table may be interesting. > > This would let us know if it is a runtime multi-user ddl issue, or > something else. Do all of your alter tables fail or just this one? > Does this one fail all the time or just some time? > > > BALDWIN, ALAN J [AG-Contractor/1000] wrote: > >>Hello all, >> >>When I execute the following SQL statement: >>ALTER TABLE OrderDetail >>ADD COLUMN blah >>varchar(15) >> >>I get this exception: >> >>java.sql.SQLException: In a base table there was a mismatch between > > the > >>requested column number 16 and the maximum number of columns 20. >> >> >>Does anyone have an idea how I can fix this, or at least find a >>workaround? >> >>Thanks, >>-Alan- >> >> > > ------------------------------------------------------------------------ > --------------------------------- > >>This e-mail message may contain privileged and/or confidential > > information, and is intended to be received only by persons entitled to > receive such information. If you have received this e-mail in error, > please notify the sender immediately. Please delete it and all > attachments from any servers, hard drives or any other media. Other use > of this e-mail by you is strictly prohibited. > >> >>All e-mails and attachments sent and received are subject to > > monitoring, reading and archival by Monsanto. The recipient of this > e-mail is solely responsible for checking for the presence of "Viruses" > or other "Malware". Monsanto accepts no liability for any damage caused > by any such code transmitted by or accompanying this e-mail or any > attachment. > > ------------------------------------------------------------------------ > --------------------------------- > >> >> > > > > --------------------------------------------------------------------------------------------------------- > This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited. > > > All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto. The recipient of this e-mail is solely responsible for checking for the presence of "Viruses" or other "Malware". Monsanto accepts no liability for any damage caused by any such code transmitted by or accompanying this e-mail or any attachment. > --------------------------------------------------------------------------------------------------------- > > >