Can anybody tell me what's wrong with this query?

Question

CREATE PROCEDURE Click(
    in  ipaddress varchar(45))
BEGIN
    DECLARE ex int;
    SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
IF ex = 0 THEN
    INSERT INTO clickactivity (IP, Clicks) VALUES(ipaddress, 0);
END IF;   
    UPDATE clickactivity SET Clicks = Clicks + 1;
END

It complains about line 4 like so:

#1064 - 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 '' at line 4

Answer 1

There are two things wrong

First, use DELIMITER $$ so semicolons are interpreted inside the stored procedure and not the command line. After defining the stored procedure, switch the delimiter back to semicolon (;).

Next, UPDATE clickactivity SET Clicks = Clicks + 1; will update every row and you don't want that.

SUGGESTION #1

Add WHERE IP = ipaddress; to the UPDATE clickactivity

DELIMITER $$
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
    DECLARE ex int;
    SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
    IF ex = 0 THEN
        INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 0);
    END IF;   
    UPDATE clickactivity SET Clicks = Clicks + 1 WHERE IP = ipaddress;
END $$
DELIMITER ;

SUGGESTION #2

Why do two or three queries to register a single click ?

Change the code to do the query so that it INSERTs and UPDATEs at the same time

DELIMITER $$
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
    INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
    ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;
END $$
DELIMITER ;

In that event, you don't need a stored procedure. Just do the INSERT

INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;

Of course, doing this works if ipaddress is the PRIMARY KEY or is a UNIQUE KEY in clickactivity.

GIVE IT A TRY !!!

Answer 2

Your syntax is ok, you just need to temporarily change the end of statement delimiter, like this:

mysql> delimiter //
mysql> CREATE PROCEDURE Click(
    ->     in  ipaddress varchar(45))
    -> BEGIN
    ->     DECLARE ex int;
    ->     SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
    -> IF ex = 0 THEN
    ->     INSERT INTO clickactivity (IP, Clicks) VALUES(ipaddress, 0);
    -> END IF;   
    ->     UPDATE clickactivity SET Clicks = Clicks + 1;
    -> END //
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter ;

If not, the MySQL command line (or other clients) will treat the first ; as the end of the procedure declaration. In other words, you need to change the delimiter in order to "insert" the semicolon character.