php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #74626 PDO ODbC does not paramaterize datetimeoffset in Microsoft SqlServer right
Submitted: 2017-05-22 02:33 UTC Modified: 2020-09-29 11:43 UTC
From: zippy1981 at gmail dot com Assigned: cmb (profile)
Status: Not a bug Package: PDO ODBC
PHP Version: 7.1.5 OS: Windows 10
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: zippy1981 at gmail dot com
New email:
PHP Version: OS:

 

 [2017-05-22 02:33 UTC] zippy1981 at gmail dot com
Description:
------------
Lets say I have the following temp table defined:

DROP TABLE IF EXISTS #dateTable;
CREATE TABLE #dateTable (
	Id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1,1),
	[timestamp] DATETIMEOFFSET NOT NULL,
	message NVARCHAR(255) NOT NULL
);

And I have an odbc pdo connection $this->cn.

I can insert a row with
        $sql = <<< EOSQL
INSERT INTO #dateTable (message, [timestamp]) VALUES (
		'Solid string insert.',
		'%s'
	);
EOSQL;
$this->cn->exec(sprintf($sql);


However, if I paramaterize it like so:

        $sql = <<< EOSQL
INSERT INTO #dateTable (message, [timestamp]) VALUES (
		'Solid string insert.',
		:timestamp
	);
EOSQL;

        $stmt = $this->cn->prepare($sql);

None of the following options work:


1. 
    $stmt->execute([date(DATE_ATOM)]);
2.
    $timestamp = date(DATE_ATOM);
    $stmt->bindParam(':timestamp', $timestamp);
    $result = $stmt->execute();
3.
    $stmt->bindValue(':timestamp', date(DATE_ATOM));
    $result = $stmt->execute();

However, if I do PDO with the SqlSvr driver it works just fine.

Test script:
---------------
https://github.com/zippy1981/PhpSqlServerDateTime

Actual result:
--------------
PDOException: SQLSTATE[22018]: Invalid character value for cast specification: 206 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Operand type clash: text is incompatible with datetimeoffset (SQLExecute[206] at ext\pdo_odbc\odbc_stmt.c:260)

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-09-29 11:43 UTC] cmb@php.net
-Status: Open +Status: Not a bug -Assigned To: +Assigned To: cmb
 [2020-09-29 11:43 UTC] cmb@php.net
Indeed, at least the ODBC Driver for SQL Server and the SQL Server
Native Client do not accept the 'T' in the date/time value[1].
date("Y-m-d H:i:sP") should work fine.  I don't think we should
work around that for PDO_ODBC; if it works with pdo_sqlsrv, fine –
that extension appears to be better for interfacing with SQL
Server anyway.

[1] <https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements#data-formats-strings-and-literals>
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Sun Oct 25 08:01:24 2020 UTC