We help IT Professionals succeed at work.

MySQL Differences?

webdude
webdude asked
on
Usually when I use ASP with Access or MS SQL I am able to do the following:

'add info to tbl1
rs.addnew
rs("name")=chuck
rs("age")=30
rs.update

'then grab the primary key ID to use for insert into tbl2
rs.movelast
theUserID=rs("userID")

'then i can use the variable theUserID to update a different table.

However MySql always takes me to the row BEFORE the last one, and Ive even tried creating a new recordset object and just going straight to rs.movelast and gettin ID, which seemed to work at first but its messing my data up somehow..is there and easy fix for this or easy way to achieve this? using ASP and MySQL?

210 points to whoever can help me out (all i have)
Comment
Watch Question

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
It is maybe the record is not save when you move to the last record. Try to add some 'buffer' or requery the SQL statement to get the LastID.
MySQL has a similar function to MSsql's @@IDENTITY.

I think if you do away with the idea of opening up a whole recordset, just so you can add to it.

Instead, just:

INSERT INTO [table] (column1, column2) VALUES ('text1', 'text2');
SELECT LAST_INSERT_ID() FROM [table]

And this should do you fine.

Commented:
First do an insert statement. Then to retrieve the Primary key ID of that inserted row, use:
sql="select userID from [tablename] where userID is null"
set rs = conn.execute(sql)
UserID = rs(0)

hope this helps.
Assuming the mySQL identity is inserted upon insert (as it should be), the userID field would NOT be NULL.
CERTIFIED EXPERT
Top Expert 2005

Commented:
AlfaNoMore,

Would it be possible that if two records were inserted in quick succession that SELECT LAST_INSERT_ID() FROM [table]might return the wrong value?


QuanB,

Do you know for certain that the ID column is populated at the moment of execution?


I am asking these questions not to be contrary, but because I don't know the answers and am curious about what is the best way to deal with this problem.

I'm not sure either. It's a tough one. I guess you could set two variables (Now(), and user IP) and write these into the database, and then use them again to retrieve the absolutely guaranteed correct data. Bit of an arsey workaround, but will be accurate...

usersIP = Request.ServerVariables("REMOTE_ADDR")
entered = Now()
sql = "INSERT INTO [table] (column1, column2, entered, byWho) " _
     & "VALUES ('data1', 'data2', '" & entered & "', '" & usersIP & "');"

conn.Execute(sql)

sql = "SELECT userID FROM [table] WHERE entered = '" & entered & "' AND byWho = '" & usersIP & "';"
RS.open sql, conn

thisID = RS("userID")
Seeing the immediate benefits of paying the £800 to have a SQL server running on the server...

Commented:
fritz, I don't know that for certain. Yes, I am assuming that the userID field is an autoincrement field. AlfaNoMore, I have previously tried to use last_insert_id() in a sql statement in ASP but it didn't work.
CERTIFIED EXPERT
Top Expert 2005

Commented:
Webdude,

What you were doing before with SQL Server was dangerous for the same reason in the question that I asked AlphaNoMore. If two people add records in quick sucession, you will be in trouble.

Here is what I suggest. Try using QuanB's solution but test it first:

1) create a table with an auto-incrementing field named iID and a text field named cTestText
2) code a small asp page  to run the following code:

strSQL = "INSERT INTO testTable(cTestText) Values('This is test text')"
set objRS= objConnection.execute(strSQL)
Response.Write(objRS(iID))

3) run this page a few times to see if you are returned the proper ID

Fritz the Blank






Author

Commented:
ok guys thanks for all the replies been up all night and need to get a few shuteyes but will test all when i awake and respond accordingly

~ thx again
Mark FranzProject Manager
CERTIFIED EXPERT

Commented:
After the update, try this;

<%
' Define Variables
maxSQL = "SELECT MAX(userID) as maxnum FROM tbl1
"

Set oRScandID = objconn.Execute(maxSQL)
maxnum = oRScandID("maxnum").Value
&>

Then you can pass maxnum.
CERTIFIED EXPERT
Top Expert 2005

Commented:
There is a function called mysql_insert_id which is in some ways analagous to the @@identity of SQL Server.


Please see:

http://download.php.net/manual/en/function.mysql-insert-id.php


So going back to the first post in this string:

'add info to tbl1
rs.addnew
rs("name")=chuck
rs("age")=30
rs.update

response.write(mysql_insert_id())

I haven't tested this, but it might be just the thing that you need.

Author

Commented:
ok awake and ready to test. Again I appreciate all the replies. I plan on testing the following things:

Alpha - setting up userIP and Now fields

QuanB - selecting null

MQFranz -  selecting MAX

Fritz - msql_insert_id

I will test each one several times running multiple inserts from 2 different machines seeing which one is most effective and will award the points to whoevers solution is best. Thanks again experts, and will report back soon.

webdude
Commented:
Using mysql_insert_id() is the way to go, since it is maintained on a per-connection basis.  The problem with using Max, is that if another connection does an insert after your insert but before you read the Max, you will get the wrong number...

See here:

http://www.mysql.com/doc/G/e/Getting_unique_ID.html

Also see here, there is a good example:

http://www.mysql.com/doc/O/D/ODBC_and_last_insert_id.html

"...if you are just going to insert the ID into another table, you can do this:

INSERT INTO foo (auto,text) VALUES(NULL,'text');
INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');

"
Mark FranzProject Manager
CERTIFIED EXPERT

Commented:
If you use a locking script on the dB with MAX you will not have any issues.

Granted it is not for high production sites that receive a hundred inputs concurrently, but if you ar ejust addinng a record and need the number, it will be fine.

But I do agree that LAST_INSERT_ID would probably be a better fit in this case...

Author

Commented:
well, although most of the examples were good ones and seemed to work, the one I think is best is using the LAST_INSERT_ID()

I think its safest since it is maintained on a per connection basis. Not exactly sure if im giving the points to the right person, but dritich showed 2 examples, and the code im using is very similar to what he had posted.

so fritz, although you mentioned something similar earlier, i am sorry if the points should go to you. when i save some more points up, i will post a foo question and award you some points as well.

Thanks all for the replies, and happy programming.

webdude

Author

Commented:
Thanks again!
CERTIFIED EXPERT
Top Expert 2005

Commented:
Webdude,

I did mention the perils of using anything other than mysql_insert_id and provided the link that would give you the necessary syntax to use it first, but the most important thing is that your problem is solved.

Fritz the Blank

Author

Commented:
Again, I am sorry fritz ~

When I get more points to give I will post a foo question for you to receive some points, I am sorry, but thanks again for helping solve my problem.

webdude

Explore More ContentExplore courses, solutions, and other research materials related to this topic.