Hi,
I have vRA 8.9.1 and have been tried to find a way to execute MS SQL Stored procedure with output parameter and use that value on a XaaS form.
I am missing something and not getting either syntax correctly or my logic is completely wrong.
The following code works perfectly fine when executing MS SQL UDF and getting back value:
var query = "DECLARE @return_value int; EXEC @return_value = [dbo].[UDF_Fetch_VALUES]'" + parameter1+ "','"+parameter2+"',"+parameter3+"; SELECT 'Return value' = @return_value";
var rowsAffected = db.readCustomQuery(query);
var receievedValue;
for each(item in rowsAffected){
receievedValue=item.getProperty("Return value");
}
return receievedValue
I am not able to do similar with SP.
My SP looks like this and of course it works perfectly if you execute it SQL Management studio:
DECLARE @return_value int
EXEC @return_value = [dbo].[DB_Instance_available_values]
@inst_db_lc = N'DB_INSTANCE_name',
@Des_value_Count = number_value
SELECT 'Return Value' = @return_value
I tried to use something I found online, like:
Call Database Stored Procedures via JDBC (vcoportal.de)
or
Executing Stored Procedures with Output Parameters through vRO | Cloud Brokers
But I don't find a way how to handle those parameters.
If someone has a working piece of code or has some tip how to do it properly, would be appreciated.
Thanks
Hi
I'm not clear on whether your procedure takes parameters? If so, the thing you need to be aware of is that in the line
var ps = con.prepareCall("{call nameOfStoredProcedure(?,?)}");
Each of the '?' characters corresponds to a parameter to your procedure. Then, in each parameter case you use
ps.setString(position, value)
Hi
Maybe this will help you in the right direction, this works for me:
var sqlQuery = "EXEC <<stored procedure name>> '" + <<some input value>> + "'";
var receivedValues = serverDatabase.readCustomQuery(SQLQuery);
// Loop through output from stored procedure
for each ( receivedValue in receivedValues ){
System.log("receivedValue : " + receivedValue);
// Returns something like this:
// DynamicWrapper (Instance) : [SQLActiveRecord]-[class com.vmware.o11n.plugin.database.ActiveRecord] -- VALUE : ActiveRecord: {COUNT=1}
// So in my case the record name is 'COUNT'
System.log("output : " + receivedValue.getProperty("COUNT"));
};
Looking at the code from vcoportal again
The section in red details the handling of the ResultSet which is returned by the execution of the call to the procedure. You can see the documentation for ResultSet here (from vroapi.com)
Anyway, since your procedure returns ONE value, you can access it by doing this
var resultSet = callStm.executeQuery();
var procedureReturnValue = null;
if(rs.next()) // if the result set has content
{
procedureReturnValue = rs.getString(1); // get the result at position 1 in the first row
}
rs.close();
callStm.close();
Note, you should use all of the try/catch logic as depicted in the example, just fit your result set handling inside where the comment says "Process the results...."
The other posted example will also work so you have your choice 🙂
-HTH
Hi,
DB team had to modify SP and declare output parameter properly.
Now this code works:
var cstmt = con.prepareCall("{call dbo.DB_Instance_available_value_check(?,?,?)}");
cstmt.setString(1,parameter1);
cstmt.setString(2, parameter2);
//3 - index of the output parameter, 12 = varchar
cstmt.registerOutParameter(3, 12);
cstmt.execute();
var output = cstmt.getString(3);
return output;
I hope this will help someone in the future.
Thanks for the response! Luckily managed to make it work after SP was modified by DB team.