Databases
StackOverflow2013
Postsdbo
POMSSQL Unicode Data
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMSSQL Unicode Data
    text
    copied!<p>I'm trying to save a MSSQL database by using PHP. I chose to use phpMSadmin to do so. The problem is that I'm getting: "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier." So I've checked the code and apparently the problem is here:</p> <pre><code>$table_query = @mssql_query('SELECT * FROM ' . $table . ';') or die(throwSQLError('unable to retrieve table data')); </code></pre> <p>The whole code is this:</p> <pre><code>&lt;?php if(!empty($_POST['skippreview']) &amp;&amp; !empty($_POST['doexport'])) $skipheader = true; include('inc/header.php'); mssql_select_db($_SESSION['database']); if(!empty($_POST['doexport'])) { $texttypes = array('binary','char','nchar','varchar','nvarchar'); $masterquery = ''; if(!empty($_POST['dropdb'])) $masterquery .= ('DROP DATABASE ' . $_SESSION['database'] . ';' . "\n"); if(!empty($_POST['createdb'])) $masterquery .= ('CREATE DATABASE ' . $_SESSION['database'] . ';' . "\n"); if(!empty($_POST['useentry'])) $masterquery .= ('USE ' . $_SESSION['database'] . ';' . "\n"); $tablecount = count($_POST['tables']); for($counter = 0; $counter &lt; $tablecount; $counter++) { $table = $_POST['tables'][$counter]; if(!empty($_POST['structure'])) { $tablequery = ('CREATE TABLE ' . $table); $columns = array(); $tablesep = explode('.',$table); if(substr_count($_POST['tables'][$counter],'.') &gt; 0) { $colquery = ('sp_columns @table_name = N\'' . $tablesep[1] . '\''); $colquery .= (', @table_owner = N\'' . $tablesep[0] . '\''); } else $colquery = ('sp_columns @table_name = N\'' . $tablesep[0] . '\''); $column_query = @mssql_query($colquery) or die(throwSQLError('unable to retrieve column data')); if(mssql_num_rows($column_query) &gt; 0) $tablequery .= ' ('; while($row = mssql_fetch_assoc($column_query)) { // Start Column Conversion $colspec = ($row['COLUMN_NAME'] . ' ' . strtoupper($row['TYPE_NAME'])); if(in_array($row['TYPE_NAME'],$texttypes)) $colspec .= ('(' . $row['PRECISION'] . ')'); if(!$row['NULLABLE']) $colspec .= ' NOT NULL'; if($row['COLUMN_DEF'] != '') $colspec .= (' DEFAULT ' . $row['COLUMN_DEF']); $tablequery .= (', ' . $colspec); // End Column Conversion } if(mssql_num_rows($column_query) &gt; 0) $tablequery .= ')'; $tablequery = str_replace('(, ','(',$tablequery); $masterquery .= ($tablequery . ';' . "\n"); } if(!empty($_POST['data'])) { $table_query = @mssql_query('SELECT * FROM ' . $table . ';') or die(throwSQLError('unable to retrieve table data')); while($row = mssql_fetch_assoc($table_query)) { if(!isset($schema)) { $schema = array(); foreach($row AS $key =&gt; $value) $schema[] = $key; } $values = array(); foreach($schema AS $col) if(is_numeric($row[$col])) $values[] = ('\'' . str_replace('\'','\'\'',$row[$col]) . '\''); else if(!empty($_POST['base64'])) $values[] = ('\'' . base64_encode(str_replace('\'','\'\'',$row[$col])) . '\''); else $values[] = ('\'' . str_replace('\'','\'\'',$row[$col]) . '\''); $masterquery .= ('INSERT INTO ' . $table . ' (' . implode(',',$schema) . ') VALUES (' . implode(',',$values) . ');' . "\n"); } } } if(!empty($_POST['procedures'])) { foreach($_POST['procedures'] AS $value) { $lines = array(); $doit = false; $data_query = @mssql_query('sp_helptext \'' . $value . '\'') or die(throwSQLError('unable to retrieve procedure')); if(!@mssql_num_rows($data_query)) { $schema_query = @mssql_query('SELECT SPECIFIC_SCHEMA FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = \'' . $value . '\';'); if($schema_query) { $schema_array = mssql_fetch_array($schema_query); $value = ($schema_array['SPECIFIC_SCHEMA'] . '.' . $value); unset($data_query); $data_query = @mssql_query('sp_helptext \'' . $value . '\'') or die(throwSQLError('unable to retrieve procedure')); if(@mssql_num_rows($data_query)) $doit = true; } } else $doit = true; if($doit) { while($row = mssql_fetch_array($data_query)) $lines[] = $row['Text']; $masterquery .= (implode('',$lines) . "\n"); } unset($lines,$doit); } } if(!empty($_POST['views'])) { foreach($_POST['views'] AS $value) { $lines = array(); $doit = false; $data_query = @mssql_query('sp_helptext \'' . $value . '\'') or die(throwSQLError('unable to retrieve procedure')); if(!@mssql_num_rows($data_query)) { $schema_query = @mssql_query('SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = \'' . $value . '\';'); if($schema_query) { $schema_array = mssql_fetch_array($schema_query); $value = ($schema_array['TABLE_SCHEMA'] . '.' . $value); unset($data_query); $data_query = @mssql_query('sp_helptext \'' . $value . '\'') or die(throwSQLError('unable to retrieve view')); if(@mssql_num_rows($data_query)) $doit = true; } } else $doit = true; if($doit) { while($row = mssql_fetch_array($data_query)) $lines[] = $row['Text']; $masterquery .= (implode('',$lines) . "\n"); } unset($lines,$doit); } } if(!empty($_POST['functions'])) { foreach($_POST['functions'] AS $value) { $lines = array(); $data_query = @mssql_query('sp_helptext \'' . $value . '\'') or die(throwSQLError('unable to retrieve function')); if($data_query) { while($row = mssql_fetch_array($data_query)) $lines[] = $row['Text']; $masterquery .= (implode('',$lines) . "\n"); unset($lines); } } } $masterquery = rtrim($masterquery); if(empty($_POST['skippreview'])) { echo '&lt;form name="form1" method="post" action="database_export_download.php"&gt;'; echo '&lt;textarea name="data" rows="30" cols="75"&gt;'; echo($masterquery); echo '&lt;/textarea&gt;'; echo '&lt;br&gt;&lt;br&gt;&lt;input type="submit" value="Save to File"&gt;'; echo '&lt;/form&gt;'; include('inc/footer.php'); } else { header('Content-type: application/x-download'); header('Content-Disposition: attachment; filename="' . $_SESSION['database'] . '.sql"'); header('Content-Length: ' . strlen($masterquery)); echo($masterquery); exit; } } $tables = array(); $db_query = mssql_query('sp_tables') or die(throwSQLError('unable to retrieve list of tables')); while($row = mssql_fetch_array($db_query)) { if($row['TABLE_TYPE'] == 'TABLE' &amp;&amp; $row['TABLE_NAME'] != 'dtproperties') { if($row['TABLE_OWNER'] != 'dbo') $tables[] = ($row['TABLE_OWNER'] . '.' . $row['TABLE_NAME']); else $tables[] = $row['TABLE_NAME']; } } ?&gt; &lt;script language="javascript"&gt; function doCheck(mode) { for(counter = 0; counter &lt; document.form1.tablecount.value; counter++) document.forms['form1'].elements['tables[]'][counter].checked = mode; } function doCheckProcedures(mode) { for(counter = 0; counter &lt; document.form1.procedurecount.value; counter++) document.forms['form1'].elements['procedures[]'][counter].checked = mode; } function doCheckViews(mode) { for(counter = 0; counter &lt; document.form1.viewcount.value; counter++) document.forms['form1'].elements['views[]'][counter].checked = mode; } function doCheckFunctions(mode) { for(counter = 0; counter &lt; document.form1.functioncount.value; counter++) document.forms['form1'].elements['functions[]'][counter].checked = mode; } &lt;/script&gt; &lt;form name="form1" method="post" action="database_export.php"&gt; &lt;input type="hidden" name="doexport" value="yes"&gt; &lt;table width="350" cellpadding="3" cellspacing="3" style="border: 1px solid"&gt; &lt;tr&gt; &lt;td align="center" colspan="3" style="background: #D0DCE0"&gt;&lt;b&gt;Tables&lt;/b&gt;&lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td style="background: #D0DCE0"&gt;&amp;nbsp;&lt;/td&gt; &lt;td align="center" style="background: #D0DCE0"&gt; &lt;b&gt;Table&lt;/b&gt; &lt;/td&gt; &lt;td align="center" style="background: #D0DCE0"&gt; &lt;b&gt;Records&lt;/b&gt; &lt;/td&gt; &lt;/tr&gt; &lt;?php $totalrecords = 0; $toggle = true; $colors = array('#DDDDDD','#CCCCCC'); if(empty($_POST['tables'])) $_POST['tables'] = array(); foreach($tables AS $row) { if($toggle) $bg = $colors[0]; else $bg = $colors[1]; $toggle = !$toggle; if(substr_count($row,'.') &gt; 0) $record_query = mssql_query('SELECT count(*) AS itemcount FROM ' . $row); else $record_query = mssql_query('SELECT count(*) AS itemcount FROM [' . $row . ']'); $record_array = mssql_fetch_array($record_query); $records = $record_array['itemcount']; $totalrecords += $records; echo '&lt;tr&gt;'; if(in_array($row,$_POST['tables'])) echo('&lt;td align="center" style="background: ' . $bg . '"&gt;&lt;input type="checkbox" name="tables[]" value="' . $row . '" checked&gt;&lt;/td&gt;'); else echo('&lt;td align="center" style="background: ' . $bg . '"&gt;&lt;input type="checkbox" name="tables[]" value="' . $row . '"&gt;&lt;/td&gt;'); echo('&lt;td style="background: ' . $bg . '"&gt;' . $row . '&lt;/td&gt;'); echo('&lt;td style="background: ' . $bg . '"&gt;' . number_format($records) . '&lt;/td&gt;'); echo '&lt;/tr&gt;'; unset($record_query,$record_array,$records); } ?&gt; &lt;tr&gt; &lt;td align="right" colspan="2" style="background: #EAEAEA"&gt; &lt;b&gt;Total Row Count:&lt;/b&gt; &lt;/td&gt; &lt;td align="right" style="background: #EAEAEA"&gt; &lt;?php echo number_format($totalrecords); ?&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td colspan="3" style="background: #D0DCE0" nowrap&gt; Select Tables:&amp;nbsp;&amp;nbsp;&lt;a href="javascript:doCheck(true);"&gt;All&lt;/a&gt; / &lt;a href="javascript:doCheck(false);"&gt;None&lt;/a&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;br&gt; &lt;?php $procs = array(); $views = array(); $functions = array(); $proc_query = @mssql_query('sp_help') or die(throwSQLError('unable to retrieve list of stored procedures')); while($row = mssql_fetch_assoc($proc_query)) { if($row['Object_type'] == 'stored procedure' &amp;&amp; ($row['Owner'] == 'dbo' || $_SETTINGS['showsysdata'])) $procs[] = $row['Name']; else if($row['Object_type'] == 'view' &amp;&amp; ($row['Owner'] == 'dbo' || $_SETTINGS['showsysdata'])) $views[] = $row['Name']; else if(substr_count($row['Object_type'],'function') &gt; 0 &amp;&amp; ($row['Owner'] == 'dbo' || $_SETTINGS['showsysdata'])) $functions[] = $row['Name']; } ?&gt; &lt;table width="350" cellpadding="3" cellspacing="3" style="border: 1px solid"&gt; &lt;tr&gt; &lt;td align="center" colspan="2" style="background: #D0DCE0"&gt; &lt;b&gt;Stored Procedures&lt;/b&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td style="background: #D0DCE0"&gt;&amp;nbsp;&lt;/td&gt; &lt;td align="center" style="background: #D0DCE0"&gt; &lt;b&gt;Name&lt;/b&gt; &lt;/td&gt; &lt;/tr&gt; &lt;?php $toggle = true; $colors = array('#DDDDDD','#CCCCCC'); foreach($procs AS $key =&gt; $value) { $row['Name'] = $value; if($toggle) $bg = $colors[0]; else $bg = $colors[1]; $toggle = !$toggle; echo '&lt;tr&gt;'; echo('&lt;td align="center" style="background: ' . $bg . '"&gt;&lt;input type="checkbox" name="procedures[]" value="' . $row['Name'] . '"&gt;&lt;/td&gt;'); echo('&lt;td style="background: ' . $bg . '" nowrap&gt;' . $row['Name'] . '&lt;/td&gt;'); echo '&lt;/tr&gt;'; unset($row); } if(count($procs) == 0) echo '&lt;tr&gt;&lt;td align="center" colspan="3" style="background: #DDDDDD"&gt;No Stored Procedures&lt;/td&gt;&lt;/tr&gt;'; ?&gt; &lt;tr&gt; &lt;td colspan="2" style="background: #D0DCE0"&gt; Select Procedures:&amp;nbsp;&amp;nbsp;&lt;a href="javascript:doCheckProcedures(true);"&gt;All&lt;/a&gt; / &lt;a href="javascript:doCheckProcedures(false);"&gt;None&lt;/a&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;br&gt; &lt;table width="350" cellpadding="3" cellspacing="3" style="border: 1px solid"&gt; &lt;tr&gt; &lt;td align="center" colspan="2" style="background: #D0DCE0"&gt; &lt;b&gt;Views&lt;/b&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td style="background: #D0DCE0"&gt;&amp;nbsp;&lt;/td&gt; &lt;td align="center" style="background: #D0DCE0"&gt; &lt;b&gt;Name&lt;/b&gt; &lt;/td&gt; &lt;/tr&gt; &lt;?php $toggle = true; $colors = array('#DDDDDD','#CCCCCC'); foreach($views AS $key =&gt; $value) { $row['Name'] = $value; if($toggle) $bg = $colors[0]; else $bg = $colors[1]; $toggle = !$toggle; echo '&lt;tr&gt;'; echo('&lt;td align="center" style="background: ' . $bg . '"&gt;&lt;input type="checkbox" name="views[]" value="' . $row['Name'] . '"&gt;&lt;/td&gt;'); echo('&lt;td style="background: ' . $bg . '" nowrap&gt;' . $row['Name'] . '&lt;/td&gt;'); echo '&lt;/tr&gt;'; unset($row); } if(count($procs) == 0) echo '&lt;tr&gt;&lt;td align="center" colspan="3" style="background: #DDDDDD"&gt;No Views&lt;/td&gt;&lt;/tr&gt;'; ?&gt; &lt;tr&gt; &lt;td colspan="2" style="background: #D0DCE0"&gt; Select Views:&amp;nbsp;&amp;nbsp;&lt;a href="javascript:doCheckViews(true);"&gt;All&lt;/a&gt; / &lt;a href="javascript:doCheckViews(false);"&gt;None&lt;/a&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;br&gt; &lt;table width="350" cellpadding="3" cellspacing="3" style="border: 1px solid"&gt; &lt;tr&gt; &lt;td align="center" colspan="2" style="background: #D0DCE0"&gt; &lt;b&gt;Functions&lt;/b&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td style="background: #D0DCE0"&gt;&amp;nbsp;&lt;/td&gt; &lt;td align="center" style="background: #D0DCE0"&gt; &lt;b&gt;Name&lt;/b&gt; &lt;/td&gt; &lt;/tr&gt; &lt;?php $toggle = true; $colors = array('#DDDDDD','#CCCCCC'); foreach($functions AS $key =&gt; $value) { $row['Name'] = $value; if($toggle) $bg = $colors[0]; else $bg = $colors[1]; $toggle = !$toggle; echo '&lt;tr&gt;'; echo('&lt;td align="center" style="background: ' . $bg . '"&gt;&lt;input type="checkbox" name="functions[]" value="' . $row['Name'] . '"&gt;&lt;/td&gt;'); echo('&lt;td style="background: ' . $bg . '" nowrap&gt;' . $row['Name'] . '&lt;/td&gt;'); echo '&lt;/tr&gt;'; unset($row); } if(count($functions) == 0) echo '&lt;tr&gt;&lt;td align="center" colspan="2" style="background: #DDDDDD"&gt;No Functions&lt;/td&gt;&lt;/tr&gt;'; ?&gt; &lt;tr&gt; &lt;td align="center" colspan="2" style="background: #D0DCE0"&gt; Select Functions:&amp;nbsp;&amp;nbsp;&lt;a href="javascript:doCheckFunctions(true);"&gt;All&lt;/a&gt; / &lt;a href="javascript:doCheckFunctions(false);"&gt;None&lt;/a&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;br&gt; &lt;input type="hidden" name="tablecount" value="&lt;?php echo(count($tables)); ?&gt;"&gt; &lt;input type="hidden" name="procedurecount" value="&lt;?php echo(count($procs)); ?&gt;"&gt; &lt;input type="hidden" name="viewcount" value="&lt;?php echo(count($views)); ?&gt;"&gt; &lt;input type="hidden" name="functioncount" value="&lt;?php echo(count($functions)); ?&gt;"&gt; &lt;table width="350" cellpadding="3" cellspacing="3" style="border: 1px solid"&gt; &lt;tr&gt; &lt;td align="center" colspan="2" style="background: #D0DCE0"&gt; &lt;b&gt;Export Options&lt;/b&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td align="right" style="background: #CCCCCC" valign="top" nowrap&gt; Selection: &lt;/td&gt; &lt;td style="background: #CCCCCC" nowrap&gt; &lt;input type="checkbox" name="structure" value="yes"&gt; Structure&lt;br&gt; &lt;input type="checkbox" name="data" value="yes"&gt; Data &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td align="right" style="background: #CCCCCC" valign="top" nowrap&gt; Preparation: &lt;/td&gt; &lt;td style="background: #CCCCCC" nowrap&gt; &lt;input type="checkbox" name="dropdb" value="yes"&gt; Drop Database First&lt;br&gt; &lt;input type="checkbox" name="createdb" value="yes"&gt; Use Create Database&lt;br&gt; &lt;input type="checkbox" name="useentry" value="yes"&gt; Add "USE" Database Switch &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td align="right" style="background: #CCCCCC" valign="top" nowrap&gt; Method: &lt;/td&gt; &lt;td style="background: #CCCCCC" nowrap&gt; &lt;input type="checkbox" name="base64" value="yes"&gt; Base-64 Encode Strings&lt;br&gt; &lt;input type="checkbox" name="skippreview" value="yes"&gt; Skip Export Preview &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td align="right" colspan="2" style="background: #D0DCE0"&gt; &lt;input type="submit" value="Export"&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/form&gt; &lt;?php include('inc/footer.php'); ?&gt; </code></pre> <p>Is there any possible way to fix this? Thank you very much.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload