|
1
|
|
|
<?php |
|
2
|
|
|
namespace Darya\Database\Connection; |
|
3
|
|
|
|
|
4
|
|
|
use Darya\Database\AbstractConnection; |
|
5
|
|
|
use Darya\Database\Error; |
|
6
|
|
|
use Darya\Database\Query\Translator; |
|
7
|
|
|
use Darya\Database\Result; |
|
8
|
|
|
use Darya\Database\Query; |
|
9
|
|
|
|
|
10
|
|
|
/** |
|
11
|
|
|
* Darya's SQL Server (MSSQL) database interface for Windows. |
|
12
|
|
|
* |
|
13
|
|
|
* @author Chris Andrew <[email protected]> |
|
14
|
|
|
*/ |
|
15
|
|
|
class SqlServer extends AbstractConnection { |
|
16
|
|
|
|
|
17
|
|
|
/** |
|
18
|
|
|
* Initiate the connection. |
|
19
|
|
|
* |
|
20
|
|
|
* @return bool |
|
21
|
|
|
*/ |
|
22
|
|
|
public function connect() { |
|
23
|
|
|
if ($this->connected()) { |
|
24
|
|
|
return true; |
|
25
|
|
|
} |
|
26
|
|
|
|
|
27
|
|
|
$host = $this->details['host']; |
|
28
|
|
|
|
|
29
|
|
|
if ($this->details['port']) { |
|
30
|
|
|
$host .= ', ' . $this->details['port']; |
|
31
|
|
|
} |
|
32
|
|
|
|
|
33
|
|
|
$this->connection = sqlsrv_connect($this->details['host'], array( |
|
34
|
|
|
'UID' => $this->details['user'], |
|
35
|
|
|
'PWD' => $this->details['pass'], |
|
36
|
|
|
'Database' => $this->details['name'] |
|
37
|
|
|
)); |
|
38
|
|
|
|
|
39
|
|
|
if ($this->error()) { |
|
40
|
|
|
return false; |
|
41
|
|
|
} |
|
42
|
|
|
|
|
43
|
|
|
return $this->connected = true; |
|
44
|
|
|
} |
|
45
|
|
|
|
|
46
|
|
|
/** |
|
47
|
|
|
* Close the connection. |
|
48
|
|
|
*/ |
|
49
|
|
|
public function disconnect() { |
|
50
|
|
|
if ($this->connected()) { |
|
51
|
|
|
sqlsrv_close($this->connection); |
|
52
|
|
|
} |
|
53
|
|
|
|
|
54
|
|
|
$this->connected = false; |
|
55
|
|
|
} |
|
56
|
|
|
|
|
57
|
|
|
/** |
|
58
|
|
|
* Retrieve the query translator. |
|
59
|
|
|
* |
|
60
|
|
|
* @return Translator |
|
61
|
|
|
*/ |
|
62
|
|
|
public function translator() { |
|
63
|
|
|
if (!$this->translator) { |
|
64
|
|
|
$this->translator = new Translator\SqlServer; |
|
65
|
|
|
} |
|
66
|
|
|
|
|
67
|
|
|
return $this->translator; |
|
68
|
|
|
} |
|
69
|
|
|
|
|
70
|
|
|
/** |
|
71
|
|
|
* Query the database for the last ID generated, if the given query is |
|
72
|
|
|
* an insert query. |
|
73
|
|
|
* |
|
74
|
|
|
* TODO: Use SCOPE_IDENTITY() instead? |
|
75
|
|
|
* |
|
76
|
|
|
* @param string $query |
|
77
|
|
|
* @return int |
|
78
|
|
|
*/ |
|
79
|
|
|
protected function queryInsertId($query) { |
|
|
|
|
|
|
80
|
|
|
if (!preg_match('/^\s*INSERT\s+INTO\b/i', $query)) { |
|
81
|
|
|
return null; |
|
82
|
|
|
} |
|
83
|
|
|
|
|
84
|
|
|
$result = sqlsrv_query($this->connection, "SELECT @@IDENTITY id"); |
|
85
|
|
|
list($id) = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC); |
|
86
|
|
|
|
|
87
|
|
|
return $id; |
|
88
|
|
|
} |
|
89
|
|
|
|
|
90
|
|
|
/** |
|
91
|
|
|
* Query the database for the number of rows affected by the last query. |
|
92
|
|
|
* |
|
93
|
|
|
* @param string $query |
|
94
|
|
|
* @return int |
|
95
|
|
|
*/ |
|
96
|
|
|
protected function queryAffected($query) { |
|
|
|
|
|
|
97
|
|
|
if (preg_match('/^\s*SELECT\b/i', $query)) { |
|
98
|
|
|
return null; |
|
99
|
|
|
} |
|
100
|
|
|
|
|
101
|
|
|
$result = sqlsrv_query($this->connection, "SELECT @@ROWCOUNT affected"); |
|
102
|
|
|
list($affected) = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC); |
|
103
|
|
|
|
|
104
|
|
|
return $affected; |
|
105
|
|
|
} |
|
106
|
|
|
|
|
107
|
|
|
/** |
|
108
|
|
|
* Query the database. |
|
109
|
|
|
* |
|
110
|
|
|
* TODO: Also simplify this. |
|
111
|
|
|
* |
|
112
|
|
|
* @param Query|string $query |
|
113
|
|
|
* @param array $parameters [optional] |
|
114
|
|
|
* @return Result |
|
115
|
|
|
*/ |
|
116
|
|
|
public function query($query, array $parameters = array()) { |
|
117
|
|
|
if (!$query instanceof Query) { |
|
118
|
|
|
$query = new Query($query, $parameters); |
|
119
|
|
|
} |
|
120
|
|
|
|
|
121
|
|
|
$this->connect(); |
|
122
|
|
|
|
|
123
|
|
View Code Duplication |
if (!$this->connected()) { |
|
|
|
|
|
|
124
|
|
|
$this->lastResult = new Result($query, array(), array(), $this->error()); |
|
125
|
|
|
|
|
126
|
|
|
$this->event('sqlserver.query', array($this->lastResult)); |
|
127
|
|
|
|
|
128
|
|
|
return $this->lastResult; |
|
129
|
|
|
} |
|
130
|
|
|
|
|
131
|
|
|
$this->lastResult = null; |
|
132
|
|
|
|
|
133
|
|
|
$this->event('sqlserver.prequery', array($query)); |
|
134
|
|
|
|
|
135
|
|
|
$mssql_result = sqlsrv_query($this->connection, $query->string, $query->parameters, array( |
|
136
|
|
|
'Scrollable' => SQLSRV_CURSOR_CLIENT_BUFFERED |
|
137
|
|
|
)); |
|
138
|
|
|
|
|
139
|
|
|
$result = array( |
|
140
|
|
|
'data' => array(), |
|
141
|
|
|
'fields' => array(), |
|
142
|
|
|
'affected' => null, |
|
143
|
|
|
'num_rows' => null, |
|
144
|
|
|
'insert_id' => null |
|
145
|
|
|
); |
|
146
|
|
|
|
|
147
|
|
|
$error = $this->error(); |
|
148
|
|
|
|
|
149
|
|
|
if ($mssql_result === false || $error) { |
|
|
|
|
|
|
150
|
|
|
$this->lastResult = new Result($query, array(), array(), $error); |
|
151
|
|
|
|
|
152
|
|
|
$this->event('sqlserver.query', array($this->lastResult)); |
|
153
|
|
|
|
|
154
|
|
|
return $this->lastResult; |
|
155
|
|
|
} |
|
156
|
|
|
|
|
157
|
|
|
$result['num_rows'] = sqlsrv_num_rows($mssql_result); |
|
158
|
|
|
|
|
159
|
|
|
if ($result['num_rows']) { |
|
160
|
|
|
while ($row = sqlsrv_fetch_array($mssql_result, SQLSRV_FETCH_ASSOC)) { |
|
161
|
|
|
if (!$result['fields']) { |
|
162
|
|
|
$result['fields'] = array_keys($row); |
|
163
|
|
|
} |
|
164
|
|
|
|
|
165
|
|
|
$result['data'][] = $row; |
|
166
|
|
|
} |
|
167
|
|
|
} |
|
168
|
|
|
|
|
169
|
|
|
$result['insert_id'] = $this->queryInsertId($query); |
|
170
|
|
|
$result['affected'] = $this->queryAffected($query); |
|
171
|
|
|
|
|
172
|
|
|
$info = array( |
|
173
|
|
|
'count' => $result['num_rows'], |
|
174
|
|
|
'fields' => $result['fields'], |
|
175
|
|
|
'affected' => $result['affected'], |
|
176
|
|
|
'insert_id' => $result['insert_id'] |
|
177
|
|
|
); |
|
178
|
|
|
|
|
179
|
|
|
$this->lastResult = new Result($query, $result['data'], $info, $error); |
|
180
|
|
|
|
|
181
|
|
|
$this->event('sqlserver.query', array($this->lastResult)); |
|
182
|
|
|
|
|
183
|
|
|
return $this->lastResult; |
|
184
|
|
|
} |
|
185
|
|
|
|
|
186
|
|
|
/** |
|
187
|
|
|
* Retrieve error information regarding the last query or connection |
|
188
|
|
|
* attempt. |
|
189
|
|
|
* |
|
190
|
|
|
* Returns null if there is no error. |
|
191
|
|
|
* |
|
192
|
|
|
* @return Error |
|
193
|
|
|
*/ |
|
194
|
|
|
public function error() { |
|
195
|
|
|
$errors = sqlsrv_errors(SQLSRV_ERR_ERRORS); |
|
196
|
|
|
|
|
197
|
|
|
if (!$errors) { |
|
198
|
|
|
return null; |
|
199
|
|
|
} |
|
200
|
|
|
|
|
201
|
|
|
return new Error($errors[0]['code'], $errors[0]['message']); |
|
202
|
|
|
} |
|
203
|
|
|
} |
|
204
|
|
|
|
darya /
framework
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.