Hey Helen,

It is a lot simpler than that. I had a table which was defined similar to this:

Create Table MyTable
( ...
MyField Numeric(15,2)
...
)

MyTable.MyField was referenced by a a stored procedure. I was able to change the datatype with a symple:

Alter Table MyTable
Alter MyField Type Double Precision;

Jason


Helen Borrie wrote:

> At 04:47 AM 1/04/2006, you wrote:
>> Hey all,
>>
>> Just this morning, I noticed a flood of error messages coming from
>> my Web server. A certain stored procedure I was
>> running was causing an "Index Unexpectedly Deleted". What I come to
>> find out is, there are fields referenced in said
>> stored procedure which had changed datatypes (mostly
>> numeric(something) -> Double Precision). Obvioulsy, there were no
>> dependency errors when these fields changed, and I am perplexed as to why...
>
> What version of Firebird? AFAIK, it's not possible to change the
> data type of a domain from numeric to double. Here is what you
> should have observed (Fb 1.5.3):
>
> create domain unstable as numeric(18,2);
> commit;
> create table unstabletable (
> marker char,
> f1 unstable,
> f2 unstable);
> commit;
> create index unstableindex on unstabletable(f1);
> commit;
> alter domain unstable type double precision;
> commit;
>
> ISC ERROR CODE:335544351
>
> unsuccessful metadata update
> Cannot change datatype for UNSTABLE. Conversion from base type
> BIGINT to DOUBLE PRECISION is not supported.
>
> So, if it actually happened, then the problem may indicate something
> worse than the "obvious" - you used direct DML on the system tables
> to effect these domain changes (or you used a tool that does that).
>
> If indeed it was these changed domains that resulted in this error,
> the actual run-time situation could/would arise if your SP was trying
> to treat an integer variable as being valid for some comparison
> operation on the changed fields, e.g. the parameter for a where
> clause or an input variable.
>
> Remember, PSQL doesn't know about domains, so the assumption must be
> that, if a domain type change done using ALTER DOMAIN is successful,
> i.e. valid, then column references will be valid. I believe ALTER
> TABLE and ALTER DOMAIN do check dependencies on SP arguments, since
> they are checkable; anything inside the module is unavailable, of course.
>
>> On top of this, I needed to remake/alter said stored procedure to
>> reflect the new field types. I was thinking, I
>> SHOULDNT be able to drop this procedure because a few calculated
>> fields are dependent on it. But sure enough, I was able
>> to drop it and readd it without a complaint from the server.
>> Everything works fine now, but I am a little worried that
>> something might be wrong with either my DB, or firebird itself.
>
> I would be worried.
>
> If you did actually "change" these domains by direct DML on the
> system tables then it's asking for trouble, and not just because of
> subtle dependencies like variables in PSQL modules. A number like
> 12345.66 as a numeric could easily be stored as 12345.6666666666667
> as a double. If the column is indexed or, worse, has a constraint on
> it, then your database is going to become corrupt as soon as you
> begin updating or inserting data. Even 0 as a numeric is not the
> same as 0 as a double.
>
> Did you use a database tool to make these changes or did you script
> them yourself? Unfortunately, there are some third-party tools out
> there that modify the system tables directly, *by design*
> circumventing the integrity rules of the database engine.
>
> ./heLen
>
>
>

--
The information transmitted herewith is sensitive information intended only for use to the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon, this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.