Big microsoft bug...

  • Thread starter Thread starter Arjan
  • Start date Start date
A

Arjan

Hi!,

Microsoft tried to create a Oracle provider on their own.
Pretty cool and with our trust in Microsoft we immediatly
started using it.

The problem is you can only update / insert standard ASCII
values using parameters. Whenever you try this with
characters like ë, è, Ç, etc the provider updates /
insertes crap.

Unless i am doing something completely stupid, this is a
big bug in the System.Data.OracleClient making it useless
for us to use ( with parameter OracleCommand ). Back to
the ODBC drivers...

I hope somebody can explain to me what i am doing wrong,
or comfirm that this is a big bug indead.
 
Hi,

Actually I used your code from the previous thread.

I created a table with the following statement:

create table mytable
(
idnr integer,
test varchar(10)
);

and inserted a row for the update.

My code was:

System.Data.OracleClient.OracleConnection myConnection =
new System.Data.OracleClient.OracleConnection
("Password=***;User ID=system;Data Source=myTNS;Persist Security
Info=True");

myConnection.Open();
System.Data.OracleClient.OracleCommand myCommand =
myConnection.CreateCommand();

myCommand.CommandText =
"Update mytable set test = " +
":myValue where idnr = :myID";

myCommand.Parameters.Add(":myValue",
System.Data.OracleClient.OracleType.VarChar,10);

myCommand.Parameters.Add(":myID",
System.Data.OracleClient.OracleType.Int32);

myCommand.Parameters[":myValue"].Value = "My problëm.";
myCommand.Parameters[":myID"].Value = 1;
myCommand.ExecuteNonQuery();

myConnection.Close();


I was not able to reproduce your behaviour so the codepage is still just a
guess.

But if you want to keep looking into it:
My server uses the same codepage as my windows (1252. i.e. on Oracle
WE8MSWIN1252). You can check the codepage on oracle with:
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

On the client you can use the c# code:
MessageBox.Show(System.Text.Encoding.Default.CodePage.ToString());

I used:
..NET Framework 1.1
MS Managed provider for Oracle Version 1.0.5000.0
Oracle 9 as Client and DB Server

hth
Uwe

Hi Uwe,

Thank you for your reply. Here is a code example of what i
did:

<snip>

The value "My problëm." will not be updated / inserted
correctly because of the ë character. Instead My
problëëm. will be posted to the database.

I am not allowed to change the Oracle database
characterset. Changing Varchar to NVarchar makes no
difference. By using no parameters i am capable of making
the correct insertion:

myCommand.CommandText = "Update tablename set fieldname
= 'My problëm' where id= 10";

myCommand.ExecuteNonQuery();

That is why i thought the Oracleprovider made the mistake.
After assigning the value to the parameter the watch
expression displays the correct value. Only after
ExecuteNonQuery the value gets messed up.

I would really like to know if you used equal code
statements to perform the update. I will do some more
research to find out more about the charactersets.

Thanks.
 
Thank you very much for going trough all that work for us
Uwe. We found out that the OLEDB provider works without
this "bug" and would probalbly switch over because we
cannot ( are not allowed to ) alter the session
characterset. You have been a great help though!
-----Original Message-----
Hi,

Actually I used your code from the previous thread.

I created a table with the following statement:

create table mytable
(
idnr integer,
test varchar(10)
);

and inserted a row for the update.

My code was:

System.Data.OracleClient.OracleConnection myConnection =
new System.Data.OracleClient.OracleConnection
("Password=***;User ID=system;Data Source=myTNS;Persist Security
Info=True");

myConnection.Open();
System.Data.OracleClient.OracleCommand myCommand =
myConnection.CreateCommand();

myCommand.CommandText =
"Update mytable set test = " +
":myValue where idnr = :myID";

myCommand.Parameters.Add(":myValue",
System.Data.OracleClient.OracleType.VarChar,10);

myCommand.Parameters.Add(":myID",
System.Data.OracleClient.OracleType.Int32);

myCommand.Parameters[":myValue"].Value = "My problëm.";
myCommand.Parameters[":myID"].Value = 1;
myCommand.ExecuteNonQuery();

myConnection.Close();


I was not able to reproduce your behaviour so the codepage is still just a
guess.

But if you want to keep looking into it:
My server uses the same codepage as my windows (1252. i.e. on Oracle
WE8MSWIN1252). You can check the codepage on oracle with:
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

On the client you can use the c# code:
MessageBox.Show (System.Text.Encoding.Default.CodePage.ToString());

I used:
..NET Framework 1.1
MS Managed provider for Oracle Version 1.0.5000.0
Oracle 9 as Client and DB Server

hth
Uwe

Hi Uwe,

Thank you for your reply. Here is a code example of what i
did:

<snip>

The value "My problëm." will not be updated / inserted
correctly because of the ë character. Instead My
problëëm. will be posted to the database.

I am not allowed to change the Oracle database
characterset. Changing Varchar to NVarchar makes no
difference. By using no parameters i am capable of making
the correct insertion:

myCommand.CommandText = "Update tablename set fieldname
= 'My problëm' where id= 10";

myCommand.ExecuteNonQuery();

That is why i thought the Oracleprovider made the mistake.
After assigning the value to the parameter the watch
expression displays the correct value. Only after
ExecuteNonQuery the value gets messed up.

I would really like to know if you used equal code
statements to perform the update. I will do some more
research to find out more about the charactersets.

Thanks.


.
Click to expand...
 
Back
Top