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

ID: 40452 Comment by: 0kph at 0kph dot com Reported By: aspen dot olmsted at alliance dot biz Status: Assigned Bug Type: PDO related Operating System: Windows 2003, XP PHP Version: 5.2.1 Assigned To: wez New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [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 ------------------------------------------------------------------------ [2008-02-29 22:20:24] ethan dot nelson at ltd dot org This bug may be due to failure of SQL Server to perform implicit conversion between character and datetime data types when the characterlength is not explicitly part of variable declaration. I posted my workaround under PDO_ODBC section. ------------------------------------------------------------------------ [2008-02-29 20:11:56] ethan dot nelson at ltd dot org This code illustrates the fact that SQL can work with a variety of date formats when moving to a SQL2005 type of datetime. To use this script, remove the sql comments '--' and comment out the appropriate params line to test PDO attempts to work with the different formats. All of them throw exceptions. <?php include("../include/config.inc"); $db = new bkg_db(); $query = "SELECT 'sqltest1' = CAST('20080101 12:07:32' AS datetime) ,'sqltest2' = CAST('2008-01-01 12:07:32' AS datetime) ,'sqltest3' = CAST('01/01/2008 12:07:32' AS datetime) ,'sqltest4' = CAST('01-01-2008 12:07:32' AS datetime) --,'pdotest' = CAST(:pdotest AS varchar) "; $params = array(':pdotest' => '20080101 12:07:32'); $params = array(':pdotest' => '2008-01-01 12:07:32'); $params = array(':pdotest' => '01/01/2008 12:07:32'); $params = array(':pdotest' => '01-01-2008 12:07:32'); $params = array(); $db->execute_query($query,$params); print_r($db->get_results()); ?> ------------------------------------------------------------------------ [2007-11-23 11:04:34] rob at tdd dot org dot uk I was experiencing the same problem with a stored procedure (SQL SERVER), however I found that I was calling the parameters in a different order in which they where expected, even though I was specifying the parameter name. -- replcate CREATE PROC hello @world VARCHAR(100), @repeat INT AS SELECT 'hello ' + REPLICATE(@world, @repeat) GO -- both give same output EXEC hello @world = 'world', @repeat = 5 EXEC hello @repeat = 5, @world = 'world' If I was to call the first exec equivalent in php using bound parameters then it works, however the php equivalent using bound parameters it fails with a function sequence error. ------------------------------------------------------------------------ [2007-10-11 14:22:29] jfdsmit at gmail dot com I have a similar experience, running PHP 5.2.4 on Windows 2003. The server /does/ accept values when they are formatted exactly yyyy-mm-dd (as per the date() format Y-m-d) /or/ if the supplied value == NULL. Empty strings and dates formatted otherwise (2007-3-12 for instance) result in a HY010 SQLSTATE Sequence error. So contrary to what daniel dot tams at gmail dot com says, the bug is formatting-dependant in my experience ------------------------------------------------------------------------ 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