#40452 [Com]: ODBC and SQL Server datetime parameter fields fail on insert

ID: 40452 Comment by: php at movsisyan dot com Reported By: aspen dot olmsted at alliance dot biz Status: No Feedback Bug Type: PDO related Operating System: Windows 2003, XP PHP Version: 5.2.1 Assigned To: wez New Comment: Hi guys, I faced to this problem some time ago when switched to odbc driver from native PDO mssql driver because have some problems with second one. After spending some time to find solution I saw that problem with datetime format - ODBC do not accept standard date format that acceptable by other PDO drivers. In insert or update queries use this format: date('Ymd'), this worked for me and I hope that be useful for others. MSSQL 2005 PHP 5.2.9 Previous Comments: ------------------------------------------------------------------------ [2009-10-28 18:43:58] siromega at gmail dot com I am still experiencing this problem at 5.2.11 Windows XP and 2003, MS SQL Server 2005. PHP PDO, ODBC driver. I am providing a test case that meets the requirements because this issue does not appear to be resolved. <?php $activeConnection = new PDO("odbc:mssql_dev", "phpbug","phpbug"); $stmt = $activeConnection->prepare("create table phpbug (id int, dt datetime)"); $stmt->execute(); $stmt = $activeConnection->prepare("insert into phpbug (id, dt) values (:id, :dt)"); $stmt->bindValue(":id", 1, PDO::PARAM_INT); $stmt->bindValue(":dt", "01-JAN-09", PDO::PARAM_STR); // There is no PDO::PARAM_DATE if (!$stmt->execute()) print_r($stmt->errorInfo()); ?> Error: [Microsoft][ODBC Driver Manager] Function sequence error (SQLExecute[0] at ext\pdo_odbc\odbc_stmt.c:133) ------------------------------------------------------------------------ [2009-05-03 01:00:03] php-bugs at lists dot php dot net No feedback was provided for this bug for over a week, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open". ------------------------------------------------------------------------ [2009-04-25 14:43:28] jani@php.net Please try using this CVS snapshot: http://snaps.php.net/php5.2-latest.tar.gz For Windows: http://windows.php.net/snapshots/ ------------------------------------------------------------------------ [2009-04-14 16:49:17] 0kph at 0kph dot com This error: SQLSTATE[HY010]: Function sequence error: 0 [Microsoft][ODBC Driver Manager] Function sequence error (SQLExecute[0] at ext\pdo_odbc\odbc_stmt.c:133) throws always when You bind value long than field in table. In fact, error disapear, when you cut value or extend field in table. /* c is field varchar(5) */ $sql = 'SELECT a FROM b WHERE c = ?'; $stmt = $pdo->prepare($sql); /* @var $stmt PDOStatement */ $stmt->bindValue(1, '1234567890');//10 chars $stmt->execute(); When You will change field c to varchar(10) - error disapear. I hope, it help in coding with ODBC. ------------------------------------------------------------------------ [2008-11-19 17:19:54] rolfpinto at hotmail dot com this is code is in php: <?php ... $date = '12/01/2007'; mssql_bind($srtproc, "@somedate", $date, SQLVARCHAR, FALSE, FALSE); ... ?> this is code in your store procedure under ms sql server ALTER PROCEDURE [dbo].[sp_agregaranimal] @somedate DATETIME, AS BEGIN SELECT CONVERT(datetime, @somedate, 102) END ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/40452 -- Edit this bug report at http://bugs.php.net/?id=40452&edit=1