Reply
Highlighted
White Belt
Posts: 9
Registered: ‎11-25-2016
Accepted Solution

SQL stored procedure does not work

Hi,

I've tried creating a procedure to spread revenue over 13 periods using MySQL as follows. Error's thrown at declaration/ setting variables. 

 

drop procedure if exists load_foo_test_data;

delimiter $$
create procedure load_foo_test_data()
BEGIN

Set @v_max = 13;
Set @v_counter =1;

start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end; $$

delimiter ;

call load_foo_test_data();

 

Please advice me on this .. thanks 


Accepted Solutions
Solution
Accepted by topic author ChatterBox
‎02-14-2017 06:56 AM
White Belt
Posts: 11
Registered: ‎01-06-2016

Re: SQL stored procedure does not work

 

In Domo each transform can only run a single command. You have, by my count, 5 commands trying to run and it will throw an error.

 

So here is your script, but I've marked each line that Domo will consider to be an individual command:


* drop procedure if exists load_foo_test_data;
* delimiter $$
* create procedure load_foo_test_data()
BEGIN
Set @v_max = 13;
Set @v_counter =1;
start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end; $$
* delimiter ;
* call load_foo_test_data();


I think you'll need to do three seperate transforms in Domo.

 

Transform 1:
DROP PROCEDURE IF EXISTS load_foo_test_data;

 

Transform 2:
CREATE PROCEDURE load_foo_test_data()
BEGIN
Set @v_max = 13;
Set @v_counter =1;
start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end;

 

Transform 3:
CALL load_foo_test_data();

 

Note: Once you've run the second transform, you cannot run it again unless you run the first one (to drop the procedure).

 

 

 

View solution in original post


All Replies
Solution
Accepted by topic author ChatterBox
‎02-14-2017 06:56 AM
White Belt
Posts: 11
Registered: ‎01-06-2016

Re: SQL stored procedure does not work

 

In Domo each transform can only run a single command. You have, by my count, 5 commands trying to run and it will throw an error.

 

So here is your script, but I've marked each line that Domo will consider to be an individual command:


* drop procedure if exists load_foo_test_data;
* delimiter $$
* create procedure load_foo_test_data()
BEGIN
Set @v_max = 13;
Set @v_counter =1;
start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end; $$
* delimiter ;
* call load_foo_test_data();


I think you'll need to do three seperate transforms in Domo.

 

Transform 1:
DROP PROCEDURE IF EXISTS load_foo_test_data;

 

Transform 2:
CREATE PROCEDURE load_foo_test_data()
BEGIN
Set @v_max = 13;
Set @v_counter =1;
start transaction;
while `v_counter` < `v_max` do
insert into bla bla bla....
set `v_counter`= `v_counter`+1;
end while;
commit;
end;

 

Transform 3:
CALL load_foo_test_data();

 

Note: Once you've run the second transform, you cannot run it again unless you run the first one (to drop the procedure).

 

 

 

White Belt
Posts: 9
Registered: ‎11-25-2016

Re: SQL stored procedure does not work

Thank you. This helped me get to the next step in solving the problem Smiley Happy

Announcements
Domo Dojo Day is May 4th! Mark your calendars for the next Domo Dojo Day! To learn more click here! Thanks!