A small example of how to call MySQL stored procedures with In and out parameters and PHP and Pb

Source: Internet
Author: User

 

The simplest example:

mysql> DELIMITER $$mysql> USE test $$Database changedmysql> DROP PROCEDURE IF EXISTS `sp_add`$$Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE sp_add(a INT, b INT,OUT c INT)    -> BEGIN     -> SET c=a+ b;    -> END$$Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;

mysql> CALL sp_add (1,2,@c);Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @c;+------+| @c   |+------+|    3 |+------+1 row in set (0.00 sec)

mysql>

A slightly complex example:

mysql> show create table t_BillNo;+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table    | Create Table                                                                                                                                                                        |+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t_BillNo | CREATE TABLE `t_billno` (  `SaleNo` bigint(20) DEFAULT NULL,  `bmh` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC |+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from t_BillNo;+--------+------+| SaleNo | bmh  |+--------+------+|      1 | 2    ||      4 | 3    ||      4 | 5    ||      7 | 7    ||     12 | 8    |+--------+------+5 rows in set (0.00 sec)mysql> mysql> DELIMITER $$mysql> USE test $$Database changedmysql> DROP PROCEDURE IF EXISTS `sp_GetMaxNumber`$$Query OK, 0 rows affected (0.01 sec)DELIMITER $$USE test $$DROP PROCEDURE IF EXISTS `sp_GetMaxNumber`$$CREATE PROCEDURE sp_GetMaxNumber (IN v_bmh VARCHAR(6), OUT v_MaxNo INT)BEGINSTART TRANSACTION;UPDATE t_BillNoSET SaleNo = IFNULL(SaleNo,0)+1 WHERE bmh = v_bmh;IF @@error_count = 0 THENBEGIN  SELECT Saleno INTO v_MaxNo FROM t_BillNo WHERE bmh = v_bmh;COMMIT;END;ELSEBEGIN     ROLLBACK;     SET v_MaxNo = 0;END;END IF;END$$DELIMITER ;mysql> CREATE PROCEDURE sp_GetMaxNumber (IN v_bmh VARCHAR(6), OUT v_MaxNo INT)    -> BEGIN    -> START TRANSACTION;    -> UPDATE t_BillNo    -> SET SaleNo = IFNULL(SaleNo,0)+1     -> WHERE bmh = v_bmh;    -> IF @@error_count = 0 THEN    -> BEGIN    ->   SELECT Saleno INTO v_MaxNo FROM t_BillNo WHERE bmh = v_bmh;    -> COMMIT;    -> END;    -> ELSE    -> BEGIN    ->      ROLLBACK;    ->      SET v_MaxNo = 0;    -> END;    -> END IF;    -> END$$Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> mysql> call sp_GetMaxNumber(8,@v_MaxNo);Query OK, 0 rows affected (0.00 sec)mysql> select @v_MaxNo;+----------+| @v_MaxNo |+----------+|       12 |+----------+1 row in set (0.00 sec)

How can I call MySQL stored procedures in PHP? I have never tested it. I have used examples from my friends as follows:

$ SQL = "Call test. sp_getmaxnumber (8, @ C );";
Mysql_query ($ SQL); // call the stored procedure of sp_getmaxnumber
$ Result = mysql_query ('select @ C ;');
$ Array = mysql_fetch_array ($ result );
Echo '<PRE>'; print_r ($ array );

 

However, when calling in petabytes, the following error is reported (for example, a netizen from itpub). If you have similar experiences, please share your experience with us:

In the cb_1.click event of the Pb w_main form:

String ls_bmh, ls_errtext
Int li_maxno
Ls_bmh = '000000'

Sp_getmaxnumber (ls_bmh, li_maxno)
Ls_errtext = sqca. sqlerrtext
MessageBox ('error', string (li_maxno) + ''+ sqlca. sqlerrtext)

But the Pb Display Error:
0 sqlstate = 37000
[MySQL] [ODBC 5.1 Driver] You have an error in your SQL syntax; check the manual that corresponds to your MySQL Server version for the right syntax to use near '? = Call sp_getmaxnumber (0, _ GBK '000000') at line 1.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.