Hello again,
As a corollary to my upgrade question, I have another issue I would
like to get some input on.
Several old databases in production were created with blob columns at
the then default blob size of 1mb. How can I go about upgrading these
columns to be longer?
This is the best approach I've come up with so far:
ALTER TABLE binarydata ADD COLUMN data2 blob(128M)
UPDATE binarydata SET data2 = data;
ALTER TABLE binarydata DROP COLUMN data RESTRICT;
RENAME COLUMN binarydata.data2 TO data;
The issue with this approach is that some deployed databases are
nearly 1GB in size with a large portion of that being in this table
I'm trying to adjust. When I ran this query as a test on such a
database, the size of the db on disk balloon to over twice its
starting size, and then failed because I was running it on a temp
drive without enough storage to complete, so I haven't been able to
fully test even if this will work.
Is there a good reason why blob columns can't have their size adjusted
directly? I've tried:
ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(128M)
but this failed with an error.
Any ideas?
Evan
