Re: DeriveParameters method fails if UDR parameter defined using LIKE

>>I've tried to send this issues to Informix support two months ago. >>Probably it haven't reached Informix yet as our internal (corporate) >>support works very slowly (only one guy who have rights to contact with >>Informix have other more important job too). Defect has been logged. When you contact IBM Informix Tech Support you can refer this defect ID idsdb00163467. Note: The defect opening doesn't guarantee expedite resolution. You need to contact IBM Informix tech support for speedy solution. -Shesh robsosno <robsosno@gmail.com> Sent by: informix-list-bounces@iiug.org 28/07/2008 22:28 To informix-list@iiug.org cc Subject Re: DeriveParameters method fails if UDR parameter defined using LIKE Rich wrote: > On 28 Jul, 16:04, Rich <informix.databa...@geos.com> wrote: >> On 25 Jul, 15:51, Rich <informix.databa...@geos.com> wrote: >> >> >> >>> On 25 Jul, 15:37, Rich <informix.databa...@geos.com> wrote: >>>> Ref: http://www-1.ibm.com/support/docview.wss?rs=681&context=SSVT2J&dc=DB5... >>>> Apparently upgrading to at last CSDK 3.00 should have fixed the >>>> problems with DeriveParameters but alas I fear not. >>>> I'm using CSDK 3.50.TC1 and I'm still having problems: >>>> 1. DeriveParameters fails with a "Specified cast is not valid" >>>> exception when deriving a parameter from a UDR parameter defined using >>>> LIKE. Columns defined as int seem fine but columns defined as varchar >>>> fail - I haven't tried other types yet. >>>> 2. A parameter called "RETURN" is always included as the first >>>> parameter in the derived results, this is hard-coded but there's no >>>> explanation as to what it is or why it's there? >>>> 3. A parameter called "Parameter1" is always appended to the list of >>>> parameters, it appears completely spurious! >>>> Although irritating I can work around #2 and #3 but #1 is damn >>>> annoying! >>>> IDS version 9.40.TC2. >>>> Anyone tried using this stuff? >>>> Thanks, >>>> Rich >>> Oh and while I'm in a grumbling mood, the coding example given in the >>> manual wouldn't even compile if you tried it - does anyone check this >>> stuff?! >>> --------------- >>> // IfxConnection - con >>> IfxCommand cmd = new IfxCommand("SP_GETUSERINFO",con); >>> IfxCommandBuilder cb = new IfxCommandBuilder(); >>> Con.Open(); >>> Cb.DeriveParameters(cmd); >>> foreach (IfxParameter param in cmd.Parameters) >>> { >>> Console.WriteLine(param.ParameterName);} >>> con.Close(); >>> --------------- >>> 1. DeriveParameters is a static method, not an instance method i.e. >>> you cannot call it from a class instance. >>> 2. C# is case-sensitive so declaring the variable as "cb" and then >>> trying to use "Cb" won't work, in fact it wouldn't even compile. >>> (ditto the con/Con variable)- Hide quoted text - >>> - Show quoted text - >> ...the problems continue: >> >> 1. Why do you ALWAYS get an "Unknown SQL Type - -110" exception if >> the final parameter is an INT? >> >> 2. Why is the final parameter not 'derived'? i.e. it's always >> missing from the collection. >> >> 3. Why is lvarchar mapped to IfxType.Clob when there's a perfectly >> reasonable IfxType.Lvarchar type?- Hide quoted text - >> >> - Show quoted text - > Oh and DateTime doesn't appear to be supported at all - you get the > Uknown SQL Type -110 exception. > > (Apologies for the 'flood') I have similar experience: DeriveParameters works correctly only in few cases. I've written code generator which for the given stored procedure generates SPL call in C#. The intent is to automate generating webservices based on SPL. My first attempt to use only C# (and DeriveParameters) failed. In the end I've used ESQL/C and DESCRIBE instead + some parameter parsing on my own. What's wrong with DeriveParameters? Here is my list: - RETURN parameter not documented; possible this is because Microsoft also haven't documented this well; - for some basic SPL it doesn't work (empty parameter collection); example: CREATE PROCEDURE calculate_balance (fAccNr char(16),fDate date, fType char(1) ) returning decimal (16,3); End Procedure; - when we create parameters collection with DeriveParameters (in this rare cases when it works) then ExecuteReader fails with "routine can not be resolved"; it appears that all output parameters are treated as input ones (there are more "?" generated placeholders than actual parameters); CSDK is 3.00 and 3.50. I've tried to send this issues to Informix support two months ago. Probably it haven't reached Informix yet as our internal (corporate) support works very slowly (only one guy who have rights to contact with Informix have other more important job too). [attachment "errdescr.zip" deleted by Sheshnarayan Agrawal/India/IBM] _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

