"Syntax error or access violation" when inserting dates using PDO and SQL Server

1
votes

I am trying to add two simple strings and one date to a SQL Server using PDO in PHP. I'm currently using the following code to do so:

$data = array(
    'Omschrijving' => 'Mijn mooie omschrijving...',
    'Toelichting' => 'Mijn leuke toelichting...'
);

# Insert data
$STH = $DBH->prepare("INSERT INTO memo (Datum, Omschrijving, Toelichting) VALUES (NOW(), :Omschrijving, :Toelichting)");
$STH->execute($data);

It works perfectly without the date, but for some reason it gives me the following error when I try to add the date:

SQLSTATE[42000]: Syntax error or access violation: 8180 [FreeTDS][SQL Server]Statement(s) could not be prepared. (SQLExecute[8180] at /builddir/build/BUILD/php-5.6.9/ext/pdo_odbc/odbc_stmt.c:254)

Does anyone know what I'm doing wrong?

Thanks in advance!

1
phpsql-serverpdotimestamp
@chris85 No the keys doesn't need colons, but they can. Both ways works - Rizier123
Thanks for noticing @chris85, I've removed the tag "MySQL", it does not apply. I am using SQL Server. - digifrog
Have you checked, that your column names and table names are correct? - Rizier123
Now() is for mysql. msdn.microsoft.com/en-us/library/ms188383.aspx @Rizier123 thanks I don't use the binding and have seen conflicting threads on that. - chris85
@Rizier123 Yes, I have checked. If I shouldn't be using NOW(), what should I use? Thanks. - digifrog

1 Answers

2
votes

Now() is a MySQL function. GetDate() is the sql-server's equivalent. Here's their documentation on the function, https://msdn.microsoft.com/en-us/library/ms188383.aspx.

So provided code should become:

$data = array(
    'Omschrijving' => 'Mijn mooie omschrijving...',
    'Toelichting' => 'Mijn leuke toelichting...'
);

# Insert data
$STH = $DBH->prepare("INSERT INTO memo (Datum, Omschrijving, Toelichting) VALUES (GETDATE(), :Omschrijving, :Toelichting)");
$STH->execute($data);