在Azure上运行PHP webjob以将记录插入SQL数据库时，我的代码在$query->execute();行上一直中断。
我是这么做的。首先，我进入我的SQL数据库，并在master db下创建一个登录:
CREATE LOGIN username WITH password='userpassword'
然后，我将此登录作为用户添加到我当前的数据库中(不是在master下，而是在mydb下):
CREATE USER username FROM LOGIN username
然后我给这个用户写权限:
EXEC sp_addrolemember N'db_datawriter', N'username'
做完这一切，然后我进入传送门。azure，打开我的App Service，导航到WebJobs，上传了一个.zip文件，里面有两个文件;
调度程序，settings.job:
{
"schedule": "0 */5 * * * *"
}
和我的主PHP代码，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状态。
[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
整个WebJob日志中最相关和最令人关注的是:
[09/10/2016 21:03:15> 82e662: INFO] Fatal error: Uncaught exception'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server . SQL原生客户端11.0][SQL Server] 'day'附近的语法错误。"连续当地D: ' ' Temp '工作' ' myjobname ' uf5ls33g.42d ' myjobzipfolder ' updateRecord.php: 20
谁能诊断出如何解决这个问题?我没有看到任何错误与我如何准备我的sql语句，或执行它。
您正在错误地使用准备好的语句，这将导致无效的SQL。
$stf = $conn->prepare("INSERT INTO mydbtable
VALUES (?, ?, 1, 2, 3, 4, 12.34 );");
$stf->execute(array($cm, $span));
你可以引用$cm和$span，这也可以工作。上述用法是使用预处理语句的方式。驱动程序处理引用。
当你的查询到达DB时，它是:
INSERT INTO mydbtable
VALUES (1day, cm, 1, 2, 3, 4, 12.34 );
所以1day和cm需要加引号。使用占位符和绑定允许驱动程序为您完成此操作，并且不需要转义这些变量中的值。