Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Informix CDC API - Is there a way to pull the change record using .NET?

    Posted Tue July 29, 2025 09:20 AM

    Hi,

    I'm trying to work with the Informix CDC API using the Informix .NET provider, but I haven't been able to find any sample code, and the documentation has been difficult to follow.

    I've put together the following code sample, but it doesn't seem to be retrieving any change records.

    The most relevant documentation I've found is here:  

    https://www.ibm.com/docs/en/informix-servers/12.10.0?topic=api-smart-large-object-read-functions


    My test code is as below:

    using IBM.Data.Informix;
using System;
using System.Data;
using System.Data.Odbc;
using System.IO;

namespace IfxOdbcOledb
{
    class Class1
    {

        static string _DbConnString = "User Id=informix;Password=xxx;" +
        "Host=localhost;Server=ol_informix1410;" +
        "Service=9090;Database=syscdcv1";


        [STAThread]
        static void Main(string[] args)
        {

            IfxConnection IfxCon = new IfxConnection(_DbConnString);

            //execute function informix.cdc_opensess() // on the same connection 
            //IfxBlob(IfxConnection connection)// construct it using the same connection 
            //IfxBlob.Open(ReadOnly) // open it 
            //IfxBlob.Read(long plofd, byte[] buff)

            IfxCommand Command = new IfxCommand();
            try
            {
                IfxCon.Open();

                Command.Connection = IfxCon;

                Command.CommandText = "EXECUTE FUNCTION syscdcv1:informix.cdc_opensess('ol_informix1410',0,0,10,1,1);";
                Command.CommandType = CommandType.Text;
                object result = Command.ExecuteScalar();

                long session_id = Convert.ToInt64(result);

                Command.CommandText = "EXECUTE FUNCTION  syscdcv1:informix.cdc_set_fullrowlogging('testdb:informix.claims_history',1);";
                Command.CommandType = CommandType.Text;
                Command.ExecuteNonQuery();


                Command.CommandText = "execute function syscdcv1: informix.cdc_startcapture(?, 0, 'testdb:informix.claims_history', 'customer,clm_no,dte_serv', 1)";
                Command.Parameters.Clear();
                Command.Parameters.Add("?", OdbcType.VarChar).Value = session_id;
                Command.ExecuteNonQuery();


                Command.CommandText = "EXECUTE FUNCTION  syscdcv1:informix.cdc_activatesess(?, 0)";
                Command.CommandType = CommandType.Text;
                Command.Parameters.Clear();
                Command.Parameters.Add("?", OdbcType.VarChar).Value = session_id;
                Command.ExecuteNonQuery();


                // make data change
                Command.CommandText = "update testdb:informix.claims_history  set dte_serv = CURRENT where hpolicy = 'HP123458'";
                Command.CommandType = CommandType.Text;
                Command.Parameters.Clear();
                Command.ExecuteNonQuery();


                IfxBlob ifxblob = new IfxBlob(IfxCon);
                ifxblob.Open(IfxSmartLOBOpenMode.ReadOnly);

                const int bufferSize = 8192; // 8 KB buffer is a common size.
                byte[] buffer = new byte[bufferSize];
                long bytesRead = 0;


                while ((bytesRead = ifxblob.Read(buffer)) > 0)
                {
                    // Write the chunk of data from our buffer into the file.
                    //fileStream.Write(buffer, 0, bytesRead);
                    Console.WriteLine($"Read {bytesRead} bytes...");
                }


                Console.WriteLine("test...");

                Command.CommandText = "execute function syscdcv1: informix.cdc_endcapture(?, 0, 'testdb:informix.claims_history')";
                Command.Parameters.Clear();

                Command.Parameters.Add("?", OdbcType.VarChar).Value = session_id;
                Command.ExecuteNonQuery();
                
                Command.CommandText = "EXECUTE FUNCTION  syscdcv1:informix.cdc_set_fullrowlogging('testdb:informix.claims_history',0);";
                Command.Parameters.Clear();
                Command.CommandType = CommandType.Text;
                Command.ExecuteNonQuery();


                Command.CommandText = "EXECUTE FUNCTION  syscdcv1:informix.cdc_closesess(?)";
                Command.CommandType = CommandType.Text;
                Command.Parameters.Clear();
                Command.Parameters.Add("?", OdbcType.VarChar).Value = session_id;
                Command.ExecuteNonQuery();

                Console.ReadLine();

                IfxCon.Close();
            }
            catch (Exception excp)
            {
                Console.WriteLine(excp.StackTrace);
            }

        }

    }
}

    Any guidance or working examples would be greatly appreciated.



    ------------------------------
    George Wen
    ------------------------------


  • 2.  RE: Informix CDC API - Is there a way to pull the change record using .NET?

    Posted Tue July 29, 2025 10:20 AM

    Not an expert in .NET at all, but to me this looks like it's missing the link between the "session_id" and the LO read:  session_id actually is a smart blob LO fd that you'd read from using ifx_lo_read() - or however this is called in .NET.

    I'd recommend looking at the cdcapi.ec esql/c demo in (server's) $INFORMIXDIR/demo/cdc, but I have a feeling that's what you modeled your .NET code after anyway ;-)

    HTH,
     Andreas



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 3.  RE: Informix CDC API - Is there a way to pull the change record using .NET?

    Posted Tue July 29, 2025 06:45 PM

    Thanks for the response, Andreas.

    You are correct regarding the Java SDK, which supports the IfxSmartBlob object with a session_id. However, the .NET SDK doesn't provide this object.

    Additionally, the code snippet in the documentation here:
    https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=api-smart-large-object-read-functions

    …is not fully consistent with the .NET SDK documentation:
    https://www.ibm.com/docs/en/informix-servers/15.0.0?topic=SSGU8G_15.1.0/com.ibm.netpr.doc/ids_net_043.html

    As a result, I'm really stuck at this point and unable to move forward.



    ------------------------------
    George Wen
    ------------------------------

    Original Message


  • 4.  RE: Informix CDC API - Is there a way to pull the change record using .NET?

    Posted Wed August 06, 2025 06:25 AM

    Hi,

    Try something like:

    using System;
