using parameters with char fields

[DarrenColes]

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'

[Shalex]

I cannot reproduce the problem with dotConnect for Oracle v 6.30.196 using the following code:

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();
    }

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).

[DarrenColes]

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.

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();
    }

[Shalex]

This is a designed behaviour because there is no round-trip to the sever. OracleParameter.OracleDbType is assigned to a default datatype basing on the value that you have set in OracleParameter.Value.