0

Normally this code works as a T-SQL script in Visual Studio except in T-SQL I add in:

declare @json nvarchar(MAX) = '{...}'

@json goes in the OPENJSON function

I am attempting to create a PHP script for the OPENJSON insert but this doesn't seem to work. I am a total beginner to all things Azure/SQL/PHP.

For reference on the OPENJSON function: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/09/22/openjson-the-easiest-way-to-import-json-text-into-table/

<?php
    include_once("connection.php");
    $JSONrow = $_POST["jsonrow"];
    $table = $_POST["table"];
    $with = "(jobticketnumber nvarchar(50), contractor nvarchar(50),joblocation nvarchar(50), ...";

    $pdo_JSONinsert = $conn -> prepare('INSERT INTO '.$table.' SELECT * FROM OPENJSON('.$JSONrow.') WITH '.$with.'');
    $pdo_JSONinsert -> execute();

    $conn = null; 
?>

Thanks for the help!

Edit: error from PDO

<br />
<b>Fatal error</b>:  Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server]Syntax error, permission violation, or other nonspecific error in D:\home\site\wwwroot\insertrow.php:7
Stack trace:
#0 D:\home\site\wwwroot\insertrow.php(7): PDO-&gt;prepare('INSERT INTO job...')
#1 {main}
  thrown in 
<b>D:\home\site\wwwroot\insertrow.php</b> on line 
<b>7</b>
<br />
jrl98
  • 23
  • 3
  • does this php script work when called directly? – Your Common Sense Mar 03 '17 at 07:53
  • no, I have tried hard coding in the jsonrow variable and table variable but no luck. Do I need to somehow declare the json as the nvarchar? The format of the prepare statement is identical to the one from Microsoft and works in Visual Studio with the declare portion. – jrl98 Mar 03 '17 at 08:06
  • Strictly speaking, this is not a prepared statement at all. That aside, do you have [error reporting properly set](http://stackoverflow.com/a/32648423/285587)? – Your Common Sense Mar 03 '17 at 08:19
  • I just wrapped it in a try/catch and this is the error: PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server]Syntax error, permission violation, or other nonspecific error in D:\home\site\wwwroot\insertrow.php:6 Stack trace: #0 D:\home\site\wwwroot\insertrow.php(6): PDO->prepare('INSERT INTO job...') #1 {main} – jrl98 Mar 03 '17 at 08:19
  • well wrapping in a try catch is not a good idea either but at least you have a clue. echo your query out and see whether it's indeed the same. – Your Common Sense Mar 03 '17 at 08:33
  • Looks like it's time to [use a prepared statement](https://phpdelusions.net/pdo#prepared) for real – Your Common Sense Mar 03 '17 at 08:35
  • haha yeah I'm still learning, I have actually come across your site before. I looked at the other answer you linked to and got rid of the try/catch and used the PDO error reporting. I echo'd the query and I was able to fix the issue! inside the OPENPDO I needed an extra set of single quotes! Thank you for your help! – jrl98 Mar 03 '17 at 08:45

0 Answers0