stored procedure

--0-1294968111-1112413157=:14785
Content-Type: text/plain; charset=us-ascii

Hi I wanted to use stored procedure in mysql 5.0.0 beta version.I was successful trying it but know i want to know how to pass arguments into stored procedure .

I found out that triggers are omly suppoted from version 5.1.* .Has Mysql 5.1 has come to the market.I yes where can i find it b'couz i couldnot find it on the net.

Can i store an mage in mysql database.How can i retrieve it from php.Please reply soon.



bye


---------------------------------
Yahoo! Messenger
Show us what our next emoticon should look like. Join the fun.
--0-1294968111-1112413157=:14785--

RE: Stored Procedure

I think what you are trying to do boils down to this

INSERT INTO ar_no_ins_outs_delete
(doc_type,doc_no,ref_doc_no,gv_dollar_amt)
SELECT t1.doc_type, t1.doc_no, t1.ref_doc_no,
concat('-',t1.gv_dollar_amt)
FROM test.ar_no_ins_outs_temp AS t1, test.ar_no_ins_outs_temp AS t2
WHERE t1.doc-type in ('PV','GV')
AND t2.doc_type in ('RG','RC','RT')
AND t1.doc_type =3D t2.doc_no
AND t1.ref_doc_no =3D t2.ref_doc_no
AND concat('-',t1.gv_dollar_amt) =3D t2.gv_dollar_amt

It is always better to use joins than to use cursors. Cursors should
always be a last resort. Joins are MUCH more efficient.

If gv_dollar_amt is a numeric type then you should replace
concat('-',t1.gv_dollar_amt) with -gv_dollar_amt.

You should check that I have not mixed up any of the column names
because I can't test this.

John B.

-----Original Message-----
From: Melissa Dougherty [mailto:melissa@cse-corp.com]=20
Sent: Friday, 16 June 2006 4:46 AM
To: win32@lists.mysql.com
Subject: Stored Procedure

