Lucee queryExecute function

[NEWBIE]

oke, so i am using this query function of lucee itself. If it’s wrong or if there is a better option let me know.

why, is there no information about how many rows are affected, I mean i need to figure out somehow whether its succeeded or not ?

queryz = queryExecute(
			sql="
				UPDATE dms_documents
				SET file_name= '#obj.name#', userId= '#obj.userId#', dt_edited= CAST(GETDATE() AS Date), languageID= '#obj.languageID#', file_type= '#obj.file_type#'
				WHERE id = '#obj.objectId#' 
			"
		);

#idk

Hi @Thomasen , for general purpose CFML and Lucee questions in the future I’d recommend you post over on the Lucee specific Discourse forum.

Now, to your questions:

You really should paramaterize your query, which means not concatenating untrusted user-provided values into the SQL which can affect performance and allow of SQL injection attacks.
You can supply positional params like so:

queryz = queryExecute(
			sql="
				UPDATE dms_documents
				SET file_name= ?, userId= ?, dt_edited= CAST(GETDATE() AS Date), languageID= ?, file_type= ?
				WHERE id = ?
			",
                       [
                           obj.name,
                           obj.userId,
                           obj.languageID,
                           obj.file_type,
                           obj.objectId
                       ]
		);

or named parameters like so:

queryz = queryExecute(
			sql="
				UPDATE dms_documents
				SET file_name= :name, userId= :userID, dt_edited= CAST(GETDATE() AS Date), languageID= :languageID, file_type= :fileType
				WHERE id = :objectID
			",
                       {
                           name : obj.name,
                           userID : obj.userId,
                           languageID : obj.languageID,
                           fileType : obj.file_type,
                           objectID : obj.objectId
                       }
		);

Well, “better” is subjective, but there are many ways to handle SQL in CFML. Other options include

  • qb a library for building queries via a programmatic DSL
  • quick - a CFML ORM library based on qb using functional programming
  • CF’s built-in Hibernate-based ORM

Here is an example of what qb would look like for an update query

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );

There may be, but this stuff gets tricky since it differs per DB and how you get it is normally DB-dependant. Often times, CF will put the number of affected rows in queryz.recordcount though from the looks of your screenshot, it may be 0 in this case. There is also the generated key available in the query result which is helpful on inserts. You have to specify a third arg to queryExecute to get the results back however

{
  results : 'local.qryResults'
}

And if CF isn’t giving it to you, you can always select it out directly yourself by adding a second statement like

SELECT @@rowcount

after the update (that’s MSSQL specific). Generally, queries like qb make this sort of thing easier.

Do you though? Usually if your SQL doesn’t throw an error, then it means it executed successfully! Unless you’re including not matching any rows in the where clause as being “unsuccessful”.