我有一个函数,它调用另一个函数将一些值写入MSSQL数据库。它接受字符串请求作为第一个参数和一个数组,其中包含要在查询中输入的值(哪里有?如果它们包含在变量中并且不能显式地写入查询中,则在查询中。下面是有问题的函数:
public function dbInsert(){
$curr_date = date('Y-m-d H:i:s');
$this->db->query("INSERT INTO DAI_REQ.META_REQUEST ".
"(DATE_RECU, DATE_TERMINEE, USER_ID, STATUS) ".
"VALUES(?, '', ?, 'R');", array($curr_date, $this->userId));
$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(?, ?, '', ?, 'N/A', 'N/A', ?, ?, ?, ?, ?, ?, ?, ?, ?, 'R', 0, 'N/A', ?, ?, ?);",
array($this->userId, $curr_date, $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'];
}
如您所见,我两次调用query
函数,在这两个查询中,我使用包含今天日期的变量$curr_date
。不幸的是,当我在运行此代码后使用sql-server查看数据库时,我在DAI_REQ.META_REQUEST
表中看到的日期与DAI_REQ.REQUEST
表中的日期不相同。后者包含正确的日期,而前者包含随机日期(尽管我每天尝试的日期都是相似的)。例如,后者是2014-06-30 14:08:40.427
,前者是2014-02-19 00:00:00.000
我还必须提到,我们有两个服务器(所以也有两个数据库),一个用于开发,一个用于公共部署。我描述的问题发生在公共部署服务器上,而不是在开发服务器上。
我还试图通过直接调用query
函数内部的getdate()
函数来删除$curr_date
变量的使用,如下所示:
$this->db->query("INSERT INTO DAI_REQ.META_REQUEST ".
"(DATE_RECU, DATE_TERMINEE, USER_ID, STATUS) ".
"VALUES(GETDATE(), '', ?, 'R');", array($this->userId));
...
$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));
是什么导致了这种行为?
getdate()
返回一个关联数组,其中包含时间戳的日期信息,如果没有给出时间戳,则返回当前本地时间。你要找的是date()
:
$curr_date = date('Y-m-d H:i:s');
详情请参阅我的其他问题
我有这种行为的原因是因为行实际上没有在第一个INSERT
查询中插入DAI_REQ.META_REQUEST
。这是由于部署服务器的DAI_REQ.META_REQUEST
表没有设置为自动增加ID
属性。