Re: Possible Feature

Paul Watson wrote: > I haven't checked the manuals but I don't believe we can do this :) > Just done a series of big DB changes and needed to recompile all the SPL > code. I use 'LIKE' for the input parameters and local variables but you > can not do a > > RETURNING LIKE <tabname>.<colname> syntax > > Would anyone else find that useful??? Paul, You cannot do it and I would submit that you do not want to be able to do this. I'll explain. Having internal variables within a routine that are defined LIKE table.column (oh, asside to Daniel Oracle's table.column%TYPE is the same as LIKE table.column in Informixese IB) is fine as these are used internally and if you modify the table the routine will be recompiled the next time it is run so no problems. HOWEVER, if you have return types that can vary you are opening a can of worms. Suppose you have written an application that expects the routine to return an integer. The routine is defined to return LIKE table.column as you suggest. One day you ALTER that column from INT to CHAR(20). Initially everything is fine, the routine recompiles and the ESQL/C or ODBC library makes the appropriate automatic conversion from CHAR(20) to INTEGER. Assuming that all of the values were initially integers as they must be, all is well. Now a month later you begin inserting values to that column (accidentally, on purpose, or maliciously) that are no longer numeric and cannot be converted or represent numeric values that do not fit into the 32bit integer host variable. Every call to the routine gets a -1213 or -1215 error and your application is hosed. Nope, this would be a VERY bad idea indeed. My approach would be to leave the original routine mostly alone (adding code to handle numeric overflows and other problems, returning an expected error) for unconverted apps to call and write a new one that returns the column's new datatype to be recoded into converted and new applications. I make it a hard and fast rule for any API that if the interface changes (ie inputs of outputs), the name of the API function must change as well. Then when the older interface is no longer references we can get rid of it altogether. Makes conversions smoother. Art S. Kagel

Art S. Kagel wrote: > Paul Watson wrote: >> I haven't checked the manuals but I don't believe we can do this :) >> Just done a series of big DB changes and needed to recompile all the SPL >> code. I use 'LIKE' for the input parameters and local variables but you >> can not do a >> RETURNING LIKE <tabname>.<colname> syntax >> >> Would anyone else find that useful??? > > Paul, > > You cannot do it and I would submit that you do not want to be able to > do this. I'll explain. > > Having internal variables within a routine that are defined LIKE > table.column (oh, asside to Daniel Oracle's table.column%TYPE is the > same as LIKE table.column in Informixese IB) is fine as these are used > internally and if you modify the table the routine will be recompiled > the next time it is run so no problems. HOWEVER, if you have return > types that can vary you are opening a can of worms. Suppose you have > written an application that expects the routine to return an integer. > The routine is defined to return LIKE table.column as you suggest. One > day you ALTER that column from INT to CHAR(20). Initially everything is > fine, the routine recompiles and the ESQL/C or ODBC library makes the > appropriate automatic conversion from CHAR(20) to INTEGER. Assuming > that all of the values were initially integers as they must be, all is > well. Now a month later you begin inserting values to that column > (accidentally, on purpose, or maliciously) that are no longer numeric > and cannot be converted or represent numeric values that do not fit into > the 32bit integer host variable. Every call to the routine gets a -1213 > or -1215 error and your application is hosed. > > Nope, this would be a VERY bad idea indeed. My approach would be to > leave the original routine mostly alone (adding code to handle numeric > overflows and other problems, returning an expected error) for > unconverted apps to call and write a new one that returns the column's > new datatype to be recoded into converted and new applications. I make > it a hard and fast rule for any API that if the interface changes (ie > inputs of outputs), the name of the API function must change as well. > Then when the older interface is no longer references we can get rid of > it altogether. Makes conversions smoother. > > Art S. Kagel Everything you wrote is true but I would question your conclusion given that this technology has existed for more than a decade in another product and not caused that problem. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