I'm still new to the MySQL SPs.... Does anyone see a problem with this
code?

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test_AuditRun_SP`$$
CREATE PROCEDURE `test`.`test_AuditRun_SP`(OUT OblDocNo varchar(50), OUT
OBLCNT INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE Obldoctype varchar(10);
DECLARE Obldocno varchar(50);
DECLARE Oblrefdocno varchar(50);
DECLARE Obldollamt varchar(50);
DECLARE Recdoctype varchar(10);
DECLARE Recdocno varchar(50);
DECLARE Recrefdocno varchar(50);
DECLARE Recdollamt varchar(50);
DECLARE curObl1 CURSOR FOR SELECT
doc_type,doc_no,ref_doc_no,concat('-',gv_dollar_amt) FROM
test.ar_no_ins_outs_temp WHERE doc_type in ('PV','GV');
DECLARE curRec2 CURSOR FOR SELECT
doc_type,doc_no,ref_doc_no,gv_dollar_amt FROM test.ar_no_ins_outs_temp
WHERE doc_type in ('RG','RC','RT');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =3D 1;
=20
OPEN curObl1;
OPEN curRec2;

REPEAT
FETCH curObl1 INTO Obldoctype, Obldocno, Oblrefdocno, Obldollamt;
FETCH curRec2 INTO Recdoctype, Recdocno, Recrefdocno, Recdollamt;

IF NOT done THEN

IF obldocno =3D recdocno and Oblrefdocno =3D Recrefdocno and
Obldollamt =3D Recdollamt THEN

INSERT into ar_no_ins_outs_delete
(doc_type,doc_no,ref_doc_no,gv_dollar_amt)
values (Obldoctype, Obldocno, Oblrefdocno, Obldollamt);

END IF;
END IF;
=20
UNTIL done END REPEAT;

CLOSE curObl1;
CLOSE curRec2;

END$$

DELIMITER ;


Thanks,

Melissa=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: Stored Procedure

I wish that would work.... but I really need to loop through a set of
records and evaluated the results. I need to look at the doc_no, then
doc_type, then check to see if the gj_dollar_amt are equal or sum to
zero.... and a couple more things. If I really need to use two cursors...
how do I setup the SP? I can seem to get the correct sequence for using two
cursors. I can get one cursor to work just fine.

Thanks for any help.

Melissa


----- Original Message -----
From:
To: ;
Sent: Sunday, June 18, 2006 7:06 PM
Subject: RE: Stored Procedure


>I think what you are trying to do boils down to this
>
> INSERT INTO ar_no_ins_outs_delete
> (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
> SELECT t1.doc_type, t1.doc_no, t1.ref_doc_no,
> concat('-',t1.gv_dollar_amt)
> FROM test.ar_no_ins_outs_temp AS t1, test.ar_no_ins_outs_temp AS t2
> WHERE t1.doc-type in ('PV','GV')
> AND t2.doc_type in ('RG','RC','RT')
> AND t1.doc_type = t2.doc_no
> AND t1.ref_doc_no = t2.ref_doc_no
> AND concat('-',t1.gv_dollar_amt) = t2.gv_dollar_amt
>
> It is always better to use joins than to use cursors. Cursors should
> always be a last resort. Joins are MUCH more efficient.
>
> If gv_dollar_amt is a numeric type then you should replace
> concat('-',t1.gv_dollar_amt) with -gv_dollar_amt.
>
> You should check that I have not mixed up any of the column names
> because I can't test this.
>
> John B.
>
> -----Original Message-----
> From: Melissa Dougherty [mailto:melissa@cse-corp.com]
> Sent: Friday, 16 June 2006 4:46 AM
> To: win32@lists.mysql.com
> Subject: Stored Procedure
>
> I'm still new to the MySQL SPs.... Does anyone see a problem with this
> code?
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `test`.`test_AuditRun_SP`$$
> CREATE PROCEDURE `test`.`test_AuditRun_SP`(OUT OblDocNo varchar(50), OUT
> OBLCNT INT)
> BEGIN
> DECLARE done INT DEFAULT 0;
> DECLARE Obldoctype varchar(10);
> DECLARE Obldocno varchar(50);
> DECLARE Oblrefdocno varchar(50);
> DECLARE Obldollamt varchar(50);
> DECLARE Recdoctype varchar(10);
> DECLARE Recdocno varchar(50);
> DECLARE Recrefdocno varchar(50);
> DECLARE Recdollamt varchar(50);
> DECLARE curObl1 CURSOR FOR SELECT
> doc_type,doc_no,ref_doc_no,concat('-',gv_dollar_amt) FROM
> test.ar_no_ins_outs_temp WHERE doc_type in ('PV','GV');
> DECLARE curRec2 CURSOR FOR SELECT
> doc_type,doc_no,ref_doc_no,gv_dollar_amt FROM test.ar_no_ins_outs_temp
> WHERE doc_type in ('RG','RC','RT');
> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
>
> OPEN curObl1;
> OPEN curRec2;
>
> REPEAT
> FETCH curObl1 INTO Obldoctype, Obldocno, Oblrefdocno, Obldollamt;
> FETCH curRec2 INTO Recdoctype, Recdocno, Recrefdocno, Recdollamt;
>
> IF NOT done THEN
>
> IF obldocno = recdocno and Oblrefdocno = Recrefdocno and
> Obldollamt = Recdollamt THEN
>
> INSERT into ar_no_ins_outs_delete
> (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
> values (Obldoctype, Obldocno, Oblrefdocno, Obldollamt);
>
> END IF;
> END IF;
>
> UNTIL done END REPEAT;
>
> CLOSE curObl1;
> CLOSE curRec2;
>
> END$$
>
> DELIMITER ;
>
>
> Thanks,
>
> Melissa
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=melissa@cse-corp.com
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Stored Procedure

I just remember another issue with declaring the cursors.... I want to use
the doc_no from the first cursor as a variable in the second cursor. That's
where I'm having the issue.

Melissa

----- Original Message -----
From:
To: ;
Sent: Sunday, June 18, 2006 7:06 PM
Subject: RE: Stored Procedure


>I think what you are trying to do boils down to this
>
> INSERT INTO ar_no_ins_outs_delete
> (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
> SELECT t1.doc_type, t1.doc_no, t1.ref_doc_no,
> concat('-',t1.gv_dollar_amt)
> FROM test.ar_no_ins_outs_temp AS t1, test.ar_no_ins_outs_temp AS t2
> WHERE t1.doc-type in ('PV','GV')
> AND t2.doc_type in ('RG','RC','RT')
> AND t1.doc_type = t2.doc_no
> AND t1.ref_doc_no = t2.ref_doc_no
> AND concat('-',t1.gv_dollar_amt) = t2.gv_dollar_amt
>
> It is always better to use joins than to use cursors. Cursors should
> always be a last resort. Joins are MUCH more efficient.
>
> If gv_dollar_amt is a numeric type then you should replace
> concat('-',t1.gv_dollar_amt) with -gv_dollar_amt.
>
> You should check that I have not mixed up any of the column names
> because I can't test this.
>
> John B.
>
> -----Original Message-----
> From: Melissa Dougherty [mailto:melissa@cse-corp.com]
> Sent: Friday, 16 June 2006 4:46 AM
> To: win32@lists.mysql.com
> Subject: Stored Procedure
>
> I'm still new to the MySQL SPs.... Does anyone see a problem with this
> code?
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `test`.`test_AuditRun_SP`$$
> CREATE PROCEDURE `test`.`test_AuditRun_SP`(OUT OblDocNo varchar(50), OUT
> OBLCNT INT)
> BEGIN
> DECLARE done INT DEFAULT 0;
> DECLARE Obldoctype varchar(10);
> DECLARE Obldocno varchar(50);
> DECLARE Oblrefdocno varchar(50);
> DECLARE Obldollamt varchar(50);
> DECLARE Recdoctype varchar(10);
> DECLARE Recdocno varchar(50);
> DECLARE Recrefdocno varchar(50);
> DECLARE Recdollamt varchar(50);
> DECLARE curObl1 CURSOR FOR SELECT
> doc_type,doc_no,ref_doc_no,concat('-',gv_dollar_amt) FROM
> test.ar_no_ins_outs_temp WHERE doc_type in ('PV','GV');
> DECLARE curRec2 CURSOR FOR SELECT
> doc_type,doc_no,ref_doc_no,gv_dollar_amt FROM test.ar_no_ins_outs_temp
> WHERE doc_type in ('RG','RC','RT');
> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
>
> OPEN curObl1;
> OPEN curRec2;
>
> REPEAT
> FETCH curObl1 INTO Obldoctype, Obldocno, Oblrefdocno, Obldollamt;
> FETCH curRec2 INTO Recdoctype, Recdocno, Recrefdocno, Recdollamt;
>
> IF NOT done THEN
>
> IF obldocno = recdocno and Oblrefdocno = Recrefdocno and
> Obldollamt = Recdollamt THEN
>
> INSERT into ar_no_ins_outs_delete
> (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
> values (Obldoctype, Obldocno, Oblrefdocno, Obldollamt);
>
> END IF;
> END IF;
>
> UNTIL done END REPEAT;
>
> CLOSE curObl1;
> CLOSE curRec2;
>
> END$$
>
> DELIMITER ;
>
>
> Thanks,
>
> Melissa
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=melissa@cse-corp.com
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Stored Procedure

I don't see that in the code you have submitted. The only way you can do
that is to declare the second cursor inside the repeat loop for the
first one. That would make things even slower.

By the way what you have coded in your stored procedure looks very like
a first attempt at a sequential file update. That is the sort of thing I
used to code in COBOL back in the 70s when our databases were stored on
magnetic tapes! I don't know about the nature of your data, but your
logic will only work if the rows match one-for-one in the two cursors.
If there are duplicate matches or mismatches your logic will fail badly.
The cursors must also be ordered. If you really need to do it this way I
can find you a good algorithm but it is much more complicated than what
you have.

So far I cannot see anything in what you want to do that cannot be done
much better by join queries where mismatches and duplicates will be
automatically handled correctly. Relational database engines are much
better at this than we are. That's why they exist! You will find the
sequential update logic buried inside all such engines.

John B.

-----Original Message-----
From: Melissa Dougherty [mailto:melissa@cse-corp.com]=20
Sent: Monday, 19 June 2006 10:13 PM
To: John Bonnett, R&D Australia; win32@lists.mysql.com
Subject: Re: Stored Procedure

I just remember another issue with declaring the cursors.... I want to
use=20
the doc_no from the first cursor as a variable in the second cursor.
That's=20
where I'm having the issue.

Melissa

----- Original Message -----=20
From:
To: ;
Sent: Sunday, June 18, 2006 7:06 PM
Subject: RE: Stored Procedure


>I think what you are trying to do boils down to this
>
> INSERT INTO ar_no_ins_outs_delete
> (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
> SELECT t1.doc_type, t1.doc_no, t1.ref_doc_no,
> concat('-',t1.gv_dollar_amt)
> FROM test.ar_no_ins_outs_temp AS t1, test.ar_no_ins_outs_temp AS t2
> WHERE t1.doc-type in ('PV','GV')
> AND t2.doc_type in ('RG','RC','RT')
> AND t1.doc_type =3D t2.doc_no
> AND t1.ref_doc_no =3D t2.ref_doc_no
> AND concat('-',t1.gv_dollar_amt) =3D t2.gv_dollar_amt
>
> It is always better to use joins than to use cursors. Cursors should
> always be a last resort. Joins are MUCH more efficient.
>
> If gv_dollar_amt is a numeric type then you should replace
> concat('-',t1.gv_dollar_amt) with -gv_dollar_amt.
>
> You should check that I have not mixed up any of the column names
> because I can't test this.
>
> John B.
>
> -----Original Message-----
> From: Melissa Dougherty [mailto:melissa@cse-corp.com]
> Sent: Friday, 16 June 2006 4:46 AM
> To: win32@lists.mysql.com
> Subject: Stored Procedure
>
> I'm still new to the MySQL SPs.... Does anyone see a problem with this
> code?
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `test`.`test_AuditRun_SP`$$
> CREATE PROCEDURE `test`.`test_AuditRun_SP`(OUT OblDocNo varchar(50),
OUT
> OBLCNT INT)
> BEGIN
> DECLARE done INT DEFAULT 0;
> DECLARE Obldoctype varchar(10);
> DECLARE Obldocno varchar(50);
> DECLARE Oblrefdocno varchar(50);
> DECLARE Obldollamt varchar(50);
> DECLARE Recdoctype varchar(10);
> DECLARE Recdocno varchar(50);
> DECLARE Recrefdocno varchar(50);
> DECLARE Recdollamt varchar(50);
> DECLARE curObl1 CURSOR FOR SELECT
> doc_type,doc_no,ref_doc_no,concat('-',gv_dollar_amt) FROM
> test.ar_no_ins_outs_temp WHERE doc_type in ('PV','GV');
> DECLARE curRec2 CURSOR FOR SELECT
> doc_type,doc_no,ref_doc_no,gv_dollar_amt FROM test.ar_no_ins_outs_temp
> WHERE doc_type in ('RG','RC','RT');
> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =3D 1;
>
> OPEN curObl1;
> OPEN curRec2;
>
> REPEAT
> FETCH curObl1 INTO Obldoctype, Obldocno, Oblrefdocno, Obldollamt;
> FETCH curRec2 INTO Recdoctype, Recdocno, Recrefdocno, Recdollamt;
>
> IF NOT done THEN
>
> IF obldocno =3D recdocno and Oblrefdocno =3D Recrefdocno and
> Obldollamt =3D Recdollamt THEN
>
> INSERT into ar_no_ins_outs_delete
> (doc_type,doc_no,ref_doc_no,gv_dollar_amt)
> values (Obldoctype, Obldocno, Oblrefdocno, Obldollamt);
>
> END IF;
> END IF;
>
> UNTIL done END REPEAT;
>
> CLOSE curObl1;
> CLOSE curRec2;
>
> END$$
>
> DELIMITER ;
>
>
> Thanks,
>
> Melissa
>
> --=20
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=3Dmelissa@cse-corp.com
>
>=20


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org