Home » Database » DB » Bug #5241

[2005-08-30 13:02 UTC] chx at mail dot tvnet dot hu

Description:
------------
Using information from user comments (some already deleted) in the PHP manual, I have found that if you want a DB dumper script then you need information whether a field is nvarchar or ntext (opposed to varchar/text). (You want to mssql_query ("set TEXTSIZE 100000;"); and select cast ( column_name as text ) as column_name ... otherwise php mssql extension barks.)

I below inserted a limited reimplementation of tableInfo which only supports $tablename as string.

Test script:
---------------
result = mssql_query(
"SELECT sysobjects.name AS tableName, syscolumns.name AS columnName, systypes.name AS
columnType, syscolumns.length AS columnLength
FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.type = systypes.type AND syscolumns.xusertype =
systypes.xusertype WHERE sysobjects.name = '$tablename'");
while ($column = mssql_fetch_assoc($result)) {
$res[] = array(
'table' => $column['tableName'],
'name' => $column['columnName'],
'type' => $column['columnType'],
'len' => $column['columnLength'],
// We only support flags for table
'flags' => ''
);
}

return $res;
}

[2005-08-30 15:30 UTC] chx at mail dot tvnet dot hu

The function I recommended was not good enough. This query http://searchvb.techtarget.com/tip/1,289483,sid8_gci876303,00.html?bucket=ETA works better.

<?php
function tableInfo($tablename, $mode = null)
{
$result = mssql_query( "SELECT sysobjects.name AS [table],syscolumns.name AS name, systypes.name AS type, syscolumns.length AS len FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id INNER JOIN systypes ON syscolumns.xtype = systypes.xtype WHERE sysobjects.name = '$tablename' AND sysobjects.xtype='U' ");
while ($column = mssql_fetch_assoc($result)) {
$res[] = $column;
}
return $res;
}

?>