Stored procedures and variable length parameters

[Guest.Visitor]

We are currently testing a VB program that calls a stored procedure on the AS/400. The stored procedure expects to receive one parameter of VARYING type. I've coded an *ENTRY PLIST on my program and defined the incoming parameter as 35 VARYING. On the VB (Client) side the parameter is defined as VARCHAR (35). If the VB program populates this parameter with anything less than 35 characters, the stored procedure DOES NOT return any records (record sets). Currently, the VB program is having to append "blanks" to the end of the parameter to make it work. Can someone tell me What are we doing wrong? TIA

[Guest.Visitor]

Alan, Have you tried to see what is passed using debug? I have been able to debug stored procedures by servicing the server job and starting debug. Then repeatedly calling the server until it hits the one I am servicing. You could also add a message or something into the server that you wait on. You may also have to change the timeout for user functions to *nomax (I do not know if this value is used for procedures). You should be able to do this using operations navigator. From the database tab, select query options and select your server job. Specify a library for your query options file and set the UDF_TIME_OUT to *MAX. David Morris

[Guest.Visitor]

David, I've added logic to the stored procedure on the AS/400 side to print the content and length of the parameter received from the client program. I noticed, that even though the parameter on the client side is defined as VARCHAR (35) the length of the receiving variable is always 35. I use the BIF %Len to retrieve the actual lenght of the variable. I should point out, that at the current time the stored procedure is not registered. Even when I did register the stored procedure and specified the parm as VARCHAR the stored procedure would NOT return any records. Apparently, the program is receiving more than just the data used to select records. This is the first time I venture into stored procedures, would you be so kind as to provide me with the CREATE PROCEDURE statement that needs to be run on the AS/400? The stored procedure in written in SQLRPGLE. Thanks

[Guest.Visitor]

Problem solved! For the benefit of others, here is the CREATE PROCEDURE statement. CREATE PROCEDURE lib/proc_name (IN parm_name VARCHAR(35)) RESULT SETS 1 LANGUAGE RPGLE READS SQL DATA EXTERNAL NAME lib/ext_pgm_name PARAMETER STYLE GENERAL