Re: Strange behaviour of sysdefaults

Joachim Verhagen wrote: Forgive the top post. Not to worry. Then engine does not use the string representation, only dbschema (and myschema) does. The engine uses the 'BASE 6' encoded value and as you have seen, since numeric default tests are actually performed as DECIMAL types, there's no problem. For your DB=Change processor, just key on the part of deflt preceding the first space. Art S. Kagel > We have a program for database upgrades that checks the current database > structure, compares it to what it should be for this upgrade and executes the > necessary alter and create statements. > > But we have a problem with the defaults in Informix. The default field in > sysdefaults changes for a field that is not modified. The real value remains > the same, but the display in sysdefaults.default changes. > > Here a demo table > create table a( a1 decimal(15,2) default 0 not null, a2 decimal(15,2) > default 0 not null)> > And next check for the defaults > select syscolumns.colName, sysdefaults.default from sysdefaults, > systables, syscolumns > where> sysdefaults.tabid=systables.tabid > and sysdefaults.colno=syscolumns.colno > and syscolumns.tabid=systables.tabid > and systables.tabname='a' > > 'a1' 'gAAAAAAAAAAA 0' > 'a2' 'gAAAAAAAAAAA 0' > > This is as expected, binary value space 0 > > Next I modify it. > alter table a modify (a1 decimal(15,0) default 1 not null)> > Again the select: > 'a1' 'wQEAAAAAAAAA 1' > 'a2' 'gAAAAAAAAAAA 0.00' > > a1 is changed as expected. > a2 has the same binary value, which I do not know how to interpret but the > ascii value has changed to 0.00. > > The program reads this as a text field and for the next upgrade tries to > upgrade a2. This results in > > 'a1' 'wQEAAAAAAAAA 1.00' > 'a2' 'gAAAAAAAAAAA 0' > > To keep consistency I tried to make a default of 0.00 and 1.00, but the > result is the same. The modified field has a value without a dot and the > other does. > > If I try it with decimal(15,0) fields it only results in more zeros > ('gAAAAAAAAAAA 0.0000000000000000'). > > Modifying both fields in that alter does result in two dotless default > values, but I do not think that will be good for the performance. > > The only solution I can think of is letting the program turn the values to > decimals before comparing. > > Can anybody explain what is going on here? > Does anybody know a better way to solve the problem? > Maybe if I knew how to interpret the binary value. > > > Joachim. > > -- > Joachim Verhagen > WWW http://www.xs4all.nl/~jcdverha/ (Science Jokes)

On Mon, 11 Dec 2006 19:14:26 -0500, "Art S. Kagel" <kagel@bloomberg.net> wrote: >Joachim Verhagen wrote: > >Forgive the top post. > >Not to worry. Then engine does not use the string representation, only >dbschema (and myschema) does. The engine uses the 'BASE 6' encoded value >and as you have seen, since numeric default tests are actually performed as >DECIMAL types, there's no problem. For your DB=Change processor, just key >on the part of deflt preceding the first space. > >Art S. Kagel > Art, Thank you for the device, but what is BASE 6? I never heard of it. Joachim. > >> We have a program for database upgrades that checks the current database >> structure, compares it to what it should be for this upgrade and executes the >> necessary alter and create statements. >> >> But we have a problem with the defaults in Informix. The default field in >> sysdefaults changes for a field that is not modified. The real value remains >> the same, but the display in sysdefaults.default changes. >> >> Here a demo table >> create table a( a1 decimal(15,2) default 0 not null, a2 decimal(15,2) >> default 0 not null)>> >> And next check for the defaults >> select syscolumns.colName, sysdefaults.default from sysdefaults, >> systables, syscolumns >> where>> sysdefaults.tabid=systables.tabid >> and sysdefaults.colno=syscolumns.colno >> and syscolumns.tabid=systables.tabid >> and systables.tabname='a' >> >> 'a1' 'gAAAAAAAAAAA 0' >> 'a2' 'gAAAAAAAAAAA 0' >> >> This is as expected, binary value space 0 >> >> Next I modify it. >> alter table a modify (a1 decimal(15,0) default 1 not null)>> >> Again the select: >> 'a1' 'wQEAAAAAAAAA 1' >> 'a2' 'gAAAAAAAAAAA 0.00' >> >> a1 is changed as expected. >> a2 has the same binary value, which I do not know how to interpret but the >> ascii value has changed to 0.00. >> >> The program reads this as a text field and for the next upgrade tries to >> upgrade a2. This results in >> >> 'a1' 'wQEAAAAAAAAA 1.00' >> 'a2' 'gAAAAAAAAAAA 0' >> >> To keep consistency I tried to make a default of 0.00 and 1.00, but the >> result is the same. The modified field has a value without a dot and the >> other does. >> >> If I try it with decimal(15,0) fields it only results in more zeros >> ('gAAAAAAAAAAA 0.0000000000000000'). >> >> Modifying both fields in that alter does result in two dotless default >> values, but I do not think that will be good for the performance. >> >> The only solution I can think of is letting the program turn the values to >> decimals before comparing. >> >> Can anybody explain what is going on here? >> Does anybody know a better way to solve the problem? >> Maybe if I knew how to interpret the binary value. >> >> >> Joachim. >> >> -- >> Joachim Verhagen >> WWW http://www.xs4all.nl/~jcdverha/ (Science Jokes) -- Joachim Verhagen WWW http://www.xs4all.nl/~jcdverha/ (Science Jokes)

Joachim Verhagen wrote: > On Mon, 11 Dec 2006 19:14:26 -0500, "Art S. Kagel" <kagel@bloomberg.net> > wrote: > > >>Joachim Verhagen wrote: >> >>Forgive the top post. >> >>Not to worry. Then engine does not use the string representation, only >>dbschema (and myschema) does. The engine uses the 'BASE 6' encoded value >>and as you have seen, since numeric default tests are actually performed as >>DECIMAL types, there's no problem. For your DB=Change processor, just key >>on the part of deflt preceding the first space. >> >>Art S. Kagel >> > > > Art, > > Thank you for the device, but what is BASE 6? I never heard of it. I pulled that from my myschema code which was itself pulled from some doc somewhere. Probably it should be 'Base 64' encoding which is the same used for sysdistrib, sysprocbody, etc. I put a small amount of effort to decoding it, but stopped when I realized that I didn't need to for myschema. I just needed the string part and the data type of the column that owns the default to determine the precise default. Art S. Kagel > Joachim. <SNIP>