Solved

Variables are not passing the correct or any data into query

Posted on 2013-12-19
1
279 Views
Last Modified: 2013-12-20
The date variables are not passing correctly in the store procedure.
This portion returns a null
(CONVERT_TZ( (FROM_UNIXTIME(@fromDate/1000)),''UTC'', @timeZone) )AS startingDate,
(CONVERT_TZ( (FROM_UNIXTIME(@toDate/1000)),''UTC'', @timeZone) ) AS endingDate,

These dates are show null

l1.started BETWEEN @startDate AND @endDate

What I am I missing that the values are not passing into the query
    CREATE PROCEDURE reportFreeCoolingTrackerTest (
            IN fromDate varchar (50),
            IN toDate varchar (50),
            IN timeZone varchar (50))
    
        BEGIN
                DECLARE startDate varchar (50);
                DECLARE endDate varchar (50);
                DECLARE mylogID Int;
    
                 SET startDate = FROM_UNIXTIME(fromDate/1000);
                 SET endDate = FROM_UNIXTIME(toDate/1000);
                 SET mylogID = (SELECT logID FROM logs l WHERE l.details LIKE 'FCT%');
                 SET @tbl = CONCAT('log',mylogID);
    
     set @q1 = CONCAT('SELECT 
        i.details,l1.item31985,l1.item31987,
        ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
        ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , '':'' ) +1))) AS cost,
        (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , '':'' ) +1)))
        *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
          time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
          time_to_sec(timediff(@endDate, @startDate)) / 3600 AS totalTimeRange,
        (CONVERT_TZ( (FROM_UNIXTIME(@fromDate/1000)),''UTC'', @timeZone) )AS startingDate, 
        (CONVERT_TZ( (FROM_UNIXTIME(@toDate/1000)),''UTC'', @timeZone) ) AS endingDate,DATABASE() AS databaseName,
         CASE
            when l1.activityId = t2.activityId THEN 1
            ELSE 0
        END AS errorCheck, t2.completed AS errorDay,a.scheduleType,@lgName,@mylogID  
    
    
      FROM logs l 
          INNER JOIN groups g ON g.groupId = l.groupId
          LEFT JOIN groups g1 ON g.parentId = g1.groupId
          LEFT JOIN groups g2 ON g1.parentId = g2.groupId
          LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
          INNER JOIN activities a ON l.logId = a.logId 
          INNER JOIN  ',  @tbl,'  l1 ON a.activityId = l1.activityId ');
    
       set @q2 = CONCAT(@q1, 'INNER JOIN ', @tbl);
       
       SET @q3 = CONCAT(@q2, ' t2 ON t2.recordId = l1.recordid + 1
          INNER JOIN items i ON l.logId = i.logId AND i.name LIKE ''%KW%''
          INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE ''%KW%''
         WHERE i.itemID = "31985"  AND  l1.activityId = 1257
          AND l1.started
            BETWEEN @startDate
                AND @endDate
         ORDER BY l1.recordId,l1.started');   
        
        PREPARE stmt FROM @q3;
               
         EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
        END //
    
    DELIMITER ;

Open in new window

0
Comment
Question by:portlight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39731233
For me it seems you are missing some single quotes.  Try this: (hopefully I didn't botch)

    CREATE PROCEDURE reportFreeCoolingTrackerTest (
            IN fromDate varchar (50),
            IN toDate varchar (50),
            IN timeZone varchar (50))
    
        BEGIN
                DECLARE startDate varchar (50);
                DECLARE endDate varchar (50);
                DECLARE mylogID Int;
    
                 SET startDate = FROM_UNIXTIME(fromDate/1000);
                 SET endDate = FROM_UNIXTIME(toDate/1000);
                 SET mylogID = (SELECT logID FROM logs l WHERE l.details LIKE 'FCT%');
                 SET @tbl = CONCAT('log',mylogID);
    
     set @q1 = CONCAT('SELECT 
        i.details,l1.item31985,l1.item31987,
        ((l1.item31985 - l1.item31987)*(time_to_sec(timediff(t2.completed, l1.completed)))) / 3600  AS kwDifference,
        ((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , '':'' ) +1))) AS cost,
        (((l1.item31985 - l1.item31987) *  (substr(l.details, instr(l.details , '':'' ) +1)))
        *(time_to_sec(timediff(t2.completed, l1.completed)) / 3600)) AS costT,
          time_to_sec(timediff(t2.completed, l1.completed)) / 3600 AS coolingHours,
          time_to_sec(timediff(', @endDate, ',', @startDate,')) / 3600 AS totalTimeRange,
        (CONVERT_TZ( (FROM_UNIXTIME(',@fromDate,'/1000)),''UTC'', ',@timeZone,') )AS startingDate, 
        (CONVERT_TZ( (FROM_UNIXTIME(',@toDate,'/1000)),''UTC'', ',@timeZone,') ) AS endingDate,DATABASE() AS databaseName,
         CASE
            when l1.activityId = t2.activityId THEN 1
            ELSE 0
        END AS errorCheck, t2.completed AS errorDay,a.scheduleType,',@lgName,',',@mylogID,'  
    
    
      FROM logs l 
          INNER JOIN groups g ON g.groupId = l.groupId
          LEFT JOIN groups g1 ON g.parentId = g1.groupId
          LEFT JOIN groups g2 ON g1.parentId = g2.groupId
          LEFT JOIN groups g3 ON g2.parentId = g3.groupId                
          INNER JOIN activities a ON l.logId = a.logId 
          INNER JOIN  ',  @tbl,'  l1 ON a.activityId = l1.activityId ');
    
       set @q2 = CONCAT(@q1, 'INNER JOIN ', @tbl);
       
       SET @q3 = CONCAT(@q2, ' t2 ON t2.recordId = l1.recordid + 1
          INNER JOIN items i ON l.logId = i.logId AND i.name LIKE ''%KW%''
          INNER JOIN users u ON l1.userId = u.userId AND i.name LIKE ''%KW%''
         WHERE i.itemID = "31985"  AND  l1.activityId = 1257
          AND l1.started
            BETWEEN ',@startDate,'
                AND ',@endDate,'
         ORDER BY l1.recordId,l1.started');   
        
        PREPARE stmt FROM @q3;
               
         EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
        END //
    
    DELIMITER ;

Open in new window


PS. vim is your highlighting friend: www.vim.org
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question