sql BEGIN creates error

Question

I'm trying to update my datebase depending on whether a row already exists or not.

In my php file I create this string:

 IF EXISTS(SELECT updown
              FROM voted_source
              WHERE ID = ? AND userID = ?)
                BEGIN
                    UPDATE voted_source
                    SET updown = TRUE
                    WHERE ID = ?;

                    UPDATE source
                    SET score_up = score_up + 1, 
                        score_down = score_down - 1
                    WHERE ID = ?;
                END;
                ELSE
                BEGIN
                    INSERT INTO voted_source (ID, userID, updown)
                    VALUES (?, ?, TRUE);

                    UPDATE source
                    SET score_up = score_up + 1,
                        score_down = score_down - 1
                    WHERE ID = ?;
                END

And I call this using pdo like so:

$pdo = new PDO('mysql: host='.$servername.';dbname='.$db, $user, $pass, array(
        PDO::ATTR_PERSISTENT => true
    ));
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $pdo->beginTransaction();

    $stmt = $pdo->prepare($sqlString);
    $stmt->execute([$sourceID, $userID, $sourceID, $sourceID, $sourceID, $userID, $sourceID ]);

    $pdo->commit();

    $pdo = null;

This returns the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN UPDATE voted_source SET updown = TRUE WHERE ID = '1'at line 4' in voteSource.php:74 Stack trace: #0 voteSource.php(74): PDOStatement->execute(Array) #1 {main} thrown in voteSource.php on line 74;

The user has the rights to SELECT INSERT and UPDATE. Any help is greatly aprreciated.

Possible duplicate of [PDO Parameterized Query - Reuse named placeholders?](https://stackoverflow.com/questions/2432084/pdo-parameterized-query-reuse-named-placeholders) — chiliNUT, Sep 14 '17 at 19:44
you can't re-use named parameters, you have `:sourceID` used 5 times and `:userID` used twice. See paragraph 2: http://php.net/manual/en/pdo.prepare.php — chiliNUT, Sep 14 '17 at 19:45
Your code is prone to race conditions (if it is not protected by locks somehow which is not shown in your snippet). There are chances that the row which has just been tested by EXISTS(...) is inserted by another connection immediately after the test. In this case, the row will be inserted twice, or an error will occur (if a unique index is affected). — Binarus, Sep 15 '17 at 08:46
@chiliNUT I changed my code to use the questionmark approach. The same error still returns. — Eizo, Sep 16 '17 at 10:16
@Binarus I may left out a bit too much. I'm using pdo with begintransaction() which should do that [php.net manual](http://php.net/manual/en/pdo.transactions.php). I editted my post to show this. — Eizo, Sep 16 '17 at 10:20
Please note that transactions are a complicated matter. I don't use PHP, and I don't have the time to read its docs. But I am quite sure that just wrapping things into a transaction won't save you from those race conditions. It depends on the indexes in use, on the isolation level, and if autocommit is in use, to name just a few. If you are interested in that subject (and you really should), I'd suggest reading the chapters about transactions, the "FOR UPDATE" and "IN SHARED MODE" modifiers and related stuff in the MySQL manuals. — Binarus, Sep 16 '17 at 12:27
Out of curiosity, did my answer help you solve your problem? — Binarus, Sep 27 '17 at 18:07
@Binarus Sorry for the really late answer, I had to put my project on hold for a little while. I couldn't fix my problem and I'm instead doing 2 seperate calls now. I've set up a reminder to return here if I ever do fix it. — Eizo, Feb 11 '18 at 19:14

Binarus · Answer 1 · 2017-09-16T12:34:19.393

It looks like you have copy-and-pasted your code from a stored procedure example you found somewhere and then altered it to your needs. From the MySQL manual, section "13.6.1 BEGIN ... END Compound-Statement Syntax" (see here):

BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs (stored procedures and functions, triggers, and events). [...]

I am understanding this in the sense that BEGIN ... END can not be used in normal queries, but only in stored procedures and functions, triggers and events; hence the error message. In your situation, you don't need them, so just leave them away.

Furthermore, from the MySQL manual, section "13.6.5.2 IF Syntax" (see here):

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

So you really should add END IF in a new line at the end of your $sqlString.

sql BEGIN creates error

1 Answers1