8.3.6 Как получить значение столбца AUTO_INCREMENT в ODBC

Существует распространенная проблема, заключающаяся в том, как получить значение автоматически сгенерированного ID из INSERT. С помощью ODBC можно сделать что-то наподобие следующего (предполагается, что auto представляет собой поле AUTO_INCREMENT ):

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

Или, если вы просто собираетесь вставить данный ID в другую таблицу, то можно сделать так:

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

See section 8.4.6.3 Как получить уникальный идентификатор для последней внесенной строки?.

Для некоторых приложений ODBC (по крайней мере, для Delphi и Access), чтобы найти недавно вставленную строку, можно использовать следующий запрос:

SELECT * FROM tbl_name WHERE auto IS NULL;

User Comments

The link for the servicepack has changed to:
http://support.microsoft.com/default.aspx?
scid=kb;EN-US;Q239114

I have noticed that the ODBC driver for windows
2000 comes up with 'driver does not support'
error or sometimes returns no rows to an asp
recordset when trying to run sql queries with
group by clauses, the same queries do work with
the admin demo tool.
(also the admin demo tool has some major problems
with memory leaks )

In an attempt utilize Excel and VBA to connect to
a remote MySQL server, I had the following
experience:

(Using Excel 97 and reference set ADO 2.6 Library)


Trying to dump a standard table output into Excel
using the Excel's "CopyFromRecordset" method will
not work, MyODBC returns "...does not support
automation." The following code worked for me to
perform the same result:

(Set OPTION=16834 in connection command if you
use BIGINT in field values)

===========================
Sub MySQL_Connect()

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL, myDSN As String
Dim fldCnt As Long
Dim xCnt, yCnt As Long

myDSN = "TEST-MySQL"

myconn.Open "DSN=" & myDSN & "; " & _
"Database=TBL_USER; " & _
"OPTION=16834; " & _
"UID=userID; " & _
"PWD=password"

mySQL = "SELECT * from `ACCOUNTCONTACT`;"

myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open

'column names
For xCnt = 0 To myrs.Fields.Count - 1
ActiveSheet.Range("A1").Offset(0, xCnt) =
_
myrs(xCnt).Name

Next

'reset
xCnt = 0
myrs.MoveFirst

'data values
Do While Not myrs.EOF
For yCnt = 0 To myrs.Fields.Count - 1
ActiveSheet.Range("A2").Offset
(xCnt, yCnt) = _
myrs(yCnt).Value
Next

myrs.MoveNext
xCnt = xCnt + 1

Loop

myrs.Close
myconn.Close

End Sub
===========================

HTH
DWilliams

does anyone know thu dao or ado how to get the
newly created autonum index value when accessing
mysql thru access 2000 - could you send sample
code [email protected]

This is luke again... i figured it out for ADO in
VB... this works

Dim conn As ADODB.Connection
Dim rsADO As ADODB.Recordset
dim intNewIndex as long

Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER=
{MySQL};SERVER=xxxx;DATABASE=xxxxx;UID=xxxx
;PWD=xxxx;OPTION=35"
conn.Open

Set rsADO = New ADODB.Recordset
rsADO.CursorLocation = adUseServer

'the where=-1 helps ado open faster!!!!!!!
rsADO.Open "Select * From tablename where
index = -1", conn, adOpenDynamic, adLockOptimistic
rsADO.AddNew

rsADO("fldName")= "stuff"

rsADO.Update

rsADO.Close

rsADO.Open "select * from tablename Where
index = LAST_INSERT_ID()", conn
rsADO.MoveFirst
intNewIndex= rsADO(strIndexName).Value
rsADO.Close

conn.Close

My VBA (Access) application sends the following
error:

ODBC-call failed
[TCX][MyODBC] You Have An error in your SQL-
syntax near '1)'

When I send the following SQL-request:

SELECT knr, effekt FROM komponenter WHERE
effekt<=0.1;

The SQL sentence works out fine, when i execute
it directly at the MySQL prompt. What can cause
this error???

I also tried to log the MyODBC activity as
descriped, but it created no logfile at all!!!

Please help me soon!

Allan Krogh Jensen
[email protected]

Many people have had a problem with using
rs.addnew with ADO, namely 'Multi- step operation
generated errors. Check each OLE DB status value if
available. No work was done.' (I know, I searched
the web in vain to find an answer and found 30+
postings in various places - 8/7/02). The problem
appears to be with the previous ODBC driver,
myodbc-2.50.19-NT. The newer version myodbc-
2.50.39-NT DOES work. However unlike the
previous the recordset cursor location MUST be
serverside, eg rsTgt.CursorLocation = adUseServer
(Setting to client gives "Driver does not support this
parameter" - regardless of whatever cursor and
locktype is used.).

When I set the CusorLocation at client,the "Select"
syntax do not work well, but If I set CusorLocation
as Server site ,it works well.
If I choose Server site,Datagrid object will get no
any data from Mysql.
I hope that Datagrid object get data and SQL-query
work well.
Help me...Please.
I Have try and try to find any article about the
problem here but I did not find any yet.

Add your own comment.