Re: Error in Create Procedure statement? Where?

[Thomas Garvey <tgarvey@xxxxxxxxxx>]

Ok, found the real problem with that Create Procedure statement that was 
generating the 'statement contains wrong number of values' error.
The problem is the @ZTREF parameter name. Name it anything else and it 
works. I had looked up the restricted names for SQL and can't find how 
it matched any, but that's how I fixed it.
Perhaps it's the REF portion, cause I can call it Garvey, or ZTREX, or a 
bunch of other stuff, and it works. Thanks, IBM. Only took me 1.5 days.

Best Regards,

Thomas Garvey


On 11/12/2020 8:46 AM, Thomas Garvey wrote:
> OK, there must be something for me to learn here. Not sure what.
> I am using the drop procedure command, followed by the create 
> procedure command, because this is running on a v5r4 server and the 
> 'create or replace procedure' does not work there.
> Also, I have written hundreds of these stored procedures, both SQL and 
> RPGLE, and they do exist as objects in the named library as specified 
> in the create procedure command. When debugging, I can add them to the 
> list of objects to debug and step through them, see variables, etc. 
> I've debugged the generated C code and the SQL code.
>
> The messages generated by the RUNSQLSTM command includes the result 
> that the Drop Procedure command worked. Are you saying that the 
> generated object is removed but somehow the SQL internal name is not, 
> and then when the Create Procedure command runs it tries to use the 
> same SQL internal name again?  If this is what you are saying, could 
> the error message generated (statement contains wrong number of 
> values) be more misleading?
>
> Here's something else that's weird... If I remove the @ZTREF input 
> parm, the RUNSQLSTM command (including the drop procedure command) 
> compiles every time.
>
> Now, to attempt to test what I think you're trying to teach me, I 
> issued the drop procedure command separately (using interactive SQL), 
> then removed the drop procedure command from the source member (so it 
> starts with just the create procedure command). The RUNSQLSTM command 
> then worked!
>
> Can you reconcile why removing a parm from the list, and not doing a 
> drop then create in one source member for a RUNSQLSTM command, produce 
> the same result?
>
> Best Regards,
>
> Thomas Garvey
>
>
> On 11/12/2020 1:25 AM, Niels Liisberg wrote:
> > Sql procedures are polymorphic  . That means that the same procedure 
> > name
> > can be used In a number of different implementations ... handy is you 
> > ie.
> > swap dates and some times the date is a real date in SQL and some times
> > maybe packed 8. Now this feature bites you since you are dropping the
> > procedure Version without parameters and on next line reusing the 
> > name with
> > parameters... so in your case I would add the “specific” key word - both
> > when you create and drop - the specific is the object name in IBM i. 
> > And my
> > guess is you are thinking of the procedure name is also the object name
> > (hence the lib / name qualifications you are using) .. but that is 
> > not the
> > case - procedure names are sql internal and will result in a “random”
> > object name. But you can controls the “randomness” by the “specific “
> > keyword
> >
> > Hop it helps
> > Niels
> >
> >
> > tor. 12. nov. 2020 kl. 00.42 skrev Thomas Garvey <tgarvey@xxxxxxxxxx>:
> >
> > > I can't afford to lose any more hair off this old head.
> > > Can someone please tell me what's wrong with this SQL Procedure code?
> > >
> > > Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 .
> > >         1 Drop   Procedure    BIO610F/ZXUZSC ;
> > >         2 Create Procedure    BIO610F/ZXUZSC (
> > >         3     in @XUUSER char(10),
> > >         4     in @XUTYPE          char( 1),
> > >         5     in @XUATTN          char( 1),
> > >         6     in @XULANG          char( 3),
> > >         7     in @XUAPRD          char( 1),
> > >         8     in @XUCLAS          char(10),
> > >         9     in @XUGRUP          char(10),
> > >        10     in @ZWHSE           char(80),
> > >        11     in @ZCOMP           char(80),
> > >        12     in @ZTREF           char(80),
> > >        13     in CallingProgram   char(10),
> > >        14     in CopyFromUserID   char(10)
> > >        15     )
> > >        16     language SQL
> > >        17     set option dbgview = *source
> > >        18
> > >
> > > And here's the error produced when RUNSQLSTM is executed...
> > >
> > > MSG ID SEV  RECORD  TEXT
> > > SQL7990   0       1  Position 1 DROP PROCEDURE statement complete.
> > > SQL0117  30       2  Statement contains wrong number of values.
> > >
> > > --
> > >
> > > Best Regards,
> > >
> > > Thomas Garvey
> > >
> > >
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) 
> > > mailing list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at https://archive.midrange.com/midrange-l.
> > >
> > > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription 
> > > related
> > > questions.
> > >
> > > Help support midrange.com by shopping at amazon.com with our affiliate
> > > link: https://amazon.midrange.com