Skip to main content
V
vovasvv
Participating Frequently
December 5, 2008
Question

Once again: Does MySQL allow multiple querys in one database transaction???

  • December 5, 2008
  • 4 replies
  • 1000 views
Hello to ALL!!!
The problem is:
I'm trying to make simple query (database: "myDB" (engine: myisam/innodb - doesn't mater) with one table: "Info" with two columns: "Id" (autoinc), "info_c" (varchar):

<cfif structkeyexists (form, "name")>
<cfquery datasource="myDB" name="qDB" >
INSERT into Info (info_c)
VALUES ('#form.name#');
SELECT @@identity AS Id
</cfquery>
</cfif>
<cfform>
<cfinput type="text" name="name">
<cfinput type="submit" name="submit">
</cfform>

BUT after "Submit" I get:

Error Executing Database Query.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; select @@identity as Id' at line 2

Please, tell me WHY???

I have:

MySQL
Server information:
MySQL version: MySQL 5.1.25-rc-community via TCP/IP
Clent Information:
Version: MySQL client version 5.1.11

Coldfusion Version Information:
Version 8,0,0,176276

Great THANKS for your answers!!!
This topic has been closed for replies.

4 replies

-
-__cfSearching__-
Inspiring
December 5, 2008
> SELECT @@identity AS Id

AFAIK, MySQL uses LAST_INSERT_ID(). @@IDENTITY is MS SQL specific. Though SCOPE_IDENITY() is recommended over @@IDENTITY.

What version of CF are you using? ColdFusion supports the "result" attribute, which will return the ID value for simple inserts. See the documentation for details

http://livedocs.adobe.com/coldfusion/8/Tags_p-q_17.html


> Do JDBC drivers for MySQL prohibit the use of multiple queries
> in a single database transaction???

For security purposes this is disabled by default. To enable it you must modify your datasource url
http://www.petefreitag.com/item/357.cfm
D
Dan_Bracuk
Inspiring
December 5, 2008
To the best of my knowlege, the only db's that permit more than one query in a cfquery tag are sybase and mssql.

I use neither cf8 or mysql, but I've heard rumours that there is something in this combination that gives you what you seek with one tag and one query.
V
vovasvvAuthor
Participating Frequently
December 5, 2008
In other words I've found two links:
first -
http://tutorial281.easycfm.com/
dates back to 2004
second -
http://tutorial480.easycfm.com/
dates back to 2006

What about today: Do JDBC drivers for MySQL prohibit the use of multiple queries in a single database transaction???
V
vovasvvAuthor
Participating Frequently
December 5, 2008
...... BUT if I use this code - all work perfect, but I don't want to use 2 query's:

<cfquery datasource="myDB" name="qDB" >
INSERT into Info (info_c)
VALUES ('#form.name#');
</cfquery>

<cfquery datasource="myDB" name="qDB1" >
SELECT @@identity AS Id
</cfquery>

The same thing - if I use LAST_INSERT_ID()
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices