Context Navigation

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

Revision 34, 29.8 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	Native mssql driver. Requires mssql client. Works on Windows.
12	To configure for Unix, see
13	http://phpbuilder.com/columns/alberto20000919.php3
14
15	*/
16
17	// security - hide paths
18	if (!defined('ADODB_DIR')) die();
19
20	//----------------------------------------------------------------
21	// MSSQL returns dates with the format Oct 13 2002 or 13 Oct 2002
22	// and this causes tons of problems because localized versions of
23	// MSSQL will return the dates in dmy or mdy order; and also the
24	// month strings depends on what language has been configured. The
25	// following two variables allow you to control the localization
26	// settings - Ugh.
27	//
28	// MORE LOCALIZATION INFO
29	// ----------------------
30	// To configure datetime, look for and modify sqlcommn.loc,
31	// typically found in c:\mssql\install
32	// Also read :
33	// http://support.microsoft.com/default.aspx?scid=kb;EN-US;q220918
34	// Alternatively use:
35	// CONVERT(char(12),datecol,120)
36	//----------------------------------------------------------------
37
38
39	// has datetime converstion to YYYY-MM-DD format, and also mssql_fetch_assoc
40	if (ADODB_PHPVER >= 0x4300) {
41	// docs say 4.2.0, but testing shows only since 4.3.0 does it work!
42	ini_set('mssql.datetimeconvert',0);
43	} else {
44	global $ADODB_mssql_mths; // array, months must be upper-case
45
46
47	$ADODB_mssql_date_order = 'mdy';
48	$ADODB_mssql_mths = array(
49	'JAN'=>1,'FEB'=>2,'MAR'=>3,'APR'=>4,'MAY'=>5,'JUN'=>6,
50	'JUL'=>7,'AUG'=>8,'SEP'=>9,'OCT'=>10,'NOV'=>11,'DEC'=>12);
51	}
52
53	//---------------------------------------------------------------------------
54	// Call this to autoset $ADODB_mssql_date_order at the beginning of your code,
55	// just after you connect to the database. Supports mdy and dmy only.
56	// Not required for PHP 4.2.0 and above.
57	function AutoDetect_MSSQL_Date_Order($conn)
58	{
59	global $ADODB_mssql_date_order;
60	$adate = $conn->GetOne('select getdate()');
61	if ($adate) {
62	$anum = (int) $adate;
63	if ($anum > 0) {
64	if ($anum > 31) {
65	//ADOConnection::outp( "MSSQL: YYYY-MM-DD date format not supported currently");
66	} else
67	$ADODB_mssql_date_order = 'dmy';
68	} else
69	$ADODB_mssql_date_order = 'mdy';
70	}
71	}
72
73	class ADODB_mssql extends ADOConnection {
74	var $databaseType = "mssql";
75	var $dataProvider = "mssql";
76	var $replaceQuote = "''"; // string to use to replace quotes
77	var $fmtDate = "'Y-m-d'";
78	var $fmtTimeStamp = "'Y-m-d H:i:s'";
79	var $hasInsertID = true;
80	var $substr = "substring";
81	var $length = 'len';
82	var $hasAffectedRows = true;
83	var $metaDatabasesSQL = "select name from sysdatabases where name <> 'master'";
84	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','dtproperties'))";
85	var $metaColumnsSQL = # xtype==61 is datetime
86	"select c.name,t.name,c.length,
87	(case when c.xusertype=61 then 0 else c.xprec end),
88	(case when c.xusertype=61 then 0 else c.xscale end)
89	from syscolumns c join systypes t on t.xusertype=c.xusertype join sysobjects o on o.id=c.id where o.name='%s'";
90	var $hasTop = 'top'; // support mssql SELECT TOP 10 * FROM TABLE
91	var $hasGenID = true;
92	var $sysDate = 'convert(datetime,convert(char,GetDate(),102),102)';
93	var $sysTimeStamp = 'GetDate()';
94	var $_has_mssql_init;
95	var $maxParameterLen = 4000;
96	var $arrayClass = 'ADORecordSet_array_mssql';
97	var $uniqueSort = true;
98	var $leftOuter = '*=';
99	var $rightOuter = '=*';
100	var $ansiOuter = true; // for mssql7 or later
101	var $poorAffectedRows = true;
102	var $identitySQL = 'select @@IDENTITY'; // 'select SCOPE_IDENTITY'; # for mssql 2000
103	var $uniqueOrderBy = true;
104	var $_bindInputArray = true;
105
106	function ADODB_mssql()
107	{
108	$this->_has_mssql_init = (strnatcmp(PHP_VERSION,'4.1.0')>=0);
109	}
110
111	function ServerInfo()
112	{
113	global $ADODB_FETCH_MODE;
114
115
116	if ($this->fetchMode === false) {
117	$savem = $ADODB_FETCH_MODE;
118	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
119	} else
120	$savem = $this->SetFetchMode(ADODB_FETCH_NUM);
121
122	if (0) {
123	$stmt = $this->PrepareSP('sp_server_info');
124	$val = 2;
125	$this->Parameter($stmt,$val,'attribute_id');
126	$row = $this->GetRow($stmt);
127	}
128
129	$row = $this->GetRow("execute sp_server_info 2");
130
131
132	if ($this->fetchMode === false) {
133	$ADODB_FETCH_MODE = $savem;
134	} else
135	$this->SetFetchMode($savem);
136
137	$arr['description'] = $row[2];
138	$arr['version'] = ADOConnection::_findvers($arr['description']);
139	return $arr;
140	}
141
142	function IfNull( $field, $ifNull )
143	{
144	return " ISNULL($field, $ifNull) "; // if MS SQL Server
145	}
146
147	function _insertid()
148	{
149	// SCOPE_IDENTITY()
150	// Returns the last IDENTITY value inserted into an IDENTITY column in
151	// the same scope. A scope is a module -- a stored procedure, trigger,
152	// function, or batch. Thus, two statements are in the same scope if
153	// they are in the same stored procedure, function, or batch.
154	return $this->GetOne($this->identitySQL);
155	}
156
157	function _affectedrows()
158	{
159	return $this->GetOne('select @@rowcount');
160	}
161
162	var $_dropSeqSQL = "drop table %s";
163
164	function CreateSequence($seq='adodbseq',$start=1)
165	{
166
167	$this->Execute('BEGIN TRANSACTION adodbseq');
168	$start -= 1;
169	$this->Execute("create table $seq (id float(53))");
170	$ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
171	if (!$ok) {
172	$this->Execute('ROLLBACK TRANSACTION adodbseq');
173	return false;
174	}
175	$this->Execute('COMMIT TRANSACTION adodbseq');
176	return true;
177	}
178
179	function GenID($seq='adodbseq',$start=1)
180	{
181	//$this->debug=1;
182	$this->Execute('BEGIN TRANSACTION adodbseq');
183	$ok = $this->Execute("update $seq with (tablock,holdlock) set id = id + 1");
184	if (!$ok) {
185	$this->Execute("create table $seq (id float(53))");
186	$ok = $this->Execute("insert into $seq with (tablock,holdlock) values($start)");
187	if (!$ok) {
188	$this->Execute('ROLLBACK TRANSACTION adodbseq');
189	return false;
190	}
191	$this->Execute('COMMIT TRANSACTION adodbseq');
192	return $start;
193	}
194	$num = $this->GetOne("select id from $seq");
195	$this->Execute('COMMIT TRANSACTION adodbseq');
196	return $num;
197
198	// in old implementation, pre 1.90, we returned GUID...
199	//return $this->GetOne("SELECT CONVERT(varchar(255), NEWID()) AS 'Char'");
200	}
201
202
203	function &SelectLimit($sql,$nrows=-1,$offset=-1, $inputarr=false,$secs2cache=0)
204	{
205	if ($nrows > 0 && $offset <= 0) {
206	$sql = preg_replace(
207	'/(^\s*select\s+(distinctrow\|distinct)?)/i','\\1 '.$this->hasTop." $nrows ",$sql);
208	$rs =& $this->Execute($sql,$inputarr);
209	} else
210	$rs =& ADOConnection::SelectLimit($sql,$nrows,$offset,$inputarr,$secs2cache);
211
212	return $rs;
213	}
214
215
216	// Format date column in sql string given an input format that understands Y M D
217	function SQLDate($fmt, $col=false)
218	{
219	if (!$col) $col = $this->sysTimeStamp;
220	$s = '';
221
222	$len = strlen($fmt);
223	for ($i=0; $i < $len; $i++) {
224	if ($s) $s .= '+';
225	$ch = $fmt[$i];
226	switch($ch) {
227	case 'Y':
228	case 'y':
229	$s .= "datename(yyyy,$col)";
230	break;
231	case 'M':
232	$s .= "convert(char(3),$col,0)";
233	break;
234	case 'm':
235	$s .= "replace(str(month($col),2),' ','0')";
236	break;
237	case 'Q':
238	case 'q':
239	$s .= "datename(quarter,$col)";
240	break;
241	case 'D':
242	case 'd':
243	$s .= "replace(str(day($col),2),' ','0')";
244	break;
245	case 'h':
246	$s .= "substring(convert(char(14),$col,0),13,2)";
247	break;
248
249	case 'H':
250	$s .= "replace(str(datepart(hh,$col),2),' ','0')";
251	break;
252
253	case 'i':
254	$s .= "replace(str(datepart(mi,$col),2),' ','0')";
255	break;
256	case 's':
257	$s .= "replace(str(datepart(ss,$col),2),' ','0')";
258	break;
259	case 'a':
260	case 'A':
261	$s .= "substring(convert(char(19),$col,0),18,2)";
262	break;
263
264	default:
265	if ($ch == '\\') {
266	$i++;
267	$ch = substr($fmt,$i,1);
268	}
269	$s .= $this->qstr($ch);
270	break;
271	}
272	}
273	return $s;
274	}
275
276
277	function BeginTrans()
278	{
279	if ($this->transOff) return true;
280	$this->transCnt += 1;
281	$this->Execute('BEGIN TRAN');
282	return true;
283	}
284
285	function CommitTrans($ok=true)
286	{
287	if ($this->transOff) return true;
288	if (!$ok) return $this->RollbackTrans();
289	if ($this->transCnt) $this->transCnt -= 1;
290	$this->Execute('COMMIT TRAN');
291	return true;
292	}
293	function RollbackTrans()
294	{
295	if ($this->transOff) return true;
296	if ($this->transCnt) $this->transCnt -= 1;
297	$this->Execute('ROLLBACK TRAN');
298	return true;
299	}
300
301	function SetTransactionMode( $transaction_mode )
302	{
303	$this->_transmode = $transaction_mode;
304	if (empty($transaction_mode)) {
305	$this->Execute('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
306	return;
307	}
308	if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
309	$this->Execute("SET TRANSACTION ".$transaction_mode);
310	}
311
312	/*
313	Usage:
314
315	$this->BeginTrans();
316	$this->RowLock('table1,table2','table1.id=33 and table2.id=table1.id'); # lock row 33 for both tables
317
318	# some operation on both tables table1 and table2
319
320	$this->CommitTrans();
321
322	See http://www.swynk.com/friends/achigrik/SQL70Locks.asp
323	*/
324	function RowLock($tables,$where,$flds='top 1 null as ignore')
325	{
326	if (!$this->transCnt) $this->BeginTrans();
327	return $this->GetOne("select $flds from $tables with (ROWLOCK,HOLDLOCK) where $where");
328	}
329
330
331	function &MetaIndexes($table,$primary=false)
332	{
333	$table = $this->qstr($table);
334
335	$sql = "SELECT i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno,
336	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,
337	CASE WHEN I.status & 2 = 2 THEN 1 ELSE 0 END AS IsUnique
338	FROM dbo.sysobjects o INNER JOIN dbo.sysindexes I ON o.id = i.id
339	INNER JOIN dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid
340	INNER JOIN dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
341	WHERE LEFT(i.name, 8) <> '_WA_Sys_' AND o.status >= 0 AND O.Name LIKE $table
342	ORDER BY O.name, I.Name, K.keyno";
343
344	global $ADODB_FETCH_MODE;
345	$save = $ADODB_FETCH_MODE;
346	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
347	if ($this->fetchMode !== FALSE) {
348	$savem = $this->SetFetchMode(FALSE);
349	}
350
351	$rs = $this->Execute($sql);
352	if (isset($savem)) {
353	$this->SetFetchMode($savem);
354	}
355	$ADODB_FETCH_MODE = $save;
356
357	if (!is_object($rs)) {
358	return FALSE;
359	}
360
361	$indexes = array();
362	while ($row = $rs->FetchRow()) {
363	if (!$primary && $row[5]) continue;
364
365	$indexes[$row[0]]['unique'] = $row[6];
366	$indexes[$row[0]]['columns'][] = $row[1];
367	}
368	return $indexes;
369	}
370
371	function MetaForeignKeys($table, $owner=false, $upper=false)
372	{
373	global $ADODB_FETCH_MODE;
374
375	$save = $ADODB_FETCH_MODE;
376	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
377	$table = $this->qstr(strtoupper($table));
378
379	$sql =
380	"select object_name(constid) as constraint_name,
381	col_name(fkeyid, fkey) as column_name,
382	object_name(rkeyid) as referenced_table_name,
383	col_name(rkeyid, rkey) as referenced_column_name
384	from sysforeignkeys
385	where upper(object_name(fkeyid)) = $table
386	order by constraint_name, referenced_table_name, keyno";
387
388	$constraints =& $this->GetArray($sql);
389
390	$ADODB_FETCH_MODE = $save;
391
392	$arr = false;
393	foreach($constraints as $constr) {
394	//print_r($constr);
395	$arr[$constr[0]][$constr[2]][] = $constr[1].'='.$constr[3];
396	}
397	if (!$arr) return false;
398
399	$arr2 = false;
400
401	foreach($arr as $k => $v) {
402	foreach($v as $a => $b) {
403	if ($upper) $a = strtoupper($a);
404	$arr2[$a] = $b;
405	}
406	}
407	return $arr2;
408	}
409
410	//From: Fernando Moreira <FMoreira@imediata.pt>
411	function MetaDatabases()
412	{
413	if(@mssql_select_db("master")) {
414	$qry=$this->metaDatabasesSQL;
415	if($rs=@mssql_query($qry,$this->_connectionID)){
416	$tmpAr=$ar=array();
417	while($tmpAr=@mssql_fetch_row($rs))
418	$ar[]=$tmpAr[0];
419	@mssql_select_db($this->database);
420	if(sizeof($ar))
421	return($ar);
422	else
423	return(false);
424	} else {
425	@mssql_select_db($this->database);
426	return(false);
427	}
428	}
429	return(false);
430	}
431
432	// "Stein-Aksel Basma" <basma@accelero.no>
433	// tested with MSSQL 2000
434	function &MetaPrimaryKeys($table)
435	{
436	global $ADODB_FETCH_MODE;
437
438	$schema = '';
439	$this->_findschema($table,$schema);
440	if (!$schema) $schema = $this->database;
441	if ($schema) $schema = "and k.table_catalog like '$schema%'";
442
443	$sql = "select distinct k.column_name,ordinal_position from information_schema.key_column_usage k,
444	information_schema.table_constraints tc
445	where tc.constraint_name = k.constraint_name and tc.constraint_type =
446	'PRIMARY KEY' and k.table_name = '$table' $schema order by ordinal_position ";
447
448	$savem = $ADODB_FETCH_MODE;
449	$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
450	$a = $this->GetCol($sql);
451	$ADODB_FETCH_MODE = $savem;
452
453	if ($a && sizeof($a)>0) return $a;
454	$false = false;
455	return $false;
456	}
457
458
459	function &MetaTables($ttype=false,$showSchema=false,$mask=false)
460	{
461	if ($mask) {
462	$save = $this->metaTablesSQL;
463	$mask = $this->qstr(($mask));
464	$this->metaTablesSQL .= " AND name like $mask";
465	}
466	$ret =& ADOConnection::MetaTables($ttype,$showSchema);
467
468	if ($mask) {
469	$this->metaTablesSQL = $save;
470	}
471	return $ret;
472	}
473
474	function SelectDB($dbName)
475	{
476	$this->database = $dbName;
477	$this->databaseName = $dbName; # obsolete, retained for compat with older adodb versions
478	if ($this->_connectionID) {
479	return @mssql_select_db($dbName);
480	}
481	else return false;
482	}
483
484	function ErrorMsg()
485	{
486	if (empty($this->_errorMsg)){
487	$this->_errorMsg = mssql_get_last_message();
488	}
489	return $this->_errorMsg;
490	}
491
492	function ErrorNo()
493	{
494	if ($this->_logsql && $this->_errorCode !== false) return $this->_errorCode;
495	if (empty($this->_errorMsg)) {
496	$this->_errorMsg = mssql_get_last_message();
497	}
498	$id = @mssql_query("select @@ERROR",$this->_connectionID);
499	if (!$id) return false;
500	$arr = mssql_fetch_array($id);
501	@mssql_free_result($id);
502	if (is_array($arr)) return $arr[0];
503	else return -1;
504	}
505
506	// returns true or false
507	function _connect($argHostname, $argUsername, $argPassword, $argDatabasename)
508	{
509	if (!function_exists('mssql_pconnect')) return null;
510	$this->_connectionID = mssql_connect($argHostname,$argUsername,$argPassword);
511	if ($this->_connectionID === false) return false;
512	if ($argDatabasename) return $this->SelectDB($argDatabasename);
513	return true;
514	}
515
516
517	// returns true or false
518	function _pconnect($argHostname, $argUsername, $argPassword, $argDatabasename)
519	{
520	if (!function_exists('mssql_pconnect')) return null;
521	$this->_connectionID = mssql_pconnect($argHostname,$argUsername,$argPassword);
522	if ($this->_connectionID === false) return false;
523
524	// persistent connections can forget to rollback on crash, so we do it here.
525	if ($this->autoRollback) {
526	$cnt = $this->GetOne('select @@TRANCOUNT');
527	while (--$cnt >= 0) $this->Execute('ROLLBACK TRAN');
528	}
529	if ($argDatabasename) return $this->SelectDB($argDatabasename);
530	return true;
531	}
532
533	function Prepare($sql)
534	{
535	$sqlarr = explode('?',$sql);
536	if (sizeof($sqlarr) <= 1) return $sql;
537	$sql2 = $sqlarr[0];
538	for ($i = 1, $max = sizeof($sqlarr); $i < $max; $i++) {
539	$sql2 .= '@P'.($i-1) . $sqlarr[$i];
540	}
541	return array($sql,$this->qstr($sql2),$max);
542	}
543
544	function PrepareSP($sql)
545	{
546	if (!$this->_has_mssql_init) {
547	ADOConnection::outp( "PrepareSP: mssql_init only available since PHP 4.1.0");
548	return $sql;
549	}
550	$stmt = mssql_init($sql,$this->_connectionID);
551	if (!$stmt) return $sql;
552	return array($sql,$stmt);
553	}
554
555	// returns concatenated string
556	// MSSQL requires integers to be cast as strings
557	// automatically cast every datatype to VARCHAR(255)
558	// @author David Rogers (introspectshun)
559	function Concat()
560	{
561	$s = "";
562	$arr = func_get_args();
563
564	// Split single record on commas, if possible
565	if (sizeof($arr) == 1) {
566	foreach ($arr as $arg) {
567	$args = explode(',', $arg);
568	}
569	$arr = $args;
570	}
571
572	array_walk($arr, create_function('&$v', '$v = "CAST(" . $v . " AS VARCHAR(255))";'));
573	$s = implode('+',$arr);
574	if (sizeof($arr) > 0) return "$s";
575
576	return '';
577	}
578
579	/*
580	Usage:
581	$stmt = $db->PrepareSP('SP_RUNSOMETHING'); -- takes 2 params, @myid and @group
582
583	# note that the parameter does not have @ in front!
584	$db->Parameter($stmt,$id,'myid');
585	$db->Parameter($stmt,$group,'group',false,64);
586	$db->Execute($stmt);
587
588	@param $stmt Statement returned by Prepare() or PrepareSP().
589	@param $var PHP variable to bind to. Can set to null (for isNull support).
590	@param $name Name of stored procedure variable name to bind to.
591	@param [$isOutput] Indicates direction of parameter 0/false=IN 1=OUT 2= IN/OUT. This is ignored in oci8.
592	@param [$maxLen] Holds an maximum length of the variable.
593	@param [$type] The data type of $var. Legal values depend on driver.
594
595	See mssql_bind documentation at php.net.
596	*/
597	function Parameter(&$stmt, &$var, $name, $isOutput=false, $maxLen=4000, $type=false)
598	{
599	if (!$this->_has_mssql_init) {
600	ADOConnection::outp( "Parameter: mssql_bind only available since PHP 4.1.0");
601	return false;
602	}
603
604	$isNull = is_null($var); // php 4.0.4 and above...
605
606	if ($type === false)
607	switch(gettype($var)) {
608	default:
609	case 'string': $type = SQLCHAR; break;
610	case 'double': $type = SQLFLT8; break;
611	case 'integer': $type = SQLINT4; break;
612	case 'boolean': $type = SQLINT1; break; # SQLBIT not supported in 4.1.0
613	}
614
615	if ($this->debug) {
616	$prefix = ($isOutput) ? 'Out' : 'In';
617	$ztype = (empty($type)) ? 'false' : $type;
618	ADOConnection::outp( "{$prefix}Parameter(\$stmt, \$php_var='$var', \$name='$name', \$maxLen=$maxLen, \$type=$ztype);");
619	}
620	/*
621	See http://phplens.com/lens/lensforum/msgs.php?id=7231
622
623	RETVAL is HARD CODED into php_mssql extension:
624	The return value (a long integer value) is treated like a special OUTPUT parameter,
625	called "RETVAL" (without the @). See the example at mssql_execute to
626	see how it works. - type: one of this new supported PHP constants.
627	SQLTEXT, SQLVARCHAR,SQLCHAR, SQLINT1,SQLINT2, SQLINT4, SQLBIT,SQLFLT8
628	*/
629	if ($name !== 'RETVAL') $name = '@'.$name;
630	return mssql_bind($stmt[1], $name, $var, $type, $isOutput, $isNull, $maxLen);
631	}
632
633	/*
634	Unfortunately, it appears that mssql cannot handle varbinary > 255 chars
635	So all your blobs must be of type "image".
636
637	Remember to set in php.ini the following...
638
639	; Valid range 0 - 2147483647. Default = 4096.
640	mssql.textlimit = 0 ; zero to pass through
641
642	; Valid range 0 - 2147483647. Default = 4096.
643	mssql.textsize = 0 ; zero to pass through
644	*/
645	function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
646	{
647
648	if (strtoupper($blobtype) == 'CLOB') {
649	$sql = "UPDATE $table SET $column='" . $val . "' WHERE $where";
650	return $this->Execute($sql) != false;
651	}
652	$sql = "UPDATE $table SET $column=0x".bin2hex($val)." WHERE $where";
653	return $this->Execute($sql) != false;
654	}
655
656	// returns query ID if successful, otherwise false
657	function _query($sql,$inputarr)
658	{
659	$this->_errorMsg = false;
660	if (is_array($inputarr)) {
661
662	# bind input params with sp_executesql:
663	# see http://www.quest-pipelines.com/newsletter-v3/0402_F.htm
664	# works only with sql server 7 and newer
665	if (!is_array($sql)) $sql = $this->Prepare($sql);
666	$params = '';
667	$decl = '';
668	$i = 0;
669	foreach($inputarr as $v) {
670	if ($decl) {
671	$decl .= ', ';
672	$params .= ', ';
673	}
674	if (is_string($v)) {
675	$len = strlen($v);
676	if ($len == 0) $len = 1;
677
678	if ($len > 4000 ) {
679	// NVARCHAR is max 4000 chars. Let's use NTEXT
680	$decl .= "@P$i NTEXT";
681	} else {
682	$decl .= "@P$i NVARCHAR($len)";
683	}
684
685	$params .= "@P$i=N". (strncmp($v,"'",1)==0? $v : $this->qstr($v));
686	} else if (is_integer($v)) {
687	$decl .= "@P$i INT";
688	$params .= "@P$i=".$v;
689	} else if (is_float($v)) {
690	$decl .= "@P$i FLOAT";
691	$params .= "@P$i=".$v;
692	} else if (is_bool($v)) {
693	$decl .= "@P$i INT"; # Used INT just in case BIT in not supported on the user's MSSQL version. It will cast appropriately.
694	$params .= "@P$i=".(($v)?'1':'0'); # True == 1 in MSSQL BIT fields and acceptable for storing logical true in an int field
695	} else {
696	$decl .= "@P$i CHAR"; # Used char because a type is required even when the value is to be NULL.
697	$params .= "@P$i=NULL";
698	}
699	$i += 1;
700	}
701	$decl = $this->qstr($decl);
702	if ($this->debug) ADOConnection::outp("<font size=-1>sp_executesql N{$sql[1]},N$decl,$params</font>");
703	$rez = mssql_query("sp_executesql N{$sql[1]},N$decl,$params", $this->_connectionID);
704
705	} else if (is_array($sql)) {
706	# PrepareSP()
707	$rez = mssql_execute($sql[1]);
708
709	} else {
710	$rez = mssql_query($sql,$this->_connectionID);
711	}
712	return $rez;
713	}
714
715	// returns true or false
716	function _close()
717	{
718	if ($this->transCnt) $this->RollbackTrans();
719	$rez = @mssql_close($this->_connectionID);
720	$this->_connectionID = false;
721	return $rez;
722	}
723
724	// mssql uses a default date like Dec 30 2000 12:00AM
725	function UnixDate($v)
726	{
727	return ADORecordSet_array_mssql::UnixDate($v);
728	}
729
730	function UnixTimeStamp($v)
731	{
732	return ADORecordSet_array_mssql::UnixTimeStamp($v);
733	}
734	}
735
736	/*--------------------------------------------------------------------------------------
737	Class Name: Recordset
738	--------------------------------------------------------------------------------------*/
739
740	class ADORecordset_mssql extends ADORecordSet {
741
742	var $databaseType = "mssql";
743	var $canSeek = true;
744	var $hasFetchAssoc; // see http://phplens.com/lens/lensforum/msgs.php?id=6083
745	// _mths works only in non-localised system
746
747	function ADORecordset_mssql($id,$mode=false)
748	{
749	// freedts check...
750	$this->hasFetchAssoc = function_exists('mssql_fetch_assoc');
751
752	if ($mode === false) {
753	global $ADODB_FETCH_MODE;
754	$mode = $ADODB_FETCH_MODE;
755
756	}
757	$this->fetchMode = $mode;
758	return $this->ADORecordSet($id,$mode);
759	}
760
761
762	function _initrs()
763	{
764	GLOBAL $ADODB_COUNTRECS;
765	$this->_numOfRows = ($ADODB_COUNTRECS)? @mssql_num_rows($this->_queryID):-1;
766	$this->_numOfFields = @mssql_num_fields($this->_queryID);
767	}
768
769
770	//Contributed by "Sven Axelsson" <sven.axelsson@bokochwebb.se>
771	// get next resultset - requires PHP 4.0.5 or later
772	function NextRecordSet()
773	{
774	if (!mssql_next_result($this->_queryID)) return false;
775	$this->_inited = false;
776	$this->bind = false;
777	$this->_currentRow = -1;
778	$this->Init();
779	return true;
780	}
781
782	/* Use associative array to get fields array */
783	function Fields($colname)
784	{
785	if ($this->fetchMode != ADODB_FETCH_NUM) return $this->fields[$colname];
786	if (!$this->bind) {
787	$this->bind = array();
788	for ($i=0; $i < $this->_numOfFields; $i++) {
789	$o = $this->FetchField($i);
790	$this->bind[strtoupper($o->name)] = $i;
791	}
792	}
793
794	return $this->fields[$this->bind[strtoupper($colname)]];
795	}
796
797	/* Returns: an object containing field information.
798	Get column information in the Recordset object. fetchField() can be used in order to obtain information about
799	fields in a certain query result. If the field offset isn't specified, the next field that wasn't yet retrieved by
800	fetchField() is retrieved. */
801
802	function &FetchField($fieldOffset = -1)
803	{
804	if ($fieldOffset != -1) {
805	$f = @mssql_fetch_field($this->_queryID, $fieldOffset);
806	}
807	else if ($fieldOffset == -1) { /* The $fieldOffset argument is not provided thus its -1 */
808	$f = @mssql_fetch_field($this->_queryID);
809	}
810	$false = false;
811	if (empty($f)) return $false;
812	return $f;
813	}
814
815	function _seek($row)
816	{
817	return @mssql_data_seek($this->_queryID, $row);
818	}
819
820	// speedup
821	function MoveNext()
822	{
823	if ($this->EOF) return false;
824
825	$this->_currentRow++;
826
827	if ($this->fetchMode & ADODB_FETCH_ASSOC) {
828	if ($this->fetchMode & ADODB_FETCH_NUM) {
829	//ADODB_FETCH_BOTH mode
830	$this->fields = @mssql_fetch_array($this->_queryID);
831	}
832	else {
833	if ($this->hasFetchAssoc) {// only for PHP 4.2.0 or later
834	$this->fields = @mssql_fetch_assoc($this->_queryID);
835	} else {
836	$flds = @mssql_fetch_array($this->_queryID);
837	if (is_array($flds)) {
838	$fassoc = array();
839	foreach($flds as $k => $v) {
840	if (is_numeric($k)) continue;
841	$fassoc[$k] = $v;
842	}
843	$this->fields = $fassoc;
844	} else
845	$this->fields = false;
846	}
847	}
848
849	if (is_array($this->fields)) {
850	if (ADODB_ASSOC_CASE == 0) {
851	foreach($this->fields as $k=>$v) {
852	$this->fields[strtolower($k)] = $v;
853	}
854	} else if (ADODB_ASSOC_CASE == 1) {
855	foreach($this->fields as $k=>$v) {
856	$this->fields[strtoupper($k)] = $v;
857	}
858	}
859	}
860	} else {
861	$this->fields = @mssql_fetch_row($this->_queryID);
862	}
863	if ($this->fields) return true;
864	$this->EOF = true;
865
866	return false;
867	}
868
869
870	// INSERT UPDATE DELETE returns false even if no error occurs in 4.0.4
871	// also the date format has been changed from YYYY-mm-dd to dd MMM YYYY in 4.0.4. Idiot!
872	function _fetch($ignore_fields=false)
873	{
874	if ($this->fetchMode & ADODB_FETCH_ASSOC) {
875	if ($this->fetchMode & ADODB_FETCH_NUM) {
876	//ADODB_FETCH_BOTH mode
877	$this->fields = @mssql_fetch_array($this->_queryID);
878	} else {
879	if ($this->hasFetchAssoc) // only for PHP 4.2.0 or later
880	$this->fields = @mssql_fetch_assoc($this->_queryID);
881	else {
882	$this->fields = @mssql_fetch_array($this->_queryID);
883	if (@is_array($$this->fields)) {
884	$fassoc = array();
885	foreach($$this->fields as $k => $v) {
886	if (is_integer($k)) continue;
887	$fassoc[$k] = $v;
888	}
889	$this->fields = $fassoc;
890	}
891	}
892	}
893
894	if (!$this->fields) {
895	} else if (ADODB_ASSOC_CASE == 0) {
896	foreach($this->fields as $k=>$v) {
897	$this->fields[strtolower($k)] = $v;
898	}
899	} else if (ADODB_ASSOC_CASE == 1) {
900	foreach($this->fields as $k=>$v) {
901	$this->fields[strtoupper($k)] = $v;
902	}
903	}
904	} else {
905	$this->fields = @mssql_fetch_row($this->_queryID);
906	}
907	return $this->fields;
908	}
909
910	/* close() only needs to be called if you are worried about using too much memory while your script
911	is running. All associated result memory for the specified result identifier will automatically be freed. */
912
913	function _close()
914	{
915	$rez = mssql_free_result($this->_queryID);
916	$this->_queryID = false;
917	return $rez;
918	}
919	// mssql uses a default date like Dec 30 2000 12:00AM
920	function UnixDate($v)
921	{
922	return ADORecordSet_array_mssql::UnixDate($v);
923	}
924
925	function UnixTimeStamp($v)
926	{
927	return ADORecordSet_array_mssql::UnixTimeStamp($v);
928	}
929
930	}
931
932
933	class ADORecordSet_array_mssql extends ADORecordSet_array {
934	function ADORecordSet_array_mssql($id=-1,$mode=false)
935	{
936	$this->ADORecordSet_array($id,$mode);
937	}
938
939	// mssql uses a default date like Dec 30 2000 12:00AM
940	function UnixDate($v)
941	{
942
943	if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixDate($v);
944
945	global $ADODB_mssql_mths,$ADODB_mssql_date_order;
946
947	//Dec 30 2000 12:00AM
948	if ($ADODB_mssql_date_order == 'dmy') {
949	if (!preg_match( "\|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4})\|" ,$v, $rr)) {
950	return parent::UnixDate($v);
951	}
952	if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
953
954	$theday = $rr[1];
955	$themth = substr(strtoupper($rr[2]),0,3);
956	} else {
957	if (!preg_match( "\|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4})\|" ,$v, $rr)) {
958	return parent::UnixDate($v);
959	}
960	if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
961
962	$theday = $rr[2];
963	$themth = substr(strtoupper($rr[1]),0,3);
964	}
965	$themth = $ADODB_mssql_mths[$themth];
966	if ($themth <= 0) return false;
967	// h-m-s-MM-DD-YY
968	return mktime(0,0,0,$themth,$theday,$rr[3]);
969	}
970
971	function UnixTimeStamp($v)
972	{
973
974	if (is_numeric(substr($v,0,1)) && ADODB_PHPVER >= 0x4200) return parent::UnixTimeStamp($v);
975
976	global $ADODB_mssql_mths,$ADODB_mssql_date_order;
977
978	//Dec 30 2000 12:00AM
979	if ($ADODB_mssql_date_order == 'dmy') {
980	if (!preg_match( "\|^([0-9]{1,2})[-/\. ]+([A-Za-z]{3})[-/\. ]+([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})\|"
981	,$v, $rr)) return parent::UnixTimeStamp($v);
982	if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
983
984	$theday = $rr[1];
985	$themth = substr(strtoupper($rr[2]),0,3);
986	} else {
987	if (!preg_match( "\|^([A-Za-z]{3})[-/\. ]+([0-9]{1,2})[-/\. ]+([0-9]{4}) +([0-9]{1,2}):([0-9]{1,2}) *([apAP]{0,1})\|"
988	,$v, $rr)) return parent::UnixTimeStamp($v);
989	if ($rr[3] <= TIMESTAMP_FIRST_YEAR) return 0;
990
991	$theday = $rr[2];
992	$themth = substr(strtoupper($rr[1]),0,3);
993	}
994
995	$themth = $ADODB_mssql_mths[$themth];
996	if ($themth <= 0) return false;
997
998	switch (strtoupper($rr[6])) {
999	case 'P':
1000	if ($rr[4]<12) $rr[4] += 12;
1001	break;
1002	case 'A':
1003	if ($rr[4]==12) $rr[4] = 0;
1004	break;
1005	default:
1006	break;
1007	}
1008	// h-m-s-MM-DD-YY
1009	return mktime($rr[4],$rr[5],0,$themth,$theday,$rr[3]);
1010	}
1011	}
1012
1013	/*
1014	Code Example 1:
1015
1016	select object_name(constid) as constraint_name,
1017	object_name(fkeyid) as table_name,
1018	col_name(fkeyid, fkey) as column_name,
1019	object_name(rkeyid) as referenced_table_name,
1020	col_name(rkeyid, rkey) as referenced_column_name
1021	from sysforeignkeys
1022	where object_name(fkeyid) = x
1023	order by constraint_name, table_name, referenced_table_name, keyno
1024
1025	Code Example 2:
1026	select constraint_name,
1027	column_name,
1028	ordinal_position
1029	from information_schema.key_column_usage
1030	where constraint_catalog = db_name()
1031	and table_name = x
1032	order by constraint_name, ordinal_position
1033
1034	http://www.databasejournal.com/scripts/article.php/1440551
1035	*/
1036
1037	?>

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

Download in other formats: