Context Navigation

sqlsrv.php @ 3635

Last change on this file since 3635 was 3635, checked in by alec, 3 years ago
Fix infinite loop when connection to DB fails using sqlsrv driver (#1486744)
File size: 39.2 KB

Line
1	<?php
2	// vim: set et ts=4 sw=4 fdm=marker:
3	// +----------------------------------------------------------------------+
4	// \| PHP versions 4 and 5 \|
5	// +----------------------------------------------------------------------+
6	// \| Copyright (c) 1998-2008 Manuel Lemos, Tomas V.V.Cox, \|
7	// \| Stig. S. Bakken, Lukas Smith, Frank M. Kromann \|
8	// \| All rights reserved. \|
9	// +----------------------------------------------------------------------+
10	// \| MDB2 is a merge of PEAR DB and Metabases that provides a unified DB \|
11	// \| API as well as database abstraction for PHP applications. \|
12	// \| This LICENSE is in the BSD license style. \|
13	// \| \|
14	// \| Redistribution and use in source and binary forms, with or without \|
15	// \| modification, are permitted provided that the following conditions \|
16	// \| are met: \|
17	// \| \|
18	// \| Redistributions of source code must retain the above copyright \|
19	// \| notice, this list of conditions and the following disclaimer. \|
20	// \| \|
21	// \| Redistributions in binary form must reproduce the above copyright \|
22	// \| notice, this list of conditions and the following disclaimer in the \|
23	// \| documentation and/or other materials provided with the distribution. \|
24	// \| \|
25	// \| Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, \|
26	// \| Lukas Smith nor the names of his contributors may be used to endorse \|
27	// \| or promote products derived from this software without specific prior\|
28	// \| written permission. \|
29	// \| \|
30	// \| THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS \|
31	// \| "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT \|
32	// \| LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS \|
33	// \| FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE \|
34	// \| REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, \|
35	// \| INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, \|
36	// \| BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS\|
37	// \| OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED \|
38	// \| AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT \|
39	// \| LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY\|
40	// \| WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE \|
41	// \| POSSIBILITY OF SUCH DAMAGE. \|
42	// +----------------------------------------------------------------------+
43	// \| Author: Frank M. Kromann <frank@kromann.info> \|
44	// +----------------------------------------------------------------------+
45	// {{{ Class MDB2_Driver_sqlsrv
46	/**
47	* MDB2 MSSQL Server (native) driver
48	*
49	* @package MDB2
50	* @category Database
51	*/
52	class MDB2_Driver_sqlsrv extends MDB2_Driver_Common
53	{
54	// {{{ properties
55
56	var $string_quoting = array('start' => "'", 'end' => "'", 'escape' => "'", 'escape_pattern' => false);
57
58	var $identifier_quoting = array('start' => '[', 'end' => ']', 'escape' => ']');
59
60	var $connection = null;
61
62	// }}}
63	// {{{ constructor
64
65	/**
66	* Constructor
67	*/
68	function __construct()
69	{
70	parent::__construct();
71
72	$this->phptype = 'sqlsrv';
73	$this->dbsyntax = 'sqlsrv';
74
75	$this->supported['sequences'] = 'emulated';
76	$this->supported['indexes'] = true;
77	$this->supported['affected_rows'] = true;
78	$this->supported['summary_functions'] = true;
79	$this->supported['transactions'] = true;
80	$this->supported['order_by_text'] = true;
81	$this->supported['savepoints'] = false;
82	$this->supported['current_id'] = 'emulated';
83	$this->supported['limit_queries'] = 'emulated';
84	$this->supported['LOBs'] = true;
85	$this->supported['replace'] = 'emulated';
86	$this->supported['sub_selects'] = true;
87	$this->supported['triggers'] = true;
88	$this->supported['auto_increment'] = true;
89	$this->supported['primary_key'] = true;
90	$this->supported['result_introspection'] = true;
91	$this->supported['prepared_statements'] = 'emulated';
92	$this->supported['identifier_quoting'] = false;
93	$this->supported['pattern_escaping'] = true;
94	$this->supported['new_link'] = true;
95
96	$this->options['DBA_username'] = false;
97	$this->options['DBA_password'] = false;
98	$this->options['database_device'] = false;
99	$this->options['database_size'] = false;
100	$this->options['max_identifiers_length'] = 128; // MS Access: 64
101	}
102
103	// }}}
104	// {{{ errorInfo()
105
106	/**
107	* This method is used to collect information about an error
108	*
109	* @param integer $error
110	* @return array
111	* @access public
112	*/
113	function errorInfo($error = null, $connection = null)
114	{
115	if (null === $connection) {
116	$connection = $this->connection;
117	}
118
119	$native_code = null;
120	$native_msg = null;
121	if ($connection) {
122	$retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
123	if ($retErrors !== null) {
124	foreach ($retErrors as $arrError) {
125	$native_msg .= "SQLState: ".$arrError[ 'SQLSTATE']."\n";
126	$native_msg .= "Error Code: ".$arrError[ 'code']."\n";
127	$native_msg .= "Message: ".$arrError[ 'message']."\n";
128	$native_code = $arrError[ 'code'];
129	}
130	}
131	}
132	if (null === $error) {
133	static $ecode_map;
134	if (empty($ecode_map)) {
135	$ecode_map = array(
136	102 => MDB2_ERROR_SYNTAX,
137	110 => MDB2_ERROR_VALUE_COUNT_ON_ROW,
138	155 => MDB2_ERROR_NOSUCHFIELD,
139	156 => MDB2_ERROR_SYNTAX,
140	170 => MDB2_ERROR_SYNTAX,
141	207 => MDB2_ERROR_NOSUCHFIELD,
142	208 => MDB2_ERROR_NOSUCHTABLE,
143	245 => MDB2_ERROR_INVALID_NUMBER,
144	319 => MDB2_ERROR_SYNTAX,
145	321 => MDB2_ERROR_NOSUCHFIELD,
146	325 => MDB2_ERROR_SYNTAX,
147	336 => MDB2_ERROR_SYNTAX,
148	515 => MDB2_ERROR_CONSTRAINT_NOT_NULL,
149	547 => MDB2_ERROR_CONSTRAINT,
150	911 => MDB2_ERROR_NOT_FOUND,
151	1018 => MDB2_ERROR_SYNTAX,
152	1035 => MDB2_ERROR_SYNTAX,
153	1801 => MDB2_ERROR_ALREADY_EXISTS,
154	1913 => MDB2_ERROR_ALREADY_EXISTS,
155	2209 => MDB2_ERROR_SYNTAX,
156	2223 => MDB2_ERROR_SYNTAX,
157	2248 => MDB2_ERROR_SYNTAX,
158	2256 => MDB2_ERROR_SYNTAX,
159	2257 => MDB2_ERROR_SYNTAX,
160	2627 => MDB2_ERROR_CONSTRAINT,
161	2714 => MDB2_ERROR_ALREADY_EXISTS,
162	3607 => MDB2_ERROR_DIVZERO,
163	3701 => MDB2_ERROR_NOSUCHTABLE,
164	7630 => MDB2_ERROR_SYNTAX,
165	8134 => MDB2_ERROR_DIVZERO,
166	9303 => MDB2_ERROR_SYNTAX,
167	9317 => MDB2_ERROR_SYNTAX,
168	9318 => MDB2_ERROR_SYNTAX,
169	9331 => MDB2_ERROR_SYNTAX,
170	9332 => MDB2_ERROR_SYNTAX,
171	15253 => MDB2_ERROR_SYNTAX,
172	);
173	}
174	if (isset($ecode_map[$native_code])) {
175	if ($native_code == 3701
176	&& preg_match('/Cannot drop the index/i', $native_msg)
177	) {
178	$error = MDB2_ERROR_NOT_FOUND;
179	} else {
180	$error = $ecode_map[$native_code];
181	}
182	}
183	}
184	return array($error, $native_code, $native_msg);
185	}
186
187	// }}}
188	// {{{ function escapePattern($text)
189
190	/**
191	* Quotes pattern (% and _) characters in a string)
192	*
193	* @param string the input string to quote
194	*
195	* @return string quoted string
196	*
197	* @access public
198	*/
199	function escapePattern($text)
200	{
201	$text = str_replace("[", "[ [ ]", $text);
202	foreach ($this->wildcards as $wildcard) {
203	$text = str_replace($wildcard, '[' . $wildcard . ']', $text);
204	}
205	return $text;
206	}
207
208	// }}}
209	// {{{ beginTransaction()
210
211	/**
212	* Start a transaction or set a savepoint.
213	*
214	* @param string name of a savepoint to set
215	* @return mixed MDB2_OK on success, a MDB2 error on failure
216	*
217	* @access public
218	*/
219	function beginTransaction($savepoint = null)
220	{
221	$this->debug('Starting transaction/savepoint', __FUNCTION__, array('is_manip' => true, 'savepoint' => $savepoint));
222	if (null !== $savepoint) {
223	if (!$this->in_transaction) {
224	return $this->raiseError(MDB2_ERROR_INVALID, null, null,
225	'savepoint cannot be released when changes are auto committed', __FUNCTION__);
226	}
227	$query = 'SAVE TRANSACTION '.$savepoint;
228	return $this->_doQuery($query, true);
229	}
230	if ($this->in_transaction) {
231	return MDB2_OK; //nothing to do
232	}
233	if (!$this->destructor_registered && $this->opened_persistent) {
234	$this->destructor_registered = true;
235	register_shutdown_function('MDB2_closeOpenTransactions');
236	}
237	if (PEAR::isError(sqlsrv_begin_transaction($this->connection))) {
238	return MDB2_ERROR;
239	}
240	$this->in_transaction = true;
241	return MDB2_OK;
242	}
243
244	// }}}
245	// {{{ commit()
246
247	/**
248	* Commit the database changes done during a transaction that is in
249	* progress or release a savepoint. This function may only be called when
250	* auto-committing is disabled, otherwise it will fail. Therefore, a new
251	* transaction is implicitly started after committing the pending changes.
252	*
253	* @param string name of a savepoint to release
254	* @return mixed MDB2_OK on success, a MDB2 error on failure
255	*
256	* @access public
257	*/
258	function commit($savepoint = null)
259	{
260	$this->debug('Committing transaction/savepoint', __FUNCTION__, array('is_manip' => true, 'savepoint' => $savepoint));
261	if (!$this->in_transaction) {
262	return $this->raiseError(MDB2_ERROR_INVALID, null, null,
263	'commit/release savepoint cannot be done changes are auto committed', __FUNCTION__);
264	}
265	if (null !== $savepoint) {
266	return MDB2_OK;
267	}
268
269	if (PEAR::isError(sqlsrv_commit($this->connection))) {
270	return MDB2_ERROR;
271	}
272	$this->in_transaction = false;
273	return MDB2_OK;
274	}
275
276	// }}}
277	// {{{ rollback()
278
279	/**
280	* Cancel any database changes done during a transaction or since a specific
281	* savepoint that is in progress. This function may only be called when
282	* auto-committing is disabled, otherwise it will fail. Therefore, a new
283	* transaction is implicitly started after canceling the pending changes.
284	*
285	* @param string name of a savepoint to rollback to
286	* @return mixed MDB2_OK on success, a MDB2 error on failure
287	*
288	* @access public
289	*/
290	function rollback($savepoint = null)
291	{
292	$this->debug('Rolling back transaction/savepoint', __FUNCTION__, array('is_manip' => true, 'savepoint' => $savepoint));
293	if (!$this->in_transaction) {
294	return $this->raiseError(MDB2_ERROR_INVALID, null, null,
295	'rollback cannot be done changes are auto committed', __FUNCTION__);
296	}
297	if (null !== $savepoint) {
298	$query = 'ROLLBACK TRANSACTION '.$savepoint;
299	return $this->_doQuery($query, true);
300	}
301
302	if (PEAR::isError(sqlsrv_rollback($this->connection))) {
303	return MDB2_ERROR;
304	}
305	$this->in_transaction = false;
306	return MDB2_OK;
307	}
308
309	// }}}
310	// {{{ _doConnect()
311
312	/**
313	* do the grunt work of the connect
314	*
315	* @return connection on success or MDB2 Error Object on failure
316	* @access protected
317	*/
318	function _doConnect($username, $password, $database=null, $persistent = false)
319	{
320	if (!PEAR::loadExtension($this->phptype)) {
321	return $this->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
322	'extension '.$this->phptype.' is not installed PHP', __FUNCTION__);
323	}
324
325	$host = $this->dsn['hostspec'] ? $this->dsn['hostspec'] : '.\\SQLEXPRESS';
326	$params = array(
327	'UID' => $username ? $username : null,
328	'PWD' => $password ? $password : null,
329	);
330	if ($database) {
331	$params['Database'] = $database;
332	}
333
334	if ($this->dsn['port'] && $this->dsn['port'] != 1433) {
335	$host .= ','.$this->dsn['port'];
336	}
337
338	$connection = @sqlsrv_connect($host, $params);
339	if (!$connection) {
340	return $this->raiseError(MDB2_ERROR_CONNECT_FAILED, null, null,
341	'unable to establish a connection', __FUNCTION__, __FUNCTION__);
342	}
343	if (null !== $database) {
344	$this->connected_database_name = $database;
345	}
346
347	if (!empty($this->dsn['charset'])) {
348	$result = $this->setCharset($this->dsn['charset'], $connection);
349	if (PEAR::isError($result)) {
350	return $result;
351	}
352	}
353
354	if (empty($this->dsn['disable_iso_date'])) {
355	@sqlsrv_query($connection,'SET DATEFORMAT ymd');
356	}
357
358	return $connection;
359	}
360
361	// }}}
362	// {{{ connect()
363
364	/**
365	* Connect to the database
366	*
367	* @return true on success, MDB2 Error Object on failure
368	*/
369	function connect()
370	{
371	if (is_resource($this->connection)) {
372	if (MDB2::areEquals($this->connected_dsn, $this->dsn)) {
373	return MDB2_OK;
374	}
375	$this->disconnect(false);
376	}
377
378	$connection = $this->_doConnect(
379	$this->dsn['username'],
380	$this->dsn['password'],
381	$this->database_name,
382	$this->options['persistent']
383	);
384	if (PEAR::isError($connection)) {
385	return $connection;
386	}
387
388	$this->connection = $connection;
389	$this->connected_dsn = $this->dsn;
390	$this->connected_database_name = $this->database_name;
391	$this->opened_persistent = $this->options['persistent'];
392	$this->dbsyntax = $this->dsn['dbsyntax'] ? $this->dsn['dbsyntax'] : $this->phptype;
393
394	return MDB2_OK;
395	}
396
397	// }}}
398	// {{{ databaseExists()
399
400	/**
401	* check if given database name exists?
402	*
403	* @param string $name name of the database that should be checked
404	*
405	* @return mixed true/false on success, a MDB2 error on failure
406	* @access public
407	*/
408	function databaseExists($name)
409	{
410	$connection = $this->_doConnect($this->dsn['username'],$this->dsn['password']);
411	if (PEAR::isError($connection)) {
412	return MDB2_ERROR_CONNECT_FAILED;
413	}
414	$result = @sqlsrv_query($connection,'select name from master..sysdatabases where name = \''.strtolower($name).'\'');
415	if (@sqlsrv_fetch($result)) {
416	return true;
417	}
418	return MDB2_ERROR_NOT_FOUND;
419	}
420
421	// }}}
422	// {{{ disconnect()
423
424	/**
425	* Log out and disconnect from the database.
426	*
427	* @param boolean $force if the disconnect should be forced even if the
428	* connection is opened persistently
429	* @return mixed true on success, false if not connected and error
430	* object on error
431	* @access public
432	*/
433	function disconnect($force = true)
434	{
435	if (is_resource($this->connection)) {
436	if ($this->in_transaction) {
437	$dsn = $this->dsn;
438	$database_name = $this->database_name;
439	$persistent = $this->options['persistent'];
440	$this->dsn = $this->connected_dsn;
441	$this->database_name = $this->connected_database_name;
442	$this->options['persistent'] = $this->opened_persistent;
443	$this->rollback();
444	$this->dsn = $dsn;
445	$this->database_name = $database_name;
446	$this->options['persistent'] = $persistent;
447	}
448
449	@sqlsrv_close($this->connection);
450	}
451	return parent::disconnect($force);
452	}
453
454	// }}}
455	// {{{ standaloneQuery()
456
457	/**
458	* execute a query as DBA
459	*
460	* @param string $query the SQL query
461	* @param mixed $types array that contains the types of the columns in
462	* the result set
463	* @param boolean $is_manip if the query is a manipulation query
464	* @return mixed MDB2_OK on success, a MDB2 error on failure
465	* @access public
466	*/
467	function &standaloneQuery($query, $types = null, $is_manip = false)
468	{
469	$user = $this->options['DBA_username']? $this->options['DBA_username'] : $this->dsn['username'];
470	$pass = $this->options['DBA_password']? $this->options['DBA_password'] : $this->dsn['password'];
471	$connection = $this->_doConnect($user, $pass, $this->database_name, $this->options['persistent']);
472	if (PEAR::isError($connection)) {
473	return $connection;
474	}
475
476	$query = $this->_modifyQuery($query, $is_manip, $this->limit, $this->offset);
477	$this->offset = $this->limit = 0;
478
479	$result = $this->_doQuery($query, $is_manip, $connection);
480	if (!PEAR::isError($result)) {
481	$result = $this->_affectedRows($connection, $result);
482	}
483
484	@sqlsrv_close($connection);
485	return $result;
486	}
487
488	// }}}
489	// {{{ _doQuery()
490
491	/**
492	* Execute a query
493	* @param string $query query
494	* @param boolean $is_manip if the query is a manipulation query
495	* @param resource $connection
496	* @param string $database_name
497	* @return result or error object
498	* @access protected
499	*/
500	function _doQuery($query, $is_manip = false, $connection = null, $database_name = null)
501	{
502	$this->last_query = $query;
503	$result = $this->debug($query, 'query', array('is_manip' => $is_manip, 'when' => 'pre'));
504	if ($result) {
505	if (PEAR::isError($result)) {
506	return $result;
507	}
508	$query = $result;
509	}
510	if ($this->options['disable_query']) {
511	$result = $is_manip ? 0 : null;
512	return $result;
513	}
514
515	if (null === $connection) {
516	$connection = $this->getConnection();
517	if (PEAR::isError($connection)) {
518	return $connection;
519	}
520	}
521	if (null === $database_name) {
522	$database_name = $this->database_name;
523	}
524
525	if ($database_name && $database_name != $this->connected_database_name) {
526	$connection = $this->_doConnect($this->dsn['username'],$this->dsn['password'],$database_name);
527	if (PEAR::isError($connection)) {
528	$err = $this->raiseError(null, null, null,
529	'Could not select the database: '.$database_name, __FUNCTION__);
530	return $err;
531	}
532	$this->connected_database_name = $database_name;
533	}
534
535	$query = preg_replace('/DATE_FORMAT$(MIN\()?([\w\|.]*)($)?\\Q, \'%Y-%m-%d\')\E/i','CONVERT(varchar(10),$1$2$3,120)',$query);
536	$query = preg_replace('/DATE_FORMAT$([\w\|.]*)\, \'\%Y\-\%m\-\%d %H\:00\:00\'$/i','CONVERT(varchar(13),$1,120)+\':00:00\'',$query);
537	$result = @sqlsrv_query($connection,$query);
538	if (!$result) {
539	$err = $this->raiseError(null, null, null,
540	'Could not execute statement', __FUNCTION__);
541	return $err;
542	}
543	$this->result = $result;
544	$this->debug($query, 'query', array('is_manip' => $is_manip, 'when' => 'post', 'result' => $result));
545	return $result;
546	}
547
548	// }}}
549	// {{{ _affectedRows()
550
551	/**
552	* Returns the number of rows affected
553	*
554	* @param resource $result
555	* @param resource $connection
556	* @return mixed MDB2 Error Object or the number of rows affected
557	* @access private
558	*/
559	function _affectedRows($connection, $result = null)
560	{
561	if (null === $result) {
562	$result = $this->result;
563	}
564	return sqlsrv_rows_affected($this->result);
565	}
566
567	// }}}
568	// {{{ _modifyQuery()
569
570	/**
571	* Changes a query string for various DBMS specific reasons
572	*
573	* @param string $query query to modify
574	* @param boolean $is_manip if it is a DML query
575	* @param integer $limit limit the number of rows
576	* @param integer $offset start reading from given offset
577	* @return string modified query
578	* @access protected
579	*/
580	function _modifyQuery($query, $is_manip, $limit, $offset)
581	{
582	if ($limit > 0) {
583	$fetch = $offset + $limit;
584	if (!$is_manip) {
585	return preg_replace('/^([\s(])SELECT( DISTINCT)?(?!\sTOP\s*\()/i',
586	"\\1SELECT\\2 TOP $fetch", $query);
587	}
588	}
589	return $query;
590	}
591
592	// }}}
593	// {{{ getServerVersion()
594
595	/**
596	* return version information about the server
597	*
598	* @param bool $native determines if the raw version string should be returned
599	* @return mixed array/string with version information or MDB2 error object
600	* @access public
601	*/
602	function getServerVersion($native = false)
603	{
604	if ($this->connected_server_info) {
605	$server_info = $this->connected_server_info;
606	} else {
607	$server_info = sqlsrv_server_info($this->connection);
608	}
609	// cache server_info
610	$this->connected_server_info = $server_info;
611	$version = $server_info['SQLServerVersion'];
612	if (!$native) {
613	if (preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $tmp)) {
614	$version = array(
615	'major' => $tmp[1],
616	'minor' => $tmp[2],
617	'patch' => $tmp[3],
618	'extra' => null,
619	'native' => $version,
620	);
621	} else {
622	$version = array(
623	'major' => null,
624	'minor' => null,
625	'patch' => null,
626	'extra' => null,
627	'native' => $version,
628	);
629	}
630	}
631	return $version;
632	}
633
634	// }}}
635	// {{{ _checkSequence
636
637	/**
638	* Checks if there's a sequence that exists.
639	*
640	* @param string $seq_name The sequence name to verify.
641	* @return bool $tableExists The value if the table exists or not
642	* @access private
643	*/
644	function _checkSequence($seq_name)
645	{
646	$query = "SELECT * FROM $seq_name";
647	$tableExists =& $this->_doQuery($query, true);
648	if (PEAR::isError($tableExists)) {
649	if ($tableExists->getCode() == MDB2_ERROR_NOSUCHTABLE) {
650	return false;
651	}
652	return false;
653	}
654	if (@sqlsrv_fetch($tableExits)) {
655	return true;
656	}
657	return false;
658	}
659
660	// }}}
661	// {{{ nextID()
662
663	/**
664	* Returns the next free id of a sequence
665	*
666	* @param string $seq_name name of the sequence
667	* @param boolean $ondemand when true the sequence is
668	* automatic created, if it
669	* not exists
670	*
671	* @return mixed MDB2 Error Object or id
672	* @access public
673	*/
674	function nextID($seq_name, $ondemand = true)
675	{
676	$sequence_name = $this->quoteIdentifier($this->getSequenceName($seq_name), true);
677	$seqcol_name = $this->quoteIdentifier($this->options['seqcol_name'], true);
678	$this->pushErrorHandling(PEAR_ERROR_RETURN);
679	$this->expectError(MDB2_ERROR_NOSUCHTABLE);
680
681	$seq_val = $this->_checkSequence($sequence_name);
682
683	if ($seq_val) {
684	$query = "SET IDENTITY_INSERT $sequence_name OFF ".
685	"INSERT INTO $sequence_name DEFAULT VALUES";
686	} else {
687	$query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (0)";
688	}
689	$result = $this->_doQuery($query, true);
690	$this->popExpect();
691	$this->popErrorHandling();
692	if (PEAR::isError($result)) {
693	if ($ondemand && !$this->_checkSequence($sequence_name)) {
694	$this->loadModule('Manager', null, true);
695	$result = $this->manager->createSequence($seq_name);
696	if (PEAR::isError($result)) {
697	return $this->raiseError($result, null, null,
698	'on demand sequence '.$seq_name.' could not be created', __FUNCTION__);
699	} else {
700	/**
701	* Little off-by-one problem with the sequence emulation
702	* here being fixed, that instead of re-calling nextID
703	* and forcing an increment by one, we simply check if it
704	* exists, then we get the last inserted id if it does.
705	*
706	* In theory, $seq_name should be created otherwise there would
707	* have been an error thrown somewhere up there..
708	*
709	* @todo confirm
710	*/
711	if ($this->_checkSequence($seq_name)) {
712	return $this->lastInsertID($seq_name);
713	}
714
715	return $this->nextID($seq_name, false);
716	}
717	}
718	return $result;
719	}
720	$value = $this->lastInsertID($sequence_name);
721	if (is_numeric($value)) {
722	$query = "DELETE FROM $sequence_name WHERE $seqcol_name < $value";
723	$result = $this->_doQuery($query, true);
724	if (PEAR::isError($result)) {
725	$this->warnings[] = 'nextID: could not delete previous sequence table values from '.$seq_name;
726	}
727	}
728	return $value;
729	}
730
731	// }}}
732	// {{{ lastInsertID()
733
734	/**
735	* Returns the autoincrement ID if supported or $id or fetches the current
736	* ID in a sequence called: $table.(empty($field) ? '' : '_'.$field)
737	*
738	* @param string $table name of the table into which a new row was inserted
739	* @param string $field name of the field into which a new row was inserted
740	*
741	* @return mixed MDB2 Error Object or id
742	* @access public
743	*/
744	function lastInsertID($table = null, $field = null)
745	{
746	return $this->queryOne("SELECT IDENT_CURRENT('$table')", 'integer');
747	}
748
749	// }}}
750	}
751
752	// }}}
753	// {{{ Class MDB2_Result_mssql
754
755	/**
756	* MDB2 MSSQL Server result driver
757	*
758	* @package MDB2
759	* @category Database
760	* @author Frank M. Kromann <frank@kromann.info>
761	*/
762	class MDB2_Result_sqlsrv extends MDB2_Result_Common
763	{
764	// {{{ constructor: function __construct($db, $result, $limit = 0, $offset = 0)
765
766	/**
767	* Constructor
768	*/
769	function __construct($db, $result, $limit = 0, $offset = 0)
770	{
771	$this->db = $db;
772	$this->result = $result;
773	$this->offset = $offset;
774	$this->limit = max(0, $limit - 1);
775	$this->cursor = 0;
776	$this->rows = array();
777	$this->numFields = sqlsrv_num_fields($result);
778	$this->fieldMeta = sqlsrv_field_metadata($result);
779	$this->numRowsAffected = sqlsrv_rows_affected($result);
780	while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
781	if ($row !== null) {
782	if ($this->offset && $this->offset_count < $this->offset) {
783	$this->offset_count++;
784	continue;
785	}
786	foreach ($row as $k => $v) {
787	if (is_object($v) && method_exists($v, 'format')) {
788	//DateTime Object
789	$row[$k] = $v->format('Y-m-d H:i:s');
790	}
791	}
792	$this->rows[] = $row; //read results into memory, cursors are not supported
793	}
794	}
795	$this->rowcnt = count($this->rows);
796	}
797
798	// }}}
799	// {{{ _skipLimitOffset()
800
801	/**
802	* Skip the first row of a result set.
803	*
804	* @param resource $result
805	* @return mixed a result handle or MDB2_OK on success, a MDB2 error on failure
806	* @access protected
807	*/
808	/* function _skipLimitOffset()
809	{
810	if ($this->limit) {
811	if ($this->rownum >= $this->limit) {
812	return false;
813	}
814	}
815	if ($this->offset) {
816	while ($this->offset_count < $this->offset) {
817	++$this->offset_count;
818	if (!is_array(@sqlsrv_fetch_array($this->result))) {
819	$this->offset_count = $this->limit;
820	return false;
821	}
822	}
823	}
824	return MDB2_OK;
825	}*/
826
827	// }}}
828	function array_to_obj($array, &$obj) {
829	foreach ($array as $key => $value) {
830	if (is_array($value)) {
831	$obj->$key = new stdClass();
832	array_to_obj($value, $obj->$key);
833	} else {
834	$obj->$key = $value;
835	}
836	}
837	return $obj;
838	}
839	// {{{ fetchRow()
840
841	/**
842	* Fetch a row and insert the data into an existing array.
843	*
844	* @param int $fetchmode how the array data should be indexed
845	* @param int $rownum number of the row where the data can be found
846	* @return int data array on success, a MDB2 error on failure
847	* @access public
848	*/
849	function fetchRow($fetchmode = MDB2_FETCHMODE_DEFAULT, $rownum = null)
850	{
851	if (!$this->result) {
852	return $this->db->raiseError(MDB2_ERROR_INVALID, null, null, 'no valid statement given', __FUNCTION__);
853	}
854	if (($this->limit && $this->rownum >= $this->limit) \|\| ($this->cursor >= $this->rowcnt \|\| $this->rowcnt == 0)) {
855	return null;
856	}
857	if (null !== $rownum) {
858	$seek = $this->seek($rownum);
859	if (PEAR::isError($seek)) {
860	return $seek;
861	}
862	}
863
864	$row = false;
865	$arrNum = array();
866	if ($fetchmode == MDB2_FETCHMODE_ORDERED \|\| $fetchmode == MDB2_FETCHMODE_DEFAULT) {
867	foreach ($this->rows[$this->cursor] as $key=>$value) {
868	$arrNum[] = $value;
869	}
870	}
871	switch($fetchmode) {
872	case MDB2_FETCHMODE_ASSOC:
873	$row = $this->rows[$this->cursor]; break;
874	case MDB2_FETCHMODE_ORDERED:
875	$row = $arrNum; break;
876	case MDB2_FETCHMODE_OBJECT:
877	$row = $this->array_to_obj($this->rows[$this->cursor],$o = new $this->db->options['fetch_class']); break;
878	case MDB2_FETCHMODE_DEFAULT:
879	default:
880	$row = $this->rows[$this->cursor] + $arrNum; break;
881	}
882	$this->cursor++;
883
884	/*
885	if ($fetchmode == MDB2_FETCHMODE_OBJECT) {
886	$row = sqlsrv_fetch_object($this->result,$this->db->options['fetch_class']);
887	} else {
888	switch($fetchmode) {
889	case MDB2_FETCHMODE_ASSOC: $fetchmode = SQLSRV_FETCH_ASSOC; break;
890	case MDB2_FETCHMODE_ORDERED: $fetchmode = SQLSRV_FETCH_NUMERIC; break;
891	case MDB2_FETCHMODE_DEFAULT:
892	default:
893	$fetchmode = SQLSRV_FETCH_BOTH;
894	}
895	$row = sqlsrv_fetch_array($this->result,$fetchmode);
896	}
897	foreach ($row as $key=>$value) {
898	if (is_object($value) && method_exists($value, 'format')) {//is DateTime object
899	$row[$key] = $value->format("Y-m-d H:i:s");
900	}
901	}*/
902
903	/*if ($fetchmode == MDB2_FETCHMODE_DEFAULT) {
904	$fetchmode = $this->db->fetchmode;
905	}*/
906	if ($fetchmode == MDB2_FETCHMODE_ASSOC && is_array($row) && $this->db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
907	$row = array_change_key_case($row, $this->db->options['field_case']);
908	}
909	if (!$row) {
910	if (false === $this->result) {
911	$err = $this->db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
912	'resultset has already been freed', __FUNCTION__);
913	return $err;
914	}
915	return null;
916	}
917	$mode = $this->db->options['portability'] & MDB2_PORTABILITY_EMPTY_TO_NULL;
918	$rtrim = false;
919	if ($this->db->options['portability'] & MDB2_PORTABILITY_RTRIM) {
920	if (empty($this->types)) {
921	$mode += MDB2_PORTABILITY_RTRIM;
922	} else {
923	$rtrim = true;
924	}
925	}
926	if ($mode) {
927	$this->db->_fixResultArrayValues($row, $mode);
928	}
929	if (!empty($this->types)) {
930	$row = $this->db->datatype->convertResultRow($this->types, $row, $rtrim);
931	}
932	if (!empty($this->values)) {
933	$this->_assignBindColumns($row);
934	}
935	++$this->rownum;
936	return $row;
937	}
938
939	// }}}
940	// {{{ _getColumnNames()
941
942	/**
943	* Retrieve the names of columns returned by the DBMS in a query result.
944	*
945	* @return mixed Array variable that holds the names of columns as keys
946	* or an MDB2 error on failure.
947	* Some DBMS may not return any columns when the result set
948	* does not contain any rows.
949	* @access private
950	*/
951	function _getColumnNames()
952	{
953	if (!$this->result) {
954	return $this->db->raiseError(MDB2_ERROR_INVALID, null, null, 'no valid statement given', __FUNCTION__);
955	}
956	$columns = array();
957	foreach ($this->fieldMeta as $col) {
958	$columns[$col['Name']] = $col['Type'];
959	}
960	if ($this->db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
961	$columns = array_change_key_case($columns, $this->db->options['field_case']);
962	}
963	return $columns;
964	}
965
966	// }}}
967	// {{{ numCols()
968
969	/**
970	* Count the number of columns returned by the DBMS in a query result.
971	*
972	* @return mixed integer value with the number of columns, a MDB2 error
973	* on failure
974	* @access public
975	*/
976	function numCols()
977	{
978	if (!$this->result) {
979	return $this->db->raiseError(MDB2_ERROR_INVALID, null, null, 'no valid statement given', __FUNCTION__);
980	}
981	$cols = $this->numFields;
982	if (!$cols) {
983	if (false === $this->result) {
984	return $this->db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
985	'resultset has already been freed', __FUNCTION__);
986	}
987	if (null === $this->result) {
988	return count($this->types);
989	}
990	return $this->db->raiseError(null, null, null,
991	'Could not get column count', __FUNCTION__);
992	}
993	return $cols;
994	}
995
996	// }}}
997	// {{{ nextResult()
998
999	/**
1000	* Move the internal result pointer to the next available result
1001	*
1002	* @return true on success, false if there is no more result set or an error object on failure
1003	* @access public
1004	*/
1005	function nextResult()
1006	{
1007	if (false === $this->result) {
1008	return $this->db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
1009	'resultset has already been freed', __FUNCTION__);
1010	}
1011	if (null === $this->result) {
1012	return false;
1013	}
1014	$ret = sqlsrv_next_result($this->result);
1015	if ($ret) {
1016	$this->cursor = 0;
1017	$this->rows = array();
1018	$this->numFields = sqlsrv_num_fields($this->result);
1019	$this->fieldMeta = sqlsrv_field_metadata($this->result);
1020	$this->numRowsAffected = sqlsrv_rows_affected($this->result);
1021	while ($row = sqlsrv_fetch_array($this->result, SQLSRV_FETCH_ASSOC)) {
1022	if ($row !== null) {
1023	if ($this->offset && $this->offset_count < $this->offset) {
1024	$this->offset_count++;
1025	continue;
1026	}
1027	foreach ($row as $k => $v) {
1028	if (is_object($v) && method_exists($v, 'format')) {//DateTime Object
1029	//$v->setTimezone(new DateTimeZone('GMT'));//TS_ISO_8601 with a trailing 'Z' is GMT
1030	$row[$k] = $v->format("Y-m-d H:i:s");
1031	}
1032	}
1033	$this->rows[] = $row;//read results into memory, cursors are not supported
1034	}
1035	}
1036	$this->rowcnt = count($this->rows);
1037	}
1038	return $ret;
1039	}
1040
1041	// }}}
1042	// {{{ free()
1043
1044	/**
1045	* Free the internal resources associated with $result.
1046	*
1047	* @return boolean true on success, false if $result is invalid
1048	* @access public
1049	*/
1050	function free()
1051	{
1052	if (is_resource($this->result) && $this->db->connection) {
1053	if (!@sqlsrv_free_stmt($this->result)) {
1054	return $this->db->raiseError(null, null, null,
1055	'Could not free result', __FUNCTION__);
1056	}
1057	}
1058	$this->result = false;
1059	return MDB2_OK;
1060	}
1061
1062	// }}}
1063	// {{{ function rowCount()
1064	/**
1065	* Returns the actual row number that was last fetched (count from 0)
1066	* @return int
1067	*
1068	* @access public
1069	*/
1070	function rowCount()
1071	{
1072	return $this->cursor;
1073	}
1074
1075	// }}}
1076	// {{{ function numRows()
1077
1078	/**
1079	* Returns the number of rows in a result object
1080	*
1081	* @return mixed MDB2 Error Object or the number of rows
1082	*
1083	* @access public
1084	*/
1085	function numRows()
1086	{
1087	return $this->rowcnt;
1088	}
1089
1090	// }}}
1091	// {{{ function seek($rownum = 0)
1092
1093	/**
1094	* Seek to a specific row in a result set
1095	*
1096	* @param int number of the row where the data can be found
1097	*
1098	* @return mixed MDB2_OK on success, a MDB2 error on failure
1099	*
1100	* @access public
1101	*/
1102	function seek($rownum = 0)
1103	{
1104	$this->cursor = min($rownum, $this->rowcnt);
1105	return MDB2_OK;
1106	}
1107
1108	// }}}
1109	}
1110
1111	// }}}
1112	// {{{ class MDB2_BufferedResult_mssql
1113
1114	/**
1115	* MDB2 MSSQL Server buffered result driver
1116	*
1117	* @package MDB2
1118	* @category Database
1119	* @author Frank M. Kromann <frank@kromann.info>
1120	*/
1121	class MDB2_BufferedResult_sqlsrv extends MDB2_Result_sqlsrv
1122	{
1123	// {{{ valid()
1124
1125	/**
1126	* Check if the end of the result set has been reached
1127	*
1128	* @return mixed true or false on sucess, a MDB2 error on failure
1129	* @access public
1130	*/
1131	function valid()
1132	{
1133	$numrows = $this->numRows();
1134	if (PEAR::isError($numrows)) {
1135	return $numrows;
1136	}
1137	return $this->rownum < ($numrows - 1);
1138	}
1139
1140	// }}}
1141
1142	}
1143
1144	// }}}
1145	// {{{ MDB2_Statement_mssql
1146
1147	/**
1148	* MDB2 MSSQL Server statement driver
1149	*
1150	* @package MDB2
1151	* @category Database
1152	* @author Frank M. Kromann <frank@kromann.info>
1153	*/
1154	class MDB2_Statement_sqlsrv extends MDB2_Statement_Common
1155	{
1156
1157	}
1158
1159	// }}}
1160
1161	?>

Note: See TracBrowser for help on using the repository browser.

Roundcube Webmail

Context Navigation

source: subversion/trunk/roundcubemail/program/lib/MDB2/Driver/sqlsrv.php @ 3635

Download in other formats: