1: <?php
2: /**
3: * This file contains the implementation of the abstract base class DBWorker
4: * for the MS SQL database using the extension mysqli.
5: *
6: * @package Database
7: *
8: * @author Oleg Schildt
9: */
10:
11: namespace SmartFactory\DatabaseWorkers;
12:
13: use function \SmartFactory\debugger;
14:
15: /**
16: * This is the class for the MS SQL database using the extension sqlsrv.
17: *
18: * This is a wrapper around the database connectivity. It offers a universal
19: * common way for working with databases of different types. Currently, MySQL, PostgreSQL and
20: * MS SQL are supported. If in the future, there will be a better solution, or
21: * the current solution turns out to be inefficient in a new version of PHP,
22: * we can easily reimplement the DB wrapper without touching the business logic code.
23: * Adding support for new database types is also much easier with this wrapping approach.
24: *
25: * @see PostgreSQL_DBWorker
26: * @see MySQL_DBWorker
27: *
28: * @author Oleg Schildt
29: */
30: class MSSQL_DBWorker extends DBWorker
31: {
32: /**
33: * Stores the resource handle of the opened connection.
34: *
35: * @var resource
36: *
37: * @author Oleg Schildt
38: */
39: protected $connection = null;
40:
41: /**
42: * Stores the resource handle of the statement
43: * of the last executed query.
44: *
45: * @var resource
46: *
47: * @author Oleg Schildt
48: */
49: protected $statement = null;
50:
51: /**
52: * Internal variable for storing of the last prepared query.
53: *
54: * @var ?string
55: *
56: * @author Oleg Schildt
57: */
58: protected ?string $prepared_query = null;
59:
60: /**
61: * Stores the state whether the last query was an insert or not.
62: *
63: * @var bool
64: *
65: * @author Oleg Schildt
66: */
67: protected bool $last_query_is_insert = false;
68:
69: /**
70: * Internal variable for storing of the current fetched row
71: * from the result of the last retrieving query.
72: *
73: * @var ?array
74: *
75: * @author Oleg Schildt
76: */
77: protected ?array $row = null;
78:
79: /**
80: * Internal variable for storing of the column names
81: * from the result of the last retrieving query.
82: *
83: * @var ?array
84: *
85: * @author Oleg Schildt
86: */
87: protected ?array $field_names = null;
88:
89: /**
90: * Internal variable for storing of the parameters
91: * of the last prepared query.
92: *
93: * @var ?array
94: *
95: * @author Oleg Schildt
96: */
97: protected ?array $query_parameters = null;
98:
99: /**
100: * Retrieves the last errors occurred while execution of the query.
101: *
102: * @return string
103: * Returns the string of errors separated by the new line symbol.
104: *
105: * @author Oleg Schildt
106: */
107: protected function sys_get_errors(): string
108: {
109: $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
110: if (empty($errors)) {
111: return "";
112: }
113:
114: $message_array = [];
115:
116: foreach ($errors as $error) {
117: $message_array[$error['message']] = $error['message'];
118: }
119:
120: return implode("\n", $message_array);
121: } // sys_get_errors
122:
123: /**
124: * Creates a clone of the dbworker that is using the same open
125: * connection.
126: *
127: * This might be useful if you want to execute some additional
128: * queries while iteration through the active results of a select query.
129: *
130: * @return MSSQL_DBWorker
131: * Returns the clone of this dbworker.
132: *
133: * @author Oleg Schildt
134: */
135: public function create_clone(): MSSQL_DBWorker
136: {
137: $cln = new MSSQL_DBWorker();
138:
139: $cln->is_clone = true;
140:
141: $cln->db_server = $this->db_server;
142: $cln->db_port = $this->db_port;
143: $cln->db_name = $this->db_name;
144: $cln->db_user = $this->db_user;
145: $cln->db_password = $this->db_password;
146:
147: return $cln;
148: } // create_clone
149:
150: /**
151: * Constructor.
152: *
153: * @author Oleg Schildt
154: */
155: public function __construct()
156: {
157: } // __construct
158:
159: /**
160: * Destructor.
161: *
162: * @return void
163: *
164: * @author Oleg Schildt
165: */
166: public function __destruct()
167: {
168: if (!$this->is_clone) {
169: $this->close_connection();
170: }
171: } // __destruct
172:
173: /**
174: * Initializes the dbworker with connection parameters.
175: *
176: * @param array $parameters
177: * Connection settings as an associative array in the form key => value:
178: *
179: * - $parameters["db_server"] - server address.
180: * - $parameters["db_port"] - server port.
181: * - $parameters["db_name"] - database name.
182: * - $parameters["db_user"] - user name.
183: * - $parameters["db_password"] - user password.
184: *
185: * @return void
186: *
187: * @author Oleg Schildt
188: */
189: public function init(array $parameters): void
190: {
191: if (!empty($parameters["db_server"])) {
192: $this->db_server = $parameters["db_server"];
193: }
194: if (!empty($parameters["db_port"])) {
195: $this->db_server = $parameters["db_port"];
196: }
197: if (!empty($parameters["db_name"])) {
198: $this->db_name = $parameters["db_name"];
199: }
200: if (!empty($parameters["db_user"])) {
201: $this->db_user = $parameters["db_user"];
202: }
203: if (!empty($parameters["db_password"])) {
204: $this->db_password = $parameters["db_password"];
205: }
206: } // init
207:
208: /**
209: * Checks whether the extension (sqlsrv) is installed which is required for work with
210: * the MySQL database.
211: *
212: * @return bool
213: * The method should return true if the extension is installed, otherwise false.
214: *
215: * @see MSSQL_DBWorker::get_extension_name()
216: *
217: * @author Oleg Schildt
218: */
219: public function is_extension_installed(): bool
220: {
221: return function_exists("sqlsrv_connect");
222: } // is_extension_installed
223:
224: /**
225: * Returns the name of the required PHP extension - "sqlsrv".
226: *
227: * @return string
228: * Returns the name of the required PHP extension - "sqlsrv".
229: *
230: * @see MSSQL_DBWorker::is_extension_installed()
231: *
232: * @author Oleg Schildt
233: */
234: public function get_extension_name(): string
235: {
236: return "sqlsrv";
237: } // get_extension_name
238:
239: /**
240: * Returns the name of the supported database - "Microsoft SQL Server".
241: *
242: * @return string
243: * Returns the name of the supported database - "Microsoft SQL Server".
244: *
245: * @author Oleg Schildt
246: */
247: public function get_rdbms_name(): string
248: {
249: return "Microsoft SQL Server";
250: } // get_rdbms_name
251:
252: /**
253: * Returns the connection state.
254: *
255: * @return bool
256: * Returns true if the connection is open, otherwise false.
257: *
258: * @see DBWorker::check_connection()
259: * @see MSSQL_DBWorker::connect()
260: * @see MSSQL_DBWorker::close_connection()
261: *
262: * @author Oleg Schildt
263: */
264: public function is_connected(): bool
265: {
266: return (!empty($this->connection) && is_resource($this->connection));
267: } // is_connected
268:
269: /**
270: * Establishes the connection to the database using the connection
271: * settings parameters specified by the initialization.
272: *
273: * @return void
274: *
275: * @throws DBWorkerException
276: * It throws an exception in the case of any errors.
277: *
278: * @see DBWorker::check_connection()
279: * @see MSSQL_DBWorker::is_connected()
280: * @see MSSQL_DBWorker::close_connection()
281: *
282: * @author Oleg Schildt
283: */
284: public function connect(): void
285: {
286: if ($this->is_connected()) {
287: return;
288: }
289:
290: sqlsrv_configure("WarningsReturnAsErrors", 0);
291:
292: $this->last_query = null;
293:
294: if (!isset($this->connection) || !$this->connection) {
295: if (empty($this->db_server) || empty($this->db_user) || empty($this->db_password)) {
296: throw new DBWorkerException("Connection data is incomplete", DBWorker::ERR_CONNECTION_DATA_INCOMPLETE);
297: }
298:
299: /*
300: This new MSSQL dirver has a BIG problem. It sticks to the encoding
301: of the Windows and ignores the database and server collation.
302:
303: If you use the UTF-8, you have two options:
304:
305: 1. Use NCHAR etc. types
306: 2. Store the UTF-8 strings as is into the 1-byte fields like varchar etc.
307:
308: The variant 1 might be undesirable because of performance.
309:
310: The variant 2 might cause problems in contrast to the old MSSQL drivers.
311: E.g. if you have the Russian locale on your Windows, but the Latin collation
312: in the database, you have no chance for the variant 2. The driver will convert
313: the data, although it should not do that but send this as is!
314:
315: There is the option SQLSRV_ENC_BINARY that prevents any conversion, but it cannot
316: be used globally and it cannot be used for simple queries. It can be used only for
317: prepared queries. It would be so easy just to allow setting SQLSRV_ENC_BINARY
318: on connection level, but they did not do this!
319:
320: Thus, you have only the following choices:
321:
322: 1. Store the unicode texts in NCHAR fields and use CharacterSet = UTF-8.
323: 2. Store the unicode texts in normal fields and use only parametrized queries.
324: You have always to specify the type SQLSRV_ENC_BINARY for each paramter
325: explicitly.
326: 3. Store the unicode texts in normal fields and ensure that the Windows locale
327: and the SQL Server are identical.
328:
329: Beacause of universality of the DBWorker independent of the database type,
330: I use the choise 3.
331: */
332:
333: $config = [
334: "UID" => $this->db_user,
335: "PWD" => $this->db_password,
336: //"CharacterSet" => "UTF-8",
337: "ReturnDatesAsStrings" => true,
338: "MultipleActiveResultSets" => false
339: ];
340:
341: $this->connection = @sqlsrv_connect($this->db_server, $config);
342: }
343:
344: if (!$this->connection) {
345: $err = $this->sys_get_errors();
346: $this->connection = null;
347:
348: throw new DBWorkerException($err, DBWorker::ERR_CONNECTION_FAILED);
349: }
350:
351: if (!empty($this->db_name)) {
352: try {
353: $this->use_database($this->db_name);
354: } catch (\Throwable $ex) {
355: $this->connection = null;
356: throw $ex;
357: }
358: }
359: } // connect
360:
361: /**
362: * Sets the database as working database.
363: *
364: * @param string $db_name
365: * The name of the database to be set as working database.
366: *
367: * @return void
368: *
369: * @throws DBWorkerException
370: * It throws an exception in the case of any errors.
371: *
372: * @author Oleg Schildt
373: */
374: public function use_database(string $db_name): void
375: {
376: $this->check_connection();
377:
378: $this->db_name = $db_name;
379:
380: $db_name = $this->escape($db_name);
381:
382: $this->execute_query("USE " . $db_name);
383: } // use_database
384:
385: /**
386: * Returns the name of the database schema if applicable.
387: *
388: * @return string
389: * Returns the name of the database schema if applicable, or an empty string.
390: *
391: * @see MSSQL_DBWorker::qualify_name_with_schema()
392: *
393: * @author Oleg Schildt
394: */
395: public function get_schema(): string
396: {
397: return "dbo";
398: } // get_schema
399:
400: /**
401: * Completes the name of a database object with the schema name if applicable.
402: *
403: * @param string $name
404: * The name of the database object to be completed with the schema name.
405: *
406: * @return string
407: * Returns the name of the database object with the schema name if applicable,
408: * otherwise the name of the database object remains unchanged.
409: *
410: * @see MSSQL_DBWorker::get_schema()
411: *
412: * @author Oleg Schildt
413: */
414: public function qualify_name_with_schema(string $name): string
415: {
416: $schema = $this->get_schema();
417:
418: if (!empty($schema)) {
419: $schema .= ".";
420: }
421:
422: return $schema . $name;
423: } // qualify_name_with_schema
424:
425: /**
426: * Executes the SQL query.
427: *
428: * @param string $query_string
429: * The SQL query to be executed.
430: *
431: * @return void
432: *
433: * @throws \Exception|DBWorkerException
434: * It throws an exception in the case of any errors.
435: *
436: * @author Oleg Schildt
437: */
438: public function execute_query(string $query_string): void
439: {
440: $this->check_connection();
441:
442: $this->last_query = $query_string;
443:
444: if ($this->logging) {
445: debugger()->debugMessage($this->last_query, "sql.log");
446: }
447:
448: $options = [];
449:
450: /*
451: If the cursor is SQLSRV_CURSOR_STATIC or other than
452: SQLSRV_CURSOR_FORWARD, retrieving of the data
453: has sometimes very poor performance. Not the query execution,
454: but the data retrieving!
455:
456: The default is SQLSRV_CURSOR_FORWARD, but it is not possible
457: to get fetched_count by this type of cursor. So we sacrifice
458: the possibility to get fetched_count for the performance.
459: The performance is more important.
460:
461: no more relevant in new version of driver
462: */
463:
464: if (preg_match("/\s*SELECT/i", $query_string)) {
465: $options = ["Scrollable" => SQLSRV_CURSOR_STATIC];
466: }
467:
468: $this->statement = @sqlsrv_query($this->connection, $query_string, [], $options);
469: if (!$this->statement) {
470: $err = $this->sys_get_errors();
471:
472: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
473: }
474: } // execute_query
475:
476: /**
477: * Prepares the SQL query with bindable variables.
478: *
479: * @param string $query_string
480: * The SQL query to be prepared.
481: *
482: * @return void
483: *
484: * @throws DBWorkerException
485: * It throws an exception in the case of any errors.
486: *
487: * @see MSSQL_DBWorker::execute_prepared_query()
488: * @see MSSQL_DBWorker::free_prepared_query()
489: *
490: * @author Oleg Schildt
491: */
492: public function prepare_query(string $query_string): void
493: {
494: $this->check_connection();
495:
496: if ($this->statement) {
497: if (is_resource($this->statement)) {
498: @sqlsrv_free_stmt($this->statement);
499: }
500: }
501:
502: $this->last_query = $query_string;
503: $this->prepared_query = $query_string;
504:
505: $params = [];
506: $this->query_parameters = [];
507:
508: $cnt = preg_match_all("/\\?/", $query_string);
509:
510: for ($i = 0; $i < $cnt; $i++) {
511: $this->query_parameters[$i] = null;
512: $params[$i] = &$this->query_parameters[$i];
513: }
514:
515: $query_appendix = "";
516:
517: // to be able to get the insert id from prepared query
518: // we have to add this appendix
519:
520: $this->last_query_is_insert = false;
521: if (preg_match("/\s*INSERT/i", $query_string)) {
522: $this->last_query_is_insert = true;
523:
524: $query_appendix = "; SELECT SCOPE_IDENTITY() AS IID";
525: }
526:
527: $options = [];
528: /*
529: If the cursor is SQLSRV_CURSOR_STATIC or other than
530: SQLSRV_CURSOR_FORWARD, retrieving of the data
531: has sometimes very poor performance. Not the query execution,
532: but the data retrieving!
533:
534: The default is SQLSRV_CURSOR_FORWARD, but it is not possible
535: to get fetched_count by this type of cursor. So we sacrifice
536: the possibility to get fetched_count for the performance.
537: The performance is more important.
538:
539: if(preg_match("/\s*SELECT/i", $query_string))
540: {
541: $options = ["Scrollable" => SQLSRV_CURSOR_STATIC];
542: }
543: */
544:
545: $this->statement = @sqlsrv_prepare($this->connection, $query_string . $query_appendix, $params, $options);
546: if (!$this->statement) {
547: $err = $this->sys_get_errors();
548:
549: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
550: }
551: } // prepare_query
552:
553: /**
554: * Stores long data from a stream.
555: *
556: * @param string $query_string
557: * The SQL query to be used for storing the long data.
558: *
559: * @param resource $stream
560: * The opened valid stream for reading the long data.
561: *
562: * Example:
563: * ```php
564: * $stream = fopen(".../large_binary.jpg", "rb");
565: *
566: * if(!$dbw->stream_long_data("UPDATE LARGE_DATA SET BLOB_DATA = ? WHERE ID = 1", $stream))
567: * {
568: * error reporting ...;
569: * }
570: * ```
571: *
572: * @return void
573: *
574: * @throws DBWorkerException
575: * It throws an exception in the case of any errors.
576: *
577: * @author Oleg Schildt
578: */
579: public function stream_long_data(string $query_string, $stream): void
580: {
581: $this->check_connection();
582:
583: if (!is_resource($stream)) {
584: throw new DBWorkerException("Stream is invalid!", DBWorker::ERR_STREAM_ERROR);
585: }
586:
587: if ($this->statement) {
588: if (is_resource($this->statement)) {
589: @sqlsrv_free_stmt($this->statement);
590: }
591: }
592:
593: $this->last_query = $query_string;
594: $this->prepared_query = $query_string;
595:
596: $params = array(&$stream);
597:
598: $options = array("SendStreamParamsAtExec" => 0);
599:
600: $this->statement = @sqlsrv_prepare($this->connection, $query_string, $params, $options);
601: if (!$this->statement) {
602: fclose($stream);
603:
604: $err = $this->sys_get_errors();
605:
606: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
607: }
608:
609: if (!@sqlsrv_execute($this->statement)) {
610: $err = $this->sys_get_errors();
611:
612: if (is_resource($this->statement)) {
613: @sqlsrv_free_stmt($this->statement);
614: }
615: $this->statement = null;
616: fclose($stream);
617:
618: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
619: }
620:
621: // Send up to 8K of parameter data to the server
622: // with each call to sqlsrv_send_stream_data.
623: while (sqlsrv_send_stream_data($this->statement)) {
624: }
625:
626: if (is_resource($this->statement)) {
627: @sqlsrv_free_stmt($this->statement);
628: }
629: $this->statement = null;
630: fclose($stream);
631: } // stream_long_data
632:
633: /**
634: * Executes the prepared SQL query.
635: *
636: * @param mixed ...$args
637: * The number of parameters may vary and be zero. An array can also be passed.
638: * These are parameters of the prepared query.
639: *
640: * @return void
641: *
642: * @throws \Exception|DBWorkerException
643: * It throws an exception in the case of any errors.
644: *
645: * @see MSSQL_DBWorker::prepare_query()
646: * @see MSSQL_DBWorker::free_prepared_query()
647: *
648: * @author Oleg Schildt
649: */
650: public function execute_prepared_query(...$args): void
651: {
652: $this->check_connection();
653:
654: if (empty($this->prepared_query) || empty($this->statement)) {
655: throw new DBWorkerException("No prepared query defined", DBWorker::ERR_QUERY_FAILED);
656: }
657:
658: if (count($args) == 1 && is_array($args[0])) {
659: $args = $args[0];
660: }
661:
662: $this->last_query = $this->prepared_query;
663:
664: $counter = 0;
665: foreach ($args as $argval) {
666: $this->query_parameters[$counter] = $argval;
667: if ($argval === null) {
668: $this->last_query = preg_replace("/\\?/", "null", $this->last_query, 1);
669: } elseif (is_int($argval)) {
670: $this->last_query = preg_replace("/\\?/", $argval, $this->last_query, 1);
671: } elseif (is_float($argval)) {
672: $this->last_query = preg_replace("/\\?/", $argval, $this->last_query, 1);
673: } else {
674: $this->last_query = preg_replace("/\\?/", \SmartFactory\preg_r_escape("'" . $this->escape($argval) . "'"), $this->last_query, 1);
675: }
676:
677: $counter++;
678: }
679:
680: if ($this->logging) {
681: debugger()->debugMessage($this->last_query, "sql.log");
682: }
683:
684: if (!@sqlsrv_execute($this->statement)) {
685: $err = $this->sys_get_errors();
686:
687: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
688: }
689: } // execute_prepared_query
690:
691: /**
692: * Executes the SQL stored procedure.
693: *
694: * @param string $procedure
695: * The name of the SQL stored procedure.
696: *
697: * All subsequent parameters are the parameters of the SQL stored procedure.
698: *
699: * @return void
700: *
701: * @throws DBWorkerException
702: * It throws an exception in the case of any errors.
703: *
704: * @author Oleg Schildt
705: */
706: public function execute_procedure(string $procedure /* arg list */): void
707: {
708: $this->check_connection();
709:
710: $args = func_get_args();
711: // prepare the arguments for placing in eval()
712: // escape single quotes
713:
714: $this->last_query = "";
715:
716: if (count($args) > 0) {
717: $proc_name = "";
718: $arg_list = "";
719:
720: $first = true;
721: foreach ($args as $argval) {
722: if ($first) {
723: $proc_name = $argval;
724: $first = false;
725: continue;
726: }
727:
728: if ($argval === null) {
729: $arg_list .= "null, ";
730: } elseif (is_int($argval)) {
731: $arg_list .= "$argval, ";
732: } elseif (is_float($argval)) {
733: $arg_list .= "$argval, ";
734: } else {
735: $arg_list .= "'" . $this->escape($argval) . "', ";
736: }
737: }
738:
739: $arg_list = trim($arg_list, ", ");
740:
741: $this->last_query = trim("EXEC $proc_name $arg_list");
742: }
743:
744: $this->execute_query($this->last_query);
745:
746: // A stored procedure may generate many result objects.
747: // We clear them, to prevent errors by subsequent select requests.
748:
749: if ($this->statement && is_resource($this->statement)) {
750: @sqlsrv_cancel($this->statement);
751: }
752: } // execute_procedure
753:
754: /**
755: * Frees the prepared query.
756: *
757: * It should be called after all executions of the prepared query.
758: *
759: * @return void
760: *
761: * @throws DBWorkerException
762: * It throws an exception in the case of any errors.
763: *
764: * @see MSSQL_DBWorker::prepare_query()
765: * @see MSSQL_DBWorker::execute_prepared_query()
766: *
767: * @author Oleg Schildt
768: */
769: public function free_prepared_query(): void
770: {
771: $this->check_connection();
772:
773: if ($this->statement && is_resource($this->statement)) {
774: @sqlsrv_free_stmt($this->statement);
775: }
776:
777: $this->statement = null;
778: $this->last_query = null;
779: $this->prepared_query = null;
780: $this->query_parameters = null;
781:
782: $this->last_query_is_insert = false;
783: } // free_prepared_query
784:
785: /**
786: * Closes the currently opened connection.
787: *
788: * @return void
789: *
790: * @see MSSQL_DBWorker::is_connected()
791: * @see MSSQL_DBWorker::connect()
792: *
793: * @author Oleg Schildt
794: */
795: public function close_connection(): void
796: {
797: $this->last_query = null;
798: $this->row = null;
799: $this->field_names = null;
800:
801: if (is_resource($this->connection)) {
802: @sqlsrv_close($this->connection);
803: }
804:
805: $this->statement = null;
806: $this->connection = null;
807: $this->query_parameters = null;
808: } // close_connection
809:
810: /**
811: * Starts the translation.
812: *
813: * @return void
814: *
815: * @throws DBWorkerException
816: * It throws an exception in the case of any errors.
817: *
818: * @see MSSQL_DBWorker::commit_transaction()
819: * @see MSSQL_DBWorker::rollback_transaction()
820: *
821: * @author Oleg Schildt
822: */
823: public function start_transaction(): void
824: {
825: $this->check_connection();
826:
827: if (!@sqlsrv_begin_transaction($this->connection)) {
828: $err = $this->sys_get_errors();
829:
830: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED);
831: }
832: } // start_transaction
833:
834: /**
835: * Commits the translation.
836: *
837: * @return void
838: *
839: * @throws DBWorkerException
840: * It throws an exception in the case of any errors.
841: *
842: * @see MSSQL_DBWorker::start_transaction()
843: * @see MSSQL_DBWorker::rollback_transaction()
844: *
845: * @author Oleg Schildt
846: */
847: public function commit_transaction(): void
848: {
849: $this->check_connection();
850:
851: if (!@sqlsrv_commit($this->connection)) {
852: $err = $this->sys_get_errors();
853:
854: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED);
855: }
856: } // commit_transaction
857:
858: /**
859: * Rolls back the translation.
860: *
861: * @return void
862: *
863: * @throws DBWorkerException
864: * It throws an exception in the case of any errors.
865: *
866: * @see MSSQL_DBWorker::start_transaction()
867: * @see MSSQL_DBWorker::commit_transaction()
868: *
869: * @author Oleg Schildt
870: */
871: public function rollback_transaction(): void
872: {
873: $this->check_connection();
874:
875: if (!@sqlsrv_rollback($this->connection)) {
876: $err = $this->sys_get_errors();
877:
878: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED);
879: }
880: } // rollback_transaction
881:
882: /**
883: * Frees the result of the previously executed retrieving query.
884: *
885: * It should be called only for the retrieving queries.
886: *
887: * @return void
888: *
889: * @throws DBWorkerException
890: * It might throw an exception in the case of any errors.
891: *
892: * @author Oleg Schildt
893: */
894: public function free_result(): void
895: {
896: $this->check_connection();
897:
898: if ($this->statement && is_resource($this->statement)) {
899: @sqlsrv_cancel($this->statement);
900: }
901:
902: $this->row = null;
903: $this->field_names = null;
904: } // free_result
905:
906: /**
907: * Returns the value of the auto increment field by the last insertion.
908: *
909: * @return ?int
910: * Returns the value of the auto increment field by the last insertion.
911: *
912: * @throws DBWorkerException
913: * It throws an exception in the case of any errors.
914: *
915: * @author Oleg Schildt
916: */
917: public function insert_id(): ?int
918: {
919: $this->check_connection();
920:
921: if ($this->last_query_is_insert) {
922: if (!$this->statement) {
923: return null;
924: }
925:
926: if (!sqlsrv_next_result($this->statement)) {
927: $err = $this->sys_get_errors();
928:
929: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED);
930: }
931:
932: $id = null;
933:
934: if (@sqlsrv_fetch($this->statement)) {
935: $id = sqlsrv_get_field($this->statement, 0);
936: }
937:
938: return $id;
939: }
940:
941: $this->statement = @sqlsrv_query($this->connection, "SELECT SCOPE_IDENTITY() AS IID");
942: if (!$this->statement) {
943: $err = $this->sys_get_errors();
944:
945: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED);
946: }
947:
948: if (!$this->fetch_row()) {
949: $err = "Identity field cannot be retrieved";
950:
951: $this->free_result();
952:
953: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED);
954: }
955:
956: $id = $this->field_by_name("IID");
957:
958: $this->free_result();
959:
960: return $id;
961: } // insert_id
962:
963: /**
964: * Fetches the next row of data from the result of the execution of the retrieving query.
965: *
966: * @return bool
967: * Returns true if the next row exists and has been successfully fetched, otherwise false.
968: *
969: * Example:
970: * ```php
971: * if(!$dbw->execute_query("SELECT FIRST_NAME, LAST_NAME FROM USERS"))
972: * {
973: * error reporting ...;
974: * }
975: *
976: * while($dbw->fetch_row())
977: * {
978: * echo $dbw->field_by_name("FIRST_NAME") . " " . $dbw->field_by_name("LAST_NAME") . "<br>";
979: * }
980: *
981: * $dbw->free_result();
982: * ```
983: *
984: * @throws DBWorkerException
985: * It throws an exception in the case of any errors.
986: *
987: * @author Oleg Schildt
988: */
989: public function fetch_row(): bool
990: {
991: $this->check_connection();
992:
993: if (!$this->statement || !is_resource($this->statement)) {
994: $err = "Result fetch error";
995: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
996: }
997:
998: $this->row = @sqlsrv_fetch_array($this->statement, SQLSRV_FETCH_ASSOC);
999: if (!$this->row) {
1000: return false;
1001: }
1002:
1003: if (!$this->field_names) {
1004: $this->field_names = array_keys($this->row);
1005: }
1006:
1007: return true;
1008: } // fetch_row
1009:
1010: /**
1011: * Fetches all rows from the result into an array.
1012: *
1013: * @param array &$rows
1014: * Target array for loading the results.
1015: *
1016: * @param ?array $dimension_keys
1017: * Array of the column names that should be used as dimensions.
1018: *
1019: * Per default, the rows are fetched as two-dimensional array.
1020: *
1021: * Example:
1022: * ```php
1023: * $rows = [];
1024: * $dbw->fetch_array($rows);
1025: *
1026: * rows[n] = ["col1" => "val1", "col2" => "val2", , "col3" => "val3", ...]
1027: * ```
1028: * If the dimension columns are specified, their values are used for the dimensions.
1029: *
1030: * Example:
1031: * ```php
1032: * $rows = [];
1033: * $dbw->fetch_array($rows, ["col1", "col2"]);
1034: *
1035: * rows["val1"]["val2"] = ["col3" => "val3", ...]
1036: * ```
1037: *
1038: * @return int
1039: * Returns the number of the fetched rows. It might be also 0.
1040: *
1041: * @throws DBWorkerException
1042: * It throws an exception in the case of any errors.
1043: *
1044: * @author Oleg Schildt
1045: */
1046: public function fetch_array(array &$rows, ?array $dimension_keys = null): int
1047: {
1048: $this->check_connection();
1049:
1050: if (!$this->statement || !is_resource($this->statement)) {
1051: $err = "Result fetch error";
1052: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1053: }
1054:
1055: $rows = [];
1056:
1057: if (!empty($dimension_keys)) {
1058: $dimension_keys = array_flip($dimension_keys);
1059: }
1060:
1061: $counter = 0;
1062: while ($row = @sqlsrv_fetch_array($this->statement, SQLSRV_FETCH_ASSOC)) {
1063: $counter++;
1064:
1065: if (!$this->field_names) {
1066: $this->field_names = array_keys($row);
1067: }
1068:
1069: if (empty($dimension_keys)) {
1070: $rows[] = $row;
1071: } else {
1072: $dimensions = array_intersect_key($row, $dimension_keys);
1073: $row = array_diff_key($row, $dimension_keys);
1074:
1075: $reference = &$rows;
1076:
1077: foreach ($dimensions as $dval) {
1078: if (empty($reference[$dval])) {
1079: $reference[$dval] = [];
1080: }
1081: $reference = &$reference[$dval];
1082: }
1083:
1084: $reference = $row;
1085:
1086: unset($reference);
1087: }
1088: }
1089:
1090: return $counter;
1091: } // fetch_array
1092:
1093: /**
1094: * Returns the number of the rows fetched by the last retrieving query.
1095: *
1096: * @return int
1097: * Returns the number of the rows fetched by the last retrieving query.
1098: *
1099: * @throws DBWorkerException
1100: * It throws an exception in the case of any errors.
1101: *
1102: * @author Oleg Schildt
1103: */
1104: public function fetched_count(): int
1105: {
1106: $this->check_connection();
1107:
1108: if (!$this->statement || !is_resource($this->statement)) {
1109: $err = "Result fetch error";
1110: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1111: }
1112:
1113: /*
1114: If the cursor is SQLSRV_CURSOR_STATIC or other than
1115: SQLSRV_CURSOR_FORWARD, retrieving of the data
1116: has sometimes very poor performance. Not the query execution,
1117: but the data retrieving!
1118:
1119: The default is SQLSRV_CURSOR_FORWARD, but it is not possible
1120: to get fetched_count by this type of cursor. So we sacrifice
1121: the possibility to get fetched_count for the performance.
1122: The performance is more important.
1123:
1124: no more relevant in new version of driver
1125: */
1126:
1127: return @sqlsrv_num_rows($this->statement);
1128: } // fetched_count
1129:
1130: /**
1131: * Returns the number of the rows affected by the last modification query.
1132: *
1133: * @return int
1134: * Returns the number of the rows affected by the last modification query.
1135: *
1136: * @throws DBWorkerException
1137: * It throws an exception in the case of any errors.
1138: *
1139: * @author Oleg Schildt
1140: */
1141: public function affected_count(): int
1142: {
1143: $this->check_connection();
1144:
1145: if (!$this->statement) {
1146: $err = "Result fetch error";
1147: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1148: }
1149:
1150: return @sqlsrv_rows_affected($this->statement);
1151: } // affected_count
1152:
1153: /**
1154: * Returns the number of the fields in the result of the last retrieving query.
1155: *
1156: * @return int
1157: * Returns the number of the fields in the result of the last retrieving query.
1158: *
1159: * @throws DBWorkerException
1160: * It throws an exception in the case of any errors.
1161: *
1162: * @author Oleg Schildt
1163: */
1164: public function field_count(): int
1165: {
1166: $this->check_connection();
1167:
1168: if (!$this->statement) {
1169: $err = "Result fetch error";
1170: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1171: }
1172:
1173: return @sqlsrv_num_fields($this->statement);
1174: } // field_count
1175:
1176: /**
1177: * Returns the value of a field specified by name.
1178: *
1179: * @param string $name
1180: * The name of the field.
1181: *
1182: * @param int $type
1183: * The type of the field.
1184: *
1185: * @return mixed
1186: * Returns the value of a field specified by name. In the case
1187: * of any error returns null.
1188: *
1189: * @throws DBWorkerException
1190: * It throws an exception in the case of any errors.
1191: *
1192: * @see MSSQL_DBWorker::field_by_num()
1193: * @see MSSQL_DBWorker::field_name()
1194: *
1195: * @author Oleg Schildt
1196: */
1197: public function field_by_name(string $name, int $type = self::DB_AS_IS): mixed
1198: {
1199: $this->check_connection();
1200:
1201: if (!$this->row) {
1202: return null;
1203: }
1204:
1205: if (!array_key_exists($name, $this->row)) {
1206: trigger_error("Field with the name '$name' does not exist in the result set!", E_USER_WARNING);
1207: return null;
1208: }
1209:
1210: if (($type == DBWorker::DB_DATE || $type == DBWorker::DB_DATETIME) && !empty($this->row[$name])) {
1211: return strtotime($this->row[$name]);
1212: }
1213:
1214: return $this->row[$name];
1215: } // field_by_name
1216:
1217: /**
1218: * Returns the value of a field specified by number.
1219: *
1220: * @param int $num
1221: * The number of the field.
1222: *
1223: * @param int $type
1224: * The type of the field.
1225: *
1226: * @return mixed
1227: * Returns the value of a field specified by number. In the case
1228: * of any error returns null.
1229: *
1230: * @throws DBWorkerException
1231: * It throws an exception in the case of any errors.
1232: *
1233: * @see MSSQL_DBWorker::field_by_name()
1234: * @see MSSQL_DBWorker::field_info_by_num()
1235: * @see MSSQL_DBWorker::field_name()
1236: *
1237: * @author Oleg Schildt
1238: */
1239: public function field_by_num(int $num, int $type = self::DB_AS_IS): mixed
1240: {
1241: $this->check_connection();
1242:
1243: if (!$this->row) {
1244: return null;
1245: }
1246:
1247: if (!array_key_exists($num, $this->field_names)) {
1248: trigger_error("Field with the index $num does not exist in the result set!", E_USER_WARNING);
1249: return null;
1250: }
1251:
1252: if (($type == DBWorker::DB_DATE || $type == DBWorker::DB_DATETIME) && !empty($this->row[$this->field_names[$num]])) {
1253: return strtotime($this->row[$this->field_names[$num]]);
1254: }
1255:
1256: return $this->row[$this->field_names[$num]];
1257: } // field_by_num
1258:
1259: /**
1260: * Returns the meta information about the field as an object with properties.
1261: *
1262: * @param int $num
1263: * The number of the field.
1264: *
1265: * @return ?array
1266: * Returns the associative array with properties.
1267: *
1268: * - $info["name"] - name of the field.
1269: * - $info["type"] - type of the field.
1270: * - $info["size"] - size of the field.
1271: * - $info["binary"] - whether the filed is binary.
1272: * - $info["numeric"] - whether the filed is numeric.
1273: * - $info["datetime"] - whether the filed is datetime.
1274: *
1275: * @throws DBWorkerException
1276: * It throws an exception in the case of any errors.
1277: *
1278: * @see MSSQL_DBWorker::field_by_num()
1279: * @see MSSQL_DBWorker::field_name()
1280: *
1281: * @author Oleg Schildt
1282: */
1283: public function field_info_by_num(int $num): ?array
1284: {
1285: $this->check_connection();
1286:
1287: if (!$this->statement) {
1288: $err = "Result fetch error";
1289: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1290: }
1291:
1292: $info = @sqlsrv_field_metadata($this->statement);
1293: if (!$info) {
1294: $err = $this->sys_get_errors();
1295:
1296: throw new DBWorkerException($err, DBWorker::ERR_QUERY_FAILED, "", [], $this->get_last_query());
1297: }
1298:
1299: if (empty($info[$num])) {
1300: return null;
1301: }
1302:
1303: $sqlsrv_type = [];
1304:
1305: $sqlsrv_type[-5] = "bigint";
1306: $sqlsrv_type[-2] = "binary";
1307: $sqlsrv_type[-7] = "bit";
1308: $sqlsrv_type[1] = "char";
1309: $sqlsrv_type[91] = "date";
1310: $sqlsrv_type[93] = "datetime";
1311:
1312: $sqlsrv_type[-155] = "datetimeoffset";
1313: $sqlsrv_type[3] = "decimal";
1314: $sqlsrv_type[6] = "float";
1315: $sqlsrv_type[-4] = "image";
1316: $sqlsrv_type[4] = "int";
1317: $sqlsrv_type[-8] = "nchar";
1318:
1319: $sqlsrv_type[-10] = "ntext";
1320: $sqlsrv_type[2] = "numeric";
1321: $sqlsrv_type[-9] = "nvarchar";
1322: $sqlsrv_type[7] = "real";
1323: $sqlsrv_type[5] = "smallint";
1324: $sqlsrv_type[-1] = "text";
1325:
1326: $sqlsrv_type[-154] = "time";
1327: $sqlsrv_type[-6] = "tinyint";
1328: $sqlsrv_type[-151] = "udt";
1329: $sqlsrv_type[-11] = "uniqueidentifier";
1330: $sqlsrv_type[-3] = "varbinary";
1331: $sqlsrv_type[12] = "varchar";
1332: $sqlsrv_type[-152] = "xml";
1333:
1334: $field_info = [];
1335:
1336: $field_info["name"] = $info[$num]["Name"];
1337: $field_info["type"] = $info[$num]["Type"];
1338: $field_info["size"] = $info[$num]["Size"];
1339:
1340: $field_info["binary"] = 0;
1341: $field_info["numeric"] = 0;
1342:
1343: if (!empty($field_info["type"])) {
1344: if (in_array($field_info["type"], [-5, 3, 6, 4, 2, 7, 5, -6])) {
1345: $field_info["numeric"] = 1;
1346: }
1347: elseif (in_array($field_info["type"], [-2, -4, -3])) {
1348: $field_info["binary"] = 1;
1349: }
1350: elseif (in_array($field_info["type"], [91, 93])) {
1351: $field_info["datetime"] = 1;
1352: }
1353: elseif (in_array($field_info["type"], [1, -8, -10, -9, -1, 12])) {
1354: $field_info["string"] = 1;
1355: }
1356:
1357: if (isset($sqlsrv_type[$field_info["type"]])) {
1358: $field_info["type"] = $sqlsrv_type[$field_info["type"]];
1359: } else {
1360: $field_info["type"] = "undefined";
1361: }
1362: }
1363:
1364: return $field_info;
1365: } // field_info_by_num
1366:
1367: /**
1368: * Returns the name of the field by number.
1369: *
1370: * @param int $num
1371: * The number of the field.
1372: *
1373: * @return ?string
1374: * Returns the name of the field by number.
1375: *
1376: * @throws DBWorkerException
1377: * It throws an exception in the case of any errors.
1378: *
1379: * @see MSSQL_DBWorker::field_by_num()
1380: * @see MSSQL_DBWorker::field_info_by_num()
1381: *
1382: * @author Oleg Schildt
1383: */
1384: public function field_name(int $num): ?string
1385: {
1386: $this->check_connection();
1387:
1388: $info = $this->field_info_by_num($num);
1389: if (!$info) {
1390: return null;
1391: }
1392:
1393: return $info["name"] ?? "";
1394: } // field_name
1395:
1396: /**
1397: * Escapes the string so that it can be used in the query without causing an error.
1398: *
1399: * @param string $str
1400: * The string to be escaped.
1401: *
1402: * @return string
1403: * Returns the escaped string.
1404: *
1405: * @see MSSQL_DBWorker::format_date()
1406: * @see MSSQL_DBWorker::format_datetime()
1407: *
1408: * @author Oleg Schildt
1409: */
1410: public function escape(string $str): string
1411: {
1412: return str_replace("'", "''", "$str");
1413: } // escape
1414:
1415: /**
1416: * Formats the date to a string compatible for the corresponding database.
1417: *
1418: * @param int $date
1419: * The date value as timestamp.
1420: *
1421: * @return string
1422: * Returns the string representation of the date compatible for the corresponding database.
1423: *
1424: * @see MSSQL_DBWorker::escape()
1425: * @see MSSQL_DBWorker::format_datetime()
1426: *
1427: * @author Oleg Schildt
1428: */
1429: public function format_date(int $date): string
1430: {
1431: if (empty($date)) {
1432: return $this->quotes_or_null("");
1433: }
1434:
1435: return $this->quotes_or_null(date("Ymd", $date));
1436: } // format_date
1437:
1438: /**
1439: * Formats the date/time to a string compatible for the corresponding database.
1440: *
1441: * @param int $datetime
1442: * The date/time value as timestamp.
1443: *
1444: * @return string
1445: * Returns the string representation of the date/time compatible for the corresponding database.
1446: *
1447: * @see MSSQL_DBWorker::escape()
1448: * @see MSSQL_DBWorker::format_date()
1449: *
1450: * @author Oleg Schildt
1451: */
1452: public function format_datetime(int $datetime): string
1453: {
1454: if (empty($datetime)) {
1455: return $this->quotes_or_null("");
1456: }
1457:
1458: return $this->quotes_or_null(date("Ymd H:i:s", $datetime));
1459: } // format_datetime
1460:
1461: /**
1462: * Prepares the value for putting to a query depending on its type. It does escaping, formatting
1463: * and quotation if necessary.
1464: *
1465: * @param mixed $value
1466: * The value to be formatted.
1467: *
1468: * @param int $type
1469: * The type of the value.
1470: *
1471: * @return string
1472: * Returns the prepared value.
1473: *
1474: * @throws DBWorkerException
1475: * It might throw an exception in the case of any errors.
1476: *
1477: * @author Oleg Schildt
1478: */
1479: function prepare_for_query(mixed $value, int $type): string
1480: {
1481: if (empty($value) && (string)$value != "0") {
1482: return "null";
1483: } else {
1484: return match ($type) {
1485: DBWorker::DB_NUMBER => $this->number_or_null($value),
1486: DBWorker::DB_DATETIME => $this->format_datetime($value),
1487: DBWorker::DB_DATE => $this->format_date($value),
1488: default => $this->quotes_or_null($value)
1489: };
1490: }
1491: } // prepare_for_query
1492:
1493: /**
1494: * Builds simple select query based on parameters.
1495: *
1496: * It is used for building queries with limits.
1497: *
1498: * @param string $table
1499: * The name of the table.
1500: *
1501: * @param array $fields
1502: * The list of request fields.
1503: *
1504: * @param string $where_clause
1505: * The where clause that should restrict the result.
1506: *
1507: * @param string $order_clause
1508: * The order clause to sort the results.
1509: *
1510: * @param int $limit
1511: * The limit how many records should be loaded. 0 for unlimited.
1512: *
1513: * @return string
1514: * Returns the built query.
1515: *
1516: * @author Oleg Schildt
1517: */
1518: function build_select_query(string $table, array $fields, string $where_clause, string $order_clause, int $limit): string
1519: {
1520: $query = "select\n";
1521:
1522: if (!empty($limit)) {
1523: $query .= "top " . $limit . "\n";
1524: }
1525:
1526: $query .= implode(", ", $fields) . "\n";
1527:
1528: $query .= "from " . $table . "\n";
1529:
1530: if (!empty($where_clause)) {
1531: $query .= $where_clause . "\n";
1532: }
1533:
1534: if (!empty($where_clause)) {
1535: $query .= $order_clause . "\n";
1536: }
1537:
1538: return $query;
1539: } // build_select_query
1540: } // MSSQL_DBWorker