using System.Data;
using IBM.Data.Informix;
using System.Text;


class sample
{

    public static string HexDump(byte[] bytes)
    {
        if (bytes == null) return "<null>";
        int len = bytes.Length;
        StringBuilder result = new StringBuilder(((len + 15) / 16) * 78);
        char[] chars = new char[78];
        // fill all with blanks
        for (int i = 0; i < 75; i++) chars[i] = ' ';
        chars[76] = '\r';
        chars[77] = '\n';

        for (int i1 = 0; i1 < len; i1 += 16)
        {
            chars[0] = HexChar(i1 >> 28);
            chars[1] = HexChar(i1 >> 24);
            chars[2] = HexChar(i1 >> 20);
            chars[3] = HexChar(i1 >> 16);
            chars[4] = HexChar(i1 >> 12);
            chars[5] = HexChar(i1 >> 8);
            chars[6] = HexChar(i1 >> 4);
            chars[7] = HexChar(i1 >> 0);

            int offset1 = 11;
            int offset2 = 60;

            for (int i2 = 0; i2 < 16; i2++)
            {
                if (i1 + i2 >= len)
                {
                    chars[offset1] = ' ';
                    chars[offset1 + 1] = ' ';
                    chars[offset2] = ' ';
                }
                else
                {
                    byte b = bytes[i1 + i2];
                    chars[offset1] = HexChar(b >> 8);
                    chars[offset1 + 1] = HexChar(b);
                    chars[offset2] = (b < 32 ? '·' : (char)b);
                }
                offset1 += (i2 == 8 ? 4 : 3);
                offset2++;
            }
            result.Append(chars);
        }
        return result.ToString();
    }

    private static char HexChar(int value)
    {
        value &= 0xF;
        if (value >= 0 && value <= 9) return (char)('0' + value);
        else return (char)('A' + (value - 10));
    }



    static string checkerror(IfxConnection conn, string func, int ret)
    {

        string rets = func;

        if (ret < 0)
        {
            IfxCommand cmmd = conn.CreateCommand();
            cmmd.CommandText = "select * from syscdcerrcodes where errcode=" + ret.ToString();
            IfxDataReader drdr = cmmd.ExecuteReader();
            drdr.Read();
            rets = " Error: " + drdr.GetString(0) + " " + drdr.GetString(1) + " " + drdr.GetString(2);
            drdr.Close();
            return rets;
        }
        return rets + " OK";

    }

