ADO.Net ODBCConnection is not returning InfoMessage event for Sybase IQ

[Former Member]

I am using ODBCConnection and trying to get back InfoMessage .. the sql anywhere driver is odbc11.dll. I am not getting the InfoMessage raised, can someone please help?

using System.Data.Odbc;
using System;
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OdbcConnection con = new OdbcConnection("DRIVER={SQL Anywhere 11};Commlinks=TCPIP{host=***.net:3053};SERVERNAME=***;DATABASEName=***;UID=dba;PASSWORD=**;Integrated=NO"))
            {
                con.InfoMessage += InfoMessage_Event;
                con.Open();
                using (OdbcCommand com = new OdbcCommand("Message 'asdfasdgdag' to client", con))
                {
                    OdbcDataReader r = com.ExecuteReader();
                }
            }
        }
        private static void InfoMessage_Event(object sender, OdbcInfoMessageEventArgs e)
        {
            Console.WriteLine(e.Message);
        }
    }
}

[jack_schueler]

You are confusing the SQL MESSAGE statement with InfoMessage evetns.

The InfoMessage event contains an OdbcErrorCollection collection with warnings sent from the data source.

MESSAGE does not generate ODBC warnings. Here is a simple example that illustrates the use of OdbcInfoMessageEventHandler. Start your demo server as follows (using an appropriate path to demo.db):

dbeng11 -n demo11 c:sa11samplesdemo.db

The result of the SQL query is non-deterministic so a warning is returned to your InfoMessage event handler.

    class Program
    {
    static void Main(string[] args)
    {
        using (OdbcConnection con = new OdbcConnection(
                    "DRIVER={SQL Anywhere 11};ServerName=Demo11;UserID=DBA;Password=sql"))
        {
        con.InfoMessage += new OdbcInfoMessageEventHandler(InfoMessage_Event);
        con.Open();
        using (OdbcCommand com = new OdbcCommand("select top 3 * from customers", con))
        {
            OdbcDataReader r = com.ExecuteReader();
        }
        con.Close();
        }
    }
    protected static void InfoMessage_Event(object sender, OdbcInfoMessageEventArgs e)
    {
        Console.WriteLine(e.Message);
    }
    }

[chris_keating]

If you were to use the SQL Anywhere .NET Data Provider, you would get the message as it inherently handles the MESSAGE...TO CLIENT.

For ODBC connections, the MESSAGE ... TO CLIENT requires the message callback to be registered by calling SQLSetConnectAttr with the ASA_REGISTER_MESSAGE_CALLBACK parameter. I have done a cursory check and did not find any mechanism to set this attribute in OdbcConnection.

[Former Member]

Can you please please give me an example c# code to register callback using SQLSetConnectAttribute

[chris_keating]

As I indicated, I could not find a mechanism to set this in OdbcConnection. Is is possible to use the SQL Anywhere .NET Data Provider which handles the MESSAGE...TO CLIENT without additional calls.

[jack_schueler]

The callback would require a call into unmanaged code so you won't find any support for SA_REGISTER_MESSAGE_CALLBACK (I believe callbacks are a SQL Anywhere invention - I've haven't seen this in the MS ODBC driver).