1: <?php
  2:     /**
  3:      * Database Adapter for Microsoft SQL Server
  4:      * Utilizes the Microsoft SQL Server extension php_mssql.dll (win) or the freetds extension (*nix)
  5:      * 
  6:      * NOTES:
  7:      *
  8:      * LimitInfo and Query utilizes an interal SQL tag QCUBED_OFFSET<#>, where # represents
  9:      * the number of rows to offset for "Limit"-based queries.  The QCUBED_OFFSET is added
 10:      * internally by SqlLimitVariablePrefix(), and it is handled (and removed from the query)
 11:      * by Query().  In error messages and DB profiling, the QCUBED_OFFSET<#> tag *WILL* appear
 12:      * (if applicable).  The framework will handle this gracefully, but obviously, if you try
 13:      * and cut and paste SQL code that contains QCUBED_OFFSET<#> into QueryAnalyzer, the query
 14:      * will fail, so just be aware of that.  If you want to do something like test queries
 15:      * with QueryAnalyzer, just remember to manually remove any QCUBED_OFFSET<#> information.
 16:      *
 17:      * MSSQL will return DATE values according to regional settings.  QCubed expects
 18:      * dates to be of the format YYYY-MM-DD.  Therefore, make sure that your
 19:      * PHP.INI file says:
 20:      *      mssql.datetimeconvert = Off
 21:      *
 22:      * Also, if you are using MSSQL Server 2K from Linux/Unix (using FreeTDS), you may
 23:      * encounter an issue with the following error:
 24:      *      WARNING! ! Some character(s) could not be converted into client's character set...
 25:      * If so, then update your connection settings in the freetds.conf to say the following:
 26:      *      client charset = UTF-8
 27:      * For consistancy, you will also want to update QApplication::$EncodingType to be UTF-8
 28:      * as well.
 29:      * @package DatabaseAdapters
 30:      */
 31:     class QSqlServerDatabase extends QDatabaseBase {
 32:         const Adapter = 'Microsoft SQL Server Database Adapter';
 33: 
 34:         protected $objMsSql;
 35: 
 36:         protected $strEscapeIdentifierBegin = '[';
 37:         protected $strEscapeIdentifierEnd = ']';
 38:         protected $blnOnlyFullGroupBy = true;
 39: 
 40:         /**
 41:          * Properly escapes $mixData to be used as a SQL query parameter.
 42:          * If IncludeEquality is set (usually not), then include an equality operator.
 43:          * So for most data, it would just be "=".  But, for example,
 44:          * if $mixData is NULL, then most RDBMS's require the use of "IS".
 45:          *
 46:          * @param mixed $mixData
 47:          * @param boolean $blnIncludeEquality whether or not to include an equality operator
 48:          * @param boolean $blnReverseEquality whether the included equality operator should be a "NOT EQUAL", e.g. "!="
 49:          * @return string the properly formatted SQL variable
 50:          */
 51:         public function SqlVariable($mixData, $blnIncludeEquality = false, $blnReverseEquality = false) {
 52:             // Are we SqlVariabling a BOOLEAN value?
 53:             if (is_bool($mixData)) {
 54:                 // Yes
 55:                 if ($blnIncludeEquality) {
 56:                     // We must include the inequality
 57: 
 58:                     if ($blnReverseEquality) {
 59:                         // Do a "Reverse Equality"
 60: 
 61:                         // Check against NULL, True then False
 62:                         if (is_null($mixData))
 63:                             return 'IS NOT NULL';
 64:                         else if ($mixData)
 65:                             return '= 0';
 66:                         else
 67:                             return '!= 0';
 68:                     } else {
 69:                         // Check against NULL, True then False
 70:                         if (is_null($mixData))
 71:                             return 'IS NULL';
 72:                         else if ($mixData)
 73:                             return '!= 0';
 74:                         else
 75:                             return '= 0';
 76:                     }
 77:                 } else {
 78:                     // Check against NULL, True then False
 79:                     if (is_null($mixData))
 80:                         return 'NULL';
 81:                     else if ($mixData)
 82:                         return '1';
 83:                     else
 84:                         return '0';
 85:                 }
 86:             }
 87: 
 88:             // Check for Equality Inclusion
 89:             if ($blnIncludeEquality) {
 90:                 if ($blnReverseEquality) {
 91:                     if (is_null($mixData))
 92:                         $strToReturn = 'IS NOT ';
 93:                     else
 94:                         $strToReturn = '!= ';
 95:                 } else {
 96:                     if (is_null($mixData))
 97:                         $strToReturn = 'IS ';
 98:                     else
 99:                         $strToReturn = '= ';
100:                 }
101:             } else
102:                 $strToReturn = '';
103: 
104:             // Check for NULL Value
105:             if (is_null($mixData))
106:                 return $strToReturn . 'NULL';
107: 
108:             // Check for NUMERIC Value
109:             if (is_integer($mixData) || is_float($mixData))
110:                 return $strToReturn . sprintf('%s', $mixData);
111: 
112:             // Check for DATE Value
113:             if ($mixData instanceof QDateTime)
114:                 return $strToReturn . sprintf("'%s'", $mixData->qFormat(QDateTime::FormatIso));
115: 
116:             // Assume it's some kind of string value
117:             return $strToReturn . sprintf("'%s'", str_replace("'", "''", $mixData));
118:         }
119: 
120:         public function SqlLimitVariablePrefix($strLimitInfo) {
121:             // Setup limit suffix (if applicable) via a TOP clause 
122:             // Add QCUBED_OFFSET tag if applicable
123: 
124:             if (strlen($strLimitInfo)) {
125:                 if (strpos($strLimitInfo, ';') !== false)
126:                     throw new Exception('Invalid Semicolon in LIMIT Info');
127:                 if (strpos($strLimitInfo, '`') !== false)
128:                     throw new Exception('Invalid Backtick in LIMIT Info');
129: 
130:                 // First figure out if we HAVE an offset
131:                 $strArray = explode(',', $strLimitInfo);
132: 
133:                 if (count($strArray) == 2) {
134:                     // Yep -- there's an offset
135:                     return sprintf(
136:                         'TOP %s QCUBED_OFFSET<%s>',
137:                         ($strArray[0] + $strArray[1]),
138:                         $strArray[0]);
139:                 } else if (count($strArray) == 1) {
140:                     return 'TOP ' . $strArray[0];
141:                 } else {
142:                     throw new QSqlServerDatabaseException('Invalid Limit Info: ' . $strLimitInfo, 0, null);
143:                 }
144:             }
145: 
146:             return null;
147:         }
148: 
149:         public function SqlLimitVariableSuffix($strLimitInfo) {
150:             return null;
151:         }
152: 
153:         public function SqlSortByVariable($strSortByInfo) {
154:             // Setup sorting information (if applicable) via a ORDER BY clause
155:             if (strlen($strSortByInfo)) {
156:                 if (strpos($strSortByInfo, ';') !== false)
157:                     throw new Exception('Invalid Semicolon in ORDER BY Info');
158:                 if (strpos($strSortByInfo, '`') !== false)
159:                     throw new Exception('Invalid Backtick in ORDER BY Info');
160: 
161:                 return "ORDER BY $strSortByInfo";
162:             }
163:             
164:             return null;
165:         }
166: 
167:         public function Connect() {
168:       // Set several ini settings
169:       ini_set ( 'mssql.textlimit' , '65536' );
170:             ini_set ( 'mssql.textsize' , '65536' );
171:             ini_set ( 'mssql.charset' , 'utf-8' );
172:             ini_set ( 'mssql.datetimeconvert' , 'Off' );
173: 
174:             // Lookup Adapter-Specific Connection Properties
175:             $strServer = $this->Server;
176:             $strName = $this->Database;
177:             $strUsername = $this->Username;
178:             $strPassword = $this->Password;
179:             $strPort = $this->Port;
180: 
181:             if ($strPort) {
182:                 // Windows Servers
183:                 if (array_key_exists('OS', $_SERVER) && stristr($_SERVER['OS'], 'Win') !== false)
184:                     $strServer .= ',' . $strPort;
185: 
186:                 // All Other Servers
187:                 else
188:                     $strServer .= ':' . $strPort;
189:             }
190: 
191:             // Connect to the Database Server
192: 
193:             // Because the MSSQL extension throws warnings, we want to avoid them
194:             set_error_handler('QcubedHandleError', 0);
195: 
196:             $this->objMsSql = mssql_connect($strServer, $strUsername, $strPassword, true);
197: 
198:             if (!$this->objMsSql) {
199:                 $objException = new QSqlServerDatabaseException('Unable to connect to Database: ' . mssql_get_last_message(), -1, null);
200:                 $objException->IncrementOffset();
201:                 throw $objException;
202:             }
203: 
204:             if (!mssql_select_db($strName, $this->objMsSql)) {
205:                 $objException = new QSqlServerDatabaseException('Unable to connect to Database: ' . mssql_get_last_message(), -1, null);
206:                 $objException->IncrementOffset();
207:                 throw $objException;
208:             }
209:             
210:             // Restore the error handler to the original
211:             restore_error_handler();
212: 
213:             // Update Connected Flag
214:             $this->blnConnectedFlag = true;
215:         }
216: 
217:         public function __get($strName) {
218:             switch ($strName) {
219:                 case 'AffectedRows':
220:                     return mssql_affected_rows($this->objMsSql);
221:                 default:
222:                     try {
223:                         return parent::__get($strName);
224:                     } catch (QCallerException $objExc) {
225:                         $objExc->IncrementOffset();
226:                         throw $objExc;
227:                     }
228:             }
229:         }
230: 
231:         protected function ExecuteQuery($strQuery) {
232:             // First, check for QCUBED_OFFSET<#> for LIMIT INFO Offseting
233:             if ( ($intPosition = strpos($strQuery, 'QCUBED_OFFSET<')) !== false) {
234:                 $intEndPosition = strpos($strQuery, '>', $intPosition);
235:                 if ($intEndPosition === false)
236:                     throw new QSqlServerDatabaseException('Invalid QCUBED_OFFSET', 0, $strQuery);
237:                 $intOffset = QType::Cast(substr($strQuery,
238:                     $intPosition + 13 /* len of QCUBED_OFFSET< */,
239:                     $intEndPosition - $intPosition - 13), QType::Integer);
240:                 $strQuery = substr($strQuery, 0, $intPosition) . substr($strQuery, $intEndPosition + 1);
241:             } else
242:                 $intOffset = 0;
243: 
244:             // Perform the Query
245: 
246:             // Because the MSSQL extension throws warnings, we want to make our mssql_query
247:             // call around no error handler
248:             set_error_handler('QcubedHandleError', 0);
249:             // To Avoid Long String Truncation
250:             mssql_query('SET TEXTSIZE 65536', $this->objMsSql);
251:             $objResult = mssql_query($strQuery, $this->objMsSql);
252:             restore_error_handler();
253:             if (!$objResult)
254:                 throw new QSqlServerDatabaseException(mssql_get_last_message(), 0, $strQuery);
255: 
256:             // Return the Result
257:             
258:             $objSqlServerDatabaseResult = new QSqlServerDatabaseResult($objResult, $this);
259: 
260:             // Perform Offsetting (if applicable)
261:             for ($intIndex = 0; $intIndex < $intOffset; $intIndex++) {
262:                 $objRow = $objSqlServerDatabaseResult->FetchRow();
263:                 if (!$objRow)
264:                     return $objSqlServerDatabaseResult;
265:             }
266:             
267:             return $objSqlServerDatabaseResult;
268:         }
269: 
270:         protected function ExecuteNonQuery($strNonQuery) {
271:             // Perform the Query
272: 
273:             // Because the MSSQL extension throws warnings, we want to make our mssql_query
274:             // call around no error handler
275:             set_error_handler('QcubedHandleError', 0);
276:             $objResult = mssql_query($strNonQuery, $this->objMsSql);
277:             restore_error_handler();
278: 
279:             if (!$objResult)
280:                 throw new QSqlServerDatabaseException(mssql_get_last_message(), 0, $strNonQuery);
281:         }
282: 
283:         public function GetTables() {
284: //          $objResult = $this->Query("SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name ASC");
285:             $objResult = $this->Query("SELECT name FROM sysobjects WHERE (OBJECTPROPERTY(id, N'IsTable') = 1) AND " .
286:                 "(name NOT LIKE N'#%') AND (OBJECTPROPERTY(id, N'IsMSShipped') = 0) AND (OBJECTPROPERTY(id, N'IsSystemTable') = 0) " .
287:                 "ORDER BY name ASC");
288: 
289:             $strToReturn = array();
290:             while ($strRowArray = $objResult->FetchRow())
291:                 array_push($strToReturn, $strRowArray[0]);
292:             return $strToReturn;
293:         }
294:         
295:         public function GetTableForId($intTableId) {
296:             $intTableId = $this->SqlVariable($intTableId);
297:             $strQuery = sprintf('
298:                 SELECT
299:                     name
300:                 FROM
301:                     sysobjects
302:                 WHERE
303:                     id = %s
304:             ', $intTableId);
305:             
306:             $objResult = $this->Query($strQuery);
307:             $objRow = $objResult->FetchRow();
308:             return $objRow[0];
309:         }
310: 
311:         public function GetFieldsForTable($strTableName) {
312:             $strTableName = $this->SqlVariable($strTableName);
313: 
314:             $strQuery = sprintf('
315:                 SELECT
316:                     syscolumns.*
317:                 FROM
318:                     syscolumns,
319:                     sysobjects
320:                 WHERE
321:                     sysobjects.name = %s    AND
322:                     sysobjects.id = syscolumns.id
323:                 ORDER BY
324:                     colorder ASC
325:             ', $strTableName);
326: 
327:             $objResult = $this->Query($strQuery);
328: 
329:             $objFields = array();
330: 
331:             while ($objRow = $objResult->GetNextRow()) {
332:                 array_push($objFields, new QSqlServerDatabaseField($objRow, $this));
333:             }
334: 
335:             return $objFields;
336:         }
337: 
338:         public function InsertId($strTableName = null, $strColumnName = null) {
339:             $strQuery = 'SELECT SCOPE_IDENTITY();';
340:             $objResult = $this->Query($strQuery);
341:             $objRow = $objResult->FetchRow();
342:             return $objRow[0];
343:         }
344: 
345:         public function Close() {
346:             mssql_close($this->objMsSql);
347: 
348:             // Update Connected Flag
349:             $this->blnConnectedFlag = false;
350:         }
351: 
352:         protected function ExecuteTransactionBegin() {
353:             $this->NonQuery('BEGIN TRANSACTION;');
354:         }
355: 
356:         protected function ExecuteTransactionCommit() {
357:             $this->NonQuery('COMMIT;');
358:         }
359: 
360:         protected function ExecuteTransactionRollBack() {
361:             $this->NonQuery('ROLLBACK;');
362:         }
363: 
364:         public function GetIndexesForTable($strTableName) {
365:             $objIndexArray = array();
366: 
367:             // Use sp_helpindex to pull the indexes
368:             $objResult = $this->Query(sprintf('exec sp_helpindex %s', $this->SqlVariable($strTableName)));
369:             while ($objRow = $objResult->GetNextRow()) {
370:                 $strIndexDescription = $objRow->GetColumn('index_description');
371:                 $strKeyName = $objRow->GetColumn('index_name');
372:                 $blnPrimaryKey = (strpos($strIndexDescription, 'primary key') !== false);
373:                 $blnUnique = (strpos($strIndexDescription, 'unique') !== false);
374:                 $strColumnNameArray = explode(', ', $objRow->GetColumn('index_keys'));
375:                 
376:                 $objIndex = new QDatabaseIndex($strKeyName, $blnPrimaryKey, $blnUnique, $strColumnNameArray);
377:                 array_push($objIndexArray, $objIndex);
378:             }
379:             
380:             return $objIndexArray;
381:         }
382: 
383:         public function GetForeignKeysForTable($strTableName) {
384:             $objForeignKeyArray = array();
385:             
386:             // Use Query to pull the FKs
387:             $strQuery = sprintf('
388:                 SELECT 
389:                     fk_table  = FK.TABLE_NAME, 
390:                     fk_column = CU.COLUMN_NAME, 
391:                     pk_table  = PK.TABLE_NAME, 
392:                     pk_column = PT.COLUMN_NAME, 
393:                     constraint_name = C.CONSTRAINT_NAME 
394:                 FROM 
395:                     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
396:                     INNER JOIN 
397:                     INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
398:                         ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
399:                     INNER JOIN 
400:                     INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
401:                         ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
402:                     INNER JOIN 
403:                     INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
404:                         ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
405:                     INNER JOIN 
406:                     ( 
407:                         SELECT 
408:                             i1.TABLE_NAME, i2.COLUMN_NAME 
409:                         FROM 
410:                             INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
411:                             INNER JOIN 
412:                             INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
413:                             ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
414:                             WHERE i1.CONSTRAINT_TYPE = \'PRIMARY KEY\' 
415:                     ) PT 
416:                     ON PT.TABLE_NAME = PK.TABLE_NAME 
417:                 WHERE
418:                     FK.TABLE_NAME = %s
419:                 ORDER BY 
420:                     constraint_name',
421:                 $this->SqlVariable($strTableName));
422:             $objResult = $this->Query($strQuery);
423: 
424:             $strKeyName = '';
425:             while ($objRow = $objResult->GetNextRow()) {
426:                 if ($strKeyName != $objRow->GetColumn('constraint_name')) {
427:                     if ($strKeyName) {
428:                         $objForeignKey = new QDatabaseForeignKey(
429:                             $strKeyName,
430:                             $strColumnNameArray,
431:                             $strReferenceTableName,
432:                             $strReferenceColumnNameArray);
433:                         array_push($objForeignKeyArray, $objForeignKey);
434:                     }
435: 
436:                     $strKeyName = $objRow->GetColumn('constraint_name');
437:                     $strReferenceTableName = $objRow->GetColumn('pk_table');
438:                     $strColumnNameArray = array();
439:                     $strReferenceColumnNameArray = array();
440:                 }
441: 
442:                 if (!array_search($objRow->GetColumn('fk_column'), $strColumnNameArray)) {
443:                     array_push($strColumnNameArray, $objRow->GetColumn('fk_column'));
444:                 }
445: 
446:                 if (!array_search($objRow->GetColumn('pk_column'), $strReferenceColumnNameArray)) {
447:                     array_push($strReferenceColumnNameArray, $objRow->GetColumn('pk_column'));
448:                 }
449:             }
450:             
451:             if ($strKeyName) {
452:                 $objForeignKey = new QDatabaseForeignKey(
453:                     $strKeyName,
454:                     $strColumnNameArray,
455:                     $strReferenceTableName,
456:                     $strReferenceColumnNameArray);
457:                 array_push($objForeignKeyArray, $objForeignKey);
458:             }
459: 
460:             // Return the Array of Foreign Keys
461:             return $objForeignKeyArray;
462:         }
463:     }
464: 
465:     /**
466:      *
467:      * @package DatabaseAdapters
468:      */
469:     class QSqlServerDatabaseException extends QDatabaseExceptionBase {
470:         public function __construct($strMessage, $intNumber, $strQuery) {
471:             parent::__construct(sprintf("MS SQL Server Error: %s", $strMessage), 2);
472:             $this->intErrorNumber = $intNumber;
473:             $this->strQuery = $strQuery;
474:         }
475:     }
476: 
477:     /**
478:      * Class to handle results sent by database upon querying
479:      * @package DatabaseAdapters
480:      */
481:     class QSqlServerDatabaseResult extends QDatabaseResultBase {
482:         protected $objMsSqlResult;
483:         protected $objDb;
484: 
485:         public function __construct($objResult, QSqlServerDatabase $objDb) {
486:             $this->objMsSqlResult = $objResult;
487:             $this->objDb = $objDb;
488:         }
489: 
490:         public function FetchArray() {
491:             return mssql_fetch_array($this->objMsSqlResult);
492:         }
493: 
494:         public function FetchFields() {
495:             $objArrayToReturn = array();
496:             while ($objSqlServerDatabaseField = $this->FetchField())
497:                 array_push($objArrayToReturn, $objSqlServerDatabaseField);
498:             return $objArrayToReturn;
499:         }
500: 
501:         public function FetchField() {
502:             if ($objField = mssql_fetch_field($this->objMsSqlResult))
503:                 return new QSqlServerDatabaseField($objField, $this->objDb);
504:         }
505: 
506:         public function FetchRow() {
507:             return mssql_fetch_row($this->objMsSqlResult);
508:         }
509: 
510:         public function CountRows() {
511:             return mssql_num_rows($this->objMsSqlResult);
512:         }
513: 
514:         public function CountFields() {
515:             return mssql_num_fields($this->objMsSqlResult);
516:         }
517: 
518:         public function Close() {
519:             mssql_free_result($this->objMsSqlResult);
520:         }
521:         
522:         public function GetNextRow() {
523:             $strColumnArray = $this->FetchArray();
524:             
525:             if ($strColumnArray)
526:                 return new QSqlServerDatabaseRow($strColumnArray);
527:             else
528:                 return null;
529:         }
530: 
531:         public function GetRows() {
532:             $objDbRowArray = array();
533:             while ($objDbRow = $this->GetNextRow())
534:                 array_push($objDbRowArray, $objDbRow);
535:             return $objDbRowArray;
536:         }
537:     }
538: 
539:     /**
540:      *
541:      * @package DatabaseAdapters
542:      */
543:     class QSqlServerDatabaseRow extends QDatabaseRowBase {
544:         protected $strColumnArray;
545: 
546:         public function __construct($strColumnArray) {
547:             $this->strColumnArray = $strColumnArray;
548:         }
549: 
550:         /**
551:          * Gets the value of a column from a result row returned by the database
552:          *
553:          * @param string                  $strColumnName Name of te column
554:          * @param null|QDatabaseFieldType $strColumnType Data type
555:          *
556:          * @return mixed
557:          */
558:         public function GetColumn($strColumnName, $strColumnType = null) {
559:             if (!isset($this->strColumnArray[$strColumnName])) {
560:                 return null;
561:             }
562:             $strColumnValue = $this->strColumnArray[$strColumnName];
563:         
564:             switch ($strColumnType) {
565:                 case QDatabaseFieldType::Bit:
566:                     return ($strColumnValue) ? true : false;
567: 
568:                 case QDatabaseFieldType::Blob:
569:                 case QDatabaseFieldType::Char:
570:                 case QDatabaseFieldType::VarChar:
571:                     return QType::Cast($strColumnValue, QType::String);
572: 
573:                 case QDatabaseFieldType::Date:
574:                 case QDatabaseFieldType::DateTime:
575:                 case QDatabaseFieldType::Time:
576:                     return new QDateTime($strColumnValue);
577: 
578:                 case QDatabaseFieldType::Float:
579:                     return QType::Cast($strColumnValue, QType::Float);
580: 
581:                 case QDatabaseFieldType::Integer:
582:                     return QType::Cast($strColumnValue, QType::Integer);
583: 
584:                 default:
585:                     return $strColumnValue;
586:             }
587:         }
588: 
589:         /**
590:          * Tells whether a particular column exists in a returned database row
591:          *
592:          * @param string $strColumnName Name of te column
593:          *
594:          * @return bool
595:          */
596:         public function ColumnExists($strColumnName) {
597:             return array_key_exists($strColumnName, $this->strColumnArray);
598:         }
599:         
600:         public function GetColumnNameArray() {
601:             return $this->strColumnArray;
602:         }
603:     }
604: 
605:     /**
606:      *
607:      * @package DatabaseAdapters
608:      */
609:     class QSqlServerDatabaseField extends QDatabaseFieldBase {
610:         public function __construct($mixFieldData, $objDb = null) {
611:             $objDatabaseRow = null;
612:             try {
613:                 $objDatabaseRow = QType::Cast($mixFieldData, 'QSqlServerDatabaseRow');
614:             } catch (QInvalidCastException $objExc) {
615:             }
616: 
617:             if ($objDatabaseRow) {
618:                 // Passed in field data is a row from select * from syscolumns for this table
619:                 $intTableId = $objDatabaseRow->GetColumn('id');
620:                 $this->strName = $objDatabaseRow->GetColumn('name');
621:                 $this->strOriginalName = $this->strName;
622:                 $this->strTable = $objDb->GetTableForId($intTableId);
623:                 $this->strOriginalTable = $this->strTable;
624:                 $this->strDefault = null; /* Not Supported */
625:                 $this->intMaxLength = $objDatabaseRow->GetColumn('length', QDatabaseFieldType::Integer);
626:                 $this->blnNotNull = ($objDatabaseRow->GetColumn('isnullable')) ? false : true;
627: 
628:                 // Determine Primary Key
629:                 $objResult = $objDb->Query(sprintf("EXEC sp_pkeys @table_name='%s'", $this->strTable));
630:                 while ($objRow = $objResult->GetNextRow()) {
631:                     if ($objRow->GetColumn('COLUMN_NAME') == $this->strName)
632:                         $this->blnPrimaryKey = true;
633:                 }               
634:                 if (!$this->blnPrimaryKey)
635:                     $this->blnPrimaryKey = false;
636: 
637:                 // UNIQUE
638:                 // First, we assume we're NOT unique
639:                 $this->blnUnique = false;
640:                 
641:                 // Now, get all the single-column indexes for this table (by indid)
642:                 $strQuery = sprintf('
643:                     SELECT
644:                         indid,
645:                         count(indid) AS column_count
646:                     FROM
647:                         sysindexkeys
648:                     WHERE
649:                         id = %s
650:                     GROUP BY
651:                         indid', $intTableId);
652:                 $objResult = $objDb->Query($strQuery);
653:                 $intIndIdArray = array();
654:                 while ($objRow = $objResult->GetNextRow())
655:                     if ($objRow->GetColumn('column_count') == 1) {
656:                         // We have a single-column index -- add it to the indid array
657:                         array_push($intIndIdArray, $objRow->GetColumn('indid', QDatabasefieldtype::Integer));
658:                     }
659:                 
660:                 if (count($intIndIdArray) > 0) {
661:                     // Get all the single-column index that indexes this column
662:                     $strQuery = sprintf('
663:                         SELECT
664:                             sysindexes.name
665:                         FROM
666:                             sysindexes,
667:                             sysindexkeys,
668:                             syscolumns
669:                         WHERE
670:                             syscolumns.colid = sysindexkeys.colid   AND
671:                             sysindexes.indid = sysindexkeys.indid   AND
672:                             sysindexkeys.indid IN (%s)              AND
673:                             syscolumns.name = %s                    AND
674:                             syscolumns.id = %s                      AND
675:                             sysindexkeys.id = %s                    AND
676:                             sysindexes.id = %s
677:                     ',
678:                         implode(',', $intIndIdArray),
679:                         $objDb->SqlVariable($this->strName),
680:                         $intTableId,
681:                         $intTableId,
682:                         $intTableId);
683:                     
684:                     $objResult = $objDb->Query($strQuery);
685:                     
686:                     while ($objRow = $objResult->FetchRow()) {
687:                         $strQuery = sprintf("SELECT indexproperty(%s, %s, 'IsUnique')",
688:                             $intTableId, $objDb->SqlVariable($objRow[0]));
689:                         $objIndexPropertyResult = $objDb->Query($strQuery);
690:                         $objRow = $objIndexPropertyResult->FetchRow();
691:                         if ($objRow[0])
692:                             $this->blnUnique = true;
693:                     }
694:                 }
695: 
696: 
697:                 // Figure out Type and Identity by using sp_columns
698:                 $objResult = $objDb->Query(sprintf("EXEC sp_columns @table_name='%s', @column_name='%s'", $this->strTable, $this->strName));
699:                 $objRow = $objResult->GetNextRow();
700: 
701:                 $strTypeName = $objRow->GetColumn('TYPE_NAME');
702:                 $intScale = $objRow->GetColumn('SCALE');
703:                 $this->blnIdentity = (strpos($strTypeName, 'identity') !== false) ? true : false;
704: 
705:                 // We're only going to use the first word of the TYPE_NAME
706:                 if (strpos($strTypeName, ' ') !== false)
707:                     $strTypeName = substr($strTypeName, 0, strpos($strTypeName, ' '));
708:                 $this->strType = $strTypeName;
709: 
710:                 switch ($strTypeName) {
711:                     case 'numeric':
712:                     case 'numeric()':
713:                     case 'decimal':
714:                     case 'decimal()':
715:                         if ($intScale == 0)
716:                             $this->strType = QDatabaseFieldType::Integer;
717:                         else
718:                             $this->strType = QDatabaseFieldType::Float;
719:                         break;
720:                     case 'bigint':
721:                     case 'int':
722:                     case 'tinyint':
723:                     case 'smallint':
724:                         $this->strType = QDatabaseFieldType::Integer;
725:                         break;
726:                     case 'money':
727:                     case 'real':
728:                     case 'float':
729:                     case 'smallmoney':
730:                         $this->strType = QDatabaseFieldType::Float;
731:                         break;
732:                     case 'bit':
733:                         $this->strType = QDatabaseFieldType::Bit;
734:                         break;
735:                     case 'char':
736:                     case 'nchar':
737:                         $this->strType = QDatabaseFieldType::Char;
738:                         break;
739:                     case 'varchar':
740:                     case 'nvarchar':
741:                         $this->strType = QDatabaseFieldType::VarChar;
742:                         break;
743:                     case 'text':
744:                     case 'ntext':
745:                     case 'binary':
746:                     case 'image':
747:                     case 'varbinary':
748:                     case 'uniqueidentifier':
749:                     case 'unique_identifier':
750:                         $this->strType = QDatabaseFieldType::Blob;
751:                         $this->intMaxLength = null;
752:                         break;
753:                     case 'datetime':
754:                     case 'smalldatetime':
755:                         $this->strType = QDatabaseFieldType::DateTime;
756:                         break;
757:                     case 'date':
758:                         $this->strType = QDatabaseFieldType::Date;
759:                         break;
760:                     case 'time':
761:                         $this->strType = QDatabaseFieldType::Time;
762:                         break;
763:                     case 'timestamp':
764:                         // System-generated Timestamp values need to be treated as plain text. They are NOT actual times, but just counters.
765:                         $this->strType = QDatabaseFieldType::VarChar;
766:                         $this->blnTimestamp = true;
767:                         break;
768:                     default:
769:                         throw new QSqlServerDatabaseException('Unsupported Field Type: ' . $strTypeName, 0, null);
770:                 }
771:             } else {
772:                 // Passed in fielddata is a mssql_fetch_field field result
773:                 $this->strName = $mixFieldData->name;
774:                 $this->strOriginalName = $mixFieldData->name;
775:                 $this->strTable = $mixFieldData->column_source;
776:                 $this->strOriginalTable = $mixFieldData->column_source;
777:                 $this->intMaxLength = $mixFieldData->max_length;
778:             }
779:         }
780:     }