pdo defining a function an then using it in a select statement

Question

My query is somewhat like this :

CREATE FUNCTION func ... ;
SELECT * FROM  ....;

I'm using php's PDO which doesn't allow more than 1 statements in a $pdo->query() , so instead I decided that I'd split the query into two, so I execute the CREATE FUNCTION query using $pdo->exec() and then use $pdo->query() on the select statement.

However, I get the error on execution of select statement that FUNCTION database.func does not exist. How do I solve this? when I run it in phpmyadmin as a single query it works fine

Edit : PHP code :

class MyClass {
function connectPDO($user,$pass,$chartset="utf8"){
    //simple function for making a new PDO object and mysql connection
    $dbname = "my_db";
    try {
        //creating new pdo
        $pdo = new PDO('mysql:host=localhost;dbname='.$dbname, $user,$pass);
        //attributes set to throw errors and exceptions which can be caught, can be changed
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        //fetch associative arrays
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);
        //default chartset
        $pdo->exec('SET NAMES "'.$chartset.'"');
      } catch (PDOException $e) {
        //on some error
        $output = "Ay caramba! there's something wrong here!.<br>";
        echo $output.$e->getMessage();
        exit();
    }

    $this->db = $pdo;
}
}

$object = new MyClass();
$object->connectPDO("user","pass");
$sqlPreliminary = "
        DROP FUNCTION IF EXISTS myFunc;
        DELIMITER //

        CREATE FUNCTION myFunc (id INT)
            RETURNS DECIMAL

            BEGIN
            RETURN id + 1;
            END //
        DELIMITER ;

";

$sqlFinal = "
    SELECT id, myFunc(id) AS plusOne  FROM table;
";
$object->db->exec($sqlPreliminary);
var_dump($object->db->query($sqlFinal)->fetchAll(PDO::FETCH_ASSOC));

Does it exists after `CREATE`? I mean: do you add it but cannot use or is it not added at all? — Voitcus, Jun 17 '13 at 12:42
When I execute only the part where function is created, pdo throws no error, so I'm assuming that it should be added. Is there a way to check it? — Peeyush Kushwaha, Jun 17 '13 at 12:47
Try this http://dev.mysql.com/doc/refman/5.1/en/show-procedure-code.html — Voitcus, Jun 17 '13 at 12:54
I'm afraid your function is not created. PDO::exec() does not return error. You might want to make PDO throw exceptions. If something goes wrong you will be noticed. — Voitcus, Jun 17 '13 at 13:07
I'm not pasting mysql code as it might confuse you, I'll try to give some demo code — Peeyush Kushwaha, Jun 17 '13 at 13:20
Can you create function via shell or phpmyadmin and then use it in your queries? P.S.: And are you sure you really need complicated logic in databas layer? — pinepain, Jun 17 '13 at 13:34
How will the function i create via phpmyadmin be persistent? — Peeyush Kushwaha, Jun 17 '13 at 13:36

score 5 · Answer 1 · edited Jun 04 '19 at 19:45

Create a user defined function using PDO simply works by exec, no delimiter is required. Here $db is PDO instance.

$db->exec('CREATE FUNCTION ConvertHTMLToText(str LONGTEXT CHARSET utf8)
           RETURNS LONGTEXT CHARSET utf8
           BEGIN
           DECLARE start, end INT DEFAULT 1;
           LOOP
           SET start = LOCATE("<", str, start);
           IF (!start) THEN RETURN str; END IF;
           SET end = LOCATE(">", str, start);
           IF (!end) THEN SET end = start; END IF;
           SET str = TRIM(INSERT(str, start, end - start + 1, ""));
           END LOOP;
           END');

Voitcus · Answer 2 · 2013-06-17T13:38:01.990

1

EDIT:

This is multiple statement. The first part is DROP FUNCTION MyFunc IF EXISTS and the second is CREATE FUNCTION.

This was my previous answer, which was correct, but OP changed the code:

You don't say what $object->db is:

You call connectPDO("user","pass"); and then use $object->db->exec($sqlPreliminary);. $object should be global in function body. At the moment it is not, so it is a local variable, which is lost after end of the function. This $object in rest of the code is something different.

edited Jun 17 '13 at 13:38

answered Jun 17 '13 at 13:24

Voitcus

4,463
4
24
40

It isn't that simple. connectPDO is actually a part of a class. updated the code . – Peeyush Kushwaha Jun 17 '13 at 13:31
You should have posted this in the very beginning. The problem is that this IS multiple statement. There is `DROP ... IF EXISTS;` – Voitcus Jun 17 '13 at 13:36
yeah, but exec is capable of running multiple statements – Peeyush Kushwaha Jun 17 '13 at 13:37
I can't test it now, but [the first comment in the Manual](http://pl1.php.net/manual/en/pdo.exec.php#71843) says it is not. – Voitcus Jun 17 '13 at 13:39
Making my queries individual works, but http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd says that exec does support multiple statements. – Peeyush Kushwaha Jun 17 '13 at 14:21

score 0 · Answer 3 · edited Jun 27 '23 at 06:04

0

I have faced the same problem. When I removed " DELIMITER // " (also // DELIMITER ; at the end), it worked. I think DELIMITER is no longer required with PHP PDO.

$sqlPreliminary = "
    DROP FUNCTION IF EXISTS myFunc;
   
    CREATE FUNCTION myFunc (id INT)
        RETURNS DECIMAL

        BEGIN
        RETURN id + 1;
        END 

";

I tested this and worked.

edited Jun 27 '23 at 06:04

Cem Kalyoncu

14,120
4
40
62

answered Nov 24 '17 at 06:44

Joshy Francis

340
7
13

pdo defining a function an then using it in a select statement

3 Answers3