Calling Oracle date functions via ODBC and .NET

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

Paul
Fri Feb 20 09:07:18 CST 2004

On Thu, 19 Feb 2004 16:31:28 -0500, Richard Morse <remorse@partners.org> wrote:

¤ 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 ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)