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
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”.