I have made an install wizard for my system. If a user wants to use my system he must make an install before starting to use it. The installer will create databases, tables, procedures and triggers.
Installer can create databases and tables successfully but not triggers nor procedures at all.
My php code explodes a .sql file if line end with ';'. But triggers start with delimiter ;; so the script gets the delimiter as a single query.
This is my php code which is reading .sql file and separates file to queries.
$f = fopen($sql_dump_file,"r+");
$sqlFile = fread($f,filesize($sql_dump_file));
$sqlArray = explode(';',$sqlFile);
// var_dump($sqlArray);
//Process the sql file by statements
foreach ($sqlArray as $stmt) {
if (strlen($stmt)>3) {
//echo $stmt;
$result = mysqli_query($db,$stmt);
if (!$result) {
//echo $stmt;
echo $sqlErrorCode = mysqli_errno($db);
echo $sqlErrorText = mysqli_error($db);
$sqlStmt = $stmt;
break;
}
}
}
I have this error
1064You 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 PROCEDURE `acct_stop`( IN S DATETIME, IN Acct_Sessio' at line 1
This is the code of my procedures
DELIMITER ;;
CREATE PROCEDURE `acct_stop`(
IN S DATETIME,
IN Acct_Session_Time INT(12),
IN Acct_Input_Octets BIGINT(20),
IN Acct_Output_Octets BIGINT(20),
IN Acct_Terminate_Cause VARCHAR(32),
IN Acct_Delay_Time INT(12),
IN Connect_Info VARCHAR(32),
IN Acct_Session_Id varchar(64),
IN SQL_User_Name VARCHAR(64),
IN NAS_IP_Address VARCHAR(15)
)
BEGIN
DECLARE Prev_Acct_Input_Octets BIGINT(20);
DECLARE Prev_Acct_Output_Octets BIGINT(20);
DECLARE Prev_Acct_Session_Time INT(12);
SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime)
INTO Prev_Acct_Input_Octets, Prev_Acct_Output_Octets,
Prev_Acct_Session_Time
FROM radacct
WHERE AcctSessionId = Acct_Session_Id
AND UserName = SQL_User_Name
AND NASIPAddress = NAS_IP_Address
AND ( AcctStopTime > 0);
IF (Prev_Acct_Session_Time IS NULL) THEN
SET Prev_Acct_Session_Time = 0;
SET Prev_Acct_Input_Octets = 0;
SET Prev_Acct_Output_Octets = 0;
END IF;
UPDATE radacct SET AcctStopTime = S,
AcctSessionTime = (Acct_Session_Time - Prev_Acct_Session_Time),
AcctInputOctets = (Acct_Input_Octets - Prev_Acct_Input_Octets),
AcctOutputOctets = (Acct_Output_Octets - Prev_Acct_Output_Octets),
AcctTerminateCause = Acct_Terminate_Cause,
AcctStopDelay = Acct_Delay_Time,
ConnectInfo_stop = Connect_Info
WHERE AcctSessionId = Acct_Session_Id
AND UserName = SQL_User_Name
AND NASIPAddress = NAS_IP_Address
AND (AcctStopTime IS NULL OR AcctStopTime=0);
END ;;
DELIMITER ;