PHP & MSSQL : "select *" returns Unicode error
Results 1 to 6 of 6

Thread: PHP & MSSQL : "select *" returns Unicode error

  1. #1
    Junior Member
    Join Date
    Sep 2007
    Posts
    5

    Question PHP & MSSQL : "select *" returns Unicode error

    I am using php to access a mssql server, and any query I attempt to run with a * yeilds a Unicode error.

    "Warning: mssql_query() [function.mssql-query]: message: 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. (severity 16) in "

    I am a LAMP developer trying to inherit a large asp site with a mssql database attached on an IIS server (ugh). I'm trying to take baby steps in getting a simple sql query though but this unicode error is baffling. Also, due to the vagueness of the problem, I havent been able to find anything on google. I also do not have the required proprietary programs needed to open up the database (I'm a notepad only kind of guy). I'm trying to get a SELECT * FROM query to work so I can see all the fields it returns. I have very limited access to the server, so any help would be appreciated, keeping that in mind.

    Is there some fundamentally elementary step that I'm messing up (Like that I don't completely understand what Unicode is, or why it would be used in a DB)? or is there some sort of compatability issue with my server? I'm starting to think that theres a field in the db, in a format that it cannot return to my php script's mssql_query(), and hence the error but mssql documentation and help forums seem to be few and far between.

    I know I'm walking into a gun fight without even a knife here, but if anyone has any clues or suggestions, I'd be glad to learn.

    A query that *did* work was "SELECT story_id,story_headline FROM stories WHERE writer = '$_GET[writer]'". The following errors out right at die("Query has a problem : $query");. The code up top is silly with $var = $var, but the connection works.

    Code:
    $myServer = $server;
    $myUser = $username;
    $myPass = $password;
    $myDB = $db; 
    
    //connection to the database
    $dbhandle = mssql_connect($myServer, $myUser, $myPass)
      or die("Couldn't connect to SQL Server on $myServer"); 
    
    //select a database to work with
    $selected = mssql_select_db($myDB, $dbhandle)
      or die("Couldn't open database $myDB"); 
    
    //declare the SQL statement that will query the database
    $query = "SELECT * FROM stories";
    //execute the SQL query and return records
    $result = mssql_query($query) 
      or die("Query has a problem : $query");
    
    $numRows = mssql_num_rows($result); 
    echo "<h1>" . $numRows . " stories" . ($numRows == 1 ? "" : "s") . " Returned </h1>"; 
    
    //display the results 
    while($row = mssql_fetch_array($result))
    {
      echo '<li><a href="/story.asp?story_id=' .$row['story_id']. '">' .$row["story_headline"]. "</a></li>";
    }
    //close the connection    
    mssql_close($dbhandle);

  2. #2
    Football Season Yet?
    Join Date
    Mar 2004
    Location
    3rd Rock
    Posts
    2,326
    This comes from the php manual for mssql_query (in the user comments section at the bottom). I don't use mssql and have never seen anything like this in mysql, so I'm not sure myself, but it may help you out or start pointing you in the right direction:

    huberkev11 at hotmail dot com
    12-May-2006 08:47
    Solution for the following Error:

    Warning: mssql_query() [function.mssql-query]: message: 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.

    This is because you are using column types of like ntext instead of text. There are 2 solutions.

    1. Change all ntext column types to text or
    2. Your query must look like: SELECT CAST(field1 AS TEXT) AS field1 FROM table
    He is my Defender, I will not be defeated!, Psalm 62:6 (New Century)

  3. #3
    Chamberlain Sxooter's Avatar
    Join Date
    Aug 2002
    Location
    Denver, CO
    Posts
    4,027
    Would updating the odbc driverrs not also work?
    PostgreSQL, because your data matters.

  4. #4
    Junior Member
    Join Date
    Sep 2007
    Posts
    5
    I managed to solve my problem through some hairpulling troubleshooting. Defender's post is exactly correct. Thanks for finding that, wish I had found that one

    Here's some tips to help you guys mess with a MSSQL db if you're a MySQL person like myself.

    First :
    Code:
    	$query = "SELECT   c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
    	FROM     INFORMATION_SCHEMA.COLUMNS c
    	WHERE    c.TABLE_NAME = 'your table here!'
    	ORDER BY c.ORDINAL_POSITION";
    	$result = mssql_query($query);
    	
    	echo "<b>Table Layout</b><pre>";
    	while ( $record = mssql_fetch_array($result) ) {
    	     print $record[1] . " | <b>" .$record[2]. '</b>.<br />'; 
    	}
    	echo "</pre>";
    Use this to scan the db for it's datatypes. Any result returned as Ntext, needs to be 'casted'.

    SELECT CAST(field1 AS TEXT) AS field1 FROM table

    This solution solved my problem. Thanks! Hopefully this post can help some others, as I found googling this problem was real tough.

  5. #5
    Football Season Yet?
    Join Date
    Mar 2004
    Location
    3rd Rock
    Posts
    2,326
    Quote Originally Posted by Byoung
    SELECT CAST(field1 AS TEXT) AS field1 FROM table

    This solution solved my problem. Thanks! Hopefully this post can help some others, as I found googling this problem was real tough.
    Then I won't tell you that I googled "Warning: mssql_query() [function.mssql-query]: message: Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library" (sans quotes) and it was the only solution that came up...
    He is my Defender, I will not be defeated!, Psalm 62:6 (New Century)

  6. #6
    Junior Member
    Join Date
    Sep 2007
    Posts
    5
    My problem was I didnt pay enough attention to the comments on the php page >.< thanks though, your help is appreciated.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •