using parameters with char fields

using parameters with char fields

Postby DarrenColes » Tue 09 Aug 2011 09:39

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'
DarrenColes
 
Posts: 47
Joined: Mon 28 Aug 2006 11:07

Postby Shalex » Wed 10 Aug 2011 11:28

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).
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

Postby DarrenColes » Wed 10 Aug 2011 14:58

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

DarrenColes
 
Posts: 47
Joined: Mon 28 Aug 2006 11:07

Postby Shalex » Thu 11 Aug 2011 17:01

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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle