DBHelper.ExecuteStoredProcedure

DBHelper.ExecuteStoredProcedure

  
Hi there,

I have an integration with an external oracle database. In this database I must get the result of an output parameter from an existing procedure. I'm using an Integration Studio action.

In order to get this information I use the code:

IDbTransaction tran = DBTransactionManager.Current.GetTransaction(ssConnectionName);
 
            try
            {
                IDbCommand cmd = DBHelper.CreateCommand(tran, ssProcedureName);
                cmd.CommandType = CommandType.StoredProcedure;
                
                DBHelper.AddParameter(cmd, "INparam1", DbType.String, ssPackageList);
                DBHelper.AddParameter(cmd, "INparam2", DbType.Int32, ssDetType);
                IDbDataParameter outParam = DBHelper.AddParameter(cmd, "OutParam", DbType.String);
                outParam.Direction = ParameterDirection.Output;
                DBHelper.ExecuteStoredProcedure(cmd, DBKind.Oracle, "READERNAME"); // Don't know what to put in this parameter
                
                ssParamOut= (String)outParam.Value;
                
            }
            catch (Exception e)
            {
                ssSuccess = false;
                ssMessage = "Ocorreu um erro ao tentar obter ...";
            }
            finally
            {
 
                DBTransactionManager.Current.ReleaseTransaction(tran);
 
            }
I don't know what to put in the parameter "ReaderName" of the line "DBHelper.ExecuteStoredProcedure(cmd, DBKind.Oracle, "READERNAME"), and I'm getting the following error because of that:

Message: Reader variable must be provided.Source: OutSystems.HubEdition.RuntimePlatformStack Trace:    at OutSystems.HubEdition.RuntimePlatform.DBHelper.ExecuteStoredProcedure(IDbCommand cmd, DBKind dbKind, String readerVarName, Boolean skipLog)
   at OutSystems.NssEXT_PQA_HVST.CssEXT_PQA_HVST.MssGetDependentPackages(String ssPackageList, Int32 ssDetType, String ssOwner, String ssPackageName, String ssConnectionName, String& ssDependenciesList, Boolean& ssSuccess, String& ssMessage)


Anyone had the same problem? Thanks.
Hello Nelson,

The DBHelper API is really an internal API, which is not documented and supported.

With that being said, for your use-case  you should use the ExecuteNonQuery() method. You can see examples of how to use the connections in this post.

If it doesn't help you, feel free to reply with your new challenges.
Thanks.

I've already read the post you talk about.

The solutions is using ExecuteNonQuery() like you said before.


 
In the following code smaple there is mentioned  DbType.String. Where can I find these Types in the API?
 
DBHelper.AddParameter(cmd, "INparam1", DbType.String, ssPackageList);

We are using the Java stack :)
 
Thanks.
 
Kind regards,
Niek.
You should use java.sql.Types enumeration to fill in values of that particular parameter. For example, if you are using an integer parameter, you would pass java.sql.Types.INTEGER to DbHelper.addParameter() .