0

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 ;
Maj.jad
  • 51
  • 1
  • 1
  • 8

0 Answers0