Insert in multiple tables

Question

Insert in multiple tables

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I want to do the following insert in MySql

    CREATE PROCEDURE cadastroLocal (var_localNome varchar(80),  var_horainicial TIME, var_horafinal TIME, var_periodoemminutos INTEGER, var_diasdasemana INTEGER)
BEGIN
   INSERT INTO LOCAL (LOCAL.NOME) VALUES (var_localNome);
   SET @localid  = LAST_INSERT_ID();
   INSERT INTO HORARIO (HORARIO.HORAINICIAL, HORARIO.HORAFINAL, HORARIO.PERIODOEMMINUTOS) VALUES (var_horainicial, var_horafinal, var_periodoemminutos);
   SET @horarioid  = LAST_INSERT_ID();
   INSERT INTO CONFIGURACAODELOCAL (CONFIGURACAODELOCAL.LOCALID, CONFIGURACAODELOCAL.DIADASEMANA, HORARIO.HORARIOID) VALUES (@localid, var_diasdasemana, @horarioid);

However, the following error appears:

You have an error in your SQL syntax; check the manual that correspond to your MySQL server version for the right syntax to use near '' at line 3

Being that nowhere exists these simple quotes. I have already looked at documentation and other questions that are for two tables.

Does anyone give an idea, or does anyone know if this is possible for more than two tables?

mysql

asked by anonymous 16.10.2014 / 19:37

2 answers

1

First of all I would like to thank you for your support.

Unlike the one suggested by the companion anmaia, it replaces in the creation the delimiter $$ by the one of the example of the mysql documentation.

So I solved my problem like this:

delimiter //

CREATE PROCEDURE cadastroLocal (var_localNome varchar(80),
                                var_horainicial TIME, 
                                var_horafinal TIME,
                                var_periodoemminutos INTEGER, 
                                var_diasdasemana INTEGER)
BEGIN

   INSERT INTO 
      LOCALL (LOCALL.NOME)
   VALUES 
      (var_localNome);

   SET @localid  = LAST_INSERT_ID();
   INSERT INTO HORARIO (HORARIO.HORAINICIAL, HORARIO.HORAFINAL, HORARIO.PERIODOEMMINUTOS) VALUES (var_horainicial, var_horafinal, var_periodoemminutos);
   SET @horarioid  = LAST_INSERT_ID();
   INSERT INTO CONFIGURACAODELOCAL (CONFIGURACAODELOCAL.LOCALID, CONFIGURACAODELOCAL.DIADASEMANA, CONFIGURACAODELOCAL.HORARIOID) VALUES (@localid, var_diasdasemana, @horarioid);
END 

//

Thank you!

16.10.2014 / 20:56

Distorted RelativeLayout on Tablet Query with LINQ, trying to perform a RIGHT JOIN

score 1 · Accepted Answer

Your problem happens because of the delimiters. You need to change the delimiter (;) for a moment, so your program can pass the procedure text to MySQL. For more details, see mysql documentation .

Try:

delimiter //

CREATE PROCEDURE cadastroLocal (var_localNome varchar(80),  
                                var_horainicial TIME, 
                                var_horafinal TIME, 
                                var_periodoemminutos INTEGER, 
                                var_diasdasemana INTEGER)
BEGIN

   INSERT INTO 
      LOCAL (LOCAL.NOME) 
   VALUES 
      (var_localNome);

   SET @localid  = LAST_INSERT_ID();
   INSERT INTO HORARIO (HORARIO.HORAINICIAL, HORARIO.HORAFINAL, HORARIO.PERIODOEMMINUTOS) VALUES (var_horainicial, var_horafinal, var_periodoemminutos)$$
   SET @horarioid  = LAST_INSERT_ID();
   INSERT INTO CONFIGURACAODELOCAL (CONFIGURACAODELOCAL.LOCALID, CONFIGURACAODELOCAL.DIADASEMANA, HORARIO.HORARIOID) VALUES (@localid, var_diasdasemana, @horarioid);

END

//

Update

I changed the delimiter as suggested by @Jesh.