none
Formal parameter '@label' was defined as OUTPUT but the actual parameter not declared OUTPUT.

    Question

  • I've recently moved from inline SQL to using prepared statements for security improvements.  When I run the SP through the prepare statement and execute it I get the following message

    I'm using the bindParam function and if parameter type is 2 (PDO::PARAM_STR) and if precission is needed I add the size as an extra parameter.

    I've included the SP at the bottom as you can see I've added a select 1 where 1=1 it was needed to fix an infinity loop I created.

    Non of the parameters should be OUTPUT parameters.

    any help would be welcome

    Thanks

    Ado

    ---------------------------------------------

    Error information from PDO object
    Array
    (
        [0] => 42000
        [1] => 8162
        [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Formal parameter '@label' was defined as OUTPUT but the actual parameter not declared OUTPUT.
    )
    PDOStatement Object
    (
        [queryString] => exec mr_vo_property_update @client_id=:client_id, @estate_id=:estate_id, @label=:label, @last_edit_date=:last_edit_date, @deed_location=:deed_location, @deed_packet_info=:deed_packet_info, @built=:built, @asset_value=:asset_value, @enable_eric=:enable_eric, @type_of=:type_of, @parent_id=:parent_id, @lease_type_id=:lease_type_id, @mr_contact_id=:mr_contact_id, @short_description=:short_description, @is_deleted=:is_deleted, @date_deleted=:date_deleted, @original_id=:original_id, @property_id=:property_id
    )
    parameters (1=> PDO::PARAM_INT , 2 => PDO::PARAM_STRING)
    Array
    (
        [:client_id] => Array
            (
                [type] => 1
                [size] =>
                [value] => 977
            )

        [:estate_id] => Array
            (
                [type] => 1
                [size] =>
                [value] => 93
            )

        [:label] => Array
            (
                [type] => 2
                [size] => 255
                [value] => the label of the property
            )

        [:last_edit_date] => Array
            (
                [type] => 2
                [size] =>
                [value] => 2010-11-03 10:57:27
            )

        [:deed_location] => Array
            (
                [type] => 2
                [size] => 255
                [value] =>
            )

        [:deed_packet_info] => Array
            (
                [type] => 2
                [size] => 255
                [value] =>
            )

        [:built] => Array
            (
                [type] => 2
                [size] => 50
                [value] =>
            )

        [:asset_value] => Array
            (
                [type] => 2
                [size] =>
                [value] => 0
            )

        [:enable_eric] => Array
            (
                [type] => 2
                [size] => 1
                [value] => N
            )

        [:type_of] => Array
            (
                [type] => 2
                [size] => 1
                [value] => L
            )

        [:parent_id] => Array
            (
                [type] => 1
                [size] =>
                [value] => 0
            )

        [:lease_type_id] => Array
            (
                [type] => 1
                [size] =>
                [value] => 1
            )

        [:mr_contact_id] => Array
            (
                [type] => 1
                [size] =>
                [value] => 19
            )

        [:short_description] => Array
            (
                [type] => 2
                [size] =>
                [value] => this is the description
            )

        [:is_deleted] => Array
            (
                [type] => 2
                [size] =>
                [value] => 0
            )

        [:date_deleted] => Array
            (
                [type] => 2
                [size] =>
                [value] =>
            )

        [:original_id] => Array
            (
                [type] => 2
                [size] => 50
                [value] => 22
            )

        [:property_id] => Array
            (
                [type] => 1
                [size] =>
                [value] => 1654
            )
    )

     

    STORED PROCEDURE

     

    CREATE  PROCEDURE dbo.mr_vo_property_update
        @property_id bigint = null,
        @client_id bigint = null,
        @estate_id bigint = null,
        @label nvarchar(255) = null,
        @last_edit_date datetime = null,
        @last_edit_by nvarchar(255) = null,
        @agent_details ntext = null,
        @deed_location nvarchar(255) = null,
        @deed_packet_info nvarchar(255) = null,
        @built nvarchar(50) = null,
        @asset_value money = null,
        @enable_eric char(1) = null,
        @type_of char(1) = null,
        @parent_id bigint = null,
        @lease_type_id bigint = null,
        @mr_contact_id bigint = null,
        @age_profile nvarchar(50) = null,
        @short_description ntext = null,
        @is_deleted int = null,
        @date_deleted datetime = null,
        @original_id varchar(50) = null
    AS
       
        update property set
            [client_id] = isnull(@client_id,[client_id]),
            [estate_id] = isnull(@estate_id,[estate_id]),
            [label] = isnull(@label,[label]),
            [last_edit_date] = @last_edit_date,
            [last_edit_by] = isnull(@last_edit_by,[last_edit_by]),
            [agent_details] = isnull(@agent_details,[agent_details]),
            [deed_location] = isnull(@deed_location,[deed_location]),
            [deed_packet_info] = isnull(@deed_packet_info,[deed_packet_info]),
            [built] = isnull(@built,[built]),
            [asset_value] = isnull(@asset_value,[asset_value]),
            [enable_eric] = isnull(@enable_eric,[enable_eric]),
            [type_of] = isnull(@type_of,[type_of]),
            [parent_id] = isnull(@parent_id,[parent_id]),
            [lease_type_id] = isnull(@lease_type_id,[lease_type_id]),
            [mr_contact_id] = isnull(@mr_contact_id,[mr_contact_id]),
            [age_profile] = isnull(@age_profile,[age_profile]),
            [short_description] = isnull(@short_description,[short_description]),
            [is_deleted] = isnull(@is_deleted,[is_deleted]),
            [date_deleted] = @date_deleted,
            [original_id] = isnull(@original_id,[original_id])
        where property_id = @property_id
      select 1 where 1=1
    GO

    Wednesday, November 03, 2010 11:04 AM

Answers

  • ...:::SOLUTION FOUND:::...

    ITS A BUG grrrrrr.

    OK my work yesterday with this lead me to correctly believe that the stored procedure was failing because I was passing an empty string "" to the parameter.  I've updated my factory objects to also set the field type to be PDO::PARAM_NULL when the type is a PDO::PARAM_STR and the string equals an empty string.

    This works because normally you will allow an string fields in your tables to be null.

    So i changed the following example block from this

        if(!is_null($obj->_deed_location)){
            $sqlParameters[count($sqlParameters)] = "@deed_location=:deed_location";
            $parameters[":deed_location"] = Array("type" => PDO::PARAM_STR, "size" => 255,"value" => $this->db->sqltidyType("nvarchar(255)", $obj->_deed_location,false));
        }

    to this

        if(!is_null($obj->_deed_location)){
            $sqlParameters[count($sqlParameters)] = "@deed_location=:deed_location";
              if(is_null($obj->_deed_location) || $obj->_deed_location == ""){
                 $parameters[":deed_location"] = Array("type" => PDO::PARAM_NULL, "size"=>0, "value" => null);
              } else {
                 $parameters[":deed_location"] = Array("type" => PDO::PARAM_STR, "size" => 255,"value" => $this->db->sqltidyType("nvarchar(255)", $obj->_deed_location,false));
              }
        }

    And it works fine now.

    • Marked as answer by IrishAdo Thursday, November 04, 2010 8:28 AM
    Thursday, November 04, 2010 8:28 AM

All replies

  •  

    Small update/info for this. 

    My project has generated two classes for each table in the database and four stored procedures for each table

    Example Classes

    property.dao.php - this is the work horse factory object it only knows how to Insert, Update, delete and load objects of the type property

    property.vo.php - this is the object that represents the record in the database and only holds column information no other functions.

     

    the dao (DATA ACCESS OBJECT) uses one of the 4 stored procedures (for update SP see above post)

     

    These all worked fine when I was building inline SQL and not using the prepared statement.   I was escaping the values as needed for DB insertion

    With PDO::Prepare when i tried to test other pages they were throwing errors as well so I modified the database prepare statement to ignore the precission value of a string and 90% of the pages now have no error.

    The above page still errors out but now with

    Array
    (
        [0] => HY104
        [1] => 0
        [2] => [Microsoft][SQL Server Native Client 10.0]Invalid precision value
    )
    PDOStatement Object
    (
        [queryString] => exec mr_vo_property_update @client_id=:client_id, @estate_id=:estate_id, @label=:label, @last_edit_date=:last_edit_date, @deed_location=:deed_location, @deed_packet_info=:deed_packet_info, @built=:built, @asset_value=:asset_value, @enable_eric=:enable_eric, @type_of=:type_of, @parent_id=:parent_id, @lease_type_id=:lease_type_id, @mr_contact_id=:mr_contact_id, @short_description=:short_description, @is_deleted=:is_deleted, @date_deleted=:date_deleted, @original_id=:original_id, @property_id=:property_id
    )

    The annoying thing is that the above error was fixed for the other pages and now this one is throwing this new error

     

    any ideas lads?

     

    Wednesday, November 03, 2010 3:12 PM
  • Another Update

    The following thread explains that if you specify the precision value as part of the bindParam function call then it thinks its an output value

    http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/c7431849-8594-4da2-bef6-8702b72391cc

    Which now leaves me with the "[Microsoft][SQL Server Native Client 10.0]Invalid precision value" error

     

    Wednesday, November 03, 2010 3:40 PM
  • Removing the following parameters from the SQL request allows it to execute

    @deed_location
    @deed_packet_info
    @built

     

    they are all defined in the DB as nvarchar(255)and are not the only fields that are nvarchar(255) in that statement

     

    I've updated all fields to be nvarchar in the property table which changes the SP above where defintions are varchar

     

    still no joy at figuring out this problem.

     

    Update :

    this thread http://msdn.microsoft.com/en-us/library/ms710963%28VS.85%29.aspx has this info for the error number

    HY104

    Invalid precision or scale value

    The value specified for the argument ColumnSize or DecimalDigits was outside the range of values supported by the data source for a column of the SQL data type specified by the ParameterType argument.

     

    The value of those fields are currently null

    modify in the value to have an empty string attached ie ."" added to the variable didn't work but adding ."ding" did

     

    I've tried to set the parameter type to PDO::PARAM_NULL if the value is null the problem is that the fields are empty strings (zero length) which seems to be the problem

    well thats enough for a day later

     

    Wednesday, November 03, 2010 3:58 PM
  • ...:::SOLUTION FOUND:::...

    ITS A BUG grrrrrr.

    OK my work yesterday with this lead me to correctly believe that the stored procedure was failing because I was passing an empty string "" to the parameter.  I've updated my factory objects to also set the field type to be PDO::PARAM_NULL when the type is a PDO::PARAM_STR and the string equals an empty string.

    This works because normally you will allow an string fields in your tables to be null.

    So i changed the following example block from this

        if(!is_null($obj->_deed_location)){
            $sqlParameters[count($sqlParameters)] = "@deed_location=:deed_location";
            $parameters[":deed_location"] = Array("type" => PDO::PARAM_STR, "size" => 255,"value" => $this->db->sqltidyType("nvarchar(255)", $obj->_deed_location,false));
        }

    to this

        if(!is_null($obj->_deed_location)){
            $sqlParameters[count($sqlParameters)] = "@deed_location=:deed_location";
              if(is_null($obj->_deed_location) || $obj->_deed_location == ""){
                 $parameters[":deed_location"] = Array("type" => PDO::PARAM_NULL, "size"=>0, "value" => null);
              } else {
                 $parameters[":deed_location"] = Array("type" => PDO::PARAM_STR, "size" => 255,"value" => $this->db->sqltidyType("nvarchar(255)", $obj->_deed_location,false));
              }
        }

    And it works fine now.

    • Marked as answer by IrishAdo Thursday, November 04, 2010 8:28 AM
    Thursday, November 04, 2010 8:28 AM