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)}