Calling Oracle date functions via ODBC and .NET

  • Thread starter Thread starter Richard Morse
  • Start date Start date
R

Richard Morse

Hi! I'm using ODBC to connect to an Oracle database. I want to do
something like the following:

---------

var conn: OdbcConnection = new OdbcConnection("my connect string here");

var sql: String = "update mytable set col1=?, col2=? where keycol=?";
var command: OdbcCommand = new OdbcCommand(sql, conn);

command.Parameters.Add("@col1", OdbcType.Int, 22).Value = 1;
if (some_test) {
command.Parameters.Add("@col2", OdbcType.Date, 7).Value = 'SYSDATE';
} else {
command.Parameters.Add("@col2", OdbcType.Date, 7).Value = null;
}
command.Parameters.Add("@keycol", OdbcType.Int, 22).Value = my_key;

command.ExecuteNonQuery();

--------

My question is, how do I do that second parameter? How can I tell it to
call SYSDATE, or set the value to null? I admit that I haven't tried
this code, because I don't think that the string 'SYSDATE' is a valid
date object. Should it work?

Thanks,
Ricky Morse
 
¤ Hi! I'm using ODBC to connect to an Oracle database. I want to do
¤ something like the following:
¤
¤ ---------
¤
¤ var conn: OdbcConnection = new OdbcConnection("my connect string here");
¤
¤ var sql: String = "update mytable set col1=?, col2=? where keycol=?";
¤ var command: OdbcCommand = new OdbcCommand(sql, conn);
¤
¤ command.Parameters.Add("@col1", OdbcType.Int, 22).Value = 1;
¤ if (some_test) {
¤ command.Parameters.Add("@col2", OdbcType.Date, 7).Value = 'SYSDATE';
¤ } else {
¤ command.Parameters.Add("@col2", OdbcType.Date, 7).Value = null;
¤ }
¤ command.Parameters.Add("@keycol", OdbcType.Int, 22).Value = my_key;
¤
¤ command.ExecuteNonQuery();
¤
¤ --------
¤
¤ My question is, how do I do that second parameter? How can I tell it to
¤ call SYSDATE, or set the value to null? I admit that I haven't tried
¤ this code, because I don't think that the string 'SYSDATE' is a valid
¤ date object. Should it work?

The SYSDATE function will not work in this instance since Oracle will not recognize it as a valid
parameter value. It's evaluated as a text (varchar) value and not a date value when passed as a
parameter argument.

Since you're using SQL command text you can simply hard code the SYSDATE function within the SQL
statement. If the value is to be null then it should be omitted from the SQL statement altogether.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top