MSSQL INSERT查询在使用PHP调用时失败特定表 [英] MSSQL INSERT query failing for a specific table when called with PHP

问题描述

我需要读取从我们的网站创建请求的日期。当创建该请求时，对应于该请求及其元请求的信息被插入到 DAI_REQ.REQUEST 和 DAI_REQ.META_REQUEST 表。我们还有一个开发服务器和一个公共部署服务器。

I need to read the date that a request was created from our website. When that request is created, the information corresponding to that request and its meta-request is inserted in the DAI_REQ.REQUEST and DAI_REQ.META_REQUEST tables, respectively. We also have a dev server and a public deployment server. The problem happens only on our deployment server for some reason..

不幸的是， INSERT 查询插入信息在 DAI_REQ.META_REQUEST 表中的元请求不起作用，但是我之后做的 SELECT （所以在我看来，这消除了数据库/表本身的任何连接问题）。我还使用与 DAI_REQ.REQUEST 上的 INSERT 查询相同的语法，因此我不认为是一个查询语法问题。我也尝试手动插入作为行在sql-server和它工作正常。最后，我使用 $ this-> userId 的值 echo code> INSERT 查询它是否包含正确的ID，它是否。我对于 $ this-> db-> query（...）的返回值也是这样，它不返回任何东西（在我们的部署服务器上）。

Unfortunately, the INSERT query to insert the information of the meta-request in the DAI_REQ.META_REQUEST table does not work, but the SELECT query I do right after does (so in my eyes, this removes any connection problems with the database/table itself). I also use the same syntax as the INSERT query I do on the DAI_REQ.REQUEST, so I do not think it is a query syntax problem. I also tried manually inserting as line within sql-server and it works fine. Finally, I echo'ed the value of $this->userId that I use as a parameter for the INSERT query to see if it contained the right ID, and it does. I did the same for the return value of $this->db->query(...), and it does NOT return anything (on our deployment server only).

我也知道我检索表中最后一行插入行的方式不完美，但这不是这里的问题，它会被改变

I also know that my way of retrieving the last inserted row in a table is not perfect, but this is not the problem at hand here and it will be changed later on.

这是发生问题的实际代码：

Here is the actual code where the problem happens:

public function dbInsert(){

    // The actual problematic query
    $this->db->query("INSERT INTO DAI_REQ.META_REQUEST ".
        "(DATE_RECU, DATE_TERMINEE, USER_ID, STATUS) ".
        "VALUES(GETDATE(), '', ?, 'R');", array($this->userId));

    // This works fine though
    $mr_select = $this->db->query("SELECT TOP 1 ID FROM DAI_REQ.META_REQUEST WHERE USER_ID = ? ORDER BY ID DESC;",
            array($this->userId));
    $mr_result = $mr_select->result_array();
    $mr_id = $mr_result[0]['ID'];

    $sim = 'N/A';
    if(isset($this->recurrenceType))
        $sim = 'Recurrent';

    $this->db->query("INSERT INTO DAI_REQ.REQUEST ".
        "(USER_ID, ASSIGNED_DATE, REQUEST_END_DATE, MODEL, EXPERIMENT, VARIABLE, START_DATE, END_DATE, ".
        "LON_FROM, LAT_FROM, LON_TO, LAT_TO, RESOLUTION, FORMAT, SIMULATION, STATUS, ".
        "CANCELLED_YN, PROJECT, MR_ID, URL_ORIGIN, DATE_EMAIL) ".
        "VALUES(?, GETDATE(), '', ?, 'N/A', 'N/A', ?, ?, ?, ?, ?, ?, ?, ?, ?, 'R', 0, 'N/A', ?, ?, ?);",
        array($this->userId, $this->model, $this->startDate, $this->endDate, 
                $this->lonFrom, $this->latFrom, $this->lonTo, $this->latTo, 
                $this->resolution, $this->format, $sim, $mr_id, $this->url_origin, $this->date_email));

    $r_select = $this->db->query("SELECT TOP 1 ID FROM DAI_REQ.REQUEST WHERE USER_ID = ? ORDER BY ID DESC;",
            array($this->userId));
    $r_result = $r_select->result_array();
    $this->id = $r_result[0]['ID'];
}

推荐答案

部署服务器正在使用未设置为自动递增 ID 列。在Microsoft SQL Server中，对于 ID 列，可以将 Identity 设置为 / code>和标识增量到您希望 ID 列增加的任何数字。

The database that the deployment server is using isn't set up to auto increment the ID column. In Microsoft SQL Server, for the ID column, you can set the Identity to Yes and Identity Increment to whatever number you want the ID column to increment by.

这篇关于MSSQL INSERT查询在使用PHP调用时失败特定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！