Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Problem with fetching nVarChars in SQLServer 2008 (Driver: MSSQL)

mssql

  • Please log in to reply
11 replies to this topic

#1 Behzadsh

Behzadsh

    CC Lurker

  • Just Joined
  • Pip
  • 6 posts

Posted 13 December 2010 - 10:30 PM

hey

I'm trying to get some nVarChar data from SQL Server 2008.
But it return ??? for non-Latin string and texts

here is a sample:

$Link = mssql_connect('SQLSERVER', 'user', 'pass');
mssql_select_db('ExampleDB', $Link);

$query = 'SELECT * FROM ExampleTable';
$result = mssql_query($query, $Link);

while ($row = mssql_fetch_assoc($result))
{
print_r($row);
}


any suggestion?

Thanks in Advance
  • 0

#2 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 14 December 2010 - 12:53 AM

You most likely did not set a collation proper to the languages used, you can use the query:
ALTER DATABASE <dbname> COLLATE <colationname>

You can find a list of collations for Microsoft SQL Server here:
SQL Collation Name

You may also set up your varchar columns with their own collations.
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#3 Behzadsh

Behzadsh

    CC Lurker

  • Just Joined
  • Pip
  • 6 posts

Posted 14 December 2010 - 01:26 AM

When I change Collation it returns this:

Warning: mssql_select_db() [function.mssql-select-db]: message: The USE database statement failed because the database collation Persian_100_BIN is not recognized by older client drivers. Try upgrading the client operating system or applying a service update to the database client software, or use a different collation.


I even tried Arabic_BIN, but output is still in strange character
  • 0

#4 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 14 December 2010 - 01:33 AM

Possibly try Persian_100_CI_AS instead, they seemed to just add it in SQL Server 2008, you are lucky!
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#5 Behzadsh

Behzadsh

    CC Lurker

  • Just Joined
  • Pip
  • 6 posts

Posted 14 December 2010 - 01:48 AM

get same warning!
I tried iconv to convert them to utf-8, but it returns some Chinese character.
why in Sql Server Management show them in correct format and encode but not working in php?
what is wrong?
  • 0

#6 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 14 December 2010 - 02:09 AM

The page/your browser may select UTF-8 encoding by default, try setting your page encoding (through meta headers) to a similar persian encoding and see if they display correctly, there are also multibyte functions PHP can use to work with strings, you can read about the MB functions in the manual.
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#7 Behzadsh

Behzadsh

    CC Lurker

  • Just Joined
  • Pip
  • 6 posts

Posted 14 December 2010 - 03:32 PM

The page/your browser may select UTF-8 encoding by default, try setting your page encoding (through meta headers) to a similar persian encoding and see if they display correctly

I tried it still getting question marks - only for string from SQL server

there are also multibyte functions PHP can use to work with strings, you can read about the MB functions in the manual.

I tried them but it worked like iconv and return Chinese character!

I don't what else should do? :(
  • 0

#8 Behzadsh

Behzadsh

    CC Lurker

  • Just Joined
  • Pip
  • 6 posts

Posted 14 December 2010 - 06:32 PM

I changed database collation to SQL_Latin1_General_Cp1256_CI_AS, and convert it to UTF-8 with iconv("CP1256", "UTF-8", $data), and it works; but something strange happened.... It only work with Stored Procedures.

running a simple select query still get "???":confused:

when I was tracing, fetching SP result return strings in CP1256 Unicode (not question mark - some strange character), but fetching mssql_query result return question mark.
What is wrong?! :c-grin:

I should say SQL Server is very weak in UTF-8 support :c-laugh: :c-whistle:
  • 0

#9 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 14 December 2010 - 06:48 PM

Heh, I assume you are entering UTF-8 into the database automatically hense the issue with conversion, try to not convert it from CP* and see if it works in the nvarchar natively, also ensure you are displaying it as UTF8:
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 

You can probably turn collation back to normal, that SHOULD work.
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#10 Behzadsh

Behzadsh

    CC Lurker

  • Just Joined
  • Pip
  • 6 posts

Posted 15 December 2010 - 05:56 AM

This one is not working either . I'm really out of energy and given up with that. :crying:
I rather use MySQL, I think MySQL is more Powerful than SQL Server. I didn't see anything special in SQL Server that MySQL doesn't have. I think MySQL have many useful feature that SQLServer doesn't have, like this one; I never had such problem in MySQL.

MySQL is so Simple and Powerful, I hate Microsoft Productions :glare:
  • 0

#11 DEViANT

DEViANT

    CC Addict

  • Just Joined
  • PipPipPipPipPip
  • 265 posts

Posted 15 December 2010 - 06:01 AM

MySQL is so Simple and Powerful, I hate Microsoft Productions :glare:


Amen to that :c-biggrin:
  • 0

:D You should rep+ me so that I can win :D

My Blog | Ask me!
Error : Satan did it

#12 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 15 December 2010 - 05:11 PM

That is true, MySQL is very simple and has a great deal of community and support behind it, it is an open source project and constantly has features added to it even weekly. Feel free to try it out and report back if it works, we can help you better!
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.






Also tagged with one or more of these keywords: mssql

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download