Load . SQL file and run with PDO

Asked

Viewed 42 times

0

Well I am mounting a small code in PHP 8, PDO and Mysql 8. It will be responsible for creating a bunch of data and loading the tables from a file. SQL

So far I’m doing it this way:

// Tenho as constantes de conexão
DB_HOST = "localhost";
DB_USER = "root";
DB_PASSWD = "";

// Aqui eu crio o novo Banco de dados
// Conexão
$conn = new PDO("mysql:host=" . DB_HOST, DB_USER, DB_PASSWD);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Cria o BD
$sql = "CREATE DATABASE IF NOT EXISTS `bdVirgem`";
$conn->exec($sql);

Good up to that point this ok, the bank is created normally. Well now I will load the contents of the file . Sql into a variable and then pass it to the PDO:

// Carrega o arquivo .SQL
$file = file_get_contents("bdVirgem.sql");

// Opções de conexão
$options = [
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8; SET time_zone='America/Sao_Paulo';",
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_CASE => PDO::CASE_NATURAL
];

// Conexão
$conn = new PDO(
    'mysql:host='.DB_HOST.';dbname='.$dbName.'',
    DB_USER,
    DB_PASSWD,
    $options
);

// Executas comandos do arquivo .sql
$stmt = $conn->prepare($file);
$stmt->execute();

Good so far the database and created and the tables are loaded, but I’m only having a BUG. At the end of the . sql file I have the creation instructions for Trigger, They are not being created. Follow an example:

CREATE TRIGGER removeProductCategory BEFORE DELETE ON productCategory
        FOR EACH ROW
    BEGIN
        UPDATE product SET categoryCode = null WHERE categoryCode = OLD.code;
    END;
    
    
    -- ++++++++++++
    
    
    CREATE TRIGGER removeCategory BEFORE DELETE ON category
        FOR EACH ROW
    BEGIN
        UPDATE cashBookEntry SET categoryCode = null WHERE categoryCode = OLD.code;
    END;
    
    
    -- +++++++++++
    
    
    CREATE TRIGGER addProductStock AFTER INSERT ON stockHandling
        FOR EACH ROW
    BEGIN
        UPDATE product SET stock = (stock + NEW.quantity), level = (level + 1) WHERE code = NEW.productCode;
    END;
    
    CREATE TRIGGER subProductStock AFTER DELETE ON stockHandling
        FOR EACH ROW
    BEGIN
        UPDATE product SET stock = (stock - OLD.quantity), level = (level - 1) WHERE code = OLD.productCode;
    END;

Well someone knows where the problem occurs?

  • Try removing the DELIMITER $ and change END$ for END; and remove DELIMITER ;

  • I tried that way and I made a mistake: #1064 - Você tem um erro de sintaxe no seu SQL próximo a '' na linha 7, Remembering that I have several Triggers file. I will update the question with an example.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.