why I cannot insert into a MySQL table from Coldfusion8?

Question

I'm using Coldfusion8 and MySQL and am trying to insert into a table.

However it is not working, no matter if I use a cfquery:

<cfquery datasource="dns" result="some">
    INSERT INTO users(iln)
    VALUES("1234567890123")
</cfquery>
<cfdump output="path.txt" var="#some#">

or Stored Procedure:

<cfstoredproc procedure="proc_insert_user" datasource="dns">
   <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="1234567890123" maxlength="13">
</cfstoredproc>

with the procedure inside MySQL:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_insert_user`(IN `user_iln` VARCHAR(13))
  LANGUAGE SQL
  NOT DETERMINISTIC
  MODIFIES SQL DATA
  SQL SECURITY INVOKER
  COMMENT ''
  BEGIN
     INSERT INTO users (iln)
     VALUES(user_iln);
  END

If I run the procedure from inside MySQL, it works. From Coldfusion however I cannot access the database. If I set a flag before and after the cfquery/storedProc, both flags show, so I assume there are no errors (I'm not getting any either)

Question: I guess I'm missing some settings in MySQL/Coldfusion. If so, where should I start searching?

Thanks for help!

EDIT:
When I try to insert a record as above, the table ID keeps increasing, but no new record is shown. So IDs are being created, but I can't see any records. Not sure if this helps.

EDIT2:
I have added a results parameter to the Cfquery. If I dump the results, I get:

CACHED: false
EXECUTIONTIME: 60
GENERATED_KEY: 42
RECORDCOUNT: 1
SQL: INSERT INTO teilnehmer(iln)
VALUES("1234567891231")

Can you see the result if you do a select statement just after the insert.. SELECT * FROM teilnehmer .. then dump out the result of the select statemet? — Jason, Jun 03 '12 at 23:53
You said this works from inside MySQL. I'm not too familiar with that, but are you using the same account in your datasource configuration? Along the lines of what @Jason is asking, it seems like it might be possible that the records are being created, just not visible in the account that you are using within MySQL. — Barry, Jun 04 '12 at 02:48
.. especially since your `result` dump proves the `insert` was successful, and the value assigned id=42. That supports @Barry 's suspicions. — Leigh, Jun 04 '12 at 03:31
@Jason: good tip. The records are inserted, they are just not visible. hm. where is the datasource configuration...? — frequent, Jun 04 '12 at 05:59

score 0 · Accepted Answer · answered Jun 04 '12 at 07:47

0

Silly me... I had the above INSERT database call inside a CFtransaction and there were some statements afterwards, one of which contained an error, which caused all prior transition statements to be reverted. So I guess the records never made it into the database after all with only the id-key increasing as the stored procedure was called.

answered Jun 04 '12 at 07:47

frequent

27,643
59
181
333

Yep, that is exactly what should happen. The purpose of the transaction is to ensure *all* statements succeed or fail together. So if even one fails, all of the statements are rolled back. However, auto incrementing id's are usually not reclaimed. http://stackoverflow.com/questions/5537296/sql-server-how-to-ensure-identity-fields-increment-correctly-even-in-case-of-ris-incremented-even-with-a-transaction-rollback – Leigh Jun 04 '12 at 14:04

why I cannot insert into a MySQL table from Coldfusion8?

1 Answers1