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