db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "BALDWIN, ALAN J [AG-Contractor/1000]" <alan.j.bald...@monsanto.com>
Subject RE: Alter Table ADD Column throws column number exception
Date Fri, 30 Mar 2007 14:02:16 GMT
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.


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.
---------------------------------------------------------------------------------------------------------


Mime
View raw message