locked
Directly execute a query using ODBC without expecting a result - this to prevent IM001 Driver does not support this function exceptions RRS feed

  • Question

  • Hello,

    I am accessing an unknown and a little old database engine from C# using ODBC. The ODBC driver is from 2004 and is not updated anymore. The driver is working properly and I am able to execute SELECT queries without any problem.

    However, when I execute an INSERT or UPDATE (and probably also a DELETE) query on this database, I get the following message:

    ERROR [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function

    Partly stacktrace:

          at System.Data.Odbc.OdbcDataReader.NextResult(Boolean disposing, Boolean allresults)

          at System.Data.Odbc.OdbcDataReader.NextResult()

          at System.Data.Odbc.OdbcDataReader.FirstResult()

          at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

          at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

          at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

    Looking at the stacktrace I see that the .NET ODBC command is expecting a result from the statement, which is not the case in this database engine. The query is succesfully executed however and the changes made by the INSERT or UPDATE statements are processed.

    I was wondering whether it's possible to execute a query on the database without expecting a result. Just fire the query and when the driver does not return an error, consider it as succesful. If I use MSQuery to execute an UPDATE query, MSQuery just displays 'Executed SQL statement succesfully'.
    In .NET I have now catched the ODBCException and ignore the exception if the message is equal to the messages as stated above. This is not a very nice approach however...

    Thanks in advance for your help!

    Wednesday, January 19, 2011 9:45 AM

Answers

  • Hi Jaap,

    Thanks for your feedback.

    I am afraid that there isn't a way to executing query and without return values. It's designed. I use reflector to watch the source code, There is not methods which can satify your needs in Odbc command.

    public OdbcDataReader ExecuteReader()
    {
      return this.ExecuteReader(CommandBehavior.Default);
    }
    public OdbcDataReader ExecuteReader(CommandBehavior behavior)
    {
      OdbcConnection.ExecutePermission.Demand();
      return this.ExecuteReaderObject(behavior, "ExecuteReader", true);
    }
    private OdbcDataReader ExecuteReaderObject(CommandBehavior behavior, string method, bool needReader, object[] methodArguments, ODBC32.SQL_API odbcApiMethod)
    {
      return target;
    }
    
    

    I think Catch the exception and ignore it is a good way to handle this scenario. Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 21, 2011 5:10 AM

All replies

  •  

    Hi Jaap,

    Welcome to MSDN forums.

    According to your description, I think the problem may relates to the Old ODBC driver.

    OdbcCommand.ExecuteNonQuery Method executes an SQL statement against the connection and returns the number of rows affected. You can refer here: http://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.executenonquery.aspx

     >>>When a trigger exists on a table and data is being added or changed in the table, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers.

    Your database engine may not support this trigger.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 20, 2011 7:13 AM
  • Hi Alan,

    Thanks for your answer. As you can see in the provided stacktrace, I did already use the 'ExeceteReader' method. But what you also can see in this stacktrace is that the base class expects a result (I think he expects the amount of affected records). This result is (due to an old ODBC driver) not available and causing the exception, I gues.

    The query is executed successfully however. My question is whether there is a way that just executes the query and does not expect an answer from the ODBC driver. I do understand that it is an issue due to an old ODBC driver, but since everything works fine I would like to have a way which does not produce an exception.

    Regards,

    Jaap

    Thursday, January 20, 2011 8:34 AM
  • Hi Jaap,

    Thanks for your feedback.

    I am afraid that there isn't a way to executing query and without return values. It's designed. I use reflector to watch the source code, There is not methods which can satify your needs in Odbc command.

    public OdbcDataReader ExecuteReader()
    {
      return this.ExecuteReader(CommandBehavior.Default);
    }
    public OdbcDataReader ExecuteReader(CommandBehavior behavior)
    {
      OdbcConnection.ExecutePermission.Demand();
      return this.ExecuteReaderObject(behavior, "ExecuteReader", true);
    }
    private OdbcDataReader ExecuteReaderObject(CommandBehavior behavior, string method, bool needReader, object[] methodArguments, ODBC32.SQL_API odbcApiMethod)
    {
      return target;
    }
    
    

    I think Catch the exception and ignore it is a good way to handle this scenario. Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 21, 2011 5:10 AM
  • Hi Jaap,

     I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 26, 2011 9:26 AM
  • Hi Alan,

    Thanks for your response.

    I don't like to catch and ignore the exception, but it's the only way I will do it that way.

    Greetings,

    Jaap

     

    Wednesday, January 26, 2011 9:29 AM