On 29 Jul, 06:40, Sheshnarayan Agrawal <shagra...@in.ibm.com> wrote: > >>I've tried to send this issues to Informix support two months ago. > >>Probably it haven't reached Informix yet as our internal (corporate) > >>support works very slowly (only one guy who have rights to contact with > >>Informix have other more important job too). > > Defect has been logged. When you contact IBM Informix Tech Support you can > refer this defect ID idsdb00163467. > Note: The defect opening doesn't guarantee expedite resolution. You need > to contact IBM Informix tech support for speedy solution. > > -Shesh > > robsosno <robso...@gmail.com> > Sent by: informix-list-boun...@iiug.org > 28/07/2008 22:28 > > To > informix-l...@iiug.org > cc > > Subject > Re: DeriveParameters method fails if UDR parameter defined using LIKE > > Rich wrote: > > On 28 Jul, 16:04, Rich <informix.databa...@geos.com> wrote: > >> On 25 Jul, 15:51, Rich <informix.databa...@geos.com> wrote: > > >>> On 25 Jul, 15:37, Rich <informix.databa...@geos.com> wrote: > >>>> Ref: > > http://www-1.ibm.com/support/docview.wss?rs=681&context=SSVT2J&dc=DB5... > > > > > > >>>> Apparently upgrading to at last CSDK 3.00 should have fixed the > >>>> problems with DeriveParameters but alas I fear not. > >>>> I'm using CSDK 3.50.TC1 and I'm still having problems: > >>>> 1.  DeriveParameters fails with a "Specified cast is not valid" > >>>> exception when deriving a parameter from a UDR parameter defined > using > >>>> LIKE.  Columns defined as int seem fine but columns defined as > varchar > >>>> fail - I haven't tried other types yet. > >>>> 2.  A parameter called "RETURN" is always included as the first > >>>> parameter in the derived results, this is hard-coded but there's no > >>>> explanation as to what it is or why it's there? > >>>> 3.  A parameter called "Parameter1" is always appended to the list of > >>>> parameters, it appears completely spurious! > >>>> Although irritating I can work around #2 and #3 but #1 is damn > >>>> annoying! > >>>> IDS version 9.40.TC2. > >>>> Anyone tried using this stuff? > >>>> Thanks, > >>>> Rich > >>> Oh and while I'm in a grumbling mood, the coding example given in the > >>> manual wouldn't even compile if you tried it - does anyone check this > >>> stuff?! > >>> --------------- > >>> // IfxConnection - con > >>> IfxCommand cmd = new IfxCommand("SP_GETUSERINFO",con); > >>> IfxCommandBuilder cb = new IfxCommandBuilder(); > >>> Con.Open(); > >>> Cb.DeriveParameters(cmd); > >>> foreach (IfxParameter param in cmd.Parameters) > >>> { > >>>     Console.WriteLine(param.ParameterName);} > >>> con.Close(); > >>> --------------- > >>> 1.  DeriveParameters is a static method, not an instance method i.e. > >>> you cannot call it from a class instance. > >>> 2.  C# is case-sensitive so declaring the variable as "cb" and then > >>> trying to use "Cb" won't work, in fact it wouldn't even compile. > >>> (ditto the con/Con variable)- Hide quoted text - > >>> - Show quoted text - > >> ...the problems continue: > > >> 1.  Why do you ALWAYS get an "Unknown SQL Type - -110" exception if > >> the final parameter is an INT? > > >> 2.  Why is the final parameter not 'derived'?  i.e. it's always > >> missing from the collection. > > >> 3.  Why is lvarchar mapped to IfxType.Clob when there's a perfectly > >> reasonable IfxType.Lvarchar type?- Hide quoted text - > > >> - Show quoted text - > > Oh and DateTime doesn't appear to be supported at all - you get the > > Uknown SQL Type -110 exception. > > > (Apologies for the 'flood') > > I have similar experience: DeriveParameters works correctly only in few > cases. I've written code generator which for the given stored procedure > generates SPL call in C#. The intent is to automate generating > webservices based on SPL. > My first attempt to use only C# (and DeriveParameters) failed. In the > end I've used ESQL/C and DESCRIBE instead + some parameter parsing on my > own. > > What's wrong with DeriveParameters? Here is my list: >   - RETURN parameter not documented; possible this is because Microsoft > also haven't documented this well; >   - for some basic SPL it doesn't work (empty parameter collection); > example: >                  CREATE PROCEDURE calculate_balance (fAccNr char(16),fDate > date, fType > char(1) ) >                  returning                               decimal (16,3);>                  End Procedure; > >   - when we create parameters collection with DeriveParameters (in this > rare cases when it works) then ExecuteReader fails with "routine can not > be resolved"; it appears that all output parameters are treated as input > ones (there are more "?" generated placeholders than actual parameters); > CSDK is 3.00 and 3.50. > > I've tried to send this issues to Informix support two months ago. > Probably it haven't reached Informix yet as our internal (corporate) > support works very slowly (only one guy who have rights to contact with > Informix have other more important job too). > > [attachment "errdescr.zip" deleted by Sheshnarayan Agrawal/India/IBM] > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text - > > - Show quoted text - (forgot to include cdi on my earlier response, but I did thank Shesh for the response...just in case anyone thinks I'm ungrateful!) Another one to add to the list - doesn't seem to handle nulls, you'll get: [HY090] [Informix .NET provider]Invalid string or buffer length This isn't much fun.