Inserting special characters into MSSQL ...

[budimir]

Hey guys,

 

I have a question about inserting special characters into MSSQL from PHP.

 

I'm from Croatia and our language has special characters like "ščćđž". I have setup collation for a MSSQL server as Croatian_CI_AI, but when I run my script the data in DB looks really strange. There is no special characters "čćšđž" in DB but some strange characters.

 

Did anyone had a problem with this??? Is there any solutions???

[crtreedude]

If the collation is correct, then your problem probably isn't mssql, it is whatever you are using to display the result of your query isn't setup to handle unicode and has the character set to support your language.

 

I did a lot of work with foreign databases, and the mssql side is very simple, but the user interface as to support the characters.

 

Just to be clear, storage is one thing, display is another. mssql only deals with storage.

[budimir]

I'm sorry, I didn't mention that I'm storing the data to MSSQL.

 

I'm looking the data directly in the DB. Not through the script.

 

And still problems...

[corbin]

What are you using to look at the DB?

[budimir]

SQL Server Managment Studio

 

I open DB, find table which I need, click right click and Open Table.

 

Then I look at the results that were stored with the PHP script.

[corbin]

Where is the data coming from, like in the PHP script.

[crtreedude]

SQL Server Managment Studio

 

I open DB, find table which I need, click right click and Open Table.

 

Then I look at the results that were stored with the PHP script.

 

Try typing in directly into a field and then reading it back, I bet you find that the Management Studio is not setup to show your UNICODE.

 

I know the Query Analyzer never did - to see the characters, I had to use a program I wrote.

 

 

[budimir]

@ corbin:

 

Data is coming from the php script I wrote. Do I need to put in formmating of that data???? Like to use UTF-8 or ISO or WIN????

 

@ crtreedude:

 

I tried typing in directly to SQL Server Managment Studio and everything is OK. But if I run my PHP script I get a lot of shit characters in the DB.

 

I'm not sure why my PHP script is not putting the correct data to MSSQL DB!

[corbin]

Make sure PHP is encoding it correctly.

 

 

What version of PHP is it?

[budimir]

How should I encode it so it could work with the MSSQL???

(I have tried UTF-8, ISO, WIN, but nothing worked ... Maybe I missed something...)

 

UTF-8, ISO , WIN ... ??

 

It's PHP 5.1

[Mchl]

I've just spend some time connecting to MSAccess through ODBC... gah... I am so glad I only have do SELECTS from this one...

[corbin]

How should I encode it so it could work with the MSSQL???

(I have tried UTF-8, ISO, WIN, but nothing worked ... Maybe I missed something...)

 

UTF-8, ISO , WIN ... ??

 

It's PHP 5.1

 

 

You will have to encode it into the same encoding as the column.  Or, an easier way would be to encode it in something standard (like Unicode 16 or something), and cast it into the column type in the query.

[budimir]

@ Corbin:

 

I'm not too sure I understood what you mean. Can you give me a short example of code, so I could understand it.

 

How do you mean encode it into the same encoding as the column?

[corbin]

Wow, I just googled for like 30 minutes and couldn't figure it out.  I get the feeling that PHP support of non ASCII values in MSSQL is very minimal.  The MSSQL extension wouldn't work, and neither would ODBC.  I wonder if there's a MSSQL version of MySQL's SET NAMES command.  Google turned up nothing.

 

 

 

So, I can't provide sample code, or code of any kind, since I have no idea.  I've used MSSQL quite a bit, but never with non-ASCII data.

[Mchl]

I can just tell, I've found nothing like that for ODBC. That's why I'm glad I'm limited only to SELECTs. And even that is tough, cause there are some non standard characters in table names...

 

I tried also changing encoding of queries via mb_strings, but to no avail... And I tried several codepages...

(BTW: budimir, It didn't help me, but you might try it nonetheless.. Access is not MSSQL, so maybe it'll help)

[budimir]

Thanks a lot for you're effort, guys.

 

MSSQL is new to me and I don't know much about it.

 

If I find any solutions to this, I will post here in case someone else will have the same problem as I do.

 

Once again, thanks a lot.

 

[ScArides]

For every poor soul trying to find a solution like i did and finding this forum:

 

I have a html form.

Users write something in it (name, company name, ..)

The form gets sent to a php page which writes the values into a database.

All special chars get screwed up into trash.

 

My solution:

After going crazy with all the encodings, i wrote my own encoding. Here is a sample of what you have to do (it's not that hard):

1) You write a javaScript that activates when you click the submit button. This script will then go through all the form fields and replace all the special chars with something like #01 #02 etc. Then it will send the form. So if you have a text 'kůň', it can translate into something like 'k#11#15'

 

2) Your php script that accepts that form must decode this back - then you can write it into the database and be fine. I found out that most of the problems are caused by the html forms, not the scripts or databases themselves. This will work around that problem.

 

Sample code:

JavaScript:

 

function encode() {

var result = '';

var temp = document.getElementById(myFormTextField).value; //Read value of the TextBox

for (var k=0; k<temp.length; k++) {             //Go through the value one character at a time

var currentChar = temp.substring(k,k+1);

if (currentChar == 'š') {     //Replace all special characters with our coding sequence

result += '#01';

} else if (currentChar == 'Š') {

result += '#02';

} else if (currentChar == 'č') {

result += '#03';

} else if (currentChar == 'Č') {

result += '#04';

} else {         //Leave standard characters as they are

result += currentChar;

}

}

document.getElementById(myFormTextField).value = result;  //Write the encoded text back into the TextBox

}

 

 

Decoding (written in java, but you'll get the idea and re-write it to php)

public String decode(String source) {

String result = "";

for (int i=0; i< source.length(); i++) {

String piece = source.substring(i,i+1);

if (piece.equals("#")) { //If we detect '#', we take a chunk of 3 characters

String chunk = source.substring(i,i+3);  // and decode the chunk

i+=2;

if (chunk.equals("#01")) {

result += "š";

} else if (chunk.equals("#02")) {

result += "Š";

} else if (chunk.equals("#03")) {

result += "č";

} else if (chunk.equals("#04")) {

result += "Č";

}

} else { //If there is no '#' char, we simply read 1 character

result += piece;

}

}

return result;

}