CODEWITHSUNDEEP
wpforacle-databasevisual-studio-2010
Joseph
Joseph

Reputation: 522

ORA-01843 not a valid month in WPF but OK on SQL Developer

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.

Upvotes: 0

Views: 481

Answers (2)

Andy
Andy

Reputation: 12276

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

Upvotes: 1

Phil Jollans
Phil Jollans

Reputation: 3759

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.

Upvotes: 1

Related Questions