Coldfusion can not insert date type into MySQL

Question

I am using Framework7 as the front-end along with Coldfusion fw1 4.2 (Lucee 5.3) for ajax JSON responses. I have written the following model\services code (without syntax errors in fw1) that is called via the appropriate controller to return JSON response flag either success or failure.

All the parameters are strings as i have carefully remarked via the appropriate getMetadata(parameter).getName()

function judge_add(string name,string usernum,string judgename,string judgedate){

    local.status={};
   
    var arrayDate=listToArray(judgedate,"-");
    var dateadded=createDate(arrayDate[1],arrayDate[2],arrayDate[3]);

    transaction{

        try{
            
            myQry1=new Query();
            myQry1.setDatasource(name);
            myQry1.addParam(name="judgename", value="#judgename#", cfsqltype="CF_SQL_VARCHAR");
            myQry1.addParam(name="dateadded", value="#DateFormat(dateadded,'YYYY-MM-DD')#", cfsqltype="CF_SQL_DATE");
            myQry1.setSQL("INSERT INTO judge (name,assigned) VALUES (:judgename,:dateadded)");
            myQry1.execute();
           
           
            var judge_added=myQry1.execute().getResult().GENERATED_KEY;

            
            myQry2=new Query();
            myQry2.setDatasource(name);
            myQry2.addParam(name="judge_added", value="#judge_added#", cfsqltype="CF_SQL_INTEGER");
            myQry2.addParam(name="usernum", value="#Val(usernum)#", cfsqltype="CF_SQL_INTEGER");
            myQry2.setSQL("INSERT INTO users_judge (judgeid,userid) VALUES (:judge_added,:usernum)");
            myQry2.execute();

            transaction action="commit";
                             
            local.status.success=1;

        }catch(any e){
            
            local.status.msg=e.Message;
            local.status.error=1;
            transaction action="rollback"; 
            
         }
    }

    return local.status;

}

Using Framework7 to run this code, I receive a JSON response with error=1 instead of success=1. Additionally, I removed the second query along with the transaction bracket and leave only try/catch and still the same error. Furthermore, I used CF_SQL_TIMESTAMP instead of CF_SQL_DATE, and the error still the same, there is no MySQL data insertion and an exception is thrown. Finally i added local.status.msg=e.Message, in order to get a detailed description of my error via json response. At first i got The named parameter [local] has not been provided as error message and now i get No matching property [GENERATED_KEY] found for MYSQL.

Any idea what could I do?

Regards

Take the try catch out and see what the underlying error message from Lucee is and update your question with the error details. — andrewdixon, Mar 15 '21 at 11:11
No matching property [GENERATED_KEY] found as error. I have already defined the field as primary key and auto increment. Regards — asimkon, Mar 15 '21 at 14:03

score 1 · Accepted Answer · answered Mar 15 '21 at 19:01

There's a Lucee dev forum post that suggests that the variable is called generatedkey (without the underscore).

The example code posted there looks like this:

<cfquery name ="insertData" datasource="testDs" result="res">
    INSERT INTO test ( name ) VALUES ( 'lucee' )
</cfquery>
<cfdump var="#res.generatedkey#" />

Another way to solve this is by selecting the generated ID right after the INSERT and working with that instead.

That can be done by executing SELECT last_insert_id().

So you'd need to add this after your first query:

myQryId=new Query();
myQryId.setDatasource(name);
myQryId.setSQL("SELECT last_insert_id() AS id");
var judge_added=myQryId.execute().getResult().id;

Coldfusion can not insert date type into MySQL

1 Answers1