Changing the MySQL query delimiter through the C API

Question

How can I change the MySQL query delimiter using the C API? I tried sending DELIMITER | as a query, complained about ..the right syntax to use near 'delimiter' at line 1..

Tried DELIMITER |; too, no luck. Tried DELIMITER |; SELECT 1|, no luck. :(

The reason I ask is that I need to create a trigger through the C API such as the following:

create trigger increase_count after insert on torrent_clients for each row
begin
   IF NEW.BytesLeft = 0 THEN
      UPDATE torrents SET Seeders = Seeders + 1 WHERE torrents.InfoHash = NEW.InfoHash;
   ELSE
      UPDATE torrents SET Leechers = Leechers + 1 WHERE torrents.InfoHash = NEW.InfoHash;
   END IF;
end|

but I don't think there is a way to do it without changing the delimiter, is there?

Thanks!

---

EDIT: Note that I do need to explicitly write the delimiter at the end, as I'm running multiple queries with only one API call (I have multi statements on)

---

EDIT2: the mysqlclient that uses the C api does this so there must be a way..

Answer 1

Did you try

DELIMITER //

Without a ";" at the end? It might be not possible to change the delimiter within a query that has multiple queries in it.

Answer 2

changing the delimiter is only needed when using the mysql client program (because it is mysql that interpretes the semicolon as statement delimiter). you don't need to change the delimiter when using the C API:

Normally, you can execute only a single SQL command with mysql_query(). A semicolon can appear in such a command only when it is syntactically allowed, and that is normally not the case! In particular, SQL does not allow for a command to end in a semicolon. (The C API will raise an error if you attempt to do so.)

The commands CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER, and the like are exceptions when they define a stored procedure or trigger: In such commands the semicolon serves as a separator between the SQL instructions that are part of the stored procedure or trigger. Such commands can be executed without problem.

p.s. you probably should set multi statements off again.

Answer 3

You can execute multiple commands using mysql_query. You will have to set some parameters though while establishing the connection. e.g., unsigned long opt_flags = CLIENT_FOUND_ROWS | CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS ;

if (0 == mysql_real_connect(mConn,mHostName,mUserName,mUserPass,mDbName,mPort,0,opt_flags)) ...

/* execute multiple statements */ status = mysql_query(mysql, "DROP TABLE IF EXISTS test_table;\ CREATE TABLE test_table(id INT);\ INSERT INTO test_table VALUES(10);\ UPDATE test_table SET id=20 WHERE id=10;\ SELECT * FROM test_table;\ DROP TABLE test_table");