sidhesh sidhesh - 1 month ago 13
MySQL Question

In Mysql stored procedure I am trying to insert an extra parameter which is not in any table

DROP PROCEDURE `spGetUserLog1`//
CREATE DEFINER=`xxx`@`xxxxx` PROCEDURE `spGetUserLog1`(
IN sUsername VARCHAR(9), IN dtFrom DATETIME, IN dtTo DATETIME,
IN OFFSET SMALLINT(1), IN ROWCOUNT SMALLINT(1), IN iGMT VARCHAR(50))
BEGIN
DECLARE UId, SR, R, MR, D SMALLINT(1);
DECLARE G,iGMT VARCHAR(50);
SELECT UserId, SubReseller, Reseller, MainReseller, Distributor,GMT
INTO UId, SR, R, MR, D ,G
FROM Customers WHERE Username=sUsername LIMIT 1;
CASE
WHEN D !=0 THEN
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,iGMT,G)FROM SmscLog a WHERE Username IN( SELECT Username FROM Customers WHERE DistributorId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";

SET @DistributorId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);

PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @DistributorId, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;

WHEN MR !=0 THEN

SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,iGMT,G) FROM SmscLog a WHERE Username IN ( SELECT Username FROM Customers WHERE MainResellerId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";


SET @MainResellerId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ',', ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);

PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @MainResellerId, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;

WHEN R !=0 THEN
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,iGMT,G) FROM SmscLog a WHERE Username IN ( SELECT Username FROM Customers WHERE ResellerId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";


SET @ResellerId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ',', ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);

PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @ResellerId, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;

WHEN SR !=0 THEN
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
CONVERT_TZ(SentAt,iGMT,G) FROM SmscLog a WHERE Username IN ( SELECT Username FROM Customers WHERE SubResellerId = ? )
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";


SET @SubResellerId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ',', ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);

PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @SubResellerId, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;
ELSE
SET @Query = "SELECT Username, Sender, Mobile,
Type,
CASE
WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
Message
WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
CONVERT( UNHEX(Message) USING utf8)
ELSE
Message
END AS Message, Length, MessageId,
IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) AS Status,
CONVERT_TZ(SentAt,iGMT,G) FROM SmscLog a WHERE Username = ?
AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";


SET @Username = sUsername;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ',', ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);

PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @Username, @SentFrom, @SentTo;
DEALLOCATE PREPARE Stmt;
END CASE;
END


In this the IGMT which I am passing is calucated in the php page using our date function I want this value to be used in the stored procedure.

But when I execute it on php page I get an error:


Undefined Variable IGMT in stored procedure.


Is there any way to pass this value inside this stored procedure? Thanks in advance..

Answer

You are building an sql statement within the procedure and then preparing and executing it. THe problem is that you are using the text "iGMT" in the prepared query rather than the value the variable contains.

You need to swap it out for a place holder (?) and pass the iGMT parameter into the execute statement. So for example the first query would become

SET @Query = "SELECT Username, Sender, Mobile, 
        Type,
        CASE
        WHEN Type = 'TEXT' OR Type = 'FLASH' THEN
            Message
        WHEN Type = 'UNICODE' OR Type = 'UNICODE-FLASH' THEN
            CONVERT( UNHEX(Message) USING utf8)
        ELSE
            Message
        END AS Message, Length, MessageId,
        IFNULL((SELECT Status FROM DLRStatus WHERE MessageId = a.MessageId),IF((DATE(SentAt)=CURRENT_DATE),'ENQUEUE','ACCEPTD')) as Status,
        CONVERT_TZ(SentAt,?,G) FROM SmscLog a WHERE Username IN ( SELECT Username FROM Customers WHERE ResellerId = ? ) 
        AND DATE(SentAt) BETWEEN ? AND ? ORDER BY SentAt DESC";

SET @DistributorId = UId;
SET @SentFrom = dtFrom;
SET @SentTo = dtTo;
SET @Lim = CONCAT(' LIMIT ', OFFSET, ROWCOUNT);
SET @Query = CONCAT(@Query, @Lim);

PREPARE Stmt FROM @Query;
EXECUTE Stmt USING @iGMT, @DistributorId, @SentFrom, @SentTo;

You would need to make this change to each statement in the procedure

Comments