Query:

select stockcode, onlinedescrhtml from code where posttoweb = true

Script:

SCRIPT
BEGIN

/************************************************************
* Tables
************************************************************/

-- Dropping all foreign key constraints

-- Dropping all other constraints

-- Dropping all indexes

SET STATUS MESSAGE TO 'Dropping all indexes for table "code"';

EXECUTE IMMEDIATE 'DROP INDEX "TempStockCode" FROM "code"';

SET PROGRESS TO 100;

-- Dropping all triggers

-- Dropping and altering existing tables

SET STATUS MESSAGE TO 'Altering table "code"';

EXECUTE IMMEDIATE 'ALTER TABLE "code"
DROP COLUMN "OnlineDescrRTF",
DROP COLUMN "OnlineDescrTEXT",
ALTER COLUMN "StockCode" AS VARCHAR(20) COLLATE "UNI" DESCRIPTION ''Unique stock code (SKU)'',
ALTER COLUMN "AltCode" AS VARCHAR(20) COLLATE "UNI" DESCRIPTION ''Alternate stock code (if non-blank must be unique)'',
ALTER COLUMN "Mfr" AS VARCHAR(40) COLLATE "UNI" DESCRIPTION ''Manufacturer'',
ALTER COLUMN "Model" AS VARCHAR(20) COLLATE "UNI" DESCRIPTION ''Model'',
ALTER COLUMN "Descr" AS VARCHAR(128) COLLATE "UNI" DESCRIPTION ''Description of item'',
ALTER COLUMN "Category" AS VARCHAR(15) COLLATE "UNI" DESCRIPTION ''Category'',
ALTER COLUMN "SubCat" AS VARCHAR(15) COLLATE "UNI" DESCRIPTION ''Sub-category'',
ALTER COLUMN "Location" AS VARCHAR(15) COLLATE "UNI" DESCRIPTION ''Inventory location'',
ALTER COLUMN "Cost" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Current cost of the item (includes any added shipping)'',
ALTER COLUMN "LastCost" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Last direct cost of the item (does not include shipping)'',
ALTER COLUMN "List" AS FLOAT DEFAULT 0.00  DESCRIPTION ''List price'',
ALTER COLUMN "Price" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Sale price'',
ALTER COLUMN "Discount1" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount price level 1'',
ALTER COLUMN "Discount2" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount price level 2'',
ALTER COLUMN "Discount3" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount price level 3'',
ALTER COLUMN "Discount4" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount price level 4'',
ALTER COLUMN "Discount5" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount price level 5'',
ALTER COLUMN "Taxable" AS BOOLEAN DEFAULT True  DESCRIPTION ''Taxable indicator'',
ALTER COLUMN "GST" AS BOOLEAN DEFAULT True  DESCRIPTION ''GST indicator'',
ALTER COLUMN "OnHand" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Quantity on hand'',
ALTER COLUMN "Minimum" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Minimum stock quantity'',
ALTER COLUMN "TargetQty" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Target reorder quantity'',
ALTER COLUMN "Serialized" AS BOOLEAN DEFAULT False  DESCRIPTION ''Boolean serialized indicator'',
ALTER COLUMN "Container" AS SMALLINT DEFAULT 0  DESCRIPTION ''Container type (0 = none, 1 = assembly, 2 = container)'',
ALTER COLUMN "NoDeplete" AS BOOLEAN DEFAULT False  DESCRIPTION ''Non-depleting indicator'',
ALTER COLUMN "Shipping" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Shipping charges for item (not currently used)'',
ALTER COLUMN "Weight" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Shipping weight'',
ALTER COLUMN "OnOrder" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Quantity currently on order'',
ALTER COLUMN "Allocated" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Quantity currently allocated on sales orders'',
ALTER COLUMN "OnRMA" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Quantity on RMA'',
ALTER COLUMN "InPreBuilt" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Quantity currently allocated to prebuilt containers (not currently in use)'',
ALTER COLUMN "Unit" AS VARCHAR(15) COLLATE "UNI" DEFAULT ''EA''  DESCRIPTION ''Sale unit of measure'',
ALTER COLUMN "OrderUnit" AS VARCHAR(15) COLLATE "UNI" DEFAULT ''EA''  DESCRIPTION ''Order unit of measure'',
ALTER COLUMN "UnitsToOrd" AS FLOAT DEFAULT 1  DESCRIPTION ''Number of sale units per order unit'',
ALTER COLUMN "NonStock" AS BOOLEAN DEFAULT False  DESCRIPTION ''Non-stock indicator'',
ALTER COLUMN "Discontinued" AS BOOLEAN DEFAULT False  DESCRIPTION ''Discontinued indicator'',
ALTER COLUMN "Notes" AS CLOB COLLATE "UNI" DESCRIPTION ''Notes (64K limit)'',
ALTER COLUMN "AutoRound" AS BOOLEAN DEFAULT False  DESCRIPTION ''Automatic rounding indicator'',
ALTER COLUMN "AutoPrice" AS SMALLINT DEFAULT 0  DESCRIPTION ''Auto pricing method (0 = none/manual, 1 = batch update, 2 = automatic)'',
ALTER COLUMN "ListMthd" AS SMALLINT DEFAULT 0  DESCRIPTION ''List pricing method (0 = manual, 1 = % mark-up from cost, 2 = margin from cost, 101 = add amount to cost)'',
ALTER COLUMN "ListPct" AS FLOAT DEFAULT 0.00  DESCRIPTION ''List pricing amount/percentage'',
ALTER COLUMN "SaleMthd" AS SMALLINT DEFAULT 0  DESCRIPTION ''Sale pricing method (0 = manual, 1 = % mark-up from cost, 2 = margin from cost, 3 = % discount from list, 101 = add amount to cost, 102 = subtract amount from list)'',
ALTER COLUMN "SalePct" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Sale pricing amount/percentage'',
ALTER COLUMN "Disc1Mthd" AS SMALLINT DEFAULT 0  DESCRIPTION ''Discount 1 pricing method (0 = manual, 1 = % mark-up from cost, 2 = margin from cost, 3 = % discount from list, 4 = discount from sale price, 101 = add amount to cost, 102 = subtract amount from list, 103 = subtract amount from sale)'',
ALTER COLUMN "Disc2Mthd" AS SMALLINT DEFAULT 0  DESCRIPTION ''Discount 2 pricing method (see discount 1 pricing method for values)'',
ALTER COLUMN "Disc3Mthd" AS SMALLINT DEFAULT 0  DESCRIPTION ''Discount 3 pricing method (see discount 1 pricing method for values)'',
ALTER COLUMN "Disc4Mthd" AS SMALLINT DEFAULT 0  DESCRIPTION ''Discount 4 pricing method (see discount 1 pricing method for values)'',
ALTER COLUMN "Disc5Mthd" AS SMALLINT DEFAULT 0  DESCRIPTION ''Discount 5 pricing method (see discount 1 pricing method for values)'',
ALTER COLUMN "Disc1Desc" AS VARCHAR(15) COLLATE "UNI" DEFAULT ''Discount 1''  DESCRIPTION ''Discount pricing level 1 description'',
ALTER COLUMN "Disc2Desc" AS VARCHAR(15) COLLATE "UNI" DEFAULT ''Discount 2''  DESCRIPTION ''Discount pricing level 2 description'',
ALTER COLUMN "Disc3Desc" AS VARCHAR(15) COLLATE "UNI" DEFAULT ''Discount 3''  DESCRIPTION ''Discount pricing level 3 description'',
ALTER COLUMN "Disc4Desc" AS VARCHAR(15) COLLATE "UNI" DEFAULT ''Discount 4''  DESCRIPTION ''Discount pricing level 4 description'',
ALTER COLUMN "Disc5Desc" AS VARCHAR(15) COLLATE "UNI" DEFAULT ''Discount 5''  DESCRIPTION ''Discount pricing level 5 description'',
ALTER COLUMN "Disc1Pct" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 1 amount/percentage'',
ALTER COLUMN "Disc2Pct" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 2 amount/percentage'',
ALTER COLUMN "Disc3Pct" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 3 amount/percentage'',
ALTER COLUMN "Disc4Pct" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 4 amount/percentage'',
ALTER COLUMN "Disc5Pct" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 5 amount/percentage'',
ALTER COLUMN "QtyPricing" AS BOOLEAN DEFAULT False  DESCRIPTION ''(currently not used)'',
ALTER COLUMN "Disc1Qty" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 1 minimum quantity'',
ALTER COLUMN "Disc2Qty" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 2 minimum quantity'',
ALTER COLUMN "Disc3Qty" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 3 minimum quantity'',
ALTER COLUMN "Disc4Qty" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 4 minimum quantity'',
ALTER COLUMN "Disc5Qty" AS FLOAT DEFAULT 0.00  DESCRIPTION ''Discount pricing level 5 minimum quantity'',
ALTER COLUMN "PrefVendor" AS VARCHAR(15) COLLATE "UNI" DESCRIPTION ''Preferred vendor'',
ALTER COLUMN "UPSInd" AS BOOLEAN DEFAULT False  DESCRIPTION ''Shippable by UPS (not currently used)'',
ALTER COLUMN "PriceCat" AS VARCHAR(15) COLLATE "UNI" DESCRIPTION ''Pricing category assigned to the stock code'',
ALTER COLUMN "HasImage" AS BOOLEAN DEFAULT False  DESCRIPTION ''Associated image flag (do not modify)'',
ADD COLUMN "HasRelated" BOOLEAN DEFAULT False  DESCRIPTION ''Indicates that there are related codes associated with the stock code'' AT 69,
ALTER COLUMN "PostToWeb" AS BOOLEAN DEFAULT False  DESCRIPTION ''Indicator if the item should be posted to your e-commerce web site'',
ALTER COLUMN "LastModDt" AS DATE DEFAULT CURRENT_DATE  DESCRIPTION ''Last modified date'',
ALTER COLUMN "LastModID" AS VARCHAR(15) COLLATE "UNI" DEFAULT ''CREATED''  DESCRIPTION ''Last modified user ID'',
ALTER COLUMN "LastSoldDt" AS DATE DESCRIPTION ''Last sold date'',
ALTER COLUMN "LastRecvDt" AS DATE DESCRIPTION ''Last received date'',
ALTER COLUMN "URL" AS VARCHAR(128) COLLATE "UNI" DESCRIPTION ''URL for more product information'',
ALTER COLUMN "OnSaleRecID" AS INTEGER DEFAULT 0  DESCRIPTION ''Record ID of sale price entry (do not modify)'',
ALTER COLUMN "NoDiscounts" AS BOOLEAN DEFAULT False  DESCRIPTION ''(not currently used)'',
ALTER COLUMN "OnlineName" AS VARCHAR(64) COLLATE "UNI" DESCRIPTION ''E-commerce Product name'',
ALTER COLUMN "OnlineDescrHTML" AS CLOB COLLATE "UNI" DESCRIPTION ''E-commerce item description, in html format'',
ALTER COLUMN "AltCode2" AS VARCHAR(20) COLLATE "UNI" DESCRIPTION ''Alternate stock code 2 (if non-blank must be unique)'',
ALTER COLUMN "AltCode3" AS VARCHAR(20) COLLATE "UNI" DESCRIPTION ''Alternate stock code 3 (if non-blank must be unique)'',
ALTER COLUMN "Reminder" AS VARCHAR(90) COLLATE "UNI" DESCRIPTION ''Text displayed when an item is sold on a transaction'',
ALTER COLUMN "GLAcct" AS VARCHAR(35) COLLATE "UNI" DESCRIPTION ''Product general ledger account'',
ALTER COLUMN "DimL" AS SMALLINT DEFAULT 0  DESCRIPTION ''Package length dimension'',
ALTER COLUMN "DimW" AS SMALLINT DEFAULT 0  DESCRIPTION ''Package width dimension'',
ALTER COLUMN "DimH" AS SMALLINT DEFAULT 0  DESCRIPTION ''Package height dimension'',
ADD COLUMN "DimUOM" VARCHAR(2) COLLATE "UNI" DESCRIPTION ''(currently not used)'' AT 87,
ALTER COLUMN "OnlineKeyWords" AS VARCHAR(128) COLLATE "UNI" DESCRIPTION ''Zen Cart item keywords'',
ALTER COLUMN "OnlineFreeShipping" AS BOOLEAN DEFAULT False  DESCRIPTION ''Zen Cart item free shipping indicator'',
ADD COLUMN "Available" FLOAT COMPUTED ALWAYS AS OnHand - Allocated - OnRMA  DESCRIPTION ''Available quantity'' AT 90

DESCRIPTION ''Stock Code Information''
VERSION 13
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 524288
MAX INDEX BUFFER SIZE 4194304
MAX BLOB BUFFER SIZE 131072
MAX PUBLISH BUFFER SIZE 32768';

SET PROGRESS TO 100;

END

---

 Comments

---

The problem was caused by the rows not being initialized with the proper BLOB block numbers during the alteration process, which would result in later rows re-using BLOB blocks already allocated for prior rows. There was an additional criteria that the table be of a certain size, which is why this was missed during the original testing of the new ALTER TABLE performance changes in 2.03 Build 10.

If you experience this issue, restore the .old versions of both the edbdatabase.edbcat database catalog file and the table files (.edbtbl, .edbidx, .edbblb) in order to restore the old data.

---

 Resolution

---

Fixed Problem on 4/28/2010 in version 2.03 build 13

---

 Products Affected

---

ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial