Dave’s Blog

I’ve been writing an n-tier PHP web application, and I’ve been working on the installer. As part of it, I needed to create stored procedures from PHP.  I hit a snag, which was the following:

$sql = "delimiter $$

drop procedure if exists spsTableDelete $$
create procedure spsTableDelete(mySession nvarchar(255),myTableID bigint unsigned)
begin

		update sTable
		set Deleted = TableID
		where TableID = myTableID;

end $$

delimiter ;";
$this->mysqli->multi_query($sql);
if (strlen($this->mysqli->error) > 0)
	echo "<p class='error'>".$this->mysqli->error."</p>";
else
	echo "$operation Procs Written<br />\n";

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 $$’ at line 1

The problems is that MySQLi does not use delimiters so the following code will work instead:

$sql = "drop procedure if exists spsTableDelete ;
create procedure spsTableDelete(mySession nvarchar(255),myTableID bigint unsigned)
begin

		update sTable
		set Deleted = TableID
		where TableID = myTableID;

end ;";

$this->mysqli->multi_query($sql);
if (strlen($this->mysqli->error) > 0)
	echo "<p class='error'>".$this->mysqli->error."</p>";
else
	echo "$operation Procs Written<br />\n";