C#: How do I get the ID number of the last row inserted using Informix

Question

I am using the Informix.NET driver (C#) to insert a row into a database table. This works fine, except I have no idea how to get the ID of the last inserted row:

Insert Command:

    //Sample Query: "INSERT INTO names (id, name) values (0, 'John Smith');"
    public static void Insert(String query)
    {
        try
        {
            using (IFX::IfxConnection connection = ConnectionManager.GetConnection())
            {
                connection.Open();

                using (IFX::IfxCommand command = new IFX::IfxCommand(query, connection))
                    command.ExecuteNonQuery();

                if (connection != null) connection.Close();
            }
        }
        catch (IFX::IfxException)
        {
            throw;
        }
        catch (Exception)
        {
            throw;
        }
    }

If I change the query so that it includes: "SELECT DBINFO ('sqlca.sqlerrd1') FROM systables WHERE tabid = 1;" I get errors (this line works using unix dbaccess) I get errors.

I've also tried to change things such as using IFX::IfxReader reader = command.ExecuteReader() to try to get the results and read those results, however I get errors. ("ERROR [HY000] [Informix .NET provider][Informix]Cannot use a select or any of the database statements in a multi-query prepare."). I've also tried to precede this with command.Prepare();, but that does nothing. I'm really not sure how to do an insert and get the ID through C# Informix.NET Client SDK.

=========
Oh, and I know I can run the two statements separately, which will work, except I'm worried that another insert will be executed in between the first and getting the ID number, which would result in errors.

I'm curious about the try block with the throws - doesn't omitting the them achieve the same effect? — Jonathan Leffler, Oct 04 '10 at 21:23
@JonathanLeffler: Yes, they're mainly in there as placement holders until a logger is decided upon, then logging will be placed in there. — myermian, Mar 30 '12 at 18:03

score 2 · Accepted Answer · answered Oct 04 '10 at 17:15

2

Granted I've never used this framework before, but I did needed like this in a different framework I worked with so what I ended up doing was doing a completely separate query to get the new element's ID.

My code was in PHP but in pseudocode it looked like:

Insert(String query)
{
   Execute the Sql Query: query
   result = Execute the Sql Query: "SELECT last_insert_id"
   return the first element of result
}

EDIT: I didn't notice your edit, and that wasn't an issue for me because it was PHP so it was single-threaded and so it was impossible for another statement to execute in the time between the two queries. However, if you add a lock to inserts you can force that both queries happen as a transaction. Also with a bit of RegEx you could probably construct a Select statement that uses the information in the Insert statement.

answered Oct 04 '10 at 17:15

Arrya Regan

1,104
8
22

Was that with informix? And, what about the issue of 2 different insert statements being executed so close nearby that it goes: insert, insert, select last_insert_id, select last_insert_id. – myermian Oct 04 '10 at 17:19
No it was using PHP's standard mysql query statements. and see my edit for tips on how to deal with that. – Arrya Regan Oct 04 '10 at 17:20
I ended up doing just that... 2 separate calls to the DB (2 separate queries) inside of a locked method. That's probably the best solution for now until I can see if IBM's Informix.NET driver is more efficient than what I have discovered with it. – myermian Oct 04 '10 at 20:45
@myermian: the value returned is the last result on the current session / connection; as long as you've not inserted anything between the insert and the query, your session will be fine. – Jonathan Leffler Oct 04 '10 at 21:25
Well I use a singleton connection, rather than creating a connection every time, so that would be an issue. I threw in a lock and have it as 2 separate calls and it works (I hope). – myermian Oct 05 '10 at 12:51

score 0 · Answer 2 · answered May 08 '14 at 17:30

Several years too late, but here's the query we use that works:

SELECT MAX(num) AS serial FROM (SELECT DBINFO('sqlca.sqlerrd1') AS num FROM sysmaster:sysdual UNION SELECT DBINFO('serial8') AS num FROM sysmaster:sysdual)

And, for the sake of completeness, here is one we use that works for MS SQL:

SELECT SCOPE_IDENTITY() AS serial

score 0 · Answer 3 · answered Nov 07 '16 at 18:07

Working code for Informix, as I had to mix from various questions to answer exactly this one

    private int LastInsert(OdbcConnection informixConn)
    {
        string idSerialSQL = "SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM systables WHERE tabid = 1;";
        OdbcCommand queryInformixCmd = new OdbcCommand(idSerialSQL, informixConn);
        int idSerial = (int)queryInformixCmd.ExecuteScalar();
        return idSerial;
    }

score 0 · Answer 4 · answered Mar 30 '12 at 14:19

My answer in C# using an Oracle Database, considering that table "testtable" has an "ID" collumn as PK with an autoinc sequence and field "testname" is a varchar field.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;

namespace ConsoleApplication3
{
class Program
{

    public static void Main(string[] args)
    {

        OracleConnection cn = new OracleConnection("your connection string here");


        string sql = "INSERT INTO testtable(testname) VALUES('testing2') RETURNING id INTO :LASTID";
        OracleParameter lastId = new OracleParameter(":LASTID", OracleDbType.Int32);

        lastId.Direction = ParameterDirection.Output;

        using (OracleCommand cmd = new OracleCommand(sql, cn))
        {
            cn.Open();
            cmd.Parameters.Add(lastId);
            cmd.ExecuteNonQuery();
            Console.WriteLine("Last ID: " + lastId.Value.ToString());
            cn.Close();
        }
        Console.WriteLine();
        Console.ReadKey(false);


    }
}
}

C#: How do I get the ID number of the last row inserted using Informix

4 Answers4

Linked