1: <?php
2:
3: /**
4: * SQL Server database access classes
5: *
6: * @package objSQL
7: * @version 3.3.0
8: * @author MT Jordan <mtjo62@gmail.com>
9: * @copyright 2013
10: * @license zlib/libpng
11: * @link http://objsql.sourceforge.net
12: */
13:
14:
15: /*************************************************************************************************************
16: * Begin database connection/utility class
17: ************************************************************************************************************/
18:
19:
20: class sqlsrv_connection extends obj_access
21: {
22: /**********************************************
23: * Internal variables
24: *********************************************/
25:
26: /**
27: * Database connection object
28: *
29: * @access private
30: * @var mixed
31: */
32: private $obj_connection;
33:
34: /**
35: * Database connection information
36: *
37: * @access private
38: * @var array
39: */
40: private $obj_datasource;
41:
42: /**********************************************
43: * Class methods
44: *********************************************/
45:
46: /**
47: * Constructor
48: *
49: * @param array $datasource
50: */
51: public function __construct( $datasource )
52: {
53: $this->obj_datasource = $datasource;
54: }
55:
56: /**
57: * Returns database connection instance
58: *
59: * @return mixed
60: */
61: protected function obj_db_connection()
62: {
63: $db_host = ( ( array_key_exists( 5, $this->obj_datasource ) ) && is_numeric( trim( $this->obj_datasource[5] ) ) ) ? "{$this->obj_datasource[1]},{$this->obj_datasource[5]}" : $this->obj_datasource[1];
64: $db_connection = sqlsrv_connect( $db_host, array( 'Database' => $this->obj_datasource[4],
65: 'UID' => $this->obj_datasource[2],
66: 'PWD' => $this->obj_datasource[3] ) );
67:
68: return is_resource( $db_connection ) ? $this->obj_connection = $db_connection : false;
69: }
70:
71: /**
72: * Closes connection to database server
73: *
74: * @access protected
75: * @return bool
76: */
77: protected function obj_db_close()
78: {
79: return sqlsrv_close( $this->obj_connection );
80: }
81:
82: /**
83: * Returns error flag for current connection instance
84: *
85: * @access protected
86: * @return bool
87: */
88: protected function obj_db_error()
89: {
90: return ( sqlsrv_errors() !== null ) ? true : false;
91: }
92:
93: /**
94: * Escapes string data for database insertion
95: *
96: * @access protected
97: * @param str $data
98: * @return str
99: */
100: protected function obj_db_escape_data( $data )
101: {
102: return str_replace( "'", "''", $data );
103: }
104:
105: /**
106: * Returns error message for current connection instance
107: *
108: * @access protected
109: * @return str
110: */
111: protected function obj_db_message()
112: {
113: $err_msg = sqlsrv_errors();
114:
115: return ( $this->obj_db_error() ) ? $err_msg[0]['message'] : null;
116: }
117:
118: /**
119: * Returns database server information
120: *
121: * @access protected
122: * @return array
123: */
124: protected function obj_db_info()
125: {
126: $query_stmt = sqlsrv_query( $this->obj_connection, "select collation_name from sys.databases" );
127: $charset = sqlsrv_fetch_object( $query_stmt );
128: $version = sqlsrv_server_info( $this->obj_connection );
129:
130: return array( $version['SQLServerVersion'], $charset->collation_name, $this->obj_datasource[4] );
131: }
132:
133: /**
134: * Returns row count for named table with arguments
135: * Returns -1 if undetermined or failure
136: *
137: * @access protected
138: * @param str $table
139: * @param str $cols
140: * @param str $where
141: * @return int
142: */
143: protected function obj_db_rowcount( $table, $cols=false, $where=false )
144: {
145: $query_cols = ( !trim( $cols ) ) ? '*' : $cols;
146: $query_where = ( !trim( $where ) ) ? '' : "WHERE $where";
147:
148: $query_stmt = sqlsrv_query( $this->obj_connection, "SELECT COUNT($query_cols) FROM $table $query_where", array(), array( 'Scrollable' => SQLSRV_CURSOR_STATIC ) );
149:
150: if ( !is_resource( $query_stmt ) )
151: return -1;
152:
153: while ( $num_rows = sqlsrv_fetch_array( $query_stmt, SQLSRV_FETCH_NUMERIC ) )
154: $rowcount = ( $num_rows[0] >= 0 ) ? $num_rows[0] : -1;
155:
156: sqlsrv_free_stmt( $query_stmt );
157:
158: return $rowcount;
159: }
160: }
161:
162:
163: /*************************************************************************************************************
164: * End database connection class
165: ************************************************************************************************************/
166:
167:
168: /*************************************************************************************************************
169: * Begin database statement class
170: ************************************************************************************************************/
171:
172:
173: class sqlsrv_statement
174: {
175: /**********************************************
176: * Internal variables
177: *********************************************/
178:
179: /**
180: * Database connection object
181: *
182: * @access private
183: * @var mixed
184: */
185: private $obj_connection;
186:
187: /**
188: * Query string
189: *
190: * @access private
191: * @var string
192: */
193: private $obj_query;
194:
195: /**********************************************
196: * Class methods
197: *********************************************/
198:
199: /**
200: * Constructor
201: *
202: * @access public
203: * @param str $query
204: * @param mixed $connection
205: * @param bool $query_type
206: */
207: function __construct( $query, $connection )
208: {
209: $this->obj_connection = $connection->obj_connection;
210: $this->obj_query = $query;
211: }
212:
213: /**
214: * Executes general query and returns resultset resource
215: *
216: * @access public
217: * @return mixed
218: */
219: public function obj_query_execute()
220: {
221: if ( ( stripos( $this->obj_query, 'select' ) === 0 ) || ( stripos( $this->obj_query, 'with' ) === 0 ) )
222: $query_stmt = sqlsrv_query( $this->obj_connection, $this->obj_query, array(), array( 'Scrollable' => SQLSRV_CURSOR_STATIC ) );
223: else
224: $query_stmt = sqlsrv_query( $this->obj_connection, $this->obj_query );
225:
226: return ( is_resource( $query_stmt ) ) ? new sqlsrv_resultset( $query_stmt, $this->obj_connection ) : false;
227: }
228: }
229:
230:
231: /*************************************************************************************************************
232: * End database statement class
233: ************************************************************************************************************/
234:
235:
236: /*************************************************************************************************************
237: * Begin database prepared statement class
238: ************************************************************************************************************/
239:
240:
241: class sqlsrv_prepare
242: {
243: /**********************************************
244: * Internal variables
245: *********************************************/
246:
247: /**
248: * Database connection object
249: *
250: * @access private
251: * @var mixed
252: */
253: private $obj_connection;
254:
255: /**
256: * Database type
257: *
258: * @access private
259: * @var str
260: */
261: private $obj_db_type;
262:
263: /**
264: * Prepared query instance
265: *
266: * @access private
267: * @var mixed
268: */
269: private $obj_prepare_instance;
270:
271: /**********************************************
272: * Class methods
273: *********************************************/
274:
275: /**
276: * Constructor
277: *
278: * @access public
279: * @param str $query
280: * @param mixed $connection
281: * @param str $dbtype
282: * @param array $param_vars
283: */
284: function __construct( $query, $connection, $dbtype, $param_vars )
285: {
286: $this->obj_connection = $connection->obj_connection;
287: $this->obj_db_type = $dbtype;
288: $this->obj_prepare_init( $query, $param_vars );
289: }
290:
291: /**
292: * SQL Server doesn't implement a binding method
293: *
294: * @access public
295: */
296: public function obj_bind() {}
297:
298: /**
299: * Destroys prepared statement resource
300: *
301: * @access public
302: * @return bool
303: */
304: public function obj_close_statement()
305: {
306: return sqlsrv_free_stmt( $this->obj_prepare_instance );
307: }
308:
309: /**
310: * Executes prepared statement and returns resultset
311: *
312: * @access public
313: * @return mixed
314: */
315: public function obj_execute()
316: {
317: $query_stmt = sqlsrv_execute( $this->obj_prepare_instance );
318:
319: return ( $query_stmt ) ? new sqlsrv_resultset( $this->obj_prepare_instance, $this->obj_connection ) : false;
320: }
321:
322: /**
323: * Frees resultset memory from prepared statement resource
324: *
325: * @access public
326: * @return bool
327: */
328: public function obj_free_statement()
329: {
330: return sqlsrv_cancel( $this->obj_prepare_instance );
331: }
332:
333: /**
334: * Returns prepared statement resource
335: *
336: * @access private
337: * @param str $query
338: * @param array $param_vars
339: * @return mixed
340: */
341: private function obj_prepare_init( $query, $param_vars )
342: {
343: if ( ( stripos( $query, 'select' ) === 0 ) || ( stripos( $query, 'with' ) === 0 ) )
344: $prepare_instance = sqlsrv_prepare( $this->obj_connection, $query, $param_vars, array( 'Scrollable' => SQLSRV_CURSOR_STATIC ) );
345: else
346: $prepare_instance = sqlsrv_prepare( $this->obj_connection, $query, $param_vars );
347:
348: return ( is_resource( $prepare_instance ) ) ? $this->obj_prepare_instance = $prepare_instance : false;
349: }
350: }
351:
352:
353: /*************************************************************************************************************
354: * End database prepared statement class
355: ************************************************************************************************************/
356:
357:
358: /*************************************************************************************************************
359: * Begin database resultset class
360: ************************************************************************************************************/
361:
362:
363: class sqlsrv_resultset
364: {
365: /**********************************************
366: * Internal variables
367: *********************************************/
368:
369: /**
370: * Query record
371: *
372: * @access private
373: * @var array
374: */
375: private $obj_record = array();
376:
377: /**
378: * Query resultset object
379: *
380: * @access private
381: * @var mixed
382: */
383: private $obj_result;
384:
385: /**********************************************
386: * Class methods
387: *********************************************/
388:
389: /**
390: * Constructor
391: *
392: * @access public
393: * @param mixed $result
394: */
395: public function __construct( $result )
396: {
397: $this->obj_result = $result;
398: }
399:
400: /**
401: * Return number of affected rows from insert/delete/update query
402: * Returns -1 if undetermined or failure
403: *
404: * @access public
405: * @return int
406: */
407: public function obj_affected_rows()
408: {
409: $affected_rows = sqlsrv_rows_affected( $this->obj_result );
410:
411: return ( $affected_rows !== false && $affected_rows >= 0 ) ? $affected_rows : -1;
412: }
413:
414: /**
415: * Returns resultset resource as associative array
416: *
417: * @access public
418: * @return mixed
419: */
420: public function obj_fetch_assoc()
421: {
422: $result = sqlsrv_fetch_array( $this->obj_result, SQLSRV_FETCH_ASSOC );
423:
424: return ( is_array( $result ) ) ? $this->obj_record = $result : null;
425: }
426:
427: /**
428: * Returns resultset resource as numeric array
429: *
430: * @access public
431: * @return mixed
432: */
433: public function obj_fetch_num()
434: {
435: $result = sqlsrv_fetch_array( $this->obj_result, SQLSRV_FETCH_NUMERIC );
436:
437: return ( is_array( $result ) ) ? $this->obj_record = $result : null;
438: }
439:
440: /**
441: * Returns resultset resource as object
442: *
443: * @access public
444: * @return mixed
445: */
446: public function obj_fetch_object()
447: {
448: $result = sqlsrv_fetch_object( $this->obj_result );
449:
450: return ( is_object( $result ) ) ? $this->obj_record = $result : null;
451: }
452:
453:
454: /**
455: * Returns resultset record
456: *
457: * @access public
458: * @param mixed $field
459: * @return mixed
460: */
461: public function obj_field( $field )
462: {
463: //get_magic_quotes deprecated in php 5.4 - added for backwards compatibility
464: return ( get_magic_quotes_runtime() ) ? stripslashes( $this->obj_record[$field] ) : $this->obj_record[$field];
465: }
466:
467: /**
468: * Frees resultset memory and destroy resultset resource
469: *
470: * @access public
471: * @return bool
472: */
473: public function obj_free_result()
474: {
475: $this->obj_record = array();
476:
477: return sqlsrv_free_stmt( $this->obj_result );
478: }
479:
480: /**
481: * Return number of fields from query
482: * Returns -1 if undetermined or failure
483: *
484: * @access public
485: * @return int
486: */
487: public function obj_num_fields()
488: {
489: $num_cols = sqlsrv_num_fields( $this->obj_result );
490:
491: return ( $num_cols !== false && $num_cols >= 0 ) ? $num_cols : -1;
492: }
493:
494: /**
495: * Returns number of rows from query
496: * Returns -1 if undetermined or failure
497: *
498: * @access public
499: * @return int
500: */
501: public function obj_num_rows()
502: {
503: $num_rows = sqlsrv_num_rows( $this->obj_result );
504:
505: return ( $num_rows !== false && $num_rows >= 0 ) ? $num_rows : -1;
506: }
507: }
508:
509:
510: /*************************************************************************************************************
511: * End database resultset class
512: ************************************************************************************************************/
513:
514:
515: /*************************************************************************************************************
516: * Begin database transaction class
517: ************************************************************************************************************/
518:
519:
520: class sqlsrv_transaction
521: {
522: /**********************************************
523: * Internal variables
524: *********************************************/
525:
526: /**
527: * Database connection instance
528: *
529: * @access private
530: * @var mixed
531: */
532: private $obj_connection;
533:
534: /**********************************************
535: * Class methods
536: *********************************************/
537:
538: /**
539: * Constructor
540: *
541: * @access public
542: * @param mixed $connection
543: */
544: public function __construct( $connection )
545: {
546: $this->obj_connection = $connection;
547:
548: //turn off autocommit
549: sqlsrv_begin_transaction( $this->obj_connection );
550: }
551:
552: /**
553: * Commits transaction for current transaction instance
554: *
555: * @access public
556: * @return bool
557: */
558: public function obj_commit()
559: {
560: return sqlsrv_commit( $this->obj_connection );
561: }
562:
563: /**
564: * Rollbacks transaction for current transaction instance
565: *
566: * @access public
567: * @param str $savepoint
568: * @return bool
569: */
570: public function obj_rollback( $savepoint=false )
571: {
572: if ( !$savepoint )
573: $rollback = sqlsrv_rollback( $this->obj_connection );
574: else
575: $rollback = sqlsrv_query( $this->obj_connection, "ROLLBACK TRANSACTION $savepoint" );
576:
577: return ( $rollback || is_resource( $rollback ) ) ? true : false;
578: }
579:
580: /**
581: * Creates transaction savepoint for current transaction instance
582: *
583: * @access public
584: * @param str $savepoint
585: * @return bool
586: */
587: public function obj_savepoint( $savepoint )
588: {
589: return ( is_resource( sqlsrv_query( $this->obj_connection, "SAVE TRANSACTION $savepoint" ) ) ) ? true : false;
590: }
591: }
592:
593:
594: /*************************************************************************************************************
595: * End database transaction class
596: ************************************************************************************************************/
597:
598:
599: ?>