Rev 1370 | Details | Compare with Previous | Last modification | View Log | RSS feed
Rev | Author | Line No. | Line |
---|---|---|---|
1219 | daniel-mar | 1 | <?php |
2 | |||
3 | /* |
||
4 | * OIDplus 2.0 |
||
5 | * Copyright 2019 - 2023 Daniel Marschall, ViaThinkSoft |
||
6 | * |
||
7 | * Licensed under the Apache License, Version 2.0 (the "License"); |
||
8 | * you may not use this file except in compliance with the License. |
||
9 | * You may obtain a copy of the License at |
||
10 | * |
||
11 | * http://www.apache.org/licenses/LICENSE-2.0 |
||
12 | * |
||
13 | * Unless required by applicable law or agreed to in writing, software |
||
14 | * distributed under the License is distributed on an "AS IS" BASIS, |
||
15 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
||
16 | * See the License for the specific language governing permissions and |
||
17 | * limitations under the License. |
||
18 | */ |
||
19 | |||
20 | namespace ViaThinkSoft\OIDplus; |
||
21 | |||
22 | // phpcs:disable PSR1.Files.SideEffects |
||
23 | \defined('INSIDE_OIDPLUS') or die; |
||
24 | // phpcs:enable PSR1.Files.SideEffects |
||
25 | |||
26 | class OIDplusDatabaseConnectionADO extends OIDplusDatabaseConnection { |
||
27 | /** |
||
28 | * @var mixed|null |
||
29 | */ |
||
30 | private $conn = null; |
||
31 | |||
32 | /** |
||
33 | * @var string|null |
||
34 | */ |
||
35 | private $last_error = null; // do the same like MySQL+PDO, just to be equal in the behavior |
||
36 | |||
37 | /** |
||
38 | * @param string $sql |
||
39 | * @param array|null $prepared_args |
||
40 | * @return OIDplusQueryResultADO |
||
41 | * @throws OIDplusConfigInitializationException |
||
42 | * @throws OIDplusException |
||
43 | * @throws OIDplusSQLException |
||
44 | */ |
||
45 | protected function doQueryPrepareEmulation(string $sql, array $prepared_args=null): OIDplusQueryResultADO { |
||
1389 | daniel-mar | 46 | $dummy = find_nonexisting_substr($sql); |
47 | $sql = str_replace('?', $dummy, $sql); |
||
1219 | daniel-mar | 48 | foreach ($prepared_args as $arg) { |
1389 | daniel-mar | 49 | $needle = $dummy; |
1219 | daniel-mar | 50 | if (is_bool($arg)) { |
51 | if ($this->slangDetectionDone) { |
||
52 | $replace = $this->getSlang()->getSQLBool($arg); |
||
53 | } else { |
||
54 | $replace = $arg ? '1' : '0'; |
||
55 | } |
||
56 | } else if (is_int($arg)) { |
||
57 | $replace = $arg; |
||
58 | } else if (is_float($arg)) { |
||
59 | $replace = number_format($arg, 10, '.', ''); |
||
1350 | daniel-mar | 60 | } else if (is_null($arg)) { |
61 | $replace = 'NULL'; |
||
1219 | daniel-mar | 62 | } else { |
63 | // TODO: More types? |
||
64 | if ($this->slangDetectionDone) { |
||
1365 | daniel-mar | 65 | $replace = "N'".$this->getSlang()->escapeString($arg)."'"; |
1219 | daniel-mar | 66 | } else { |
1365 | daniel-mar | 67 | $replace = "N'".str_replace("'", "''", $arg)."'"; |
1219 | daniel-mar | 68 | } |
69 | } |
||
70 | $pos = strpos($sql, $needle); |
||
71 | if ($pos !== false) { |
||
72 | $sql = substr_replace($sql, $replace, $pos, strlen($needle)); |
||
73 | } |
||
74 | } |
||
1389 | daniel-mar | 75 | $sql = str_replace($dummy, '?', $sql); |
1226 | daniel-mar | 76 | return $this->doQuery($sql); |
1219 | daniel-mar | 77 | } |
78 | |||
79 | /** |
||
1226 | daniel-mar | 80 | * @var int |
81 | */ |
||
82 | private $rowsAffected = 0; |
||
83 | |||
84 | /** |
||
85 | * @return int |
||
86 | */ |
||
87 | public function rowsAffected(): int { |
||
88 | return $this->rowsAffected; |
||
89 | } |
||
90 | |||
91 | /** |
||
1219 | daniel-mar | 92 | * @param string $sql |
93 | * @param array|null $prepared_args |
||
94 | * @return OIDplusQueryResultADO |
||
95 | * @throws OIDplusException |
||
96 | */ |
||
97 | public function doQuery(string $sql, array $prepared_args=null): OIDplusQueryResult { |
||
98 | $this->last_error = null; |
||
99 | if (is_null($prepared_args)) { |
||
1226 | daniel-mar | 100 | try { |
1240 | daniel-mar | 101 | $fetchableRowsExpected = $this->slangDetectionDone ? $this->getSlang()->fetchableRowsExpected($sql) : str_starts_with(trim(strtolower($sql)),'select'); |
102 | if ($fetchableRowsExpected) { |
||
1365 | daniel-mar | 103 | $res = new \COM("ADODB.Recordset", NULL, 65001/*CP_UTF8*/); |
1219 | daniel-mar | 104 | |
1226 | daniel-mar | 105 | $res->Open($sql, $this->conn, 3/*adOpenStatic*/, 3/*adLockOptimistic*/); /** @phpstan-ignore-line */ |
106 | |||
107 | $deb = new OIDplusQueryResultADO($res); |
||
108 | |||
109 | // These two lines are important, otherwise INSERT queries won't have @@ROWCOUNT and stuff... |
||
110 | // It's probably this an MARS issue (multiple result sets open at the same time), |
||
111 | // especially because the __destruct() raises an Exception that the dataset is already closed... |
||
112 | $deb->prefetchAll(); |
||
113 | $res->Close(); /** @phpstan-ignore-line */ |
||
114 | |||
1227 | daniel-mar | 115 | // Important: Do num_rows() after prefetchAll(), because |
116 | // at OLE DB provider for SQL Server, RecordCount is -1 for queries |
||
117 | // which don't have physical row tables, e.g. "select max(id) as maxid from ###log" |
||
118 | // If we have prefetched the table, then RecordCount won't be checked; |
||
119 | // instead, the prefetched array will be counted. |
||
120 | $this->rowsAffected = $deb->num_rows(); |
||
121 | |||
1226 | daniel-mar | 122 | return $deb; |
123 | |||
124 | } else { |
||
125 | $this->conn->Execute($sql, $this->rowsAffected); |
||
1365 | daniel-mar | 126 | |
127 | // Alternatively: |
||
128 | //$cmd = new \COM("ADODB.Command", NULL, 65001/*CP_UTF8*/); |
||
129 | //$cmd->CommandText = $sql; |
||
130 | //$cmd->CommandType = 1/*adCmdText*/; |
||
131 | //$cmd->ActiveConnection = $this->conn; |
||
132 | //$cmd->Execute(); |
||
133 | |||
1226 | daniel-mar | 134 | return new OIDplusQueryResultADO(null); |
135 | } |
||
1219 | daniel-mar | 136 | } catch (\Exception $e) { |
137 | $this->last_error = $e->getMessage(); |
||
138 | throw new OIDplusSQLException($sql, $this->error()); |
||
139 | } |
||
140 | |||
141 | } else { |
||
142 | return $this->doQueryPrepareEmulation($sql, $prepared_args); |
||
143 | } |
||
144 | } |
||
145 | |||
146 | /** |
||
147 | * @return string |
||
148 | */ |
||
149 | public function error(): string { |
||
150 | $err = $this->last_error; |
||
151 | if ($err == null) $err = ''; |
||
152 | |||
153 | $err = html_to_text($err); // The original ADO Exception is HTML |
||
154 | |||
155 | return vts_utf8_encode($err); // UTF-8 encode, because ADO might output weird stuff ... |
||
156 | } |
||
157 | |||
158 | /** |
||
159 | * @return void |
||
160 | * @throws OIDplusConfigInitializationException |
||
161 | * @throws OIDplusException |
||
162 | */ |
||
163 | protected function doConnect()/*: void*/ { |
||
164 | if (strtoupper(substr(PHP_OS, 0, 3)) !== 'WIN') { |
||
165 | throw new OIDplusConfigInitializationException(_L('Functionality only available on Windows systems')); |
||
166 | } |
||
167 | |||
168 | if (!class_exists('COM')) { |
||
1221 | daniel-mar | 169 | throw new OIDplusConfigInitializationException(_L('To use %1, please enable the lines "extension=%2" and "extension_dir=ext" in the configuration file %3.',get_class(),'com_dotnet',php_ini_loaded_file() ? php_ini_loaded_file() : 'PHP.ini')); |
1219 | daniel-mar | 170 | } |
171 | |||
172 | // Try connecting to the database |
||
173 | |||
174 | $conn = new \COM("ADODB.Connection", NULL, 65001/*CP_UTF8*/); |
||
175 | |||
1220 | daniel-mar | 176 | $connStr = OIDplus::baseConfig()->getValue('ADO_CONNECTION_STRING', 'Provider=MSOLEDBSQL;Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=oidplus;Integrated Security=SSPI'); |
1219 | daniel-mar | 177 | |
1228 | daniel-mar | 178 | // TODO: Nothing seems to work! Unicode characters entered in SQL Management Studio are not showing up in OIDplus |
179 | //$connStr .= ";Client_CSet=UTF-8;Server_CSet=Windows-1251"; |
||
180 | //$connStr .= ";Client_CSet=Windows-1251;Server_CSet=UTF-8"; |
||
181 | //$connStr .= ";Client_CSet=Windows-1251;Server_CSet=Windows-1251"; |
||
182 | //$connStr .= ";Client_CSet=UTF-8;Server_CSet=UTF-8"; |
||
183 | //$connStr .= ";CharacterSet=65001"; |
||
184 | |||
1219 | daniel-mar | 185 | try { |
186 | if (stripos($connStr, "charset=") === false) { |
||
187 | // Try to extend DSN with charset |
||
1370 | daniel-mar | 188 | // Note: For MySQL, must be utf8mb4 or utf8, and not UTF-8 |
1219 | daniel-mar | 189 | try { |
190 | /** @phpstan-ignore-next-line */ |
||
191 | $conn->Open("$connStr;charset=utf8mb4"); |
||
192 | $this->conn = $conn; |
||
193 | } catch (\Exception $e1) { |
||
194 | try { |
||
195 | /** @phpstan-ignore-next-line */ |
||
196 | $conn->Open("$connStr;charset=utf8"); |
||
197 | $this->conn = $conn; |
||
198 | } catch (\Exception $e2) { |
||
199 | try { |
||
200 | /** @phpstan-ignore-next-line */ |
||
201 | $conn->Open("$connStr;charset=UTF-8"); |
||
202 | $this->conn = $conn; |
||
203 | } catch (\Exception $e3) { |
||
204 | /** @phpstan-ignore-next-line */ |
||
205 | $conn->Open($connStr); |
||
206 | $this->conn = $conn; |
||
207 | } |
||
208 | } |
||
209 | } |
||
210 | } else { |
||
211 | /** @phpstan-ignore-next-line */ |
||
212 | $conn->Open($connStr); |
||
213 | $this->conn = $conn; |
||
214 | } |
||
215 | } catch (\Exception $e) { |
||
216 | $message = $e->getMessage(); |
||
217 | $message = vts_utf8_encode($message); // Make UTF-8 if it is NOT already UTF-8. Important for German Microsoft Access. |
||
218 | throw new OIDplusConfigInitializationException(trim(_L('Connection to the database failed!').' '.$message)); |
||
219 | } |
||
220 | |||
221 | $this->last_error = null; |
||
222 | |||
223 | try { |
||
224 | /** @phpstan-ignore-next-line */ |
||
1364 | daniel-mar | 225 | $this->conn->Execute("SET NAMES 'UTF-8'"); // Does most likely NOT work with ADO. Try adding ";CHARSET=UTF8" (or similar) to the DSN |
1219 | daniel-mar | 226 | } catch (\Exception $e) { |
227 | } |
||
228 | |||
229 | try { |
||
230 | /** @phpstan-ignore-next-line */ |
||
231 | $this->conn->Execute("SET CHARACTER SET 'UTF-8'"); // Does most likely NOT work with ADO. Try adding ";CHARSET=UTF8" (or similar) to the DSN |
||
232 | } catch (\Exception $e) { |
||
233 | } |
||
234 | |||
235 | try { |
||
236 | /** @phpstan-ignore-next-line */ |
||
237 | $this->conn->Execute("SET NAMES 'utf8mb4'"); // Does most likely NOT work with ADO. Try adding ";CHARSET=UTF8" (or similar) to the DSN |
||
238 | } catch (\Exception $e) { |
||
239 | } |
||
240 | } |
||
241 | |||
242 | /** |
||
243 | * @return void |
||
244 | */ |
||
245 | protected function doDisconnect()/*: void*/ { |
||
246 | if (!is_null($this->conn)) { |
||
1226 | daniel-mar | 247 | if ($this->conn->State != 0) $this->conn->Close(); |
1219 | daniel-mar | 248 | $this->conn = null; |
249 | } |
||
250 | } |
||
251 | |||
252 | /** |
||
253 | * @return array |
||
254 | */ |
||
255 | private function connectionProperties(): array { |
||
256 | $ary = array(); |
||
257 | for ($i=0; $i<$this->conn->Properties->Count; $i++) { |
||
258 | $ary[$this->conn->Properties->Item($i)->Name] = $this->conn->Properties->Item($i)->Value; |
||
259 | } |
||
260 | return $ary; |
||
261 | } |
||
262 | |||
263 | /** |
||
264 | * @var int |
||
265 | */ |
||
266 | private $trans_level = 0; |
||
267 | |||
268 | /** |
||
269 | * @return bool |
||
270 | */ |
||
271 | public function transaction_supported(): bool { |
||
272 | // DBPROPVAL_TC_NONE 0 TAs werden nicht unterstützt |
||
273 | // DBPROPVAL_TC_DML 1 TAs können nur DML ausführen. DDLs verursachen Fehler. |
||
274 | // DBPROPVAL_TC_DDL_COMMIT 2 TAs können nur DML ausführen. DDLs bewirken einen COMMIT. |
||
275 | // DBPROPVAL_TC_DDL_IGNORE 4 TAs können nur DML statements enthalten. DDL statements werden ignoriert. |
||
276 | // DBPROPVAL_TC_ALL 8 TAs werden vollständig unterstützt. |
||
277 | // DBPROPVAL_TC_DDL_LOCK 16 TAs können DML+DDL statements sein. Tabellen oder Indices erhalten bei Modifikation aber eine Lock für die Dauer der TA. |
||
278 | $props = $this->connectionProperties(); |
||
279 | return $props['Transaction DDL'] >= 8; |
||
280 | } |
||
281 | |||
282 | /** |
||
283 | * @return int |
||
284 | */ |
||
285 | public function transaction_level(): int { |
||
286 | if (!$this->transaction_supported()) { |
||
287 | // TODO? |
||
288 | return 0; |
||
289 | } |
||
290 | return $this->trans_level; |
||
291 | } |
||
292 | |||
293 | /** |
||
294 | * @return void |
||
295 | * @throws OIDplusException |
||
296 | */ |
||
297 | public function transaction_begin()/*: void*/ { |
||
298 | if (!$this->transaction_supported()) { |
||
299 | // TODO? |
||
300 | return; |
||
301 | } |
||
302 | if ($this->trans_level > 0) throw new OIDplusException(_L('Nested transactions are not supported by this database plugin.')); |
||
303 | $this->trans_level = $this->conn->BeginTrans(); |
||
304 | } |
||
305 | |||
306 | /** |
||
307 | * @return void |
||
308 | */ |
||
309 | public function transaction_commit()/*: void*/ { |
||
310 | if (!$this->transaction_supported()) { |
||
311 | // TODO? |
||
312 | return; |
||
313 | } |
||
314 | $this->conn->CommitTrans(); |
||
315 | $this->trans_level--; |
||
316 | } |
||
317 | |||
318 | /** |
||
319 | * @return void |
||
320 | */ |
||
321 | public function transaction_rollback()/*: void*/ { |
||
322 | if (!$this->transaction_supported()) { |
||
323 | // TODO? |
||
324 | return; |
||
325 | } |
||
326 | $this->conn->RollbackTrans(); |
||
327 | $this->trans_level--; |
||
328 | } |
||
1220 | daniel-mar | 329 | |
330 | /** |
||
331 | * @return array |
||
332 | */ |
||
333 | public function getExtendedInfo(): array { |
||
1221 | daniel-mar | 334 | $props = $this->connectionProperties(); |
1222 | daniel-mar | 335 | if (isset($props['Provider Name'])) { |
336 | // https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver16 |
||
337 | if (strtoupper($props['Provider Name']) == 'SQLOLEDB.DLL') { |
||
338 | $props['OLE DB for SQL Server Provider Generation'] = _L('Generation %1', 1); |
||
339 | } else if (strtoupper($props['Provider Name']) == 'SQLNCLI11.DLL') { |
||
340 | $props['OLE DB for SQL Server Provider Generation'] = _L('Generation %1', 2); |
||
341 | } else if (strtoupper($props['Provider Name']) == 'MSOLEDBSQL.DLL') { |
||
342 | $props['OLE DB for SQL Server Provider Generation'] = _L('Generation %1', 3); |
||
343 | } |
||
344 | } |
||
1221 | daniel-mar | 345 | if (isset($props['Password'])) $props['Password'] = '['._L('redacted').']'; |
346 | return $props; |
||
1220 | daniel-mar | 347 | } |
348 | |||
1219 | daniel-mar | 349 | } |