ColdFusion: attempting to query a database in CFScript

Question

My boss wants me to use cfscript instead of tags to interact with the database. Does anyone know of good textbooks? I bought an Adobe ColdFusion application development book, volume 2. But it has very little scripting. I did google and found this site , but that didn’t explain much.

Does any authority know about any good tutorials on accessing a database in CFScript?

Basically I need to convert the following to using CFScript:

<cfquery name="drafts" datasource="ICEchat"> SELECT * from Messages where IsTemp=1 and LinkA=#FORM.LinkA# and LinkB=#FORM.LinkA# </cfquery> <cfif drafts.recordcount GT '0'> <cfquery name="Attachments" datasource="ICEchat"> SELECT * FROM Attachments where id=2 </cfquery> { Message:"<cfoutput query="drafts">#Message#</cfoutput>", Attachments:[<cfoutput query="attachments"> "#url#"<cfif attachments.currentRow LT attachments.recordcount>,</cfif> </cfoutput>]} <cfelse> <cfquery name="addrecord" datasource="ICEchat"> INSERT INTO Messages VALUES(1,1,' ',1) </cfquery> { Message:"NA", Attachments:[]} </cfif> 

Answer 1

From the 4th link on google for the cfscript tutorial:

 <CFSCRIPT> myQry = new Query(); // new query object myQry.setSQL("select bookid, title, genre from app.books where bookid = :bookid"); //set query myQry.addParam(name="bookid",value="5",CFSQLTYPE="CF_SQL_INTEGER"); // add query param qryRes = myQry.execute(); // execute query writedump(qryRes.getResult().recordcount, true); // get resultcount writedump(qryRes.getResult(), false); // dump result writeoutput('<BR>'); </CFSCRIPT> 

That should tell you everything you need to know.

In addition, you really shouldn't create JSON manually, no matter how simple it is. Use serializeJson() .

Answer 2

Not tested this, but it should do it.

 <cfscript> local.drafts = new Query(); local.drafts.setDatasource("ICEchat"); local.drafts.addParam(name="linkA", value="#form.linkA#", cfsqltype="CF_SQL_VARCHAR"); local.drafts.addParam(name="linkB", value="#form.linkB#", cfsqltype="CF_SQL_VARCHAR"); local.drafts.setSQL("SELECT * from Messages where IsTemp=1 and LinkA = :linkA and LinkB = :linkA"); local.drafts.execute().getResult(); if (local.drafts.recordcount GT 0) { local.attachments = new Query(); local.attachments.setDatasource("ICEchat"); local.attachments.setSQL("SELECT * FROM Attachments where id=2"); local.attachments.execute().getResult(); WriteOutput("{ Message: "); for (i=1; i LTE local.drafts.recordcount; i=i+1) { WriteOutput(local.drafts.message[i]); } WriteOutput(", Attachments: "); for (i=1; i LTE local.attachments.recordcount; i=i+1) { WriteOutput(local.drafts.url[i]); if (i LT local.attachments.recordcount) { WriteOutput(", "); } } WriteOutput("}"); } else { local.q = new Query(); local.q.setDatasource("ICEchat"); local.q.setSQL("INSERT INTO Messages VALUES(1,1,' ',1)"); local.q.execute(); WriteOutput("{ Message:"NA", Attachments:[]}"); } </cfscript> 

Answer 3

I was looking for a solution for the same error. I do not get errors or errors. Chatted with Ray Camden on the last day, but everything he suggested does not do what I need. I worked on converting a website from standard CF tags to cfscript.

Ray suggested that .execute (); this is what pulls the record, and suggested that this is all necessary: ​​x = queryExecute (); will retrieve the record number, with (x) being the request. He suggested not using getPrefix (); but I read that .getPrefix (); this is what pulls the record. He is experienced, I'm sure you know Ray Camden, but I keep getting the same error no matter what I try to do inside my code.