CODEWITHSUNDEEP
phpsql-serverunicode
GMA
GMA

Reputation: 6096

SQL Server: "Unicode data in a Unicode-only collation or ntext data..." issue, but can't change FreeTDS version

I'm having a familiar issue when trying to SELECT columns of type nvarchar from my SQL Server Database using PHP:

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.

I know that I can workaround this using SELECT CAST(columnName as TEXT) as columnName from tableName... but that's hacky and annoying. Plus Adminer won't work as long as there's this error, and I can't configure Adminer to CAST its column names.

In similar questions and in the PHP docs many people have mentioned that you can fix this problem by changing the file /etc/freetds.conf/... but I'm on Windows, not *nix, so no such file exists. (I contacted support at my hosting company to confirm this.)

Is there any way I can select nvarchar columns without having to add the annoying and tedious CAST boilerplate every time? And to get Adminer to work?

Or is my only option to switch to a different driver than DB-Library?

Upvotes: 1

Views: 8097

Answers (1)

Chand Prakash
Chand Prakash

Reputation: 196

Windows solution:- You can always use Windows drivers for connecting to Mssql datasbase.(The Native drivers for windows are available and are not hard to install I belive. I can send that if it is that required.)

I have the solution for making connection to mssql when your machine is Linux based (I would specifically say Centos). The mistakes I was making was I didn't configure the FreeTds that was located in /etc/freetds.conf it will not be here in case of Ubuntu systems.

Check the line that says "A typical windows system" configure that part Please check this below one

[connectionname]
host = **nameOrIpofTheServer**
port = 1433
tds version = 8.0
client charset = utf-8

Use the connection name in your code. Note if you want these settings to work use connection name The connection object the PDO will look something like this:-

$dbh = new PDO ("dblib:host=**$hostname**:$port;dbname=$dbname","$username","$pw"); //

General one 

$dbh = new PDO ("dblib:host='**connectionname**':$port;dbname=$dbname","$username","$pw");

//actual connnection with connection name Now try to select data from your mssql database having UTF-8 characters. I will work.

Next issue that you will face while working on this environment will be:- Saving the UTF-8 Characters to database. A simple but complex solution you will have to prefix N in front of database Query where you will be assigning value to the field. For Example:- UPDATE [users] SET [Address]=N'большой' WHERE [ID]= 1; Here Address is UTF-8 Field. Like NTEXT, NCHAR and NVARCHAR.

Upvotes: 2

Related Questions