Run MySQL stored procedure in SC

Developers Programming
#1

Can someone please tell me how to run a stored procedure in SC. I want the procedure to pick values from a form.
so rather than having a number for example 12212345. I want it to be {number}, is this possible?

Here is my procedure (I need to replace 79090200 with {from} and 79090233 with {to}):
DELIMITER $$

CREATE DEFINER=root@localhost PROCEDURE sp_increment()
BEGIN

DECLARE start_val INT;
DECLARE end_val INT;

SET start_val=79090200;
SET end_val=79090233;

WHILE start_val <= end_val DO
INSERT INTO awb (gsa, no, status, aid) VALUES(000, start_val, “N”, 0);
SET start_val = start_val + 11;
END WHILE;

END

Any help would mean a lot.

#2

Hi,
this should work.

DELIMITER $$

CREATE DEFINER=root@localhost PROCEDURE sp_increment(IN start_val INT, IN end_val INT)
BEGIN

WHILE start_val <= end_val DO
INSERT INTO awb (gsa, no, status, aid) VALUES(000, start_val, “N”, 0);
SET start_val = start_val + 11;
END WHILE;

END

In you app: sc_exec_sql(“CALL sp_increment({from}, {to})”);

jsb

#3

[QUOTE=jsbinca;21982]Hi,
this should work.

DELIMITER $$

CREATE DEFINER=root@localhost PROCEDURE sp_increment(IN start_val INT, IN end_val INT)
BEGIN

WHILE start_val <= end_val DO
INSERT INTO awb (gsa, no, status, aid) VALUES(000, start_val, “N”, 0);
SET start_val = start_val + 11;
END WHILE;

END

In you app: sc_exec_sql(“CALL sp_increment({from}, {to})”);

jsb[/QUOTE]

Thank you so much. Works perfectly. You my friend are a life saver!

Just another question if you dont mind. Can you please tell me how I can also get the gsa value from the form?

Thanks.

#4

Just add it to your procedure:

DELIMITER $$

CREATE OR ALTER PROCEDURE sp_increment(In gsa_val INT, IN start_val INT, IN end_val INT)
BEGIN

WHILE start_val <= end_val DO
INSERT INTO awb (gsa, no, status, aid) VALUES(gsa_val, start_val, “N”, 0);
SET start_val = start_val + 11;
END WHILE;

END

In you app: sc_exec_sql(“CALL sp_increment({gsa}, {from}, {to})”);

jsb

#5

Thank you. I knew it was something like that. I mixed up the sequence at the start that’s why it didnt work.

Once again a super thank you.

#6

Is there any way to count the number of records entered from the stored procedure and display that in a message?

Thanks

#7

Here we go.

Change your procedure (see the variable @vz)

CREATE PROCEDURE sp_increment(In gsa_val INT, IN start_val INT, IN end_val INT)
BEGIN

SET @vz = 0;

WHILE start_val <= end_val DO
INSERT INTO awb (gsa, no, status, aid) VALUES(gsa_val, start_val, “N”, 0);
SET start_val = start_val + 2;

SET @vz = @vz+1;

END WHILE;

SELECT @vz AS cnt;

END

In your application.

sc_lookup(ins,“CALL sp_increment({gsa}, {from}, {to})”);
if(isset({ins[0][0]}))
{
echo 'Number of inserted records: '.{ins[0][0]};
}

jsb

#8

JSB, you my friend are the best! Thank you.

UPDATE: Everything works fine, however I get the following error message after the insert (the number or records inserted also shows up here, you can see the attached image):

Database access
Commands out of sync; you can’t run this command now

error.png

#9

Hi,
check if your db-user has sufficient rights (execute). Even though the message text suggests something else. I’ve been there. :slight_smile:
To isolate the problem, try your procedure call on a control form. Put your call in onValidate and see what happens.

jsb

#10

Hi there,

Thanks for all the help.

I tried it on a control form. Still getting the same error. However now its letting me view the sql query, that according to Sc may be causing the problem. Its the select query used to pull data for a drop down on the control form.

I’ll try removing the select query and see what happens.

error2.jpg

#11

Update: Yup, its being caused by the select field!

#12

Hi there. I haven’t figured out whats going on with the select. However I wanted to ask for your help on another matter, only if you have the time.

I have created another procedure along the lines you mentioned.
Here is my procedure:


DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_awbreturn`(IN `gsa_val` int, IN `start_val` int, IN `end_val` int, IN `agent_val` int, IN `orig_date` datetime)
BEGIN

SET @vz = 0;

WHILE start_val <= end_val DO
INSERT INTO awb_returned (awb_pre, awb_no, return_date, aid, orig_issue_date) VALUES(gsa_val, start_val, NOW(), agent_val, orig_date);
SET start_val = start_val + 11;

SET @vz = @vz+1;

END WHILE;

SELECT @vz AS cnt;

END

For some reason it doesnt update the table
Here is the code from the form:

sc_lookup(ins,"CALL sp_awbreturn({gsa}, {from}, {to}, {agent}, {origdate})");
if(isset({ins[0][0]}))
{
echo 'AWBs Returned: '.{ins[0][0]};
}

Am I doing something wrong? As always any help would mean a lot. Thanks

#13

Since DATE/DATETIME is handled as a string in MYSQL you have to encapsulate your date field ({origdate}) in single quotation marks.

sc_lookup(ins,"CALL sp_awbreturn({gsa}, {from}, {to}, {agent}, '{origdate}')");

jsb

#14

I see. Thank you for all the help. Much appreciated.

#15

and then, how can I, invoque a Mysql Stored procedure from any scriptcase aplication, I need to know the syntax , i have almost an eternety, trying but no succes