    static void Main(string[] args)
    {

        IfxConnection conn;
        IfxBlob vblob;
        int maxSize = 512;
        IfxDataReader drdr;
        IfxCommand cmmd;
        long  ssid = 0;

        byte[] vblobBuff = new byte[maxSize];

        try
        {

            conn = new IfxConnection("Server=ids1210;Database=syscdcv1;User ID=informix; password=ximrofni;DB_LOCALE=en_US.819;");
            conn.Open();

            cmmd = conn.CreateCommand();
            cmmd.CommandText = "execute function informix.cdc_opensess('ids1210',0,-1,100,1,1)";
            drdr = cmmd.ExecuteReader();
            drdr.Read();
            ssid = drdr.GetInt32(0);
            drdr.Close();
            Console.WriteLine("ssid " + ssid.ToString());
                  

            cmmd.CommandText = "execute function cdc_set_fullrowlogging('stores7:informix.t1',1);";
            drdr = cmmd.ExecuteReader();
            drdr.Read();
            Console.WriteLine(checkerror(conn, "cdc_set_fullrowlogging", drdr.GetInt32(0)));
            drdr.Close();

            cmmd.CommandText = "execute function cdc_startcapture(" + ssid + ",0,'stores7:informix.t1','c1',1)";
            drdr = cmmd.ExecuteReader();
            drdr.Read();
            Console.WriteLine(checkerror(conn, "dc_startcapture", drdr.GetInt32(0)));
            drdr.Close();

            cmmd.CommandText = "execute function cdc_activatesess(" + ssid + ",0)";
            drdr = cmmd.ExecuteReader();
            drdr.Read();
            Console.WriteLine(checkerror(conn, "cdc_activatesess", drdr.GetInt32(0)));
            drdr.Close();

            Console.WriteLine("Do something with stores7.informix.t1");

//          IfxSmartLOBLocator test = new IfxSmartLOBLocator("");
            vblob = new IfxBlob(conn);             
            vblob.Open(IfxSmartLOBOpenMode.ReadOnly);
			
			while (true) {
            long readed = vblob.Read(ssid, vblobBuff);
            //long readed=vblob.Read(vblobBuff, 0, maxSize, 0, IfxSmartLOBWhence.Current);
            if (readed>0)
                Console.WriteLine(HexDump(vblobBuff));
            //else
              //  Console.WriteLine("!!!empty blob"); 
			
			if (Console.KeyAvailable)
					break;
			System.Threading.Thread.Sleep(1000);	
			
			}
            vblob.Close();

            cmmd.CommandText = "execute function cdc_set_fullrowlogging('stores7:informix.t1',0);";
            drdr = cmmd.ExecuteReader();
            drdr.Read();
            Console.WriteLine(checkerror(conn, "cdc_set_fullrowlogging", drdr.GetInt32(0)));
            drdr.Close();

            cmmd.CommandText = "execute function cdc_endcapture(" + ssid + ",0,'stores7:informix.t1')";
            drdr = cmmd.ExecuteReader();
            drdr.Read();
            Console.WriteLine(checkerror(conn, "cdc_endcapture", drdr.GetInt32(0)));
            drdr.Close();

            cmmd.CommandText = "execute function cdc_closesess(" + ssid + ")";
            drdr = cmmd.ExecuteReader();
            drdr.Read();
            Console.WriteLine(checkerror(conn, "cdc_closesess", drdr.GetInt32(0)));
            drdr.Close();

            conn.Close();
        }

        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }

}


    Not 100% sure if it works as it is quite old (1210), but I found it in my C# collection and usually I only keep stuff that works ;)

    Javier 



    ------------------------------
    Javier Sagrera
    ------------------------------

    Original Message


  • 5.  RE: Informix CDC API - Is there a way to pull the change record using .NET?

    Posted Wed August 06, 2025 07:31 PM

    Thanks, Javier! Your code works!

    It seems the key difference is that I was using ExecuteNonQuery() instead of ExecuteReader(), which caused the issue.

    The next hurdle is mapping the incoming stream to a CDC record. Reverse‑engineering the content manually would be extremely tedious, so having a sample code snippet for that would be really helpful.



    ------------------------------
    George Wen
    ------------------------------

    Original Message


  • 6.  RE: Informix CDC API - Is there a way to pull the change record using .NET?

    Posted Thu August 07, 2025 04:09 AM

    That's good news.

    For "decoding" the data, check the ESQL/C demo included with the server ($INFORMIXDIR/demo/cdc)

    /* Table of the CDC_ log rectypes and their symbolic names as
 * published in the Manual
 */
cdc_logrec_t  logrectab[NUM_CDCLOGRECTYPES] =
{
    {CDC_REC_BEGINTX,   "CDC_REC_BEGINTX"},
    {CDC_REC_COMMTX,    "CDC_REC_COMMTX"},
    {CDC_REC_RBTX,      "CDC_REC_RBTX"},
    {CDC_REC_INSERT,    "CDC_REC_INSERT"},
    {CDC_REC_DELETE,    "CDC_REC_DELETE"},
    {CDC_REC_UPDBEF,    "CDC_REC_UPDBEF"},
    {CDC_REC_UPDAFT,    "CDC_REC_UPDAFT"},
    {CDC_REC_DISCARD,   "CDC_REC_DISCARD"},
    {CDC_REC_TRUNCATE,  "CDC_REC_TRUNCATE"},
    {CDC_REC_TABSCHEMA, "CDC_REC_TABSCHEM"},
    {CDC_REC_TIMEOUT,   "CDC_REC_TIMEOUT"},
    {CDC_REC_ERROR,     "CDC_REC_ERROR"}

};

    Even if is not C#, it should help to understand how to handle the data stream and convert it to a "readable" format.

    Javier



    ------------------------------
    Javier Sagrera
    ------------------------------

    Original Message


Related Content