• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Calling a MySQL stored procedure without return value triggers a mysql_num_rows() return type warning

#1
[eluser]LeonardoGaiero[/eluser]
Greetings, I believe I found a bug in how raw MySQL queries are handled by codeIgniter. I have a function in my model that calls a database stored procedure, which looks like this:

Code:
function insertAtt($savedName, $realName, $fileSize, $table, $ID) {
        $dt = $this->helpdesk->getDateNow();
        
        // Start transaction
        $this->db->trans_start();
        
        // Initialize username variable (for auditing purposes)
        $this->db->query("select @authname := '" . $this->session->userdata('user') . "'");
        
        // Procedure call
        $this->db->query("call insertAtts('$savedName', '$realName', '$fileSize', '$dt', '$table', '$ID')");

        // End transaction
        $this->db->trans_complete();

        // Returning transaction result
        return ($this->db->trans_status());
    }

What this code does is make a raw call to the insertAtts procedure, which looks like this:

Code:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
drop procedure if exists insertAtts;
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertAtts`(
    attSN varchar(255),
    attRN varchar(255),
    attSZ int(10),
    attDT datetime,
    tbl varchar(3),
    tblID mediumint(8)
)
BEGIN
    declare attID mediumint(8);
    start transaction;
    SELECT AUTO_INCREMENT FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = 'helpdesk' AND TABLE_NAME = 'hd_attachments' into attID;
    if tbl = "rep" then
//...
        commit;
    elseif tbl = "art" then
//...
        commit;
    elseif tbl = "not" then
//...
        commit;
    elseif tbl = "tic" then
//...
        commit;
    else rollback;
    end if;
END

So there is no direct return value to this procedure. The query works and all; however, the result of $this->db->query() seems to be triggering this error:

Code:
ERROR - 2011-05-10 11:21:52 --> Severity: Warning  --> mysql_num_rows() expects parameter 1 to be resource, boolean given E:\HelpTest\system\database\drivers\mysql\mysql_result.php 37

What I'm guessing is that the query is expecting a dataset to be returned, instead of the boolean representing the success or failure of the query. Can this behavior be confirmed, and if so, are there any better methods of calling stored procedures? (I didn't actually take the time to RE the database drivers to find out the former, sorry about that.) Thanks in advance, please let me know your collective thoughts.

#2
[eluser]LeonardoGaiero[/eluser]
Still no reply? All I'd like is to figure out this warning.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.