0

i have a multiple statments code like below.

Set db = Server.createObject("Adodb.Connection")
db.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};option=67108864; SERVER=" & mysql_server & "; UID=" & mysql_user & "; pwd=" & mysql_pass & "; db=" & mysql_db & ";"
set rss=db.execute("INSERT INTO boardlar (baslik, hesap_id) VALUES ('Deneme','1');select LAST_INSERT_ID() as lastid from boardlar")

response.write rss("lastid")

i checked it on phpmyadmin and it works. But in asp or php it didnt work.

it says not exixts no field from query. how can i resolve it?

user692942
  • 16,398
  • 7
  • 76
  • 175
rapstar2004
  • 111
  • 1
  • 1
  • 11
  • Does [this comment](https://stackoverflow.com/questions/19061238/cant-get-last-insert-id-to-work-from-asp-classic#comment28180883_19061238) on an existing question help? – user692942 Apr 12 '20 at 17:24
  • 1
    Try adjusting the `OPTION` flags to `67108867` which is a combination of `FIELD_LENGTH`: "Do not Optimize Column Width", `FOUND_ROWS`: "Return Matching Rows" and `MULTI_STATEMENTS`: "Support multiple sql statements in a batch". – user692942 Apr 12 '20 at 17:43
  • Is [this](https://stackoverflow.com/q/50095273/692942) helpful? – user692942 Apr 12 '20 at 17:58

1 Answers1

1

This is likely to do with the combination of OPTION flags as these can be combined. The general consensus is to have the following three flags;

  1. FLAG_FIELD_LENGTH (1) - "Do not optimize Column Width" 1
  2. FLAG_FOUND_ROWS (2) - "Return matching rows"
  3. FLAG_MULT_STATEMENTS (67108864) - "Support multiple SQL statements in a batch"

When these values are combined the OPTION parameter value becomes 67108867.

Which means a simple change to;

db.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};option=67108867; SERVER=" & mysql_server & "; UID=" & mysql_user & "; pwd=" & mysql_pass & "; db=" & mysql_db & ";"

1. The FLAG_FIELD_LENGTH has been depreciated from the ODBC connector GUI since version 3.51.18

Another solution

It does seem that it's the lack of FLAG_FOUND_ROWS in the OPTION parameters that causes this particular problem but as a quick test you could see if you can access the query result via an ordinal field without using the alias, something like this;

<%
'Assumed declarations, not setting server, user and pass as you will have these defined.
Dim db, res, mysql_server, mysql_user, mysql_pass

Set db = Server.CreateObject("Adodb.Connection")
db.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};option=67108864; SERVER=" & mysql_server & "; UID=" & mysql_user & "; pwd=" & mysql_pass & "; db=" & mysql_db & ";"
Set rss=db.execute("INSERT INTO boardlar (baslik, hesap_id) VALUES ('Deneme','1');select LAST_INSERT_ID() as lastid from boardlar")

'Use ordinal positioning instead of field alias to return the value.
Call Response.Write(rss(0))
%>

Useful links

user692942
  • 16,398
  • 7
  • 76
  • 175