如何使用Doctrine DBAL在Oracle数据库上执行查询时如何转义oracle保留关键字？

问题描述：

我正在尝试使用Doctrine DBAL在Oracle数据库上执行以下查询。我之前执行过其他查询，所以它不是连接问题。问题源于试图逃脱保留字Number。我需要选择一个列作为Number，以供以后的逻辑使用。查询的简化工作版本如下：如何使用Doctrine DBAL在Oracle数据库上执行查询时如何转义oracle保留关键字？

SELECT instructor.SecNum AS "Number" 
     FROM (
      SELECT dbA.ID, 
        dbA.ClsNumber as SecNum, 
        dbA.CrsCatlgNbr, 
        ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA.CrsCatlgNbr) AS rn 
      FROM dbA 
      WHERE (dbA.SubCd = '5') 
      AND (dbA.ID IS NOT NULL) 
      ) instructor, 
      (
      SELECT dbB.SecNum 
      FROM dbB 
      WHERE (dbB.SubCd = '5') 
      ) student 
     WHERE (instructor.rn = 1) 
     AND (instructor.SecNum = student.SecNum);

的PHP代码如下

// set up connection stuff and relevant variables 
$sectionTable = array(); 
$dss1prdDatabase->executeQuery(
"SELECT instructor.SecNum AS \"Number\" 
     FROM (
      SELECT dbA.ID 
       dbA.ClsNumber as SecNum 
       dbA.CrsCatlgNbr 
       ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA CrsCatlgNbr) AS rn 
      FROM dbA 
      WHERE (dbA.SubCd = '5') 
      AND (dbA.ID IS NOT NULL) 
      ) instructor, 
      (
      SELECT dbB.SecNum 
      FROM dbB 
      WHERE (dbB.SubCd = '5') 
      ) student 
     WHERE (instructor.rn = 1) 
     AND (instructor.SecNum = student.SecNum);", $sectionTable);

当我这样做，我得到一个ORA-00911: invalid character错误。

我也第一次尝试准备语句，然后逃走列名绑定到查询：

// ... 
$ESCAPEDNUMBER = '\"Number\"'; 
$sql = "SELECT instructor.SecNum AS ? 
     FROM (
      SELECT dbA.ID 
       dbA.ClsNumber as SecNum 
       dbA.CrsCatlgNbr 
       ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA CrsCatlgNbr) AS rn 
      FROM dbA 
      WHERE (dbA.SubCd = '5') 
      AND (dbA.ID IS NOT NULL) 
      ) instructor, 
      (
      SELECT dbB.SecNum 
      FROM dbB 
      WHERE (dbB.SubCd = '5') 
      ) student 
     WHERE (instructor.rn = 1) 
     AND (instructor.SecNum = student.SecNum);" 
$stmt = $dbalDatabaseConnection->prepare($sql); 
$stmt->bindvalue(1, $ESCAPEDNUMBER); 
$stmt->execute($sectionTable);

但是，这给了我一个不同的错误：ORA-00923: FROM keyword not found where expected.

也发现ORA-00923错误当我说$ESCAPEDNUMBER = addslashes('"Number"');

我不知道是什么问题。有任何想法吗？

同样我的问题是如何正确地使用Doctrine DBAL在php查询中转义oracle保留字。我使用引号，因为在甲骨文的报价查询需要，否则你会得到一个DBAL ORA-00923: FROM keyword not found where expected" error.

相关文档，可以发现here 而且还

这是MySQL的事情，我不知道是否它转换到Oracle，但你尝试添加'\''绕柱的名字吗？例如'\'dbA.ClsNumber \''编辑：看起来像[Oracle方法将使用双引号]（http://*.com/questions/1162381/how-do-i-escape-a-reserved-word -in-oracle） – sjagr

是的，Oracle使用双引号，这就是为什么我一直在逃避引号等等。 – thed0ctor

这看起来不像有效的SQL - 第二个选择列表缺少逗号。 –

答

明白了。问题是查询结尾处的分号。这在控制台中工作正常，但在运行DBAL时显然会给出ORA-00911: invalid character。去图...

固定查询：

// set up connection stuff and relevant variables 
$sectionTable = array(); 
$dss1prdDatabase->executeQuery(
"SELECT instructor.SecNum AS \"Number\" 
     FROM (
      SELECT dbA.ID 
       dbA.ClsNumber as SecNum 
       dbA.CrsCatlgNbr 
       ROW_Number() OVER (PARTITION BY dbA.ClsNumber ORDER BY dbA CrsCatlgNbr) AS rn 
      FROM dbA 
      WHERE (dbA.SubCd = '5') 
      AND (dbA.ID IS NOT NULL) 
      ) instructor, 
      (
      SELECT dbB.SecNum 
      FROM dbB 
      WHERE (dbB.SubCd = '5') 
      ) student 
     WHERE (instructor.rn = 1) 
     AND (instructor.SecNum = student.SecNum)", $sectionTable);

如何使用Doctrine DBAL在Oracle数据库上执行查询时如何转义oracle保留关键字？

相关推荐