Improve DB field_data() for MSSQL / SQL Server
diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php
index 53f2e55..0ae8d7b 100644
--- a/system/database/drivers/mssql/mssql_driver.php
+++ b/system/database/drivers/mssql/mssql_driver.php
@@ -344,22 +344,45 @@
*/
protected function _list_columns($table = '')
{
- return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'";
+ return "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'";
}
// --------------------------------------------------------------------
/**
- * Field data query
- *
- * Generates a platform-specific query so that the column data can be retrieved
+ * Returns an object with field data
*
* @param string $table
- * @return string
+ * @return array
*/
- protected function _field_data($table)
+ public function field_data($table = '')
{
- return 'SELECT TOP 1 * FROM '.$this->protect_identifiers($table);
+ if ($table === '')
+ {
+ return ($this->db_debug) ? $this->display_error('db_field_param_missing') : FALSE;
+ }
+
+ $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
+ FROM INFORMATION_SCHEMA.Columns
+ WHERE TABLE_NAME = '.$this->escape($table);
+
+ if (($query = $this->query($sql)) === FALSE)
+ {
+ return FALSE;
+ }
+ $query = $query->result_object();
+
+ $retval = array();
+ for ($i = 0, $c = count($query); $i < $c; $i++)
+ {
+ $retval[$i] = new stdClass();
+ $retval[$i]->name = $query[$i]->COLUMN_NAME;
+ $retval[$i]->type = $query[$i]->DATA_TYPE;
+ $retval[$i]->max_length = ($query[$i]->CHARACTER_MAXIMUM_LENGTH > 0) ? $query[$i]->NUMERIC_PRECISION : $query[$i]->CHARACTER_MAXIMUM_LENGTH;
+ $retval[$i]->default = $query[$i]->COLUMN_DEFAULT;
+ }
+
+ return $retval;
}
// --------------------------------------------------------------------
diff --git a/system/database/drivers/mssql/mssql_result.php b/system/database/drivers/mssql/mssql_result.php
index c74c70a..a8f850d 100644
--- a/system/database/drivers/mssql/mssql_result.php
+++ b/system/database/drivers/mssql/mssql_result.php
@@ -94,16 +94,14 @@
public function field_data()
{
$retval = array();
- while ($field = mssql_fetch_field($this->result_id))
+ for ($i = 0, $c = $this->num_field(); $i < $c; $i++)
{
- $F = new stdClass();
- $F->name = $field->name;
- $F->type = $field->type;
- $F->max_length = $field->max_length;
- $F->primary_key = 0;
- $F->default = '';
+ $field = mssql_fetch_field($this->result_id, $i);
- $retval[] = $F;
+ $retval[$i] = new stdClass();
+ $retval[$i]->name = $field->name;
+ $retval[$i]->type = $field->type;
+ $retval[$i]->max_length = $field->max_length;
}
return $retval;
diff --git a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
index a44fd3a..cf8b026 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
@@ -176,6 +176,44 @@
// --------------------------------------------------------------------
/**
+ * Returns an object with field data
+ *
+ * @param string $table
+ * @return array
+ */
+ public function field_data($table = '')
+ {
+ if ($table === '')
+ {
+ return ($this->db_debug) ? $this->display_error('db_field_param_missing') : FALSE;
+ }
+
+ $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
+ FROM INFORMATION_SCHEMA.Columns
+ WHERE TABLE_NAME = '.$this->escape($table);
+
+ if (($query = $this->query($sql)) === FALSE)
+ {
+ return FALSE;
+ }
+ $query = $query->result_object();
+
+ $retval = array();
+ for ($i = 0, $c = count($query); $i < $c; $i++)
+ {
+ $retval[$i] = new stdClass();
+ $retval[$i]->name = $query[$i]->COLUMN_NAME;
+ $retval[$i]->type = $query[$i]->DATA_TYPE;
+ $retval[$i]->max_length = ($query[$i]->CHARACTER_MAXIMUM_LENGTH > 0) ? $query[$i]->NUMERIC_PRECISION : $query[$i]->CHARACTER_MAXIMUM_LENGTH;
+ $retval[$i]->default = $query[$i]->COLUMN_DEFAULT;
+ }
+
+ return $retval;
+ }
+
+ // --------------------------------------------------------------------
+
+ /**
* Update statement
*
* Generates a platform-specific update string from the supplied data
diff --git a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
index 1a63072..58c673e 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
@@ -205,6 +205,44 @@
// --------------------------------------------------------------------
/**
+ * Returns an object with field data
+ *
+ * @param string $table
+ * @return array
+ */
+ public function field_data($table = '')
+ {
+ if ($table === '')
+ {
+ return ($this->db_debug) ? $this->display_error('db_field_param_missing') : FALSE;
+ }
+
+ $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
+ FROM INFORMATION_SCHEMA.Columns
+ WHERE TABLE_NAME = '.$this->escape($table);
+
+ if (($query = $this->query($sql)) === FALSE)
+ {
+ return FALSE;
+ }
+ $query = $query->result_object();
+
+ $retval = array();
+ for ($i = 0, $c = count($query); $i < $c; $i++)
+ {
+ $retval[$i] = new stdClass();
+ $retval[$i]->name = $query[$i]->COLUMN_NAME;
+ $retval[$i]->type = $query[$i]->DATA_TYPE;
+ $retval[$i]->max_length = ($query[$i]->CHARACTER_MAXIMUM_LENGTH > 0) ? $query[$i]->NUMERIC_PRECISION : $query[$i]->CHARACTER_MAXIMUM_LENGTH;
+ $retval[$i]->default = $query[$i]->COLUMN_DEFAULT;
+ }
+
+ return $retval;
+ }
+
+ // --------------------------------------------------------------------
+
+ /**
* Update statement
*
* Generates a platform-specific update string from the supplied data
diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php
index a3d3177..1e18295 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_driver.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php
@@ -325,22 +325,45 @@
*/
protected function _list_columns($table = '')
{
- return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'";
+ return "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'";
}
// --------------------------------------------------------------------
/**
- * Field data query
- *
- * Generates a platform-specific query so that the column data can be retrieved
+ * Returns an object with field data
*
* @param string $table
- * @return string
+ * @return array
*/
- protected function _field_data($table)
+ public function field_data($table = '')
{
- return 'SELECT TOP 1 * FROM '.$this->protect_identifiers($table);
+ if ($table === '')
+ {
+ return ($this->db_debug) ? $this->display_error('db_field_param_missing') : FALSE;
+ }
+
+ $sql = 'SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, COLUMN_DEFAULT
+ FROM INFORMATION_SCHEMA.Columns
+ WHERE TABLE_NAME = '.$this->escape($table);
+
+ if (($query = $this->query($sql)) === FALSE)
+ {
+ return FALSE;
+ }
+ $query = $query->result_object();
+
+ $retval = array();
+ for ($i = 0, $c = count($query); $i < $c; $i++)
+ {
+ $retval[$i] = new stdClass();
+ $retval[$i]->name = $query[$i]->COLUMN_NAME;
+ $retval[$i]->type = $query[$i]->DATA_TYPE;
+ $retval[$i]->max_length = ($query[$i]->CHARACTER_MAXIMUM_LENGTH > 0) ? $query[$i]->NUMERIC_PRECISION : $query[$i]->CHARACTER_MAXIMUM_LENGTH;
+ $retval[$i]->default = $query[$i]->COLUMN_DEFAULT;
+ }
+
+ return $retval;
}
// --------------------------------------------------------------------
diff --git a/system/database/drivers/sqlsrv/sqlsrv_result.php b/system/database/drivers/sqlsrv/sqlsrv_result.php
index 6d24cc9..b084b84 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_result.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_result.php
@@ -94,16 +94,12 @@
public function field_data()
{
$retval = array();
- foreach (sqlsrv_field_metadata($this->result_id) as $offset => $field)
+ foreach (sqlsrv_field_metadata($this->result_id) as $i => $field)
{
- $F = new stdClass();
- $F->name = $field['Name'];
- $F->type = $field['Type'];
- $F->max_length = $field['Size'];
- $F->primary_key = 0;
- $F->default = '';
-
- $retval[] = $F;
+ $retval[$i] = new stdClass();
+ $retval[$i]->name = $field['Name'];
+ $retval[$i]->type = $field['Type'];
+ $retval[$i]->max_length = $field['Size'];
}
return $retval;