Uipath DB2 Stored procedure

[icehouselux]

I have a stored procedure in DB2 with three parameters, I wanted to call that. I get the following error

Run query: ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0469 -IN, OUT, or INOUT not valid for parameter 1 in procedure XXXXXXXXXX in XXXXXX.
ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7973 - SQL create package for UIPATHAFBA in QGPL has failed.

[loginerror (Maciej Kuźmicz)]

Hi @icehouselux

Could you please share the way you configured this in Studio? Also, could you double check with the Database 1.9.0 package version?

[icehouselux]

@loginerror I have configured using the following connection string. I tested the connection and it is successfull.

“Driver={iSeries Access ODBC Driver};system=XXXXXXXXXXXXXXX;uid=XXXXXXXXXXX;pwd=XXXXXXXXXXX”

I tried the same with database 1.9.0 package version. Same error

[loginerror (Maciej Kuźmicz)]

Thank you. I would also appreciate some screenshots of how the Run Query activity is configured

[icehouselux]

@loginerror

[loginerror (Maciej Kuźmicz)]

Would it be possible to share a mock of the query without any sensitive data in it, and also show how the parameters are configured? That’s the most important part

The simplest mock query that would reproduce the issue is fine.

[icehouselux]

@loginerror Here is the query I have

“{Call StoredProcedureName(‘S*’,‘’,‘’)}”

[icehouselux]

@loginerror parameters are configured like this.

[icehouselux]

@loginerror when I changed the call to “{Call StoredProcedureName(Parm1, Parm2, Parm3)}” I get the following error…

System.Data.Odbc.OdbcException: ERROR [42S22] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0206 - Column or global variable XXXX not found.
ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0206 - Column or global variable XXXX not found.[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7973 - SQL create package for UIPATHAFBA in QGPL has failed. at UiPath.Database.Activities.ExecuteQuery.HandleException(Exception ex, Boolean continueOnError)
at UiPath.Database.Activities.ExecuteQuery.ExecuteInternalAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
at UiPath.Database.Activities.AsyncTaskCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

[loginerror (Maciej Kuźmicz)]

Hi @icehouselux

A bit of a slower reply, but I had to try a few things.

Note: I had to guess what the stored procedure was that caused the original issue, but eventually trimmed it down to something like this:

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE StoredProcedureName (
    INOUT param1 VARCHAR(255),
    INOUT param2 CHAR(1),
    INOUT param3 CHAR(1) 
)
P1: 
  BEGIN
     SET param1 = param1||'test';
     SET param2 = 't';
     SET param3 = 'e';
  END
--#SET TERMINATOR ;

I then tried to call this from Studio and would indeed get a similar error to yours when trying to call it with:
"CALL StoredProcedureName('test*',?,?)"

My error:

01/20/2025 15:42:50 Run Command: ERROR [42886] [IBM][CLI Driver][DB2/NT64] SQL0469N The parameter mode OUT or INOUT is not valid for a parameter in the routine named "STOREDPROCEDURENAME" with specific name "SQL250120153853129" (parameter number "1", name "PARAM1"). SQLSTATE=42886

It isn’t the same exact error, but I failed at reproducing your exact one and continued from here.

My notes:

• it looks like one cannot write to a System.Char (a separate casting error occurs on runtime), but using a String variable when configuring the parameters fixed that:
  
  
• when calling the stored procedure, I only used question marks like so:
  "CALL StoredProcedureName(?,?,?)"
• to pass something into the stored procedure as part of the IN OUT variable, I assigned the original value to the first argument with the Assign activity:
  
  

For me, this made this query work, and it correctly appended the first INOUT variable as per my sample query:

Could you please let me know if this helps in your case?

[icehouselux]

@loginerror yes it did! Thanks for your help