andyMatthews.net

User Greg Morphis asked a question today over on the House of Fusion mailing list.

I just ran into a problem with some old code one of team mates did. He used <cfqueryparams but did not specify a cfsqltype. We upgraded our DB from Oracle 9i to 10g and all of the sudden we're getting Error Executing Database Query errors. Logs show A nonnumeric character was found when expecting a numeric character. One of the queries had a date column and the cfqueryparam looked like "<cfqueryparam value="foo" /> no cfsqltype and according to the docs it's default is CF_SQL_CHAR. So I need to go through the 1321 <cfqueryparams and look for ones with no cf_sql_type. Is there a regex I can throw into Eclipse to help find these queries?

I tried a few things but couldn't get a good match so I asked about it on Twitter. A few minutes later Jason Dean, of 12 Robots fame, came back to me with a great answer that worked perfectly. Here's the test strings, and the regex he came up with. After that we'll dissect the regular expression

• • <cfqueryparam static string
  • ( begins a capturing group
  • . matches any single character
  • ? matches the preceding character 0 or 1 times
  • [^cf_sql_type] square brackets make a character set, but the caret ^ inverts that. This section now matches everything BUT the string cf_sql_type
  • ) ends a capturing group
  • + matches the preceding token or set 1 or more times. Greedy, matching as many characters as possible
  • ? when immediately following a +, it converts it to a lazy match, matching as few characters as possible

By the way...if you see any errors in this regex, or have a suggestion that's more elegant or would work better, feel free to post it.

Update: On a side note, Grant Skinner has an excellent regex tester on his website.