When I create an OdbcCommand and add parameters, parameters that are of VarChar 4000 are translated into text type in generated sql. My sample code is:
OdbcConnection dbConnection = new OdbcConnection("DSN=database;UID=uid;PWD=pwd;");
try
{
dbConnection.Open();
OdbcCommand odbcCommand = new OdbcCommand();
odbcCommand.CommandText = "UPDATE Table SET Notes = ? WHERE Notes = ?";
odbcCommand.Connection = dbConnection;
odbcCommand.Parameters.Add("", OdbcType.VarChar, 3999, "");
odbcCommand.Parameters.Add("", OdbcType.VarChar, 4000, "");
odbcCommand.Parameters[0].Value = "0";
odbcCommand.Parameters[1].Value = "1";
odbcCommand.ExecuteNonQuery();
}
finally
{
dbConnection.Close();
}
This is the generated SQL, that I got from SQL Server Profiler
exec sp_executesql N'UPDATE Table SET Notes = @P1 WHERE Notes = @P2',N'@P1 varchar(3999),@P2 text','0','1'
Even though MSDN does not say anything about maximum sizes for VarChar
here. Does anyone know why this happens and if this is configurable?
I believe it's a OdbcCommand specific thing because the update SQL statement that Simple Data generates for other field that is stored as varchar(4000) in the SQL Server database is generated correctly.