Context Navigation

source: Dev/trunk/rdfapi/util/adodb/drivers/adodb-odbc_mssql.inc.php @ 12

Last change on this file since 12 was 12, checked in by basvannuland, 14 years ago
Added RAP RDF API Added RDF reader writer for save and load survey
File size: 9.3 KB

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: