Search code examples
c#db2odbcdb2-luw

Using db2 odbc parameters with Timestamp C#

I am trying to use parameters for a DB2 query using ODbc in C#, but the Timestamp field is giving me issues. I am using DB2 Client 9.7 and updateDate is a DateTime type. Here is my code:

string commandString = "INSERT INTO DATABASENAME(USERNAME, UPDATE_DATE) VALUES ('?', TIMESTAMP_FORMAT('?', 'YYYY-MM-DD HH24:MI:SS'))";

OdbcConnection con = new OdbcConnection(conString);
OdbcCommand command = new OdbcCommand(commandString, con);

command.Parameters.AddWithValue("?username", userName);
command.Parameters.AddWithValue("?update_date", updateDate.ToString("yyyy-MM-dd HH:mm:ss"));

//execute the stuff
con.Open();
command.ExecuteNonQuery();
con.Close();

This is the error I am receiving:

ERROR [42815] [IBM][CLI Driver][DB2] SQL0171N  The data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.TIMESTAMP_FORMAT" is incorrect. Parameter name: "".  SQLSTATE=42815

I have also tried using the regular TIMESTAMP() function included in DB2 using both formats it accepts (Ex. TIMESTAMP ('2016-10-20-12.00.00.000000') TIMESTAMP ('2016-10-20 12:00:00')), but that gives me this error:

ERROR [22007] [IBM][CLI Driver][DB2] SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

Anyone know where I am going wrong?

EDIT: It works without using parameters, can parameters not be used with DB2?

Solution

  • I have no way of testing this but I think you should not enclose parameter markers in quotes, they are parameters, not literals. Your statement should look like

    string commandString = "INSERT INTO DATABASENAME(USERNAME, UPDATE_DATE) VALUES (?,?)";

    and then you also won't need double conversion from DateTime to string to TIMESTAMP:

    command.Parameters.AddWithValue("?username", userName);
command.Parameters.AddWithValue("?update_date", updateDate);