Example 4
In the
simple_stored_procedure_call_with_parameters
thread, the
GetPatientById
stored procedure is called with:
{call GetPatientById(patients.id, fname OUT, lname OUT, dob OUT)}
The input parameter is patients.id and has
a value of 1
. This fails.
Testing Tool result:
outbound db thread
Test with MESSAGE 1
1
Running DB Write action failed.
SQL result:
{call GetPatientById(IN Id, OUT fname, OUT lname, OUT dob)}
Oct 31, 2014 11:18:52 AM com.lawson.cloverleaf.dbprotocol.CDBStoredProcedure execDbWrite
SEVERE: boo!
Stored procedure:
CREATE PROCEDURE `GetPatientById`(IN p_id INT, OUT p_fname CHAR, OUT p_lname CHAR, OUT p_DOB CHAR)
BEGIN
SELECT fname, lname, DOB
FROM patients
WHERE id = p_id;
END
In the example above, the stored procedure definition is incorrect, so use:
CREATE PROCEDURE `test`.`GetPatientById`(IN p_id INT, OUT p_fname VARCHAR(48), OUT p_lname VARCHAR(48), OUT p_DOB VARCHAR(48))
BEGIN
SELECT fname, lname, DOB into p_fname, p_lname, p_DOB FROM patients WHERE id = p_id limit 1;
END
Testing Tool result:
Command Issued: hcidbprotocoltest -e ASCII -f nl simple_sp_call1 C:/cloverleaf/cis6.2/integrator/mysql/data/data.dat
Command output:
outbound db thread
Test with MESSAGE 1
1,John,Smith,2014
The output value of <GetPatientById.fname> is: John
The output value of <GetPatientById.lname> is: Smith
The output value of <GetPatientById.dob> is: 250173
Running DB Write action succeeded.
SQL result:
{call GetPatientById(1, fname OUT, lname OUT, dob OUT)}