0

When I call an Oracle function in a WPF app passing in a date like:

var getRateQry = new StringBuilder();
getRateQry.Append("SELECT fn_get_rate(");
getRateQry.Append(string.Format("TO_DATE('{0}','MM/DD/YYYY HH24:MI:SS')", implemStartDt.ToString(System.Globalization.CultureInfo.InvariantCulture)));
getRateQry.Append(") DUMMY FROM DUAL");

I am returning an ORA-01843 error in OleDB.ExecuteReader(). I pass the date as 04/01/2018 16:00:00 for instance.

But when running a T-SQL query with TO_DATE in the where clause, it works just fine.

I am using VS2010 (set to Win7 compatibility mode since my OS is Win10). What is weird also is that my other colleagues having the same workstation setup doesnt have problems as mine.

I am stuck as I don't know what I have installed last for me to encounter this problem.

UPDATE # 1: My language settings in my workstation is set in English(United States) but I dont think it is related.

When I uninstalled OracleXE (64-bit). Take note: 64-bit. That is when I can run the query in the code correctly. I'll try to install the 32-bit edition as I need it to run SQL Developer and see if the problem will still persist.

Lesson learned: Do not just install 64-bit products just because your OS is a 64-bit.

UPDATE # 2: I can still run the query in code even though OracleXE 32-bit is installed. I am still amazed as to what causes the error if OracleXE 64-bit is installed.

But anyway, I'll let this setup be it for now as this is working for my purpose.

Joseph
  • 502
  • 4
  • 15

2 Answers2

1

You're building your query as a string. This is a really bad idea since it opens you up to sql injection as well as this sort of problem. You should instead write a parameterised query and pass the date parameter as datetime rather than part of a string.

You should probably read up on this more, but here's an example thread contains some code to think about:

Why do we always prefer using parameters in SQL statements?

I suggest also that you look into an orm such as Entity Framework or Dapper.

As it is I would check the culture settings on your machine. https://www.windowscentral.com/how-change-date-and-time-formats-windows-10

Andy
  • 11,864
  • 2
  • 17
  • 20
  • appreciated your answer and concern about SQL injection attacks. however, I can't edit this part of my code since this is production code and am not required to edit it. And as I said, in my other colleagues workstations, they can call the function without problems. So it's up to me to check on my installations on what is causing this problem. – Joseph Apr 04 '18 at 03:29
  • Your culture settings must be different somehow. – Andy Apr 04 '18 at 07:47
  • Weird. Your team should still be using parameterised queries. Even if you currently have some weird architecture where just a string is passed. – Andy Apr 05 '18 at 08:58
  • we will consider that soon as our app isn't exposed to Internet anyway. – Joseph Apr 05 '18 at 11:08
1

This is basically the same as Andy's answer. You should try to build your query as a command object with a date parameter.

There are lots of ways of doing database access and I don't know how you are doing it. With OleDb, it might look something like this.

OleDbConnection db ;

// Get or open connection 
OleDbCommand cmd = db.CreateCommand();
cmd.CommandText = "SELECT fn_get_rate(TO_DATE('{?}') DUMMY FROM DUAL";
cmd.Parameters.Add("@P1",OleDbType.DBTimeStamp).Value = implemStartDt ;

OleDbDataReader rdr = cmd.ExecuteReader();

This still has an SQL statement, but a ? as placeholder for the parameter.

At a lower level, the date will be formatted and inserted into the SQL statement, but you don't have to handle the details.

And as Andy said, you are better protected against SQL injection attacks, even if this not really an issue in your application.

Phil Jollans
  • 3,605
  • 2
  • 37
  • 50
  • appreciated your answer and concern about SQL injection attacks. however, I can't edit this part of my code since this is production code and am not required to edit it. And as I said, in my other colleagues workstations, they can call the function without problems. So it's up to me to check on my installations on what is causing this problem. – Joseph Apr 03 '18 at 10:18