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
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
.. especially since your result dump proves the insert was successful, and the value assigned id=42. That supports @Barry 's suspicions. — Leigh
@Jason: good tip. The records are inserted, they are just not visible. hm. where is the datasource configuration...? — frequent

frequent frequent · Accepted Answer · 2012-06-04T07:47:00

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.

why I cannot insert into a MySQL table from Coldfusion8?

1 Answers