MySQL not inserting using PDO and Stored Procs

I'm using PDO and stored procedures with MySQL (InnoDB) and for some reason data isn't being inserted - sort of.

When I view the properties on the table the Auto_increment is going up each time I run it and it's going through without errors. At the end, I'm selecting the variables I'm passing in and it's all good.

However, even with the table "incrementing", the data is not there. If I select on what it says the auto_increment ID is, I get nothing. If I select * and order desc, again, it doesn't show the latest.

Any ideas what could be causing this?

Thanks!

Here's the Stored Proc:

DELIMITER $$

USE `DB`$$
DROP PROCEDURE IF EXISTS `addVal`$$
CREATE DEFINER=`data`@`%` PROCEDURE `addVal`(

        v_val_accountid INT,
        v_val_groupid INT,
        v_val_validationtypeid INT,
        v_val_validationstatusid INT,
        v_val_key VARCHAR(255),
        v_val_expirationdate DATETIME

)
MAIN:BEGIN

        INSERT `Validation` (
            Val_AccountId,
            Val_GroupId,
            Val_ValidationTypeId,
            Val_ValidationStatusId,
            Val_Key,
            Val_ExpirationDate

        ) VALUES (
            v_val_accountid,
            v_val_groupid,
            v_val_validationtypeid,
            v_val_validationstatusid,
            v_val_key,
            v_val_expirationdate
        );

END$$

DELIMITER;

Here's the PDO prepared statement:

    $key = '123456789';
    $group_id = 0;
    $type_id = 2;
    $status_id = 1;
    $date = 'DATE_ADD(NOW(), INTERVAL 1 DAY)';

    $stmt = $this->db->prepare("CALL addVal(:account_id, :group_id, :type_id, :status_id, :key, :date)");

    $stmt->bindParam(':account_id', $account_id, PDO::PARAM_INT);
    $stmt->bindParam(':group_id', $group_id, PDO::PARAM_INT);
    $stmt->bindParam(':type_id', $type_id, PDO::PARAM_INT);
    $stmt->bindParam(':status_id', $status_id, PDO::PARAM_INT);
    $stmt->bindParam(':key', $key, PDO::PARAM_STR);
    $stmt->bindParam(':date', $date, PDO::PARAM_STR);

Answers


I think you are getting some kind of error in the stored procedure because DATE_ADD(NOW(), INTERVAL 1 DAY) is a function, not string so you should not assign it as PDO::PARAM_STR.

You can probably change that part to something like:

// REPLACE PARAMETER FROM PARAM TO ACTUAL DATA_ADD FUNCTION
    $stmt = $this->db->prepare("CALL addVal(:account_id, :group_id, :type_id, :status_id, :key, DATE_ADD(NOW(), INTERVAL 1 DAY))");

...

// COMMENT OUT BINDING
//    $stmt->bindParam(':date', $date, PDO::PARAM_STR);

Hope this help!


Need Your Help

Why can I go up to this number with an integer?

memory integer

I am learning C and I read in the Kernighan&Ritchie's book that integers int were included in a set specific set [-32767;32767]. I tried to verify this assertion by writing the following program

Iterating on each element from an array of arrays on Postgresql

sql arrays postgresql plpgsql postgresql-8.3

I have a VARCHAR of numbers inside my stored procedure, these numbers are organized as arrays, I will show an example below:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.