Azure:PHP SQL Webjob无法正常工作.为什么在查询"execute()"上抛出该错误?陈述? [英] Azure: PHP SQL Webjob not working. Why does it get thrown on query "execute()" statement?
问题描述
为了在Azure上运行PHP网络作业以将记录插入SQL数据库中，我的代码在$query->execute();行上不断中断.
In efforts to run a PHP webjob on Azure to insert a record into a SQL database, my code kept breaking on the $query->execute(); line.
这就是我所做的.首先，我进入SQL数据库并在master db下创建登录名:
Here's what I did. First I went into my SQL database and created a login under the master db:
CREATE LOGIN username WITH password='userpassword'
然后，我以用户身份将此登录名添加到当前数据库中(不在master下，但在mydb下):
Then, I added this login as a user in my current database (not under master, but under mydb ):
CREATE USER username FROM LOGIN username
然后我通过以下命令为该用户授予写权限:
Then I gave this user write privileges with this command:
EXEC sp_addrolemember N'db_datawriter', N'username'
完成所有这些操作之后，我进入portal.azure，打开了我的App Service，导航到WebJobs，并上传了一个内部包含两个文件的.zip文件；
Having done all this, I then went into portal.azure, and opened my App Service, navigated to WebJobs and uploaded a .zip file with two files inside;
调度程序，settings.job:
{
"schedule": "0 */5 * * * *"
}
还有我的主要PHP代码updateRecord.php:
And my main PHP code, updateRecord.php:
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
try {
$conn = new PDO ( "sqlsrv:server = myazuresite.database.windows.net,1433; Database = mydb", "username", "userpassword");
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch ( PDOException $e ) {
print( "Error connecting to SQL Server." );
}
$cm = 'cm';
$span = '1day';
$stf = $conn->prepare("INSERT INTO mydbtable
VALUES ( $cm, $span, 1, 2, 3, 4, 12.34 );");
$stf->execute();
echo $stf;
unset($conn);
unset($stmt);
?>
当我启动WebJob时，它的状态从Running变为Pending restart.
As I launched the WebJob, it went from Running status to Pending restart.
[09/10/2016 21:03:14 > 82e662: SYS INFO] Detected WebJob file/s were updated, refreshing WebJob
[09/10/2016 21:03:14 > 82e662: SYS INFO] Status changed to Starting
[09/10/2016 21:03:14 > 82e662: SYS INFO] Run script 'updateRecord.php' with script host - 'PhpScriptHost'
[09/10/2016 21:03:14 > 82e662: SYS INFO] Status changed to Running
[09/10/2016 21:03:15 > 82e662: INFO]
[09/10/2016 21:03:15 > 82e662: INFO] Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'day'.' in D:\local\Temp\jobs\continuous\myjobname\uf5ls33g.42d\myjobzipfolder\updateRecord.php:20
[09/10/2016 21:03:15 > 82e662: INFO] Stack trace:
[09/10/2016 21:03:15 > 82e662: INFO] #0 D:\local\Temp\jobs\continuous\gAnaytics\uf5ls33g.42d\myjobzipfolder\updateRecord.php(20): PDOStatement->execute()
[09/10/2016 21:03:15 > 82e662: INFO] #1 {main}
[09/10/2016 21:03:15 > 82e662: INFO] thrown in D:\local\Temp\jobs\continuous\jobs\uf5ls33g.42d\myjobzipfolder\updateRecord.php on line 20
[09/10/2016 21:03:15 > 82e662: SYS ERR ] Job failed due to exit code 255
[09/10/2016 21:03:15 > 82e662: SYS INFO] Process went down, waiting for 60 seconds
[09/10/2016 21:03:15 > 82e662: SYS INFO] Status changed to PendingRestart
整个Web作业日志中最相关的，也是最相关的:
The most relevant of the entire WebJob Log and most concerning being:
[09/10/2016 21:03:15> 82e662:信息]致命错误:未捕获的异常 消息为"SQLSTATE [42000]"的"PDOException":[Microsoft] [SQL Server Native Client 11.0] [SQL Server]"day"附近的语法不正确."在 D:\ local \ Temp \ jobs \ continuous \ myjobname \ uf5ls33g.42d \ myjobzipfolder \ updateRecord.php:20
[09/10/2016 21:03:15 > 82e662: INFO] Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'day'.' in D:\local\Temp\jobs\continuous\myjobname\uf5ls33g.42d\myjobzipfolder\updateRecord.php:20
谁能诊断出如何解决此问题?我准备或执行sql语句的方式没有发现任何问题.
Can anyone diagnose how to solve this problem? I'm not seeing anything wrong with how I prepare my sql statement, or execute it.
推荐答案
您不正确地使用了准备好的语句，这将导致无效的SQL.
You are using prepared statements incorrectly and this is resulting in invalid SQL.
$stf = $conn->prepare("INSERT INTO mydbtable
VALUES (?, ?, 1, 2, 3, 4, 12.34 );");
$stf->execute(array($cm, $span));
您可以引用$cm和$span，这也可以.上面的用法是准备使用语句的方式.驱动程序处理报价.
You could quote the $cm and $span and this also would work. The above usage is the way prepared statements are meant to be used though. The driver handles the quoting.
当查询到达数据库时，它是:
When your query arrived at the DB it was:
INSERT INTO mydbtable
VALUES (1day, cm, 1, 2, 3, 4, 12.34 );
，因此需要引用1day和cm.使用占位符和绑定可以使驱动程序为您执行此操作，并且不需要转义那些变量中的值.
so the 1day and cm needed to be quoted. Using the placeholders and binding allows the driver to do this for you, and escaping values in those variables isn't needed.
这篇关于Azure:PHP SQL Webjob无法正常工作.为什么在查询"execute()"上抛出该错误?陈述?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
