Re: Error in Create Procedure statement? Where? -- MIDRANGE-L

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