----- Original Message -----
From: "Henrik Lebtien Mohr" <[EMAIL PROTECTED]>
Sent: Friday, February 23, 2001 3:57 PM
> If any of you have tried the following with ASP/ADO and MySQL, please
inform
> me of how you did it:
I've done a lot of this in my time, so I know most of the tricks.
> I use ASP and adodb.recordset to connect to a MySQL-database.
> I use the following code to insert into the db:
<snip code>
First Rule Of ADO/MySql:
Never ever use Recordset for insertion/updating. Period.
You will run into nothing but trouble. First one you already spotted
yourself - How do you get back the auto_incremented value? Other than that
I've spent most of half a year mailing with Monty debugging ADO's way of
using ODBC. The conclusion is, that ADO doesn't comply correctly with ODBC
standards, which means your update's will be 1) incredibly slow (I'll
explain later) 2) Fields of type TEXT will not work with NULL values. Just
to mention the worst problems.
Instead use INSERT and UPDATE sql statements:
' Insert
sql = "insert into tblGroup (group_name, group_slogan) values ('" & strGName
& "', '" & strGSlogan & "')"
connect.execute(sql)
' Get autoinc
sql = "select group_id from tblGroup where group_id = last_insert_id()"
set rs = connect.execute(sql)
if not rs.eof then
group_id = rs("group_id")
end if
rs.close
set rs = nothing
Remember to escape any ' s and CrLf's in your strings strGName and
strGSlogan. E.g:
strGName = replace(strGName, "'", "\'")
strGName = replace(strGName, vbCrLf, "\n")
Regarding the autoinc value, why not just use "select last_insert_id() as
group_id" you might ask. Well, try it - you'll find that it doesn't work.
But the above example does.
Do an update like this:
sql = "update tblGroup set group_name = '" & strGName & "', group_slogan =
'" & strGSlogan & "'"
connect.execute(sql)
Ok, so why is updates using Recordset slow?
Doing the debugging with Monty we did a LOT of digging in the log files.
When you make an update using the recordset, ADO actually translates into an
update sql statement, only it cannot figure out which field is the primary
key. So instead it puts all fields of the table into the WHERE expression!
E.g.
Table foo
+----+---------+--------------+
|id |name |address |
+----+---------+--------------+
|5 |Carsten |Volderslevvej |
+----+---------+--------------+
(id is primary key)
set rs = server.createObject("adodb.recordset")
sql = "select * from foo where id = 5"
rs.open sql, connect
rs("name") = "Gehling"
rs.update
This will be translated into:
UPDATE foo SET name = 'Gehling' WHERE (id = 5 AND name = 'Carsten' AND
address = 'Volderslevvej')
instead of just
UPDATE foo SET name = 'Gehling' WHERE id = 5
Now imagine that address is a TEXT field containing a text 2 MB long. Then
this 2 MB text will be sent through the system even though it isn't updated.
Hope this answers your questions. If you have more, feel free to ask me,
even offlist as I don't always read all messages on this list. I can't
garantee that I will supply such a lon answer everytime though :-)
Og nu da jeg så nærmere på din email adresse, er du selvfølgelig velkommen
til at spørge på dansk... ;-)
- Carsten
~~~~~~~~~~~~~~~~
Sarum
Volderslevvej 55, 5260 Odense S.
tlf: 66 15 08 58 ~ mobil: 29 45 74 22
e-mail: [EMAIL PROTECTED]
http://www.sarum.dk
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