Yes, I'm aware of the pitfalls of using this 'return' syntax, and if I choose to use it then I have the responsible for maintaining type consistency. But it would make life a lot easier :-) Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 European Conference. Vienna, Austria. 2-6 October 2006 Visit http://www.iiug.org/conf for more information. -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Posted At: 21 August 2006 09:46 Posted To: comp.databases.informix Conversation: Possible Feature Subject: Re: Possible Feature Paul Watson wrote: > I haven't checked the manuals but I don't believe we can do this :) > Just done a series of big DB changes and needed to recompile all the > SPL code. I use 'LIKE' for the input parameters and local variables > but you can not do a > > RETURNING LIKE <tabname>.<colname> syntax > > Would anyone else find that useful??? Paul, You cannot do it and I would submit that you do not want to be able to do this. I'll explain. Having internal variables within a routine that are defined LIKE table.column (oh, asside to Daniel Oracle's table.column%TYPE is the same as LIKE table.column in Informixese IB) is fine as these are used internally and if you modify the table the routine will be recompiled the next time it is run so no problems. HOWEVER, if you have return types that can vary you are opening a can of worms. Suppose you have written an application that expects the routine to return an integer. The routine is defined to return LIKE table.column as you suggest. One day you ALTER that column from INT to CHAR(20). Initially everything is fine, the routine recompiles and the ESQL/C or ODBC library makes the appropriate automatic conversion from CHAR(20) to INTEGER. Assuming that all of the values were initially integers as they must be, all is well. Now a month later you begin inserting values to that column (accidentally, on purpose, or maliciously) that are no longer numeric and cannot be converted or represent numeric values that do not fit into the 32bit integer host variable. Every call to the routine gets a -1213 or -1215 error and your application is hosed. Nope, this would be a VERY bad idea indeed. My approach would be to leave the original routine mostly alone (adding code to handle numeric overflows and other problems, returning an expected error) for unconverted apps to call and write a new one that returns the column's new datatype to be recoded into converted and new applications. I make it a hard and fast rule for any API that if the interface changes (ie inputs of outputs), the name of the API function must change as well. Then when the older interface is no longer references we can get rid of it altogether. Makes conversions smoother. Art S. Kagel

Paul Watson wrote: > Yes, I'm aware of the pitfalls of using this 'return' syntax, and if I > choose to use it then I have the responsible for maintaining type > consistency. But it would make life a lot easier :-) > > Oooww! Me! Me! Me Sir! Code Generator Sir! Integrated with a tool that alters the table and rebuilds the stored procedures in one go! David. > Paul Watson > Tel: +44 1414161772 > Mob: +44 7818003457 > Web: www.oninit.com > > GO FURTHER with DB2 > GET THERE FASTER with Informix. > Attend the IDUG 2006 European Conference. > Vienna, Austria. 2-6 October 2006 > Visit http://www.iiug.org/conf for more information. > > > > -----Original Message----- > From: Art S. Kagel [mailto:kagel@bloomberg.net] > Posted At: 21 August 2006 09:46 > Posted To: comp.databases.informix > Conversation: Possible Feature > Subject: Re: Possible Feature > > > Paul Watson wrote: > > I haven't checked the manuals but I don't believe we can do this :) > > Just done a series of big DB changes and needed to recompile all the > > SPL code. I use 'LIKE' for the input parameters and local variables > > but you can not do a > > > > RETURNING LIKE <tabname>.<colname> syntax > > > > Would anyone else find that useful??? > > Paul, > > You cannot do it and I would submit that you do not want to be able to > do this. I'll explain. > > Having internal variables within a routine that are defined LIKE > table.column (oh, asside to Daniel Oracle's table.column%TYPE is the > same as LIKE table.column in Informixese IB) is fine as these are used > internally and if you modify the table the routine will be recompiled > the next time it is run so no problems. HOWEVER, if you have return > types that can vary you are opening a can of worms. Suppose you have > written an application that expects the routine to return an integer. > The routine is defined to return > LIKE table.column as you suggest. One day you ALTER that column from > INT to CHAR(20). Initially everything is fine, the routine recompiles > and the ESQL/C or ODBC library makes the appropriate automatic > conversion from > CHAR(20) to INTEGER. Assuming that all of the values were initially > integers as they must be, all is well. Now a month later you begin > inserting values to that column (accidentally, on purpose, or > maliciously) that are no longer numeric and cannot be converted or > represent numeric values that do not fit into the 32bit integer host > variable. Every call to the routine gets a -1213 or -1215 error and > your application is hosed. > > Nope, this would be a VERY bad idea indeed. My approach would be to > leave the original routine mostly alone (adding code to handle numeric > overflows and other problems, returning an expected error) for > unconverted apps to call and write a new one that returns the column's > new datatype to be recoded into converted and new applications. I make > it a hard and fast rule for any API that if the interface changes (ie > inputs of outputs), the name of the API function must change as well. > Then when the older interface is no longer references we can get rid of > it altogether. Makes conversions smoother. > > Art S. Kagel

>Paul Watson wrote: >> Yes, I'm aware of the pitfalls of using this 'return' syntax, and if I >> choose to use it then I have the responsible for maintaining type >> consistency. But it would make life a lot easier :-) >> >> > >Oooww! Me! Me! Me Sir! > >Code Generator Sir! Got one > >Integrated with a tool that alters the table and rebuilds the stored procedures in one go! > >David. Or even just re-compile the existing code - so much easier - if people don't want to use the syntax so be it. I'm sure of are old enough remember the debate of 'DEFINE record LIKE' in 4gl [cutting]