Mysql: running queries like CREATE FUNCTION...

[DmitryEfimenko]

hi, I can successfully run any standard query like select mycol from mytable, but when I try to run the following, it throws an error:

var sql =
"DELIMITER // " + 
"create function date_trunc(vInterval varchar(7), vDate timestamp) " +
"returns timestamp " +
"begin " +
    "declare toReturn timestamp; " +

    "if vInterval = 'year' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(YEAR, '1900-01-01', vDate) YEAR); " +
    "elseif vInterval = 'quarter' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(QUARTER, '1900-01-01', vDate) QUARTER); " +
    "elseif vInterval = 'month' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(MONTH, '1900-01-01', vDate) MONTH); " +
    "elseif vInterval = 'week' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(WEEK, '1900-01-01', vDate) WEEK); " +
    "elseif vInterval = 'day' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(DAY, '1900-01-01', vDate) DAY); " +
    "elseif vInterval = 'hour' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(HOUR, '1900-01-01', vDate) HOUR); " +
    "elseif vInterval = 'minute' then set toReturn = date_add('1900-01-01', interval TIMESTAMPDIFF(MINUTE, '1900-01-01', vDate) MINUTE); " +
    "END IF; " +

    "return toReturn; " +
"end// " +
"DELIMITER ;";

connection.query(sql, function(err, rows, fields) {
  if (err) throw err;
  else console.log('success');
});

The error that I'm getting is:
ER_PARSE_ERROR: 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 'DELIMITER // create function date_trunc(vInterval varchar(7), vDate timestamp) r' at line 1

I thought setting the config option multipleStatements to true might help, but it didn't.
Any help is appreciated!

[dougwilson]

The reason is that "'DELIMITER //" is not actually SQL, rather it is a direct command to the MySQL command line binary. This module sends all commands directly to your MySQL server and does no commands itself, which is why it would never work.

[DmitryEfimenko]

so I deleted DELIMITER related code (first and last line and the // and the end) - and it worked.
I might have misinterpreted your answer, but to me you made it sound like there is no way I can achieve it.

[dougwilson]

but to me you made it sound like there is no way I can achieve it.

This is correct, there is no way to achieve the functionality of the DELIMITER command, and that is a non-SQL command that is specific to the mysql command line binary client you are using.

You can read more about this in the MySQL manual at https://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

In the above statement, the lower-case mysql refers to the command line client, because it will use the ; as command separators. In this library, since it's direct to your server, DELIMITER is unnecessary, because each .query call is the way to cause command separation.

[DmitryEfimenko]

That is a great explanation. Thanks for clarifications!