none
[PHP7 - ODBC Driver 13] Error If my query has a criteria's length larger than columns' max length RRS feed

  • Question

  • I've created a stackoverflow question with more details here http://stackoverflow.com/questions/42464609

    I'm not completely sure if this error comes from PHP because it appears since I updated ODBC Driver.

    Basically, if I have a column `VARCHAR(10)` and my query looks like " SELECT * FROM [table] WHERE [column] = 'elevenchars' " I'll get this error:

    SQLSTATE[HY010]: Function sequence error: 0 [Microsoft][ODBC Driver Manager] Error en la secuencia de funci�n (SQLExecute[0] at ext\pdo_odbc\odbc_stmt.c:260) (SQL: SELECT* FROM table WHERE [column] = 'elevenchars')

    Any idea about this?


    • Edited by Frondor Sunday, February 26, 2017 5:06 AM
    • Moved by Dan GuzmanMVP Sunday, February 26, 2017 1:52 PM Move question to more specialized forum
    Sunday, February 26, 2017 5:05 AM

All replies

  • You mention the error started after you updated the driver. Do you mean only the ODBC driver was updated or also the Microsoft Drivers for PHP?

    An ODBC function sequence error is generally due to errors in the way the low-level ODBC driver is called. Looking at the examples in your SO question, the working queries all seem to specify literals whereas the problem ones are all parameterized and an issue only when the parameter values exceeds the defined column length. I'm not a PHP guy but these symptoms suggest the problem may be with the Microsoft Drivers for PHP or a higher level component that isn't using it properly. I can see how this error might occur if parameters are not defined or set correctly.

    Of course, no results will ever be returned when the actual value exceeds the defined column length. Attempting such a query suggests a possible application design issue since user input should be limited to the defined length. Just sayin.

    I'll move this question to the Microsoft Drivers for PHP forum where there is more PHP expertise.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, February 26, 2017 1:51 PM
  • You mention the error started after you updated the driver. Do you mean only the ODBC driver was updated or also the Microsoft Drivers for PHP?

    An ODBC function sequence error is generally due to errors in the way the low-level ODBC driver is called. Looking at the examples in your SO question, the working queries all seem to specify literals whereas the problem ones are all parameterized and an issue only when the parameter values exceeds the defined column length. I'm not a PHP guy but these symptoms suggest the problem may be with the Microsoft Drivers for PHP or a higher level component that isn't using it properly. I can see how this error might occur if parameters are not defined or set correctly.

    Of course, no results will ever be returned when the actual value exceeds the defined column length. Attempting such a query suggests a possible application design issue since user input should be limited to the defined length. Just sayin.

    I'll move this question to the Microsoft Drivers for PHP forum where there is more PHP expertise.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Hi Dan!
    To be honest, this could have been happening since the very moment I installed ODBC Driver 13 for SQL Server (https://www.microsoft.com/en-us/download/details.aspx?id=50420) in my PC, because I don't remember testing this particular case of querying things like that (with clauses' length larger than the columns schema).  You're totally right! This is all about application design but in the projects I'm making use of this driver, DB schema tends to change quite often because that's a side-project out of my hands. If the schema changes in some point, this error may start appearing in production.

    Thanks for your reply. Is there anything I can do now to help with the diagnosis and testing?

    • Edited by Frondor Sunday, February 26, 2017 4:43 PM
    Sunday, February 26, 2017 4:40 PM
  • Just traced the exception all the way to the Connection.php file

    1. in Connection.php line 770
    2. at Connection->runQueryCallback('select * from [inventory] where [Name] = ?', array('elevenchars'), object(Closure)) in Connection.php line 726
    3. at Connection->run('select * from [inventory] where [Name] = ?', array('elevenchars'), object(Closure)) in ODBCConnection.php line 59
    4. at ODBCConnection->select('select * from [inventory] where [Name] = ?', array('elevenchars'), true) in Builder.php line 1648
    5. at Builder->runSelect() in Builder.php line 1634

    github.com/laravel/framework/blob/5.3/src/Illuminate/Database/Connection.php#L323

    Error appears just after

    $statement->execute();

     at line #337

    You can replicate the error with this code:

        $serverName = '111.11.111.111,1433';
        $database = '';
        $uid = '';
        $pwd = '';
    
        $pdo = new PDO(
            "odbc:Driver={ODBC Driver 13 for SQL Server};Server={$serverName};Database={$database}",
            $uid,
            $pwd,
            array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            )
        );
    	$sql = 'select * from [inventory] where [Name] = ?';
    	$sth = $pdo->prepare($sql);
    	$sth->execute(['someStringLongerThanColumnsSchema']);
    	
    $result = $sth->fetchAll(PDO::FETCH_OBJ);




    The problem seems to be with parameter binding. for example, this won't throw an error:
    $sql = "select * from [inventory] where [Name] = 'someStringLongerThanColumnsSchema'";
    • Edited by Frondor Sunday, February 26, 2017 7:47 PM
    Sunday, February 26, 2017 6:18 PM
  • I see you've already reported this as an issue on the GitHub repro (https://github.com/Microsoft/msphpsql/issues/307). That's probably the best avenue unless someone here has another idea.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, February 27, 2017 3:07 AM