Could you please tell me whether the following is a known/expected behaviour or if i have found a bug in your product. The issue relates to using CHAR fields and passing a parameter into a query that is shorter than the length of the CHAR field.
If i set up a table as follows
create table USERS
(
USER_ID VARCHAR2(6),
ACCESS_PIN CHAR(8)
)
and do
insert into users (user_id,access_pin) values ('111111','123456');
(ie I am inserting a record where access_pin only contains 6 characters into a CHAR(8) so it will get padded).
I then get different results from each of the two following queries:
(this one returns no records)
select * from users where access_pin = :acc_code
and pass in '123456' as the parameter
and
(this one returns 1 record)
select * from users where access_pin = '123456'
using parameters with char fields
I cannot reproduce the problem with dotConnect for Oracle v 6.30.196 using the following code:
Tell us your current version (x.xx.xxx) of dotConnect for Oracle and how we should modify the code above to reproduce the issue in our environment. Also please post your connection string (roughly, without credentials).
Code: Select all
using (OracleConnection conn = new OracleConnection()) {
//conn.ConnectionString = "server=***;uid=***;pwd=***;";
conn.ConnectionString = "Direct=true;server=dboracle;SID=***;uid=***;pwd=***;";
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from users where access_pin = :acc_code";
cmd.Parameters.Add("acc_code", OracleDbType.Char).Value = "123456";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
Console.WriteLine(reader[0].ToString());
}
Console.ReadKey();
}
-
- Posts: 51
- Joined: Mon 28 Aug 2006 11:07
I'm using version 6.10
We are using the ParameterCheck = true option rather than adding the parameters manually. This seems to result in the parameter type being set to Varchar rather than Char so I'm guessing this is the cause of the problem.
We cannot add the parameter types manually in our application because it has no way of knowing it needs to be a char when it runs the query.
We are using the ParameterCheck = true option rather than adding the parameters manually. This seems to result in the parameter type being set to Varchar rather than Char so I'm guessing this is the cause of the problem.
We cannot add the parameter types manually in our application because it has no way of knowing it needs to be a char when it runs the query.
Code: Select all
using (OracleConnection conn = new OracleConnection()) {
conn.ConnectionString = "server=***;uid=***;pwd=***;";
//conn.ConnectionString = "Direct=true;server=dboracle;SID=***;uid=***;pwd=***;";
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from users where access_pin = :acc_code";
cmd.ParameterCheck = true;
cmd.Prepare();
cmd.Parameters["acc_code"].Value = "123456";
//cmd.Parameters.Add("acc_code", OracleDbType.Char).Value = "123456";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
Console.WriteLine(reader[0].ToString());
}
Console.ReadKey();
}