sbornstein2
asked on
SQL Server 2008 - can't insert and see polish characters in SQL table
I have a SQL Server 2008 database and I tried inserting this:
insert into MYTABLE
(MY_ID, MY_NAME, MY_NAME_N)
values
(25, 'Zledzie w [mietanie RosóB', 'Zledzie w [mietanie RosóB')
I am using the first MY_NAME as varchar and second MY_NAME_N as nvarchar. When I do a select * I don't see the polish characters. I am trying this as a test to fix something bigger. I am not sure if its the editor with SQL Management Studio or if its really stored correct.
Any help
insert into MYTABLE
(MY_ID, MY_NAME, MY_NAME_N)
values
(25, 'Zledzie w [mietanie RosóB', 'Zledzie w [mietanie RosóB')
I am using the first MY_NAME as varchar and second MY_NAME_N as nvarchar. When I do a select * I don't see the polish characters. I am trying this as a test to fix something bigger. I am not sure if its the editor with SQL Management Studio or if its really stored correct.
Any help
Use nvarchar for all field. This I a Unicode version of varchar. As some polish cars are Unicode the fields need to support it
ASKER
I still dont see the chacrters correct when I select query even from the nvarchar:
the post here is not correct here is what I had.
the post here is not correct here is what I had.
insert into MYTABLE
(MYID, MY_NAME, MY_NAME_N)
values
(25, 'Zledzie w [mietanie RosóB', 'Zledzie w [mietanie RosóB')
You can see the hat on the S and the slash in the l
Now here is the result when I query in SQL Management studio.
FUND_ID FUND_NAME FUND_NAME_N
25 Sledzie w smietanie Rosól Sledzie w smietanie Rosól
You have to insert it this way:
insert into MYTABLE
(MY_ID, MY_NAME, MY_NAME_N)
values
(25, 'Zledzie w [mietanie RosóB', N'Zledzie w [mietanie RosóB')
N prefix causes the string processing as Unicode string. N is not necessary for non Unicode columns.
insert into MYTABLE
(MY_ID, MY_NAME, MY_NAME_N)
values
(25, 'Zledzie w [mietanie RosóB', N'Zledzie w [mietanie RosóB')
N prefix causes the string processing as Unicode string. N is not necessary for non Unicode columns.
ASKER
damn even this editor is converting my characters wrong. ughhh
Have u tried changing to a polish collation on the fields?
ASKER
I am going to try this pcelba but this is odd when I change the column to Polish when I do my insert statement that column does not show up it is there though.
ASKER
it still works though if I insert. Can I copy from the browser from a polish menu where the copy into the editor seems right but the select still pulls it back wrong.
ASKER
if this works then I think it will point me in the right direction. I first just want to be able to see it coming out right. So I changed it to the Polish collate and it looks right under options for the column. I did the insert just copying and pasting from my browser a polish menu item that has the slashed l etc. It looks right in sql management studio insert statement but on the select still coming back without any characters at all
The Polish output needs appropriate font also. You have to use Unicode font or proper font for polish characters. This could also be affected by Windows regional settings where you can set code page for non-unicode programs.
ASKER
pcelba this is great info. What I am really up against is I have an oracle database at work. The database is set to 32UTF and even though Toad shows the data that it doesnt have the characters, if I use PLSQL Developer and hit this little box to open up a popup box I do see the right values. In my web application however I don't see the right characters. So I am trying to attack this at different angles to see what I need to do on the web server and or database but I think the database is okay to handle this.
ASKER
is it possible that if the database shows things right then maybe someone in Poland with the settings on their PC may be able to see the correct characters in the browser you think? Is there anyway I can test that and make my machine look the same?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How did you get your image you posted to show the polish characters in the result grid?
Print Screen, then paste it to MS Paint, and then cut the part which should be posted.
ASKER
No I mean how did your query show the results of the polish characters and mine is not when you run the query.
What font do you use to display query results in a grid?
Check that you have the polish local installed on the sql server (in your regional settings in the control panel)
This is true, however if the local is not on the local server sql will not be able to use the collation.
EG: see screen shot of data, without changing the collation, but having Polish installed:
EG: see screen shot of data, without changing the collation, but having Polish installed:
create table MYTABLE
(
MY_ID int,
MY_NAME varchar (200) ,
MY_NAME_N nvarchar(200)
)
insert into MYTABLE (MY_ID, MY_NAME, MY_NAME_N)
values (25, 'Zledzie w [mietanie RosóB', N'Zledzie w [mietanie RosóB')
insert into MYTABLE (MY_ID, MY_NAME, MY_NAME_N)
values (25, 'Zledzie w [mietanie RosóB', N'!0<K5 ?>?C;O@=K5 B5<K =545;8')
select * from MYTABLE
polishdata.jpg
ASKER
Thanks so much everyone for your help on this one. So my goal is to get this working on my machine based in the US. So I want to simulate what someone in poland will have on their machine to make sure it works. Do I need to place the "N" before the text value to insert each time?
Yes.
Have you got the Polish Local installed on your OS ?
Have you got the Polish Local installed on your OS ?
ASKER
Okay so this is what I have done so far:
The database I am using is Oracle now but it is set to AL32UTF8 at work. I realized I also had to set the NLS_LANG setting in regedit to AMERICAN_AMERICA.AL32UTF8.
So now my values are looking right in PL/SQL developer actually. In my web application however I am seeing for example the l with the slash through it come back as a capital "A" with a little circle over it.
I think I am getting there but not over the hump yet. It seems like you all mention here I need to have the settings on the client and the server. I am setting everything to AL32UTF8 unicode. I also tried the Polish setting in NLS_LANG but still get the same A with circle over it etc.
The database I am using is Oracle now but it is set to AL32UTF8 at work. I realized I also had to set the NLS_LANG setting in regedit to AMERICAN_AMERICA.AL32UTF8.
So now my values are looking right in PL/SQL developer actually. In my web application however I am seeing for example the l with the slash through it come back as a capital "A" with a little circle over it.
I think I am getting there but not over the hump yet. It seems like you all mention here I need to have the settings on the client and the server. I am setting everything to AL32UTF8 unicode. I also tried the Polish setting in NLS_LANG but still get the same A with circle over it etc.
ASKER
In PL/SQL it the values do look correct when I run a query and I see the polish characters. Do I need maybe to set something else or install something else for polish fonts to see it right in my web browser and code I see the same thing coming out of the database.
The polish installation is maybe not necessary. Some operating systems have it preinstalled like my Vista even supports Chinese.
in your IDE: After selecting the text - check if your variable holding the polish data is displayed correctly in the quick watch window
ASKER
okay so next what I did was set my default keyboard to Polish and then used the right ALT key and it types in the characters perfectly with the slash etc. I inserted into the database and it looks correct when I use PL/SQL developer again I see the characters correct. When I retrieve them it still looks screwed up in the browser and in the code behind I have in debug move it makes different characters, It has capital A with the circle over it for the L and lower case l with the slash. It looks right in the the grid view in PL/SQL
ASKER
zadeveloper it does not look right in my variable it also has the A with circle over it. I tried varchar and the nvarchar field.
Client browser should be able to display characters in given charset. EE uses UTF-8 but it seems some Polish characters are not properly encoded ([Z) which is most probably caused by EE database settings.
ok, If using the db manager you can view the data correctly .. then the data is stored correct - dont change that any more ....(It should be nvarchar)
Whcih IDE / Language are you using ?
Could you attache the section of code where you read the data from the DB .
Whcih IDE / Language are you using ?
Could you attache the section of code where you read the data from the DB .
ASKER
Åukas for example
ASKER
actually the code is just a direct query to the database table and then tied to a datagrid. I just added the foreach loop so I could also view it in code.
BTW, to test international characters entry you may use http://polish.typeit.org/
could you attach the code anyway please.
ASKER
I added in my web config globization and also a meta tag in my content page:
requestEncoding="utf-8" responseEncoding="utf-8"
Meta:
<meta http-equiv="content-type" content="text/html;charset =utf-8" />
requestEncoding="utf-8" responseEncoding="utf-8"
Meta:
<meta http-equiv="content-type" content="text/html;charset
ASKER
Its using Entity Framework and a LINQ statement such as:
public List<View> GetViewsAll()
{
using (viewMeta = new ViewMetaEntities())
{
foreach (View vw in viewMeta.Views)
{
string test = vw.Name;
}
List<View> views = (from view in viewMeta.Views
select view).ToList();
return views;
}
}
vw.Name is the field and in entity framework its just setup as:
<Property Name="VIEW_NAME" Type="NVARCHAR2" Nullable="false" MaxLength="50" />
<ScalarProperty Name="Name" ColumnName="VIEW_NAME" />
<Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
public List<View> GetViewsAll()
{
using (viewMeta = new ViewMetaEntities())
{
foreach (View vw in viewMeta.Views)
{
string test = vw.Name;
}
List<View> views = (from view in viewMeta.Views
select view).ToList();
return views;
}
}
vw.Name is the field and in entity framework its just setup as:
<Property Name="VIEW_NAME" Type="NVARCHAR2" Nullable="false" MaxLength="50" />
<ScalarProperty Name="Name" ColumnName="VIEW_NAME" />
<Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
ASKER
So the database seems correct now and the settings look correct on my machine at least that I can see the correct characters in PL/SQL fine with my settings to UTF8. So I think I am down to the application now, I will be glad when this issue is resolved. Plugging away for two weeks on this. Thanks for all your help all.
Am I correct in saying that the variable : test does not display corrtect if you do a quick watch ?
ASKER
this is from the watch window
watch.JPG
watch.JPG
ASKER
yes the watch window and local window are showing the invalid characters in the string. So even though it shows correct in PL/SQL grid view I am still not sure if its storing it correct if thats the issue or the way it comes back. The grid in PL/SQL is showing everything perfect with the L with stroke through it, S with hat etc. The output in the code is showing it as all garbage.
ASKER
if output the results in PL/SQL to excel it also looks fine.
ASKER
Entity framework XML file shows:
<?xml version="1.0" encoding="utf-8"?>
<?xml version="1.0" encoding="utf-8"?>
could you try this:
string sql = " select * view ";
string strconn = "some oracle connection string";
using (OracleConnection conn = new OracleConnection(strconn))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
using (IDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
byte[] byteArray = (Byte[])dataReader["Name"];
var test = Encoding.Ascii.GetString(byteArray);
}
}
}
}
ASKER
I can set in my entity framework that field to a byte or binary or sbyte. But when I go to use the conversion to ASCII its looking for byte[] array I think.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am using the entity framework so I will need to setup the other connection using the oracle provider etc.
ASKER
now I get the nvarchar2 is not compatible with Byte. Oy.. :) I will keep playing
Change to string are check results.
ASKER
thanks guys, I had to do multiple things.
- Change the server to use AL32UTF8 character set
- Add to the connection string on the application side Unicode=True
- Change the font style in grids to Arial Unicode MS
- Change the server to use AL32UTF8 character set
- Add to the connection string on the application side Unicode=True
- Change the font style in grids to Arial Unicode MS