[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2623
  • Last Modified:

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


0
sbornstein2
Asked:
sbornstein2
  • 27
  • 12
  • 11
2 Solutions
 
zadeveloperCommented:
Use nvarchar for all field. This I a Unicode version of varchar. As some polish cars are Unicode the fields need to support it
0
 
sbornstein2Author Commented:
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.
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

Open in new window

0
 
pcelbaCommented:
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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
sbornstein2Author Commented:
damn even this editor is converting my characters wrong.  ughhh
0
 
pcelbaCommented:
Send it as a picture:

Polish.jpg
0
 
zadeveloperCommented:
Have u tried changing to a polish collation on the fields?
0
 
pcelbaCommented:
You should also use proper collation:

Polish2.jpg
0
 
sbornstein2Author Commented:
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.
0
 
sbornstein2Author Commented:
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.
0
 
sbornstein2Author Commented:
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
0
 
pcelbaCommented:
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.
0
 
sbornstein2Author Commented:
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.
0
 
sbornstein2Author Commented:
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?
0
 
pcelbaCommented:
Unicode is a key for international database development.

You should post questions about international characters in web browsers in their appropriate sections because here are probably not experts which could answer it quickly and accurately. Also Oracle has its own zone.

To test polish environment you may simply look in Control panel Regional settings and set it. Additional info is on Microsoft web (e.g. for Vista it is here: http://www.microsoft.com/globaldev/vista/vistahome.mspx)
0
 
sbornstein2Author Commented:
How did you get your image you posted to show the polish characters in the result grid?
0
 
pcelbaCommented:
Print Screen, then paste it to MS Paint, and then cut the part which should be posted.
0
 
sbornstein2Author Commented:
No I mean how did your query show the results of the polish characters and mine is not when you run the query.
0
 
pcelbaCommented:
What font do you use to display query results in a grid?
0
 
zadeveloperCommented:
Check that you have the polish local installed on the sql server (in your regional settings in the control panel)
0
 
pcelbaCommented:
The result also depends on database collation:
Latin1-CP1.jpg
Cech-CI-AS.jpg
0
 
zadeveloperCommented:
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:

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

Open in new window

polishdata.jpg
0
 
sbornstein2Author Commented:
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?
0
 
zadeveloperCommented:
Yes.
Have you got the Polish Local installed on your OS ?
0
 
sbornstein2Author Commented:
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.
0
 
sbornstein2Author Commented:
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.
0
 
pcelbaCommented:
The polish installation is maybe not necessary. Some operating systems have it preinstalled like my Vista even supports Chinese.
0
 
zadeveloperCommented:
in your IDE: After selecting the text - check if your variable holding the polish data is displayed correctly in the quick watch window
0
 
sbornstein2Author Commented:
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
0
 
sbornstein2Author Commented:
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.
0
 
pcelbaCommented:
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.
0
 
zadeveloperCommented:
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 .
0
 
sbornstein2Author Commented:
Łukas for example
0
 
sbornstein2Author Commented:
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.
0
 
pcelbaCommented:
BTW, to test international characters entry you may use http://polish.typeit.org/
0
 
zadeveloperCommented:
could you attach the code anyway please.
0
 
sbornstein2Author Commented:
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" />


0
 
sbornstein2Author Commented:
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" />

0
 
sbornstein2Author Commented:
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.
0
 
zadeveloperCommented:
Am I correct in saying that the variable : test does not display corrtect if you do a quick watch ?
0
 
sbornstein2Author Commented:
this is from the watch window
watch.JPG
0
 
sbornstein2Author Commented:
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.
0
 
sbornstein2Author Commented:
if output the results in PL/SQL to excel it also looks fine.
0
 
sbornstein2Author Commented:
Entity framework XML file shows:

<?xml version="1.0" encoding="utf-8"?>
0
 
zadeveloperCommented:
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);
          } 
      } 
   } 
}         

Open in new window

0
 
sbornstein2Author Commented:
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.
0
 
zadeveloperCommented:
set the field to byte[]
yes, it is requiring a byute[]
0
 
sbornstein2Author Commented:
I am using the entity framework so I will need to setup the other connection using the oracle provider etc.
0
 
sbornstein2Author Commented:
now I get the nvarchar2 is not compatible with Byte.  Oy.. :) I will keep playing
0
 
zadeveloperCommented:
Change to string are check results.
0
 
sbornstein2Author Commented:
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 27
  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now