Context Navigation

source: trunk/phpgwapi/inc/adodb/drivers/adodb-odbc_mssql.inc.php @ 34

Revision 34, 9.0 KB checked in by niltonneto, 17 years ago (diff)
Versão nova do ADODB (4.5 para 4.95)
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2	/*
3	V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved.
4	Released under both BSD license and Lesser GPL library license.
5	Whenever there is any discrepancy between the two licenses,
6	the BSD license will take precedence.
7	Set tabs to 4 for best viewing.
8
9	Latest version is available at http://adodb.sourceforge.net
10
11	MSSQL support via ODBC. Requires ODBC. Works on Windows and Unix.
12	For Unix configuration, see http://phpbuilder.com/columns/alberto20000919.php3
13	*/
14
15	// security - hide paths
16	if (!defined('ADODB_DIR')) die();
17
18	if (!defined('_ADODB_ODBC_LAYER')) {
19	include(ADODB_DIR."/drivers/adodb-odbc.inc.php");
20	}
21
22
23	class ADODB_odbc_mssql extends ADODB_odbc {
24	var $databaseType = 'odbc_mssql';
25	var $fmtDate = "'Y-m-d'";
26	var $fmtTimeStamp = "'Y-m-d H:i:s'";
27	var $_bindInputArray = true;
28	var $metaTablesSQL="select name,case when type='U' then 'T' else 'V' end from sysobjects where (type='U' or type='V') and (name not in ('sysallocations','syscolumns','syscomments','sysdepends','sysfilegroups','sysfiles','sysfiles1','sysforeignkeys','sysfulltextcatalogs','sysindexes','sysindexkeys','sysmembers','sysobjects','syspermissions','sysprotects','sysreferences','systypes','sysusers','sysalternates','sysconstraints','syssegments','REFERENTIAL_CONSTRAINTS','CHECK_CONSTRAINTS','CONSTRAINT_TABLE_USAGE','CONSTRAINT_COLUMN_USAGE','VIEWS','VIEW_TABLE_USAGE','VIEW_COLUMN_USAGE','SCHEMATA','TABLES','TABLE_CONSTRAINTS','TABLE_PRIVILEGES','COLUMNS','COLUMN_DOMAIN_USAGE','COLUMN_PRIVILEGES','DOMAINS','DOMAIN_CONSTRAINTS','KEY_COLUMN_USAGE'))";
29	var $metaColumnsSQL = "select c.name,t.name,c.length from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
30	var $hasTop = 'top'; // support mssql/interbase SELECT TOP 10 * FROM TABLE
31	var $sysDate = 'GetDate()';
32	var $sysTimeStamp = 'GetDate()';
33	var $leftOuter = '*=';
34	var $rightOuter = '=*';
35	var $substr = 'substring';
36	var $length = 'len';
37	var $ansiOuter = true; // for mssql7 or later
38	var $identitySQL = 'select @@IDENTITY'; // 'select SCOPE_IDENTITY'; # for mssql 2000
39	var $hasInsertID = true;
40	var $connectStmt = 'SET CONCAT_NULL_YIELDS_NULL OFF'; # When SET CONCAT_NULL_YIELDS_NULL is ON,
41	# concatenating a null value with a string yields a NULL result
42
43	function ADODB_odbc_mssql()
44	{
45	$this->ADODB_odbc();
46	//$this->curmode = SQL_CUR_USE_ODBC;
47	}
48
49	// crashes php...
50	function ServerInfo()
51	{
52	global $ADODB_FETCH_MODE;
53	$save = $ADODB_FETCH_MODE;
54	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
55	$row = $this->GetRow("execute sp_server_info 2");
56	$ADODB_FETCH_MODE = $save;
57	if (!is_array($row)) return false;
58	$arr['description'] = $row[2];
59	$arr['version'] = ADOConnection::_findvers($arr['description']);
60	return $arr;
61	}
62
63	function IfNull( $field, $ifNull )
64	{
65	return " ISNULL($field, $ifNull) "; // if MS SQL Server
66	}
67
68	function _insertid()
69	{
70	// SCOPE_IDENTITY()
71	// Returns the last IDENTITY value inserted into an IDENTITY column in
72	// the same scope. A scope is a module -- a stored procedure, trigger,
73	// function, or batch. Thus, two statements are in the same scope if
74	// they are in the same stored procedure, function, or batch.
75	return $this->GetOne($this->identitySQL);
76	}
77
78
79	function MetaForeignKeys($table, $owner=false, $upper=false)
80	{
81	global $ADODB_FETCH_MODE;
82
83	$save = $ADODB_FETCH_MODE;
84	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
85	$table = $this->qstr(strtoupper($table));
86
87	$sql =
88	"select object_name(constid) as constraint_name,
89	col_name(fkeyid, fkey) as column_name,
90	object_name(rkeyid) as referenced_table_name,
91	col_name(rkeyid, rkey) as referenced_column_name
92	from sysforeignkeys
93	where upper(object_name(fkeyid)) = $table
94	order by constraint_name, referenced_table_name, keyno";
95
96	$constraints =& $this->GetArray($sql);
97
98	$ADODB_FETCH_MODE = $save;
99
100	$arr = false;
101	foreach($constraints as $constr) {
102	//print_r($constr);
103	$arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
104	}
105	if (!$arr) return false;
106
107	$arr2 = false;
108
109	foreach($arr as $k => $v) {
110	foreach($v as $a => $b) {
111	if ($upper) $a = strtoupper($a);
112	$arr2[$a] = $b;
113	}
114	}
115	return $arr2;
116	}
117
118	function &MetaTables($ttype=false,$showSchema=false,$mask=false)
119	{
120	if ($mask) {$this->debug=1;
121	$save = $this->metaTablesSQL;
122	$mask = $this->qstr($mask);
123	$this->metaTablesSQL .= " AND name like $mask";
124	}
125	$ret =& ADOConnection::MetaTables($ttype,$showSchema);
126
127	if ($mask) {
128	$this->metaTablesSQL = $save;
129	}
130	return $ret;
131	}
132
133	function &MetaColumns($table)
134	{
135	$arr = ADOConnection::MetaColumns($table);
136	return $arr;
137	}
138
139
140	function &MetaIndexes($table,$primary=false)
141	{
142	$table = $this->qstr($table);
143
144	$sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
145	CASE WHEN I.indid BETWEEN 1 AND 254 AND (I.status & 2048 = 2048 OR I.Status = 16402 AND O.XType = 'V') THEN 1 ELSE 0 END AS IsPK,
146	CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
147	FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
148	INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
149	INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
150	WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
151	ORDER BY O.name, I.Name, K.keyno";
152
153	global $ADODB_FETCH_MODE;
154	$save = $ADODB_FETCH_MODE;
155	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
156	if ($this->fetchMode !== FALSE) {
157	$savem = $this->SetFetchMode(FALSE);
158	}
159
160	$rs = $this->Execute($sql);
161	if (isset($savem)) {
162	$this->SetFetchMode($savem);
163	}
164	$ADODB_FETCH_MODE = $save;
165
166	if (!is_object($rs)) {
167	return FALSE;
168	}
169
170	$indexes = array();
171	while ($row = $rs->FetchRow()) {
172	if (!$primary && $row[5]) continue;
173
174	$indexes[$row[0]]['unique'] = $row[6];
175	$indexes[$row[0]]['columns'][] = $row[1];
176	}
177	return $indexes;
178	}
179
180	function _query($sql,$inputarr)
181	{
182	if (is_string($sql)) $sql = str_replace('\|\|','+',$sql);
183	return ADODB_odbc::_query($sql,$inputarr);
184	}
185
186	function SetTransactionMode( $transaction_mode )
187	{
188	$this->_transmode = $transaction_mode;
189	if (empty($transaction_mode)) {
190	$this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
191	return;
192	}
193	if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
194	$this->Execute("SET TRANSACTION ".$transaction_mode);
195	}
196
197	// "Stein-Aksel Basma" <basma@accelero.no>
198	// tested with MSSQL 2000
199	function &MetaPrimaryKeys($table)
200	{
201	global $ADODB_FETCH_MODE;
202
203	$schema = '';
204	$this->_findschema($table,$schema);
205	//if (!$schema) $schema = $this->database;
206	if ($schema) $schema = "and k.table_catalog like '$schema%'";
207
208	$sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
209	information_schema.table_constraints tc
210	where tc.constraint_name = k.constraint_name and tc.constraint_type =
211	'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
212
213	$savem = $ADODB_FETCH_MODE;
214	$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
215	$a = $this->GetCol($sql);
216	$ADODB_FETCH_MODE = $savem;
217
218	if ($a && sizeof($a)>0) return $a;
219	$false = false;
220	return $false;
221	}
222
223	function &SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
224	{
225	if ($nrows > 0 && $offset <= 0) {
226	$sql = preg_replace(
227	'/(^\s*select\s+(distinctrow\|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql);
228	$rs =& $this->Execute($sql,$inputarr);
229	} else
230	$rs =& ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
231
232	return $rs;
233	}
234
235	// Format date column in sql string given an input format that understands Y M D
236	function SQLDate($fmt, $col=false)
237	{
238	if (!$col) $col = $this->sysTimeStamp;
239	$s = '';
240
241	$len = strlen($fmt);
242	for ($i=0; $i < $len; $i++) {
243	if ($s) $s .= '+';
244	$ch = $fmt[$i];
245	switch($ch) {
246	case 'Y':
247	case 'y':
248	$s .= "datename(yyyy,$col)";
249	break;
250	case 'M':
251	$s .= "convert(char(3),$col,0)";
252	break;
253	case 'm':
254	$s .= "replace(str(month($col),2),' ','0')";
255	break;
256	case 'Q':
257	case 'q':
258	$s .= "datename(quarter,$col)";
259	break;
260	case 'D':
261	case 'd':
262	$s .= "replace(str(day($col),2),' ','0')";
263	break;
264	case 'h':
265	$s .= "substring(convert(char(14),$col,0),13,2)";
266	break;
267
268	case 'H':
269	$s .= "replace(str(datepart(hh,$col),2),' ','0')";
270	break;
271
272	case 'i':
273	$s .= "replace(str(datepart(mi,$col),2),' ','0')";
274	break;
275	case 's':
276	$s .= "replace(str(datepart(ss,$col),2),' ','0')";
277	break;
278	case 'a':
279	case 'A':
280	$s .= "substring(convert(char(19),$col,0),18,2)";
281	break;
282
283	default:
284	if ($ch == '\\') {
285	$i++;
286	$ch = substr($fmt,$i,1);
287	}
288	$s .= $this->qstr($ch);
289	break;
290	}
291	}
292	return $s;
293	}
294
295	}
296
297	class ADORecordSet_odbc_mssql extends ADORecordSet_odbc {
298
299	var $databaseType = 'odbc_mssql';
300
301	function ADORecordSet_odbc_mssql($id,$mode=false)
302	{
303	return $this->ADORecordSet_odbc($id,$mode);
304	}
305	}
306	?>

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

Download in other formats: