Geek asks and answers

Argument type in MySQL procedures?

I am writing several MySQL routines for a web application, and something that amazes me is the lack of argument type checking in general.

For example, if I have the following:

CREATE PROCEDURE foo(n CHAR(4))

I can call it what I want, he will accept it and take only the first four characters. But if I want to do something like this:

use base;
DELIMITER $$
DROP PROCEDURE IF EXISTS open $$

CREATE PROCEDURE open(n INT)
  BEGIN
    SELECT * FROM prod_charts LIMIT n;
  END $$

DELIMITER ;

It just crashes when called with a non-int parameter. And there is no feedback: when called from php, I just don’t get anything, and when I try it in phpMyAdmin, they send me back to the home page.

So my question is: how can I make this a little safer? Is there a way to check the type of a variable in these procedures?

+3
source share
1 answer

, , mySQL. RDMS, MS SQL, , isNumeric(), .

mySQL , , .

CREATE FUNCTION ISNUMERIC(myVal VARCHAR(1024)) 
RETURNS TINYINT(1) DETERMINISTIC 
RETURN myVal REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

( ) .

0

More articles:


All Articles