BBS.PROGENIC.COM / Chat / Coding / @@Identity in MySql / Latest Posts

RE: @@Identity in MySql

proge — Mon, 25 Jan 2010 04:22:50 GMT

orly?

RE: @@Identity in MySql

scriptjunkie — Sun, 24 Jan 2010 23:00:15 GMT

he obviously is using it for mysql injection teqniques the identify string is the hard part of the injection

RE: @@Identity in MySql

wax — Wed, 05 Sep 2007 13:02:44 GMT

Sounds about right, not sure how ado.net's connection pooling comes into play here though, it keeps connections alive so theoretically that could mess things up. But you'd have to read up / test that really.

That being said though, any sort of trigger will definitely live inside your connection's scope, so if one of those does an insert somewhere on an identity field because of something you did, you're in trouble. Moral of the story - use scope_identity(), there's no reason not to

RE: @@Identity in MySql

proge — Wed, 05 Sep 2007 12:19:06 GMT

wax (9/5/2007)

I hope it's not actually the same as @@identity, but rather scope_identity()..

scope_identity() is the last inserted id for your scope (query, stored proc, trigger)

@@identity is last inserted id for the database for your connection

Or in other words - on busy systems with heavy inserting going on, @@identity can return the wrong id

hmm from what I read it is for the current connection which i assume means between con.open and con.close.

Sound right to you?

RE: @@Identity in MySql

_Infernal_ — Wed, 05 Sep 2007 11:29:37 GMT

mhm....all i can agree with is that databasez are bitches
hed trouble with swl at absolutel every site i worked on
absolutely no exceptions

RE: @@Identity in MySql

wax — Wed, 05 Sep 2007 11:28:28 GMT

I hope it's not actually the same as @@identity, but rather scope_identity()..

scope_identity() is the last inserted id for your scope (query, stored proc, trigger)

@@identity is last inserted id for the database for your connection

Or in other words - on busy systems with heavy inserting going on, @@identity can return the wrong id

RE: @@Identity in MySql

proge — Wed, 05 Sep 2007 10:52:16 GMT

It's for an antispam system i'm working on. It's been quite interesting learning how mysql differs from mssql.

Two other differences that stumped me for a while were that mysql doesn't have GetDate() as an sql function so i had to swap that to Now(), and the one I got stuck on the longest was when you use parameterized queries, mssql variables are named like @Variable but in mysql it's ?Variable. It gave me no error messages when i used @Variable it just didn't work, took me f**king forever to work that out .

RE: @@Identity in MySql

_Infernal_ — Wed, 05 Sep 2007 10:12:06 GMT

just out of curiosity
where are u using this

just if ya want to say

RE: @@Identity in MySql

proge — Wed, 05 Sep 2007 08:27:29 GMT

sorted it's SELECT LAST_INSERT_ID() and it can be used in c# like so:

 public int InsertContactList(objContactList list)
        {
            MySqlCommand cmd;
            conn.Open();
            cmd = new MySqlCommand("insert into contact_lists (ListName,UserID) values (?ListName,?UserID);Select LAST_INSERT_ID();", conn);
            
            cmd.Parameters.AddWithValue("?ListName", list.ListName);
            cmd.Parameters.AddWithValue("?UserID", list.UserID);
            int i = (int)(long)cmd.ExecuteScalar();
            conn.Close();
            return i;
        }

@@Identity in MySql

proge — Wed, 05 Sep 2007 07:55:38 GMT

I'm trying to get the row id for the record i just entered, i could select the max id for the primary key but this is going to be a huge multithreaded system and can't risk returning the wrong ID.

In mssql you have @@Identiy which can return the id of the row you added, anybody know if there is an equivalant in mysql?