× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.