scope_identity - SQL Server and Oracle

scope_identity - SQL Server and Oracle

Postby slimtimbodin » Sat 14 Aug 2010 17:31

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.
slimtimbodin
 
Posts: 2
Joined: Sat 14 Aug 2010 17:18

Postby Shalex » Mon 16 Aug 2010 14:50

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();
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Postby slimtimbodin » Tue 14 Sep 2010 18:53

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
slimtimbodin
 
Posts: 2
Joined: Sat 14 Aug 2010 17:18

Postby Shalex » Wed 15 Sep 2010 12:34

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();
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect Universal