http://coldfusion.com logo
Join Slack
Powered by
trying to debug and replicate a specific sql error...
# cfml-general
w
trying to debug and replicate a specific sql error being thrown from a query. anyone know if you can explicitly throw an error of type Database and specify a particular sql error code? i need to be able to force the same type of error getting thrown in order to come up with some handling for it
t
could you do it with java it is likely to be driver specific
w
well, i'm trying to simulate a deadlocking event where one query is victimized by another process. just a hard thing to force. i'm trying to run an uncommitted transaction in SSMS while running the template containing the affected query, but i don't seem to reach the timeout point.
and yeah, if i could throw the correct error be it in java or otherwise, that would be great, but you can't explicitly throw type="database" errors it seems
t
even if you throw the error in a java class?
w
if you can point to an example that would be great. meanwhile, i think the lock timeout is forever by default, so i'm looking into setting it to a few seconds for the purpose of this query
c
what about putting it into a database stored proc with try catch? you should be able to raise the error in the proc, migh be able to do checks that way or make the stored proc handle the lock condition itself.
a
I don't follow this bit:
if you can explicitly throw an error of type Database and specify a particular sql error code? i need to be able to force the same type of error getting thrown in order to come up with some handling for it
If you know the specifics of the exception, how come you need to actually explicitly throw it to be able to "come up with some handling for it"?
w
i'm trying to replicate a deadlock and do some handling of it. i'm focused on the 'victim' query, i have no control over the other process that is creating the deadlock situation. so in trying to replicate it, i was merely trying to simulate the deadlock scenario, which i got to close enough to by beginning a transaction in SSMS and doing an update query to the table but not explicitly committing or rolling back the transaction, then adding a SET LOCK_TIMEOUT xxx statement to the top of my cfquery to try and force a deadlock timeout. it wasn't exactly what i was hoping for but created similar enough conditions for me to do what i needed to do.
in a deadlock timeout like this, the sql is entirely valid, and it's a backend exception that's thrown by the db, so i couldn't replicate it just by mangling the sql itself or anything like that
and the idea is to grab the sql string originally attempted, but which was victimized in the deadlock, and simply retry that sql statement after sleeping a few seconds. that's why i needed to try and get the exact exception thrown in a deadlock, so i could get the sql intact as it would be in the actual exception. throwing custom errors and whatnot to simulate it would not include the sql in the catch
a
Right, so this was just a "I've written the fix, I just wanna test that it works" scenario, basically.
The SQL returned in that exception has re-inlined all the parameter values though, I think? Not sure it'll just be a matter of recycling the returned SQL.
I'd perhaps take this approach:
Copy code
// RetryDecorator.cfc
component implements=SameInterfaceAsOriginalObject { // so it can be a drop-in replacement

    function init(originalObject) {
        variables.originalObject = arguments.originalObject
    }
    
    function originalMethod(same, method, signature) {
        try {
            return variables.originalObject.originalMethod(argumentCollection=arguments)
        
        } catch (database e) {
            if (possibly more conditional logic to identify the deadlock) {
                sleep(a while)
                return variables.originalObject.originalMethod(argumentCollection=arguments)
            }
            rethrow
        }
    }
}
w
that's essentially what we do in cfcs as well, however i'm dealing with legacy code in a cfm template and refactoring to cfc isn't in the cards
✅ 1
a
How long's it been "legacy" for... to still have 
<cfquery>
calls in a .cfm file??
No code written after about 2005 should be like that 😐
😅 1
w
looks like first iteration of it was 2007
Open in Slack
PreviousNext
Powered by