scope_identity - SQL Server and Oracle

[slimtimbodin]

I am using Devart Universal to write some code that will allow connection to SQL Server or Oracle.

Can you give a simple example of how to insert a record and returning the primary key that was used when the record inserted?

I have been trying to use ExecuteScalar() similar to this:

insert into customers (FirstName, LastName) values ('Some', 'Dude'); {myMacro}

myMacro is defined as a select scope_identity(); for SQL Server and select customers_seq.CURRVAL from dual;

customers_seq is my sequence that is used in an insert trigger that assigns the primary key to the record

But that doesn't work because it appears that I can't use the first semicolon after 'Dude') to put multiple statements into one execute command. In Oracle I get an invalid character.

[Shalex]

The issue you have encountered is caused by Oracle limitation: only one SQL statement can be put into one command. As a workaround, we recommend you using the RETURNING clause. Please try the following code:

Code: Select all

        UniConnection conn = new UniConnection();
        conn.ConnectionString = "provider=SQL Server;Data Source=MSSQL2008RTM;Initial Catalog=Test;User Id=sa;";
        UniMacro mac = new UniMacro("myMacro", "; set @a = scope_identity()", "SQL Server");
        conn.Macros.Add(mac);
        UniMacro mac2 = new UniMacro("myMacro", "returning id into :a", "Oracle");
        conn.Macros.Add(mac2);
        UniCommand cmd = new UniCommand("insert into testtable (field) values ('Dude') {myMacro} ");
        cmd.Connection = conn;
        conn.Open();
        cmd.Parameters.Add("a", UniDbType.Int).Direction = System.Data.ParameterDirection.Output;
        int response = cmd.ExecuteNonQuery();
        int i = (int)cmd.Parameters[0].Value;
        Console.WriteLine(response);
        conn.Close();

        conn.ConnectionString = "provider=Oracle;Password=tiger;User Id=Scott;Data Source=ora1110;";
        conn.Open();
        int response2 = cmd.ExecuteNonQuery(); 
        int i2 = (int)cmd.Parameters[0].Value;
        Console.WriteLine(response2);
        conn.Close();

[slimtimbodin]

Hi Shalex,

This worked absolutely perfectly and now my code is supporting Oracle and SQL Server.

Can you show how to to this with MySQL?

I tried:

UniMacro mac3 = new UniMacro("myMacro", "; set @a = LAST_INSERT_ID()"), "MySQL");

But that ended up with an exception of:

OUT parameters are supported only for CommandType = StoredProcedure mode

[Shalex]

Unfortunately MySQL does not support OUT parameters in protocol yet. dotConnect for MySQL (run time of the Standard Edition of this provider is included to dotConnect Universal Professional) allows to handle this situaltion via reader (docs). On the other hand, Oracle's command doesn't allow to use several statements in one command (to put "select id" statement for all providers). Please try this workaround:

Code: Select all

            UniConnection conn = new UniConnection();
            UniMacro mac = new UniMacro("myMacro", "; set @a = scope_identity()", "SQL Server");
            conn.Macros.Add(mac);
            UniMacro mac2 = new UniMacro("myMacro", "returning id into :a", "Oracle");
            conn.Macros.Add(mac2);
            UniMacro mac3 = new UniMacro("myMacro", "; select LAST_INSERT_ID()", "MySQL");
            conn.Macros.Add(mac3);
            UniCommand cmd = new UniCommand("insert into testtable (field) values ('Dude') {myMacro} ");
            cmd.Parameters.Add("a", UniDbType.Int).Direction = System.Data.ParameterDirection.Output;
            cmd.Connection = conn;

            conn.ConnectionString = "provider=SQL Server;Data Source=MSSQL2008RTM;Initial Catalog=Test;User Id=sa;";
            conn.Open();
            cmd.ExecuteNonQuery();
            int i = (int)cmd.Parameters[0].Value;
            Console.WriteLine(i);
            conn.Close();

            conn.ConnectionString = "provider=Oracle;Password=tiger;User Id=Scott;Data Source=ora1110;";
            conn.Open();
            cmd.ExecuteNonQuery();
            int i2 = (int)cmd.Parameters[0].Value;
            Console.WriteLine(i2);
            conn.Close();

            conn.ConnectionString = "provider=MySQL;server=db;port=3308;database=test;uid=root;pwd=root;";
            conn.Open();
            cmd.Parameters.Clear();
            int i3 = Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Parameters.Add("a", UniDbType.Int).Direction = System.Data.ParameterDirection.Output;
            Console.WriteLine(i3);
            conn.Close();