ColdFusion: Trying to query database in CFScript

From the 4th link on google for "cfscript query 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 ought to tell you everything you need to know.

Also, you really should not be creating JSON manually, no matter how simple it is. Use serializeJson().


Didn't test this, but this 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>

Tags:

Coldfusion