0

I'm migrating from Oracle to Tibero database using C#.

Issue is when adding parameter of "CHAR" type using OleDbParameter, data can not be retrieved unless it is filled with following spaces according to DB column size.

(Using OracleParameter or using OdbcParameter, it is possible to query normally without filling empty space as the actual column size)?

String sSQL = @"SELECT *
                          FROM V_PROD_MA
                         WHERE CAR_TYPE = :CAR_TYPE
                           AND BODY_NO  = :BODY_NO";

        OleDbDataAdapter adapter = null;
        //OracleDataAdapter adapter = null;
        DataTable         table   = new DataTable();

        try
        {
            Open();

            adapter = new OleDbDataAdapter(sSQL, Connetion);

            adapter.SelectCommand.Parameters.Add("CAR_TYPE", OleDbType.Char, 4).Value = sCarType;
            adapter.SelectCommand.Parameters.Add("BODY_NO", OleDbType.Char, 6).Value = sBodyNo;

            adapter.Fill(table);
        }

Above is a test source code and when I set sCarType = 'D0F ' then data is retrieved, but when I set sCarType = 'D0F' data is not retrieved.

Actual column size is char(4).

You might suggest to change the SQL WHERE statement like trim(car_type), but we have about 2,000 SQL statements to change, so we want to avoid that kind of solution.

And also we use a framework for wrapping each SQL command, parameters and the framework doesn't have information of column size to fill with empty spaces.

Is there any other way to avoid this kind of issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
justin.kim
  • 1
  • 1
  • You could complain to your database vendor. ANSI SQL mandates that `'D0F '` and `'D0F'` compare equal (e.g. trailing spaces are ignored) precisely because of this issue. Oracle respects this, so does SQL Server. (It can cause other surprises, of course.) Tibero might have a setting that governs this (I don't know). Even if the parameter were passed as a `CHAR(3)`, in the comparison it should be padded by promotion to `CHAR(4)` (the type of the column). Does this query work if you use a literal `'D0F'`? If so, the error is in the DB driver; if not, it's in the engine itself. – Jeroen Mostert Nov 07 '18 at 10:40
  • thanks. it works with ‘D0F’ when query using DB tool that uses JDBC. maybe there’s some configuration to make it work on OleDB – justin.kim Nov 09 '18 at 09:03

0 Answers0