wbstech
asked on
Using PHP to get MSSQL data - Error: "Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library"
I am trying to use a PHP webpage to get data from MSSQL and get the error message shown in the Question Title.
This is my connection code:
$hostname = "MAIN";
$username = "sa";
$password = "";
$dbName = "NewSQL";
MSSQL_CONNECT($hostname,$u
mssql_select_db($dbName) or DIE("Table unavailable");
$query = "SELECT * FROM Customer";
$result = mssql_query($query);
$numrows = mssql_num_rows($result);
I have found out the problem is down to 2 NTEXT fields in the MSSQL table I am trying to select from. I have found this solution:
mssql_query("SELECT CAST(field1 AS TEXT) AS field1 FROM table;");
Which would solve it - but I want to do a 'SELECT *'.
Can anyone provide me with a better solution or a way of rewriting my currently located solution to 'SELECT *' and simultaneously select the 2 NTEXT fields 'AS TEXT'?
Thanks.
This is my connection code:
$hostname = "MAIN";
$username = "sa";
$password = "";
$dbName = "NewSQL";
MSSQL_CONNECT($hostname,$u
mssql_select_db($dbName) or DIE("Table unavailable");
$query = "SELECT * FROM Customer";
$result = mssql_query($query);
$numrows = mssql_num_rows($result);
I have found out the problem is down to 2 NTEXT fields in the MSSQL table I am trying to select from. I have found this solution:
mssql_query("SELECT CAST(field1 AS TEXT) AS field1 FROM table;");
Which would solve it - but I want to do a 'SELECT *'.
Can anyone provide me with a better solution or a way of rewriting my currently located solution to 'SELECT *' and simultaneously select the 2 NTEXT fields 'AS TEXT'?
Thanks.
A better approach would be to change the column data type from ntext to text. Unless, of course you are using double-byte characters such as chinese, in which case your select with CAST statement would fail, too.
>>The below line fixed it:<<
Please close the question. See here:
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
>>The below line fixed it:<<
Please close the question. See here:
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The below line fixed it:
$query = "SELECT CusID, OperaRef, CoName, Name, Add1, Add2, Add3, City, PostCode, Tel, Fax, Mobile, Email, Custype, CAST(Notes AS Text) As Notes, SendMail, Status, CAST(Hyperlink AS Text) AS Hyperlink FROM Customer";