Executing SQL Stored Procedures from WinCC OA

[vince2e]

Hello World!


Does anyone have experience executing Stored Proceduresin SQL from WinCC OA? No matter what I do, I keep getting this error:

WCCOAui (1), 2016.08.18 16:26:57.419, CTRL, SEVERE, 5/ctrl, Location of the following log entry:
Module: _QuickTest_
Panel: C:\\Siemens\\Automation\\WinCC_OA\\3.14\\Gill_Corp\\panels\\MAIN_OFFICIAL.pnl []
Object: 477 named: "PUSH_BUTTON3" of type: PUSH_BUTTON
Script: Clicked
Library: C:\\Siemens\\Automation\\WinCC_OA\\3.14\\scripts\\libs\\std.ctl
Line: 35
WCCOAui (1), 2016.08.18 16:26:57.419, SYS, SEVERE, 54, Unexpected state, rdbSelectRCT::dbOpenRecordset, Err: -1, Count: 1, Errnr: -2147217900, Nativ: 201, State: 37000, Descr: [Microsoft][SQL Server Native Client 11.0][SQL Server]Procedure or function 'spp_MicTableWorkOrder_Fetch' expects parameter '@WO', which was not supplied., Sql: EXEC dbo.spp_MicTableWorkOrder_Fetch
WCCOAui1:[dyn_dyn_anytype 0 items
WCCOAui1:]


//////////////////////////////////////////////////////////////////////////




// This is my code to execute the Stored Procedure

#uses "CtrlADO"
#uses "rdb.ctl"

Button()
{

dbConnection db;
string insertQuery;
dyn_dyn_anytype result;


// SQL Query
insertQuery = "EXEC dbo.spp_MicTableWorkOrder_Fetch '303774' " ;

// Open db connection
rdbOpen(db);

// Select from db
rdbSelect(db, insertQuery, result);

//Display Results
DebugN(result);




rdbClose(db);

}

///////////


Please help! Thanks

[vogler]

use rdbExecute instead of rdbSelect!
regards,
Andreas

[vince2e]

Thanks Andreas! Using rdbExecute() and defining my parameters solved the issue.

I changed the code to what is shown below and it worked (note: all parameters have to be on one line)


#uses "CtrlADO"
#uses "rdb.ctl"

Button()
{

dbConnection db;
string insertQuery;
dyn_dyn_anytype result;


// SQL Query
insertQuery = "EXEC dbo.spp_MicTableWorkOrder_Fetch @WO = 333333,@Lot= 1, @PartNumber = 2,@PartDescription = 'test',@Quantity =1,@UM='t',@DesiredDate = '2016.07.18',@DueDate='2016.07.18',@Thickness = 0.5,@Density= 44.4,@Customer= 'u',@CustomerPO= 'r',@CustomerPartNumber = 'f',@Specification = 's'";



// Open db connection
rdbOpen(db);

// Execute Query in db
rdbExecute(db, insertQuery);

//Display Results
// DebugN(result);




rdbClose(db);

}

[vince2e]

How about returning data from the stored procedure? Is there a way to do this without using rdbSelect()?

[vogler]

Hi,
I don't know how this can be done in MS-SQL Server, but in Oracle you can execute a function within a select statement:
rdbSelect("SELECT yourFunctionName(1, 2, 3) FROM DUAL")
then you can get a result set with one row and one column, the return value of a function.

i think getting output parameter does not work with OA & CtrlADO (but maybe there are new functions which i do not know)

Some time ago we did it in that way: The stored procedure will store result values in Oracle package variables. After calling the function the result values can be queried with "SELECT package.result_1, package.result_2, ... FROM DUAL" ... it's a Workaround.

regards,
Andreas

[tcoressel]

I am able to connect and query a db on my local server

rdbDefRdb = "DRIVER=SQL Server;server=DESKTOP-EK4OGNV;Database=MTI_EQUIP;UID=;PWD=;"; //connection string


however I am unable to connect to a remote server named TIMCORESSEL-PC with the same db.


rdbDefRdb = "DRIVER=SQL Server;server=TIMCORESSEL-PC;Database=MTI_EQUIP;UID=;PWD=;"; //connection string


Any suggestions?