Calling Oracle date functions via ODBC and .NET

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
 
P

Paul Clement

¤ 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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top