You are not logged in.
Hi, I need some help with the tmysqlsp component;
I have the following stored procedure;
DROP PROCEDURE IF EXISTS `reg_scmyds2`.`TEST_GETADDRESS1_10`$$
CREATE DEFINER=`root`@`%` PROCEDURE `reg_scmyds2`.`TEST_GETADDRESS1_10`(IN c_account_code VARCHAR(12), OUT address_line1 VARCHAR(50))
SELECT addr.address_line1 into address_line1
FROM address addr, account ac
WHERE ac.account_code = c_account_code
AND addr.address_id = ac.service_address_id;
and am attemtping to call this procedure by the attached job design.
The schema of the input file is the one input column: c_account_code
The schema of the output file is the outbound column: address_line1
I get this error when I try to run;
Starting job stored_proc_read at 10:30 18/03/2009.
[trace] connecting to socket on port 4476 ...
[stat] connecting to socket on port 3907 ...
DBI::db=HASH(0x1fd9064)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at C:\TOS-Win32-r21383-V3.0.3\workspace\.Perl\IX_LOGS_Q1.job_stored_proc_read_0.1.pl line 584.
Job stored_proc_read ended at 10:30 18/03/2009. [exit code=0]
I think the problems are in the schemas, but can not work it out, Any help would be appreciated.
Heres a better image to show what I currently have, you can see from the run stats that the input value is the only value being passed out of the stored procedure call, whereas I would expect the value being returned by the procedure to be here.
I have simplified the procedure drop/creation and everything works well:
in tMysqlRow_2, the query is:
'DROP PROCEDURE IF EXISTS `TEST_GETADDRESS1_10`'
in tMysqlRow_1, the query is:
' CREATE PROCEDURE `TEST_GETADDRESS1_10`( IN c_account_code VARCHAR(12), OUT address_line1 VARCHAR(50) ) BEGIN SELECT length(c_account_code) into address_line1; END '
The output of my job is:
Starting job topic5875 at 18:08 18/03/2009. .------------------------. | tLogRow_1 | +----------+-------------+ | code | code_length | +----------+-------------+ | big code | 8 | '----------+-------------' Job topic5875 ended at 18:08 18/03/2009. [exit code=0]