Buy this Reference Manual in softcover from Barnes & Noble!
Существует распространенная проблема, заключающаяся в том, как получить значение автоматически сгенерированного 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;
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
The link for the servicepack has changed to:
http://support.microsoft.com/default.aspx?
scid=kb;EN-US;Q239114
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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 )
| Posted by Dennis Williams on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
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
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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]
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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
| Posted by Allan Krogh Jensen on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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]
| Posted by [name withheld] on Tuesday July 9 2002, @5:46am | [Delete] [Edit] |
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.).
| Posted by on Monday September 16 2002, @12:05am | [Delete] [Edit] |
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.