1: <?php
  2:     /**
  3:      * Database Adapter for Microsoft SQL Server 2005/2008
  4:      * Utilizes the native Microsoft SQL Server extension php_sqlsrv.dll version 1.1 (win)
  5:      * http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx
  6:          *
  7:          * Requirements:
  8:          * - Windows XP SP3/2003 SP1/Vista/2008/7 or later
  9:          * - Microsoft SQL Server 2008 Native Client
 10:          * - PHP 5.2.4 or later
 11:          * - SQL Server Driver for PHP 1.1
 12:      *
 13:      * @package DatabaseAdapters
 14:      */
 15: 
 16:     /**
 17:      * Database Adapter for Microsoft SQL Server 2005/2008
 18:      *
 19:      *
 20:      * LimitInfo and Query utilizes an interal SQL tag QCUBED_OFFSET<#>, where # represents
 21:      * the number of rows to offset for "Limit"-based queries.  The QCUBED_OFFSET is added
 22:      * internally by SqlLimitVariablePrefix(), and it is handled (and removed from the query)
 23:      * by Query().  In error messages and DB profiling, the QCUBED_OFFSET<#> tag *WILL* appear
 24:      * (if applicable).  The framework will handle this gracefully, but obviously, if you try
 25:      * and cut and paste SQL code that contains QCUBED_OFFSET<#> into QueryAnalyzer, the query
 26:      * will fail, so just be aware of that.  If you want to do something like test queries
 27:      * with QueryAnalyzer, just remember to manually remove any QCUBED_OFFSET<#> information.
 28:      *
 29:      * ENHANCEMENTS over MSSQL based-driver:
 30:      * - varchar limit of 254 characters no longer exists, uses full varchar(8000)
 31:          * - supports different date formats
 32:          * - faster
 33:      *
 34:      * DATE VALUES:
 35:      * If the date format of your database is different from the standard english format of
 36:      * 'YYYY-MM-DD hhhh:mm:ss' you have to declare it by setting the option 'dateformat'
 37:      * in the database connection array in the configuration.inc.php.
 38:      * For instance if your database wants german date settings you would set it to
 39:      * 'DD.MM.YYYY hhhh:mm:ss'.
 40:      *
 41:      * LIMITATIONS:
 42:      * - GROUP BY
 43:      * Using GROUP BY clause is broken because:
 44:      * a) Current QQuery implementation is MySQL specific. Standard SQL wants every column
 45:      * in the SELECT statement, that is not an aggregate statement, in the GROUP BY clause
 46:      * and this behaviour is currently not supported. This limitation is valid too for the
 47:      * MSSQL based adapter.
 48:      *
 49:      * - Datatypes currently not fully supported
 50:      * sql_variant, varbinary(max), geometry, geography, hierarchyid, uniqueidentifier
 51:      *
 52:      *
 53:      * @copyright   Copyright (C) 2009,2010 Andreas Krohn
 54:      * @author  Andreas Krohn <akrohn.pronet@googlemail.com>, based on code by Mike Ho
 55:      * @license http://www.opensource.org/licenses/mit-license.php
 56:      * @package DatabaseAdapters
 57:      */
 58:     class QSqlServer2005Database extends QDatabaseBase {
 59:         const Adapter = 'Microsoft SQL Server 2005/2008 Database Adapter';
 60: 
 61:         protected $objSqlSrvConn;
 62:         protected $objMostRecentStatement; // is always the last executed statement
 63: 
 64:         protected $strEscapeIdentifierBegin = '[';
 65:         protected $strEscapeIdentifierEnd = ']';
 66:         protected $blnOnlyFullGroupBy = true;
 67: 
 68:         // Default query options array
 69:         protected $mixedOptionsArray = array(
 70:         );
 71: 
 72:         /**
 73:          * Returns extended error information about the last sqlsrv operation performed
 74:          *
 75:          * @param string strError Returns Errormessage By Reference
 76:          * @param string strLastErrorCode Returns last ErrorCode By Reference
 77:          * @return string Errorinformation
 78:          */
 79:         private function GetErrorInformation(&$strError, &$strLastErrorCode) {
 80:             $strError = '';
 81:             $strLastErrorCode = '';
 82: 
 83:             // Determine only the errorinformation
 84:             $objErrors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
 85:             if(!is_null($objErrors)) {
 86:                 // Get all errors
 87:                 foreach($objErrors as $strErrorArray) {
 88:                     $strError .= ' SQLSTATE: '.$strErrorArray['SQLSTATE'].', Code: '.$strErrorArray['code'].', Message: '.$strErrorArray['message'];
 89:                     $strLastErrorCode = $strErrorArray['code'];
 90:                 }
 91:             }
 92:         }
 93: 
 94:         /**
 95:          * Properly escapes $mixData to be used as a SQL query parameter.
 96:          * If IncludeEquality is set (usually not), then include an equality operator.
 97:          * So for most data, it would just be "=".  But, for example,
 98:          * if $mixData is NULL, then most RDBMS's require the use of "IS".
 99:          *
100:          * @param mixed $mixData
101:          * @param boolean $blnIncludeEquality whether or not to include an equality operator
102:          * @param boolean $blnReverseEquality whether the included equality operator should be a "NOT EQUAL", e.g. "!="
103:          * @return string the properly formatted SQL variable
104:          */
105:         public function SqlVariable($mixData, $blnIncludeEquality = false, $blnReverseEquality = false) {
106:             // Are we SqlVariabling a BOOLEAN value?
107:             if (is_bool($mixData)) {
108:                 // Yes
109:                 if ($blnIncludeEquality) {
110:                     // We must include the inequality
111: 
112:                     if ($blnReverseEquality) {
113:                         // Do a "Reverse Equality"
114: 
115:                         // Check against NULL, True then False
116:                         if (is_null($mixData))
117:                             return 'IS NOT NULL';
118:                         else if ($mixData)
119:                             return '= 0';
120:                         else
121:                             return '!= 0';
122:                     } else {
123:                         // Check against NULL, True then False
124:                         if (is_null($mixData))
125:                             return 'IS NULL';
126:                         else if ($mixData)
127:                             return '!= 0';
128:                         else
129:                             return '= 0';
130:                     }
131:                 } else {
132:                     // Check against NULL, True then False
133:                     if (is_null($mixData))
134:                         return 'NULL';
135:                     else if ($mixData)
136:                         return '1';
137:                     else
138:                         return '0';
139:                 }
140:             }
141: 
142:             // Check for Equality Inclusion
143:             if ($blnIncludeEquality) {
144:                 if ($blnReverseEquality) {
145:                     if (is_null($mixData))
146:                         $strToReturn = 'IS NOT ';
147:                     else
148:                         $strToReturn = '!= ';
149:                 } else {
150:                     if (is_null($mixData))
151:                         $strToReturn = 'IS ';
152:                     else
153:                         $strToReturn = '= ';
154:                 }
155:             } else
156:                 $strToReturn = '';
157: 
158:             // Check for NULL Value
159:             if (is_null($mixData))
160:                 return $strToReturn . 'NULL';
161: 
162:             // Check for NUMERIC Value
163:             if (is_integer($mixData) || is_float($mixData))
164:                 return $strToReturn . sprintf('%s', $mixData);
165: 
166:             // Check for DATE Value and use the desired db-date-format
167:             if ($mixData instanceof QDateTime) {
168:                 return $strToReturn . sprintf("'%s'", $mixData->qFormat($this->DateFormat));
169:             }
170: 
171:             // Assume it's some kind of string value
172:             return $strToReturn . sprintf("'%s'", str_replace("'", "''", $mixData));
173:         }
174: 
175:         public function SqlLimitVariablePrefix($strLimitInfo) {
176:             // Setup limit suffix (if applicable) via a TOP clause
177:             // Add QCUBED_OFFSET tag if applicable
178: 
179:             if (strlen($strLimitInfo)) {
180:                 if (strpos($strLimitInfo, ';') !== false)
181:                     throw new Exception('Invalid Semicolon in LIMIT Info');
182:                 if (strpos($strLimitInfo, '`') !== false)
183:                     throw new Exception('Invalid Backtick in LIMIT Info');
184: 
185:                 // First figure out if we HAVE an offset
186:                 $strArray = explode(',', $strLimitInfo);
187: 
188:                 if (count($strArray) == 2) {
189:                     // Yep -- there's an offset
190:                     return sprintf(
191:                         'TOP %s QCUBED_OFFSET<%s>',
192:                         ($strArray[0] + $strArray[1]),
193:                         $strArray[0]);
194:                 } else if (count($strArray) == 1) {
195:                     return 'TOP ' . $strArray[0];
196:                 } else {
197:                     throw new QSqlServer2005DatabaseException('Invalid Limit Info: ' . $strLimitInfo, 0, null);
198:                 }
199:             }
200: 
201:             return null;
202:         }
203: 
204:         public function SqlLimitVariableSuffix($strLimitInfo) {
205:             return null;
206:         }
207: 
208:         public function SqlSortByVariable($strSortByInfo) {
209:             // Setup sorting information (if applicable) via a ORDER BY clause
210:             if (strlen($strSortByInfo)) {
211:                 if (strpos($strSortByInfo, ';') !== false)
212:                     throw new Exception('Invalid Semicolon in ORDER BY Info');
213:                 if (strpos($strSortByInfo, '`') !== false)
214:                     throw new Exception('Invalid Backtick in ORDER BY Info');
215: 
216:                 return "ORDER BY $strSortByInfo";
217:             }
218: 
219:             return null;
220:         }
221: 
222:         public function Connect() {
223:             // Lookup Adapter-Specific Connection Properties
224:             $strServer = $this->Server;
225:             $strName = $this->Database;
226:             $strUsername = $this->Username;
227:             $strPassword = $this->Password;
228:             $strPort = $this->Port;
229: 
230:             if ($strPort) {
231:                 // Windows Servers
232:                 if (array_key_exists('OS', $_SERVER) && stristr($_SERVER['OS'], 'Win') !== false)
233:                     $strServer .= ',' . $strPort;
234:                 // All Other Servers
235:                 else
236:                     $strServer .= ':' . $strPort;
237:             }
238: 
239:             // define the characterset for the sqlsrv driver
240:             // special handling for utf-8 data
241:             $strCharacterset = (QApplication::$EncodingType == 'UTF-8') ? 'UTF-8' : 'SQLSRV_ENC_CHAR';
242: 
243:             // Connect to the Database Server
244: 
245:             // Disable warnings as errors behavior
246:             sqlsrv_configure("WarningsReturnAsErrors", 0);
247: 
248:             // Set connection parameters
249:             $strConnectionInfoArray = array(
250:                 'UID'=>$strUsername
251:                 , 'PWD'=>$strPassword
252:                 , 'Database'=>$strName
253:                 , 'CharacterSet'=>$strCharacterset
254:             );
255: 
256:             // Connect using SQL Server Authentication
257:             $this->objSqlSrvConn = sqlsrv_connect($strServer, $strConnectionInfoArray);
258:             if($this->objSqlSrvConn === false) {
259:                 // Determine the errorinformation
260:                 $this->GetErrorInformation($strErrorinformation, $strErrorCode);
261:                 $objException = new QSqlServer2005DatabaseException('Unable to connect: ' . $strErrorinformation, $strErrorCode, null);
262:                 $objException->IncrementOffset();
263:                 throw $objException;
264:             }
265: 
266:             // Update Connected Flag
267:             $this->blnConnectedFlag = true;
268:         }
269: 
270:         public function __get($strName) {
271:             switch ($strName) {
272:                 case 'AffectedRows':
273:                     return sqlsrv_rows_affected($this->objMostRecentStatement);
274:                 default:
275:                     try {
276:                         return parent::__get($strName);
277:                     } catch (QCallerException $objExc) {
278:                         $objExc->IncrementOffset();
279:                         throw $objExc;
280:                     }
281:             }
282:         }
283: 
284:         protected function ExecuteQuery($strQuery) {
285:             // First, check for QCUBED_OFFSET<#> for LIMIT INFO Offseting
286:             if ( ($intPosition = strpos($strQuery, 'QCUBED_OFFSET<')) !== false) {
287:                 $intEndPosition = strpos($strQuery, '>', $intPosition);
288:                 if ($intEndPosition === false)
289:                     throw new QSqlServer2005DatabaseException('Invalid QCUBED_OFFSET', 0, $strQuery);
290:                 $intOffset = QType::Cast(substr($strQuery,
291:                     $intPosition + 13 /* len of QCUBED_OFFSET< */,
292:                     $intEndPosition - $intPosition - 13), QType::Integer);
293:                 $strQuery = substr($strQuery, 0, $intPosition) . substr($strQuery, $intEndPosition + 1);
294:             } else
295:                 $intOffset = 0;
296: 
297:             // Perform the Query
298:             $objResult = sqlsrv_query($this->objSqlSrvConn, $strQuery, NULL, $this->mixedOptionsArray);
299:             if ($objResult === false) {
300:                 // Determine the errorinformation
301:                 $this->GetErrorInformation($strErrorinformation, $strErrorCode);
302:                 throw new QSqlServer2005DatabaseException($strErrorinformation, $strErrorCode, $strQuery);
303:             }
304: 
305:             // Remember most recent statement
306:             $this->objMostRecentStatement = $objResult;
307: 
308:             // Return the Result
309:             $objSqlServerDatabaseResult = new QSqlServer2005DatabaseResult($objResult, $this);
310: 
311:             // Perform Offsetting (if applicable)
312:             for ($intIndex = 0; $intIndex < $intOffset; $intIndex++) {
313:                 $objRow = $objSqlServerDatabaseResult->FetchRow();
314:                 if (!$objRow)
315:                     return $objSqlServerDatabaseResult;
316:             }
317: 
318:             return $objSqlServerDatabaseResult;
319:         }
320: 
321:         protected function ExecuteNonQuery($strNonQuery) {
322:             // Perform the NonQuery
323:             $objResult = sqlsrv_query($this->objSqlSrvConn, $strNonQuery, NULL, $this->mixedOptionsArray);
324:             if ($objResult === false) {
325:                 // Determine the errorinformation
326:                 $this->GetErrorInformation($strErrorinformation, $strErrorCode);
327:                 throw new QSqlServer2005DatabaseException($strErrorinformation, $strErrorCode, $strNonQuery);
328:             }
329: 
330:             // Remember most recent statement
331:             $this->objMostRecentStatement = $objResult;
332:         }
333: 
334:         public function GetTables() {
335:             $objResult = $this->Query("
336:                 SELECT  obj.name
337:                 FROM    sys.objects obj
338:                 WHERE   obj.type = 'U' AND
339:                     obj.name NOT LIKE N'#%' AND
340:                     obj.is_ms_shipped = 0
341:                 ORDER BY obj.name ASC
342:             ");
343: 
344:             $strToReturn = array();
345:             while ($strRowArray = $objResult->FetchRow()) {
346:                 array_push($strToReturn, $strRowArray[0]);
347:             }
348:             return $strToReturn;
349:         }
350: 
351:         public function GetTableForId($intTableId) {
352:             $intTableId = $this->SqlVariable($intTableId);
353:             $strQuery = sprintf('
354:                 SELECT  obj.name
355:                 FROM    sys.objects obj
356:                 WHERE   obj.object_id = %s
357:             ', $intTableId);
358: 
359:             $objResult = $this->Query($strQuery);
360:             $objRow = $objResult->FetchRow();
361:             return $objRow[0];
362:         }
363: 
364:         public function GetFieldsForTable($strTableName) {
365:             $strTableName = $this->SqlVariable($strTableName);
366: 
367:             $strQuery = sprintf("
368:                 SELECT  col.*
369:                     , typ.name AS data_type
370:                     , typ.scale AS scale
371:                     , obj.name AS table_name
372:                     , CAST(ex.value AS VARCHAR(8000)) AS comment
373:                 FROM sys.columns col
374:                 JOIN sys.objects obj ON obj.object_id = col.object_id 
375:                 JOIN sys.types typ ON typ.system_type_id = col.system_type_id AND typ.user_type_id = col.user_type_id
376:                 LEFT JOIN sys.extended_properties ex ON ex.major_id = col.object_id AND ex.minor_id = col.column_id AND ex.name = 'MS_Description' AND ex.class = 1 
377:                 WHERE   obj.name = %s
378:                 ORDER BY col.column_id ASC
379:             ", $strTableName);
380: 
381:             $objResult = $this->Query($strQuery);
382: 
383:             $objFields = array();
384: 
385:             while ($objRow = $objResult->GetNextRow()) {
386:                 array_push($objFields, new QSqlServer2005DatabaseField($objRow, $this));
387:             }
388: 
389:             return $objFields;
390:         }
391: 
392:         public function InsertId($strTableName = null, $strColumnName = null) {
393:             $strQuery = 'SELECT SCOPE_IDENTITY();';
394:             $objResult = $this->Query($strQuery);
395:             $objRow = $objResult->FetchRow();
396:             return $objRow[0];
397:         }
398: 
399:         public function Close() {
400:             sqlsrv_close($this->objSqlSrvConn);
401: 
402:             // Update Connected Flag
403:             $this->blnConnectedFlag = false;
404:         }
405: 
406:         /**
407:          * Begin transaction
408:          */
409:         protected function ExecuteTransactionBegin() {
410:             if (sqlsrv_begin_transaction($this->objSqlSrvConn) === false) {
411:                 // Determine the errorinformation
412:                 $this->GetErrorInformation($strErrorinformation, $strErrorCode);
413:                 throw new QSqlServer2005DatabaseException($strErrorinformation, $strErrorCode, $strNonQuery);
414:             }
415:         }
416: 
417:         /**
418:          * Commit transaction
419:          */
420:         protected function ExecuteTransactionCommit() {
421:             if (sqlsrv_commit($this->objSqlSrvConn) === false) {
422:                 // Determine the errorinformation
423:                 $this->GetErrorInformation($strErrorinformation, $strErrorCode);
424:                 throw new QSqlServer2005DatabaseException($strErrorinformation, $strErrorCode, $strNonQuery);
425:             }
426:         }
427: 
428:         /**
429:          * Rollback transaction
430:          */
431:         protected function ExecuteTransactionRollBack() {
432:             if (sqlsrv_rollback($this->objSqlSrvConn) === false) {
433:                 // Determine the errorinformation
434:                 $this->GetErrorInformation($strErrorinformation, $strErrorCode);
435:                 throw new QSqlServer2005DatabaseException($strErrorinformation, $strErrorCode, $strNonQuery);
436:             }
437:         }
438: 
439:         public function GetIndexesForTable($strTableName) {
440:             $objIndexArray = array();
441: 
442:             // Use sp_helpindex to pull the indexes
443:             $objResult = $this->Query(sprintf("EXEC sp_helpindex %s", $this->SqlVariable($strTableName)));
444:             while ($objRow = $objResult->GetNextRow()) {
445:                 $strIndexDescription = $objRow->GetColumn('index_description');
446:                 $strKeyName = $objRow->GetColumn('index_name');
447:                 $blnPrimaryKey = (strpos($strIndexDescription, 'primary key') !== false);
448:                 $blnUnique = (strpos($strIndexDescription, 'unique') !== false);
449:                 $strColumnNameArray = explode(', ', $objRow->GetColumn('index_keys'));
450: 
451:                 $objIndex = new QDatabaseIndex($strKeyName, $blnPrimaryKey, $blnUnique, $strColumnNameArray);
452:                 array_push($objIndexArray, $objIndex);
453:             }
454: 
455:             return $objIndexArray;
456:         }
457: 
458:         public function GetForeignKeysForTable($strTableName) {
459:             $objForeignKeyArray = array();
460: 
461:             // Use Query to pull the FKs
462:             $strQuery = sprintf("
463:                 SELECT  fk_table = FK.TABLE_NAME
464:                   , fk_column = CU.COLUMN_NAME
465:                   , pk_table = PK.TABLE_NAME
466:                   , pk_column = PT.COLUMN_NAME
467:                   , constraint_name = C.CONSTRAINT_NAME
468:                 FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
469:                 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
470:                 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
471:                 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
472:                 INNER JOIN (SELECT  i1.TABLE_NAME
473:                           , i2.COLUMN_NAME
474:                     FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
475:                     INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
476:                     WHERE   i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME = PK.TABLE_NAME
477:                 WHERE   FK.TABLE_NAME = %s
478:                 ORDER BY C.constraint_name
479:             ",
480:             $this->SqlVariable($strTableName));
481:             $objResult = $this->Query($strQuery);
482: 
483:             $strKeyName = '';
484:             while ($objRow = $objResult->GetNextRow()) {
485:                 if ($strKeyName != $objRow->GetColumn('constraint_name')) {
486:                     if ($strKeyName) {
487:                         $objForeignKey = new QDatabaseForeignKey(
488:                             $strKeyName,
489:                             $strColumnNameArray,
490:                             $strReferenceTableName,
491:                             $strReferenceColumnNameArray);
492:                         array_push($objForeignKeyArray, $objForeignKey);
493:                     }
494: 
495:                     $strKeyName = $objRow->GetColumn('constraint_name');
496:                     $strReferenceTableName = $objRow->GetColumn('pk_table');
497:                     $strColumnNameArray = array();
498:                     $strReferenceColumnNameArray = array();
499:                 }
500: 
501:                 if (!array_search($objRow->GetColumn('fk_column'), $strColumnNameArray)) {
502:                     array_push($strColumnNameArray, $objRow->GetColumn('fk_column'));
503:                 }
504: 
505:                 if (!array_search($objRow->GetColumn('pk_column'), $strReferenceColumnNameArray)) {
506:                     array_push($strReferenceColumnNameArray, $objRow->GetColumn('pk_column'));
507:                 }
508:             }
509: 
510:             if ($strKeyName) {
511:                 $objForeignKey = new QDatabaseForeignKey(
512:                     $strKeyName,
513:                     $strColumnNameArray,
514:                     $strReferenceTableName,
515:                     $strReferenceColumnNameArray);
516:                 array_push($objForeignKeyArray, $objForeignKey);
517:             }
518: 
519:             // Return the Array of Foreign Keys
520:             return $objForeignKeyArray;
521:         }
522:     }
523: 
524:     /**
525:      *
526:      * @package DatabaseAdapters
527:      */
528:     class QSqlServer2005DatabaseException extends QDatabaseExceptionBase {
529:         public function __construct($strMessage, $intNumber, $strQuery) {
530:             parent::__construct(sprintf("SQL Server Error: %s", $strMessage), 2);
531:             $this->intErrorNumber = $intNumber;
532:             $this->strQuery = $strQuery;
533:         }
534:     }
535: 
536:     /**
537:      * Class to handle results sent by database upon querying
538:      * @package DatabaseAdapters
539:      */
540:     class QSqlServer2005DatabaseResult extends QDatabaseResultBase {
541:         protected $objSqlSrvResult;
542:         protected $objDb;
543: 
544:         public function __construct($objResult, QSqlServer2005Database $objDb) {
545:             $this->objSqlSrvResult = $objResult;
546:             $this->objDb = $objDb;
547:         }
548: 
549:         public function FetchArray() {
550:             $strColumnArray = sqlsrv_fetch_array($this->objSqlSrvResult);
551:             if ($strColumnArray === false) {
552:                 // Determine the errorinformation
553:                 $strErrorInformation    = '';
554:                 $strErrorCode       = '';
555:                 $this->GetErrorInformation($strErrorinformation, $strErrorCode);
556:                 throw new QSqlServer2005DatabaseException($strErrorinformation, $strErrorCode, $strNonQuery);
557:             }
558:             return $strColumnArray;
559:         }
560: 
561:         /**
562:          * Function is never used
563:          * @todo
564:          */
565:         public function FetchFields() {
566:             return null;  // Not used
567:         }
568: 
569:         /**
570:          * Function is never used
571:          * @todo
572:          */
573:         public function FetchField() {
574:             return null;  // Not used
575:         }
576: 
577:         public function FetchRow() {
578:             return sqlsrv_fetch_array($this->objSqlSrvResult, SQLSRV_FETCH_NUMERIC);
579:         }
580: 
581:         public function CountRows() {
582:             return sqlsrv_num_rows($this->objSqlSrvResult);
583:         }
584: 
585:         public function CountFields() {
586:             return sqlsrv_num_fields($this->objSqlSrvResult);
587:         }
588: 
589:         public function Close() {
590:             sqlsrv_free_stmt($this->objSqlSrvResult);
591:         }
592: 
593:         public function GetNextRow() {
594:             $strColumnArray = $this->FetchArray();
595: 
596:             if (!is_null($strColumnArray))
597:                 return new QSqlServer2005DatabaseRow($strColumnArray);
598:             else
599:                 return null;
600:         }
601: 
602:         public function GetRows() {
603:             $objDbRowArray = array();
604:             while ($objDbRow = $this->GetNextRow())
605:                 array_push($objDbRowArray, $objDbRow);
606:             return $objDbRowArray;
607:         }
608:     }
609: 
610:     /**
611:      *
612:      * @package DatabaseAdapters
613:      */ 
614:     class QSqlServer2005DatabaseRow extends QDatabaseRowBase {
615:         protected $strColumnArray;
616: 
617:         public function __construct($strColumnArray) {
618:             $this->strColumnArray = $strColumnArray;
619:         }
620: 
621:         /**
622:          * Gets the value of a column from a result row returned by the database
623:          *
624:          * @param string                  $strColumnName Name of te column
625:          * @param null|QDatabaseFieldType $strColumnType Data type
626:          *
627:          * @return mixed
628:          */
629:         public function GetColumn($strColumnName, $strColumnType = null) {
630:             if (!isset($this->strColumnArray[$strColumnName])) {
631:                 return null;
632:             }
633:             $strColumnValue = $this->strColumnArray[$strColumnName];
634:     
635:             switch ($strColumnType) {
636:                 case QDatabaseFieldType::Bit:
637:                     return ($strColumnValue) ? true : false;
638: 
639:                 case QDatabaseFieldType::Blob:
640:                 case QDatabaseFieldType::Char:
641:                 case QDatabaseFieldType::VarChar:
642:                     return QType::Cast($strColumnValue, QType::String);
643: 
644:                 case QDatabaseFieldType::Date:
645:                 case QDatabaseFieldType::DateTime:
646:                 case QDatabaseFieldType::Time:
647:                     return new QDateTime($strColumnValue);
648: 
649:                 case QDatabaseFieldType::Float:
650:                     return QType::Cast($strColumnValue, QType::Float);
651: 
652:                 case QDatabaseFieldType::Integer:
653:                     return QType::Cast($strColumnValue, QType::Integer);
654: 
655:                 default:
656:                     return $strColumnValue;
657:             }
658:         }
659: 
660:         /**
661:          * Tells whether a particular column exists in a returned database row
662:          *
663:          * @param string $strColumnName Name of te column
664:          *
665:          * @return bool
666:          */
667:         public function ColumnExists($strColumnName) {
668:             return array_key_exists($strColumnName, $this->strColumnArray);
669:         }
670:         
671:         public function GetColumnNameArray() {
672:             return $this->strColumnArray;
673:         }
674:     }
675: 
676:     /**
677:      *
678:      * @package DatabaseAdapters
679:      */
680:     class QSqlServer2005DatabaseField extends QDatabaseFieldBase {
681:         public function __construct($mixFieldData, $objDb = null) {
682:             $objDatabaseRow = null;
683:             try {
684:                 $objDatabaseRow = QType::Cast($mixFieldData, 'QSqlServer2005DatabaseRow');
685:             } catch (QInvalidCastException $objExc) {
686:             }
687: 
688:             if ($objDatabaseRow) {
689:                 // Passed in field data is a row from select * from sys.columns for this table
690:                 $intTableId = $objDatabaseRow->GetColumn('object_id');
691:                 $this->strName = $objDatabaseRow->GetColumn('name');
692:                 $this->strOriginalName = $this->strName;
693:                 $this->strTable = $objDatabaseRow->GetColumn('table_name');
694:                 $this->strOriginalTable = $this->strTable;
695:                 $this->strDefault = null; /* Not Supported */
696:                 $this->intMaxLength = $objDatabaseRow->GetColumn('max_length', QDatabaseFieldType::Integer);
697:                 $this->blnNotNull = ($objDatabaseRow->GetColumn('is_nullable')) ? false : true;
698:                 $this->blnIdentity = ($objDatabaseRow->GetColumn('is_identity')) ? true : false;
699:                 $this->strType = $objDatabaseRow->GetColumn('data_type');
700:                 $this->strComment = $objDatabaseRow->GetColumn('comment'); 
701: 
702:                 $intScale = $objDatabaseRow->GetColumn('scale', QDatabaseFieldType::Integer);
703: 
704:                 // PRIMARY KEY / UNIQUE
705:                 $this->blnPrimaryKey = false;
706:                 $this->blnUnique = false;
707:                 // Determine it with the standardized information_schema views
708:                 $objResult = $objDb->Query(sprintf("
709:                     SELECT   kcu.column_name
710:                           , tc.constraint_type
711:                     FROM     information_schema.table_constraints tc
712:                           , information_schema.key_column_usage kcu
713:                     WHERE    tc.table_name = %s
714:                         AND tc.constraint_type IN ('UNIQUE', 'PRIMARY KEY')
715:                         AND kcu.table_name = tc.table_name
716:                         AND kcu.table_schema = tc.table_schema
717:                         AND kcu.constraint_name = tc.constraint_name
718:                 ", $objDb->SqlVariable($this->strTable)));
719:                 // Search for current column and look if it's a primary key or unique
720:                 while ($objRow = $objResult->GetNextRow()) {
721:                     if ($objRow->GetColumn('column_name') == $this->strName) {
722:                         // Primary Key?
723:                         if ($objRow->GetColumn('constraint_type') == 'PRIMARY KEY') {
724:                             $this->blnPrimaryKey = true;
725:                             $this->blnUnique = true; // is also UNIQUE
726:                         }
727:                         // Unique?
728:                         if ($objRow->GetColumn('constraint_type') == 'UNIQUE') {
729:                             $this->blnUnique = true;
730:                         }
731:                     }
732:                 }
733: 
734:                 switch ($this->strType) {
735:                     case 'numeric':
736:                     case 'decimal':
737:                         if ($intScale == 0)
738:                             $this->strType = QDatabaseFieldType::Integer;
739:                         else
740:                             $this->strType = QDatabaseFieldType::Float;
741:                         break;
742:                     case 'bigint':
743:                     case 'int':
744:                     case 'tinyint':
745:                     case 'smallint':
746:                         $this->strType = QDatabaseFieldType::Integer;
747:                         break;
748:                     case 'money':
749:                     case 'real':
750:                     case 'float':
751:                     case 'smallmoney':
752:                         $this->strType = QDatabaseFieldType::Float;
753:                         break;
754:                     case 'bit':
755:                         $this->strType = QDatabaseFieldType::Bit;
756:                         break;
757:                     case 'char':
758:                     case 'nchar':
759:                         $this->strType = QDatabaseFieldType::Char;
760:                         break;
761:                     case 'varchar':
762:                     case 'nvarchar':
763:                     case 'sysname':
764:                     case 'uniqueidentifier':
765:                         // varchar(max) and nvarchar(max) are recognized by intMaxLength = -1 and will be a blob-type
766:                         if ($this->intMaxLength > -1) {
767:                             $this->strType = QDatabaseFieldType::VarChar;
768:                         }
769:                         else {
770:                             $this->strType = QDatabaseFieldType::Blob;
771:                             $this->intMaxLength = null;
772:                         }
773:                         break;
774:                     case 'text':
775:                     case 'ntext':
776:                     case 'binary':
777:                     case 'image':
778:                     case 'varbinary':
779:                     case 'xml':
780:                     case 'udt':
781:                     case 'geometry':
782:                     case 'geography':
783:                     case 'hierarchyid':
784:                     case 'sql_variant':
785:                         $this->strType = QDatabaseFieldType::Blob;
786:                         $this->intMaxLength = null;
787:                         break;
788:                     case 'datetime':
789:                     case 'datetime2':
790:                     case 'smalldatetime':
791:                         $this->strType = QDatabaseFieldType::DateTime;
792:                         break;
793:                     case 'date':
794:                         $this->strType = QDatabaseFieldType::Date;
795:                         break;
796:                     case 'time':
797:                         $this->strType = QDatabaseFieldType::Time;
798:                         break;
799:                     case 'timestamp':
800:                         // System-generated Timestamp values need to be treated as plain text
801:                         $this->strType = QDatabaseFieldType::VarChar;
802:                         $this->blnTimestamp = true;
803:                         break;
804:                     default:
805:                         throw new QSqlServer2005DatabaseException('Unsupported DataType: ' . $this->strType, 0, null);
806:                 }
807:             } else {
808:                 // Passed in fielddata is a sqlsrv_fetch_field field result
809:                 $this->strName = $mixFieldData->name;
810:                 $this->strOriginalName = $mixFieldData->name;
811:                 $this->strTable = $mixFieldData->column_source;
812:                 $this->strOriginalTable = $mixFieldData->column_source;
813:                 $this->intMaxLength = $mixFieldData->max_length;
814:             }
815:         }
816:     }