Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Inserting Binary Data Into SQL Server With ZF1 and PHP 7

DZone's Guide to

Inserting Binary Data Into SQL Server With ZF1 and PHP 7

It's a basic fact of web development that applications need to interact with servers and databases. Learn how to use PHP and Zend for this task.

· Web Dev Zone ·
Free Resource

Deploying code to production can be filled with uncertainty. Reduce the risks, and deploy earlier and more often. Download this free guide to learn more. Brought to you in partnership with Rollbar.

If you want to insert binary data into SQL Server in Zend Framework 1, then you probably used the trick of setting an array as the parameter's value with the info required by the sqlsrv driver as noted in Some notes on SQL Server blobs with sqlsrv.

Essentially you do this:

$data['filename'] = 'test.gif';
$data["file_contents"] = array(
    $binaryData,
    SQLSRV_PARAM_IN,
    SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), 
    SQLSRV_SQLTYPE_VARBINARY('max')
);
$db->insert($data);

Where $db is an instance of Zend_Db_Adapter_Sqlsrv.

If you use SQL Server with ZF1 and happen to have updated to PHP 7, then you may have found that you get this error:

An invalid PHP type for parameter 2 was specified

(At least, that's what happened to me!)

Working through the problem, I discovered that this is due to Zend_Db_Statement_Sqlsrv converting the $params array to references with this code:

// make all params passed by reference
$params_ = array();
$temp    = array();
$i       = 1;
foreach ($params as $param) {
    $temp[$i]  = $param;
    $params_[] = &$temp[$i];
    $i++;
}
$params = $params_;

The Sqlsrv driver (v4) for PHP 7 does not like this!

As Zend Framework 1 is EOL, we can't get a fix into upstream and update the new release, so we have to write our solution.

We want to override Zend_Db_Statement_Sqlsrv::_execute() with our own code. To do this we first need to override Zend_Db_Adapter_Sqlsrv (Also, let's assume we already have an App directory registered with the autoloader).

Firstly, our adapter:

App/Db/Adapter/Sqlsrv.php:

<?php

class App_Db_Adapter_Sqlsrv extends Zend_Db_Adapter_Sqlsrv
{
    protected $_defaultStmtClass = 'App_Db_Statement_Sqlsrv';
}

This class simply changes the default statement class to our new one. Now, we can write our Statement class:

App/Db/Statement/Sqlsrv.php:

<?php

class App_Db_Statement_Sqlsrv extends Zend_Db_Statement_Sqlsrv
{
    /**
     * Executes a prepared statement.
     *
     * @param array $params OPTIONAL Values to bind to parameter placeholders.
     * @return bool
     * @throws Zend_Db_Statement_Exception
     */
    public function _execute(array $params = null)
    {
        $connection = $this->_adapter->getConnection();
        if (!$this->_stmt) {
            return false;
        }

        if ($params !== null) {
            if (!is_array($params)) {
                $params = array($params);
            }

            // make all OUT or INOUT params passed by reference
            $params_ = array();
            $temp    = array();
            $i       = 1;
            foreach ($params as $param) {
                $temp[$i]  = $param;
                if (is_array($param) && in_array($param[1], [SQLSRV_PARAM_OUT, SQLSRV_PARAM_INOUT])) {
                    $params_[] = &$temp[$i];
                } else {
                    $params_[] = $temp[$i];
                }
                $i++;
            }
            $params = $params_;
        }

        $this->_stmt = sqlsrv_query($connection, $this->_originalSQL, $params);

        if (!$this->_stmt) {
            require_once 'Zend/Db/Statement/Sqlsrv/Exception.php';
            throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
        }

        $this->_executed = true;

        return (!$this->_stmt);
    }
}

This class takes the _execute() method from Zend_Db_Statement_Sqlsrv and makes the necessary changes to the section that creates parameter references. Specifically, we only create a reference if the parameter has a direction of SQLSRV_PARAM_OUT or SQLSRV_PARAM_INOUT:

// make all OUT or INOUT params passed by reference
$params_ = array();
$temp    = array();
$i       = 1;
foreach ($params as $param) {
    $temp[$i]  = $param;
    if (is_array($param) && in_array($param[1], [SQLSRV_PARAM_OUT, SQLSRV_PARAM_INOUT])) {
        $params_[] = &$temp[$i];
    } else {
        $params_[] = $temp[$i];
    }
    $i++;
}
$params = $params_;

Next, we need to register our new adapter with Zend_Application's Database resource. This is done in the config file:

application/configs/application.ini:

resources.db.params.adapterNamespace = "App_Db_Adapter"

That's it.

We can now insert binary data into our SQL Server database from PHP 7 using the latest sqlsrv drivers.


This article was originally published here.

Deploying code to production can be filled with uncertainty. Reduce the risks, and deploy earlier and more often. Download this free guide to learn more. Brought to you in partnership with Rollbar.

Topics:
web dev ,zend framework ,sql ,php

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}