Rev 1227 | Rev 1240 | Go to most recent revision | 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 { |
||
46 | $sql = str_replace('?', chr(1), $sql); |
||
47 | foreach ($prepared_args as $arg) { |
||
48 | $needle = chr(1); |
||
49 | if (is_bool($arg)) { |
||
50 | if ($this->slangDetectionDone) { |
||
51 | $replace = $this->getSlang()->getSQLBool($arg); |
||
52 | } else { |
||
53 | $replace = $arg ? '1' : '0'; |
||
54 | } |
||
55 | } else if (is_int($arg)) { |
||
56 | $replace = $arg; |
||
57 | } else if (is_float($arg)) { |
||
58 | $replace = number_format($arg, 10, '.', ''); |
||
59 | } else { |
||
60 | // TODO: More types? |
||
61 | if ($this->slangDetectionDone) { |
||
62 | $replace = "'".$this->getSlang()->escapeString($arg ?? '')."'"; |
||
63 | } else { |
||
64 | $replace = "'".str_replace("'", "''", $arg)."'"; |
||
65 | } |
||
66 | } |
||
67 | $pos = strpos($sql, $needle); |
||
68 | if ($pos !== false) { |
||
69 | $sql = substr_replace($sql, $replace, $pos, strlen($needle)); |
||
70 | } |
||
71 | } |
||
72 | $sql = str_replace(chr(1), '?', $sql); |
||
1226 | daniel-mar | 73 | return $this->doQuery($sql); |
1219 | daniel-mar | 74 | } |
75 | |||
76 | /** |
||
1226 | daniel-mar | 77 | * @var int |
78 | */ |
||
79 | private $rowsAffected = 0; |
||
80 | |||
81 | /** |
||
82 | * @return int |
||
83 | */ |
||
84 | public function rowsAffected(): int { |
||
85 | return $this->rowsAffected; |
||
86 | } |
||
87 | |||
88 | /** |
||
1219 | daniel-mar | 89 | * @param string $sql |
90 | * @param array|null $prepared_args |
||
91 | * @return OIDplusQueryResultADO |
||
92 | * @throws OIDplusException |
||
93 | */ |
||
94 | public function doQuery(string $sql, array $prepared_args=null): OIDplusQueryResult { |
||
95 | $this->last_error = null; |
||
96 | if (is_null($prepared_args)) { |
||
1226 | daniel-mar | 97 | try { |
98 | if (str_starts_with(trim(strtolower($sql)),'select')) { |
||
99 | $res = new \COM("ADODB.Recordset"); |
||
1219 | daniel-mar | 100 | |
1226 | daniel-mar | 101 | $res->Open($sql, $this->conn, 3/*adOpenStatic*/, 3/*adLockOptimistic*/); /** @phpstan-ignore-line */ |
102 | |||
103 | $deb = new OIDplusQueryResultADO($res); |
||
104 | |||
105 | // These two lines are important, otherwise INSERT queries won't have @@ROWCOUNT and stuff... |
||
106 | // It's probably this an MARS issue (multiple result sets open at the same time), |
||
107 | // especially because the __destruct() raises an Exception that the dataset is already closed... |
||
108 | $deb->prefetchAll(); |
||
109 | $res->Close(); /** @phpstan-ignore-line */ |
||
110 | |||
1227 | daniel-mar | 111 | // Important: Do num_rows() after prefetchAll(), because |
112 | // at OLE DB provider for SQL Server, RecordCount is -1 for queries |
||
113 | // which don't have physical row tables, e.g. "select max(id) as maxid from ###log" |
||
114 | // If we have prefetched the table, then RecordCount won't be checked; |
||
115 | // instead, the prefetched array will be counted. |
||
116 | $this->rowsAffected = $deb->num_rows(); |
||
117 | |||
1226 | daniel-mar | 118 | return $deb; |
119 | |||
120 | } else { |
||
121 | $this->conn->Execute($sql, $this->rowsAffected); |
||
122 | return new OIDplusQueryResultADO(null); |
||
123 | } |
||
1219 | daniel-mar | 124 | } catch (\Exception $e) { |
125 | $this->last_error = $e->getMessage(); |
||
126 | throw new OIDplusSQLException($sql, $this->error()); |
||
127 | } |
||
128 | |||
129 | } else { |
||
130 | return $this->doQueryPrepareEmulation($sql, $prepared_args); |
||
131 | } |
||
132 | } |
||
133 | |||
134 | /** |
||
135 | * @return string |
||
136 | */ |
||
137 | public function error(): string { |
||
138 | $err = $this->last_error; |
||
139 | if ($err == null) $err = ''; |
||
140 | |||
141 | $err = html_to_text($err); // The original ADO Exception is HTML |
||
142 | |||
143 | return vts_utf8_encode($err); // UTF-8 encode, because ADO might output weird stuff ... |
||
144 | } |
||
145 | |||
146 | /** |
||
147 | * @return void |
||
148 | * @throws OIDplusConfigInitializationException |
||
149 | * @throws OIDplusException |
||
150 | */ |
||
151 | protected function doConnect()/*: void*/ { |
||
152 | if (strtoupper(substr(PHP_OS, 0, 3)) !== 'WIN') { |
||
153 | throw new OIDplusConfigInitializationException(_L('Functionality only available on Windows systems')); |
||
154 | } |
||
155 | |||
156 | if (!class_exists('COM')) { |
||
1221 | daniel-mar | 157 | 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 | 158 | } |
159 | |||
160 | // Try connecting to the database |
||
161 | |||
162 | $conn = new \COM("ADODB.Connection", NULL, 65001/*CP_UTF8*/); |
||
163 | |||
1220 | daniel-mar | 164 | $connStr = OIDplus::baseConfig()->getValue('ADO_CONNECTION_STRING', 'Provider=MSOLEDBSQL;Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=oidplus;Integrated Security=SSPI'); |
1219 | daniel-mar | 165 | |
1228 | daniel-mar | 166 | // TODO: Nothing seems to work! Unicode characters entered in SQL Management Studio are not showing up in OIDplus |
167 | //$connStr .= ";Client_CSet=UTF-8;Server_CSet=Windows-1251"; |
||
168 | //$connStr .= ";Client_CSet=Windows-1251;Server_CSet=UTF-8"; |
||
169 | //$connStr .= ";Client_CSet=Windows-1251;Server_CSet=Windows-1251"; |
||
170 | //$connStr .= ";Client_CSet=UTF-8;Server_CSet=UTF-8"; |
||
171 | //$connStr .= ";CharacterSet=65001"; |
||
172 | |||
1219 | daniel-mar | 173 | try { |
174 | if (stripos($connStr, "charset=") === false) { |
||
175 | // Try to extend DSN with charset |
||
176 | // Note: For MySQL, must be utf8 or utf8, and not UTF-8 |
||
177 | try { |
||
178 | /** @phpstan-ignore-next-line */ |
||
179 | $conn->Open("$connStr;charset=utf8mb4"); |
||
180 | $this->conn = $conn; |
||
181 | } catch (\Exception $e1) { |
||
182 | try { |
||
183 | /** @phpstan-ignore-next-line */ |
||
184 | $conn->Open("$connStr;charset=utf8"); |
||
185 | $this->conn = $conn; |
||
186 | } catch (\Exception $e2) { |
||
187 | try { |
||
188 | /** @phpstan-ignore-next-line */ |
||
189 | $conn->Open("$connStr;charset=UTF-8"); |
||
190 | $this->conn = $conn; |
||
191 | } catch (\Exception $e3) { |
||
192 | /** @phpstan-ignore-next-line */ |
||
193 | $conn->Open($connStr); |
||
194 | $this->conn = $conn; |
||
195 | } |
||
196 | } |
||
197 | } |
||
198 | } else { |
||
199 | /** @phpstan-ignore-next-line */ |
||
200 | $conn->Open($connStr); |
||
201 | $this->conn = $conn; |
||
202 | } |
||
203 | } catch (\Exception $e) { |
||
204 | $message = $e->getMessage(); |
||
205 | $message = vts_utf8_encode($message); // Make UTF-8 if it is NOT already UTF-8. Important for German Microsoft Access. |
||
206 | throw new OIDplusConfigInitializationException(trim(_L('Connection to the database failed!').' '.$message)); |
||
207 | } |
||
208 | |||
209 | $this->last_error = null; |
||
210 | |||
211 | try { |
||
212 | /** @phpstan-ignore-next-line */ |
||
213 | $this->conn->Execute( "SET NAMES 'UTF-8'"); // Does most likely NOT work with ADO. Try adding ";CHARSET=UTF8" (or similar) to the DSN |
||
214 | } catch (\Exception $e) { |
||
215 | } |
||
216 | |||
217 | try { |
||
218 | /** @phpstan-ignore-next-line */ |
||
219 | $this->conn->Execute("SET CHARACTER SET 'UTF-8'"); // Does most likely NOT work with ADO. Try adding ";CHARSET=UTF8" (or similar) to the DSN |
||
220 | } catch (\Exception $e) { |
||
221 | } |
||
222 | |||
223 | try { |
||
224 | /** @phpstan-ignore-next-line */ |
||
225 | $this->conn->Execute("SET NAMES 'utf8mb4'"); // Does most likely NOT work with ADO. Try adding ";CHARSET=UTF8" (or similar) to the DSN |
||
226 | } catch (\Exception $e) { |
||
227 | } |
||
228 | } |
||
229 | |||
230 | /** |
||
231 | * @return void |
||
232 | */ |
||
233 | protected function doDisconnect()/*: void*/ { |
||
234 | if (!is_null($this->conn)) { |
||
1226 | daniel-mar | 235 | if ($this->conn->State != 0) $this->conn->Close(); |
1219 | daniel-mar | 236 | $this->conn = null; |
237 | } |
||
238 | } |
||
239 | |||
240 | /** |
||
241 | * @return array |
||
242 | */ |
||
243 | private function connectionProperties(): array { |
||
244 | $ary = array(); |
||
245 | for ($i=0; $i<$this->conn->Properties->Count; $i++) { |
||
246 | $ary[$this->conn->Properties->Item($i)->Name] = $this->conn->Properties->Item($i)->Value; |
||
247 | } |
||
248 | return $ary; |
||
249 | } |
||
250 | |||
251 | /** |
||
252 | * @var int |
||
253 | */ |
||
254 | private $trans_level = 0; |
||
255 | |||
256 | /** |
||
257 | * @return bool |
||
258 | */ |
||
259 | public function transaction_supported(): bool { |
||
260 | // DBPROPVAL_TC_NONE 0 TAs werden nicht unterstützt |
||
261 | // DBPROPVAL_TC_DML 1 TAs können nur DML ausführen. DDLs verursachen Fehler. |
||
262 | // DBPROPVAL_TC_DDL_COMMIT 2 TAs können nur DML ausführen. DDLs bewirken einen COMMIT. |
||
263 | // DBPROPVAL_TC_DDL_IGNORE 4 TAs können nur DML statements enthalten. DDL statements werden ignoriert. |
||
264 | // DBPROPVAL_TC_ALL 8 TAs werden vollständig unterstützt. |
||
265 | // 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. |
||
266 | $props = $this->connectionProperties(); |
||
267 | return $props['Transaction DDL'] >= 8; |
||
268 | } |
||
269 | |||
270 | /** |
||
271 | * @return int |
||
272 | */ |
||
273 | public function transaction_level(): int { |
||
274 | if (!$this->transaction_supported()) { |
||
275 | // TODO? |
||
276 | return 0; |
||
277 | } |
||
278 | return $this->trans_level; |
||
279 | } |
||
280 | |||
281 | /** |
||
282 | * @return void |
||
283 | * @throws OIDplusException |
||
284 | */ |
||
285 | public function transaction_begin()/*: void*/ { |
||
286 | if (!$this->transaction_supported()) { |
||
287 | // TODO? |
||
288 | return; |
||
289 | } |
||
290 | if ($this->trans_level > 0) throw new OIDplusException(_L('Nested transactions are not supported by this database plugin.')); |
||
291 | $this->trans_level = $this->conn->BeginTrans(); |
||
292 | } |
||
293 | |||
294 | /** |
||
295 | * @return void |
||
296 | */ |
||
297 | public function transaction_commit()/*: void*/ { |
||
298 | if (!$this->transaction_supported()) { |
||
299 | // TODO? |
||
300 | return; |
||
301 | } |
||
302 | $this->conn->CommitTrans(); |
||
303 | $this->trans_level--; |
||
304 | } |
||
305 | |||
306 | /** |
||
307 | * @return void |
||
308 | */ |
||
309 | public function transaction_rollback()/*: void*/ { |
||
310 | if (!$this->transaction_supported()) { |
||
311 | // TODO? |
||
312 | return; |
||
313 | } |
||
314 | $this->conn->RollbackTrans(); |
||
315 | $this->trans_level--; |
||
316 | } |
||
1220 | daniel-mar | 317 | |
318 | /** |
||
319 | * @return array |
||
320 | */ |
||
321 | public function getExtendedInfo(): array { |
||
1221 | daniel-mar | 322 | $props = $this->connectionProperties(); |
1222 | daniel-mar | 323 | if (isset($props['Provider Name'])) { |
324 | // https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver16 |
||
325 | if (strtoupper($props['Provider Name']) == 'SQLOLEDB.DLL') { |
||
326 | $props['OLE DB for SQL Server Provider Generation'] = _L('Generation %1', 1); |
||
327 | } else if (strtoupper($props['Provider Name']) == 'SQLNCLI11.DLL') { |
||
328 | $props['OLE DB for SQL Server Provider Generation'] = _L('Generation %1', 2); |
||
329 | } else if (strtoupper($props['Provider Name']) == 'MSOLEDBSQL.DLL') { |
||
330 | $props['OLE DB for SQL Server Provider Generation'] = _L('Generation %1', 3); |
||
331 | } |
||
332 | } |
||
1221 | daniel-mar | 333 | if (isset($props['Password'])) $props['Password'] = '['._L('redacted').']'; |
334 | return $props; |
||
1220 | daniel-mar | 335 | } |
336 | |||
1219 | daniel-mar | 337 | } |