CODEWITHSUNDEEP
mysqlprocedure
Luis Parada
Luis Parada

Reputation: 118

Prepared statement inside mysq stored procedure

I have tried to build a mysql stored procedure but seems to have different errors, I'm just trying to run a prepared statement from the stored procedure to achieve inserting some data in a table dynamically.

Can anyone check and tell me where my error is?

Thanks

    DELIMITER $$
    CREATE PROCEDURE insert_tracking(IN deviceid VARCHAR(15),IN timing timestamp,IN valid tinyint(1),IN latitude double,IN longitude double,IN speed double,IN course double,IN power double,IN comando varchar(45))
    BEGIN
        DECLARE query VARCHAR(150);
        SET query = "INSERT INTO tracking_? (device_id,time,valid,latitude,longitude,speed,course,power,command) VALUES (?,?,?,?,?,?,?,?,?)";
        PREPARE stmt FROM query;
        EXECUTE stmt USING deviceid,deviceid,timing,valid,latitude,longitude,speed,course,power,comando;
    END $$

I also tried this concatenation but it doesn't work.

    DELIMITER $$
    CREATE PROCEDURE insert_tracking(IN deviceid VARCHAR(15),IN timing timestamp,IN valid tinyint(1),IN latitude double,IN longitude double,IN speed double,IN course double,IN power double,IN comando varchar(45))
    BEGIN
        DECLARE query VARCHAR(200);
        SET query = CONCAT('INSERT INTO tracking_',deviceid,' (device_id,time,valid,latitude,longitude,speed,course,power,command) VALUES (?,?,?,?,?,?,?,?,?)');
        PREPARE stmt FROM query;
        EXECUTE stmt USING deviceid,timing,valid,latitude,longitude,speed,course,power,comando;
        DEALLOCATE PREPARE stmt;
    END $$

After editing I have this, I tested the concat statements and they work separately but the prepared statement keeps having issues, it doesn't work.

    DELIMITER $$
    CREATE PROCEDURE insert_tracking(IN deviceid VARCHAR(15),IN timing timestamp,IN valid tinyint(1),IN latitude double,IN longitude double,IN speed double,IN course double,IN power double,IN comando varchar(45))
    BEGIN
        SET @deviceid = deviceid;
        SET @nsert = CONCAT('INSERT INTO tracking_',@deviceid); 
        SET @query = CONCAT(@nsert,' (device_id,time,valid,latitude,longitude,speed,course,power,command) VALUES ("?","?","?","?","?","?","?","?","?")');
        SET @fquery = @query;
        PREPARE stmt FROM @fquery;
        EXECUTE stmt USING deviceid,timing,valid,latitude,longitude,speed,course,power,comando;
    END $$

Upvotes: 1

Views: 411

Answers (1)

skv
skv

Reputation: 1803

I believe this is because Table and Column names cannot be replaced by parameters in prepared statements. Try getting the full table name in and test if you want to verify.

Code taken from (http://dev.af83.com/2007/05/30/use-parameters-for-field-and-table-names-in-a-mysql-prepare-statement-to-create-truly-dynamic-sql.html) credits to author

This function can allow dynamic table names and column names 

DELIMITER //
DROP FUNCTION  IF EXISTS substrCount//
CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, '')))/length(delim);//

DROP FUNCTION  IF EXISTS charsplit//
CREATE FUNCTION charsplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');//

DROP FUNCTION  IF EXISTS replacefirst//
CREATE FUNCTION replacefirst(haystack varchar(255), needle varchar(255),replacestr varchar(255)) returns varchar(255)
BEGIN
    IF LOCATE(needle,haystack)>0 THEN
        SET @replaced=concat(left(haystack,LOCATE(needle,haystack)-LENGTH(needle)),replacestr,right(haystack,LENGTH(haystack)-LOCATE(needle,haystack)));
    ELSE
        SET @replaced=haystack;
    END IF;
    RETURN @replaced;
END;//

DROP FUNCTION  IF EXISTS properprepare//
CREATE FUNCTION properprepare(template varchar(255), args varchar(255)) returns varchar(255)
BEGIN
 SET @i=0;
 SET @numargs = substrCount(args,',');
  WHILE @i <= @numargs DO
      SET @i= @i+ 1;
      SET template=replacefirst(template,'?',charsplit(args,',',@i));
  END WHILE;
RETURN template;
END;//

DELIMITER ;

Upvotes: 1

Related Questions