phpmyadmin stored procedure: insert not working

Question

I created a procedure in phpmyadmin and when I try to insert a new row, it doesn't work. I'm trying to create a new row in the table "VOTE_TOKEN" if the current date is the same as "LAST_DATE" in the table RESERVATION. Here is the definition of my procedure:

BEGIN
DECLARE date_container_st varchar(15);
DECLARE date_array varchar(15);
DECLARE date_today varchar(15);
DECLARE date_today_array varchar(15);
SELECT OWNER_ID, LOCATING_ID, LAST_DATE   
FROM RESERVATION                                       
WHERE ID = res_id;

SET date_container_st=RESERVATION.LAST_DATE ;
SET date_today=(CHAR(15), GETDATE(), 101);

IF (date_today=RESERVATION.LAST_DATE)
THEN
       INSERT INTO VOTE_TOKEN(TARGET_ID, VOTING_ID, IS_B_OWNER) 
       VALUES(RESERVATION.OWNER_ID, RESERVATION.LOCATING_ID, 0);                       
       INSERT INTO VOTE_TOKEN(TARGET_ID, VOTING_ID, IS_B_OWNER)
       VALUES(RESERVATION.LOCATING_ID, RESERVATION.OWNER_ID, 1);  
END IF;  END

When I call this procedure, phpmyadmin only returns a small table containing the "owner_id", "locating_id" and "last_date" and doesn't create any row in the table "VOTE_TOKEN"

Thanks for helping

`mysql` or `sql-server`? You've tagged `phpmyadmin`, however, PHP MyAdmin doesn't work with SQL Server, so I *suspect* the former. — Thom A, Dec 30 '17 at 21:55
since you tagged phpmyadmin, is this a suspected issue with the phpmyadmin software specifically (as opposed to just an issue with a MySQL query), or do you just happen to be using that as your MySQL GUI? If the latter, then this tag is irrelevant. — ADyson, Dec 30 '17 at 21:56
Anyway, `SET date_container_st=RESERVATION.LAST_DATE ;` appears to make no sense, and neither does `IF (date_today=RESERVATION.LAST_DATE)`. You can't set a single variable to be, or a compare a variable to, an entire column. Which of the rows in the reservation table are you trying to select? You realise that your SELECT statement doesn't do anything except print to the console? It doesn't affect the later parts of your query. Read up about assigning query results to variables. — ADyson, Dec 30 '17 at 21:58
I want to get the values of "LAST_DATE", "OWNER_ID" and "LOCATING_ID" for the row where the id is "res_id" and insert 2 new rows in "VOTE_TOKEN" with these values. I don't really know how to deal with this... — , Dec 30 '17 at 22:07

Shadow · Accepted Answer · 2017-12-30T22:52:38.320

The problem is that you do not fetch the values returned by the select into variables, therefore you cannot work with them. None of your RESERVATION.xxx variables exist. You can use the select ... into ... statement for assigning values from your query to variables.

...
DECLARE V_OWNER_ID bigint;
DECLARE V_LOCATING_ID bigint;
DECLARE V_LAST_DATE date;

SELECT OWNER_ID, LOCATING_ID, LAST_DATE 
INTO V_OWNER_ID, V_LOCATING_ID, V_LAST_DATE  
FROM RESERVATION                                       
WHERE ID = res_id;
...

Use the variables V_* in your code in place of the RESERVATION.xxx variables.

Also, you need to use curdate() function to get today's date. You are using an ms sql server version of the code.

However, the ehole code can be simplified as:

   INSERT INTO VOTE_TOKEN(TARGET_ID, VOTING_ID, IS_B_OWNER) 
   SELECT RESERVATION.OWNER_ID, RESERVATION.LOCATING_ID, 0
   FROM RESERVATION
   WHERE RESERVATION.ID = res_id AND RESERVATION.LAST_DATE=curdate();                       
   INSERT INTO VOTE_TOKEN(TARGET_ID, VOTING_ID, IS_B_OWNER) 
   SELECT RESERVATION.OWNER_ID, RESERVATION.LOCATING_ID, 1
   FROM RESERVATION
   WHERE RESERVATION.ID = res_id AND RESERVATION.LAST_DATE=curdate();

No need for variables and if.

Thanks very much for your answer. "LAST_DATE" is stored as a varchar, so how could I convert "curdate()" to a varchar? Thank you — , Dec 30 '17 at 23:04
You could have googled it... https://stackoverflow.com/questions/2392413/convert-datetime-value-into-string-in-mysql — Shadow, Dec 30 '17 at 23:08
@J.Pelle don't store dates as strings, this is a design flaw in your database — ADyson, Dec 31 '17 at 06:16

phpmyadmin stored procedure: insert not working

1 Answers1