1: <?php
2:
3: /**
4: * SQL Server database access classes
5: *
6: * @package objSQL
7: * @version 3.0.1
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:
135: /*************************************************************************************************************
136: * End database connection class
137: ************************************************************************************************************/
138:
139:
140: /*************************************************************************************************************
141: * Begin database statement class
142: ************************************************************************************************************/
143:
144:
145: class sqlsrv_statement
146: {
147: /**********************************************
148: * Internal variables
149: *********************************************/
150:
151: /**
152: * Database connection object
153: *
154: * @access private
155: * @var mixed
156: */
157: private $obj_connection;
158:
159: /**
160: * Query string
161: *
162: * @access private
163: * @var string
164: */
165: private $obj_query;
166:
167: /**********************************************
168: * Class methods
169: *********************************************/
170:
171: /**
172: * Constructor
173: *
174: * @access public
175: * @param str $query
176: * @param mixed $connection
177: * @param bool $query_type
178: */
179: function __construct( $query, $connection )
180: {
181: $this->obj_connection = $connection->obj_connection;
182: $this->obj_query = $query;
183: }
184:
185: /**
186: * Executes general query and returns resultset resource
187: *
188: * @access public
189: * @return mixed
190: */
191: public function obj_query_execute()
192: {
193: if ( ( stripos( $this->obj_query, 'select' ) === 0 ) || ( stripos( $this->obj_query, 'with' ) === 0 ) )
194: $query_stmt = sqlsrv_query( $this->obj_connection, $this->obj_query, array(), array( 'Scrollable' => SQLSRV_CURSOR_STATIC ) );
195: else
196: $query_stmt = sqlsrv_query( $this->obj_connection, $this->obj_query );
197:
198: return ( is_resource( $query_stmt ) ) ? new sqlsrv_resultset( $query_stmt, $this->obj_connection ) : false;
199: }
200: }
201:
202:
203: /*************************************************************************************************************
204: * End database statement class
205: ************************************************************************************************************/
206:
207:
208: /*************************************************************************************************************
209: * Begin database prepared statement class
210: ************************************************************************************************************/
211:
212:
213: class sqlsrv_prepare
214: {
215: /**********************************************
216: * Internal variables
217: *********************************************/
218:
219: /**
220: * Database connection object
221: *
222: * @access private
223: * @var mixed
224: */
225: private $obj_connection;
226:
227: /**
228: * Prepared query instance
229: *
230: * @access private
231: * @var mixed
232: */
233: private $obj_prepare_instance;
234:
235: /**********************************************
236: * Class methods
237: *********************************************/
238:
239: /**
240: * Constructor
241: *
242: * @access public
243: * @param mixed $connection
244: * @param str $query
245: * @param array $param_vars
246: */
247: function __construct( $connection, $query, $param_vars )
248: {
249: $this->obj_connection = $connection->obj_connection;
250: $this->obj_prepare_init( $query, $param_vars );
251: }
252:
253: /**
254: * SQL Server doesn't implement a binding method
255: *
256: * @access public
257: */
258: public function obj_bind() {}
259:
260: /**
261: * Destroys prepared statement resource
262: *
263: * @access public
264: * @return bool
265: */
266: public function obj_close_statement()
267: {
268: return sqlsrv_free_stmt( $this->obj_prepare_instance );
269: }
270:
271: /**
272: * Executes prepared statement and returns resultset
273: *
274: * @access public
275: * @return mixed
276: */
277: public function obj_execute()
278: {
279: $query_stmt = sqlsrv_execute( $this->obj_prepare_instance );
280:
281: return ( $query_stmt ) ? new sqlsrv_resultset( $this->obj_prepare_instance, $this->obj_connection ) : false;
282: }
283:
284: /**
285: * Frees resultset memory from prepared statement resource
286: *
287: * @access public
288: * @return bool
289: */
290: public function obj_free_statement()
291: {
292: return sqlsrv_cancel( $this->obj_prepare_instance );
293: }
294:
295: /**
296: * Returns prepared statement resource
297: *
298: * @access private
299: * @param str $query
300: * @param array $param_vars
301: * @return mixed
302: */
303: private function obj_prepare_init( $query, $param_vars )
304: {
305: if ( ( stripos( $query, 'select' ) === 0 ) || ( stripos( $query, 'with' ) === 0 ) )
306: $prepare_instance = sqlsrv_prepare( $this->obj_connection, $query, $param_vars, array( 'Scrollable' => SQLSRV_CURSOR_STATIC ) );
307: else
308: $prepare_instance = sqlsrv_prepare( $this->obj_connection, $query, $param_vars );
309:
310: return ( is_resource( $prepare_instance ) ) ? $this->obj_prepare_instance = $prepare_instance : false;
311: }
312: }
313:
314:
315: /*************************************************************************************************************
316: * End database prepared statement class
317: ************************************************************************************************************/
318:
319:
320: /*************************************************************************************************************
321: * Begin database resultset class
322: ************************************************************************************************************/
323:
324:
325: class sqlsrv_resultset
326: {
327: /**********************************************
328: * Internal variables
329: *********************************************/
330:
331: /**
332: * Query record
333: *
334: * @access private
335: * @var array
336: */
337: private $obj_record = array();
338:
339: /**
340: * Query resultset object
341: *
342: * @access private
343: * @var mixed
344: */
345: private $obj_result;
346:
347: /**********************************************
348: * Class methods
349: *********************************************/
350:
351: /**
352: * Constructor
353: *
354: * @access public
355: * @param mixed $result
356: */
357: public function __construct( $result )
358: {
359: $this->obj_result = $result;
360: }
361:
362: /**
363: * Return number of affected rows from insert/delete/update query
364: * Returns -1 if undetermined or failure
365: *
366: * @access public
367: * @return int
368: */
369: public function obj_affected_rows()
370: {
371: $affected_rows = sqlsrv_rows_affected( $this->obj_result );
372:
373: return ( $affected_rows !== false && $affected_rows >= 0 ) ? $affected_rows : -1;
374: }
375:
376: /**
377: * Returns resultset resource as associative array
378: *
379: * @access public
380: * @return mixed
381: */
382: public function obj_fetch_assoc()
383: {
384: $result = sqlsrv_fetch_array( $this->obj_result, SQLSRV_FETCH_ASSOC );
385:
386: return ( is_array( $result ) ) ? $this->obj_record = $result : null;
387: }
388:
389: /**
390: * Returns resultset resource as numeric array
391: *
392: * @access public
393: * @return mixed
394: */
395: public function obj_fetch_num()
396: {
397: $result = sqlsrv_fetch_array( $this->obj_result, SQLSRV_FETCH_NUMERIC );
398:
399: return ( is_array( $result ) ) ? $this->obj_record = $result : null;
400: }
401:
402: /**
403: * Returns resultset resource as object
404: *
405: * @access public
406: * @return mixed
407: */
408: public function obj_fetch_object()
409: {
410: $result = sqlsrv_fetch_object( $this->obj_result );
411:
412: return ( is_object( $result ) ) ? $this->obj_record = $result : null;
413: }
414:
415:
416: /**
417: * Returns resultset record
418: *
419: * @access public
420: * @param mixed $field
421: * @return mixed
422: */
423: public function obj_field( $field )
424: {
425: //get_magic_quotes deprecated in php 5.4 - added for backwards compatibility
426: return ( get_magic_quotes_runtime() ) ? stripslashes( $this->obj_record[$field] ) : $this->obj_record[$field];
427: }
428:
429: /**
430: * Frees resultset memory and destroy resultset resource
431: *
432: * @access public
433: * @return bool
434: */
435: public function obj_free_result()
436: {
437: $this->obj_record = array();
438:
439: return sqlsrv_free_stmt( $this->obj_result );
440: }
441:
442: /**
443: * Return number of fields from query
444: * Returns -1 if undetermined or failure
445: *
446: * @access public
447: * @return int
448: */
449: public function obj_num_fields()
450: {
451: $num_cols = sqlsrv_num_fields( $this->obj_result );
452:
453: return ( $num_cols !== false && $num_cols >= 0 ) ? $num_cols : -1;
454: }
455:
456: /**
457: * Returns number of rows from query
458: * Returns -1 if undetermined or failure
459: *
460: * @access public
461: * @return int
462: */
463: public function obj_num_rows()
464: {
465: $num_rows = sqlsrv_num_rows( $this->obj_result );
466:
467: return ( $num_rows !== false && $num_rows >= 0 ) ? $num_rows : -1;
468: }
469: }
470:
471:
472: /*************************************************************************************************************
473: * End database resultset class
474: ************************************************************************************************************/
475:
476:
477: /*************************************************************************************************************
478: * Begin database transaction class
479: ************************************************************************************************************/
480:
481:
482: class sqlsrv_transaction
483: {
484: /**********************************************
485: * Internal variables
486: *********************************************/
487:
488: /**
489: * Database connection instance
490: *
491: * @access private
492: * @var mixed
493: */
494: private $obj_connection;
495:
496: /**********************************************
497: * Class methods
498: *********************************************/
499:
500: /**
501: * Constructor
502: *
503: * @access public
504: * @param mixed $connection
505: */
506: public function __construct( $connection )
507: {
508: $this->obj_connection = $connection;
509:
510: //turn off autocommit
511: $this->obj_autocommit_mode( sqlsrv_begin_transaction( $this->obj_connection ) );
512: }
513:
514: /**
515: * Returns autocommit mode
516: *
517: * @access private
518: * @param bool $trans
519: * @return bool
520: */
521: private function obj_autocommit_mode( $trans )
522: {
523: return ( $trans ) ? true : false;
524: }
525:
526: /**
527: * Commits transaction for current transaction instance
528: *
529: * @access public
530: * @return bool
531: */
532: public function obj_commit()
533: {
534: return sqlsrv_commit( $this->obj_connection );
535: }
536:
537: /**
538: * Rollbacks transaction for current transaction instance
539: *
540: * @access public
541: * @param str $savepoint
542: * @return bool
543: */
544: public function obj_rollback( $savepoint=false )
545: {
546: if ( !$savepoint )
547: $rollback = sqlsrv_rollback( $this->obj_connection );
548: else
549: $rollback = sqlsrv_query( $this->obj_connection, "ROLLBACK TRANSACTION $savepoint" );
550:
551: return ( $rollback || is_resource( $rollback ) ) ? true : false;
552: }
553:
554: /**
555: * Creates transaction savepoint for current transaction instance
556: *
557: * @access public
558: * @param str $savepoint
559: * @return bool
560: */
561: public function obj_savepoint( $savepoint )
562: {
563: return ( is_resource( sqlsrv_query( $this->obj_connection, "SAVE TRANSACTION $savepoint" ) ) ) ? true : false;
564: }
565: }
566:
567:
568: /*************************************************************************************************************
569: * End database transaction class
570: ************************************************************************************************************/
571:
572:
573: ?>