You are not logged in.
Announcement
Unanswered posts
|
#1 2009-03-18 11:53:07
- cagey76
- Member
- Registered: 2008-02-19
- Posts: 52
tmysqlsp
Hi, I need some help with the tmysqlsp component;
I have the following stored procedure;
DELIMITER $$
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))
BEGIN
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;
select address_line1;
END $$
DELIMITER ;
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 ...
[trace] connected
[stat] connecting to socket on port 3907 ...
[stat] connected
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.
Offline
#4 2009-03-18 17:05:41
- cagey76
- Member
- Registered: 2008-02-19
- Posts: 52
Re: tmysqlsp
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.
Cheers
Offline
#6 2009-03-18 18:15:00
Re: tmysqlsp
I have simplified the procedure drop/creation and everything works well:
in tMysqlRow_2, the query is:
Code:
'DROP PROCEDURE IF EXISTS `TEST_GETADDRESS1_10`'
in tMysqlRow_1, the query is:
Code:
'
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:
Code:
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]
Offline