http://coldfusion.com logo
Join Slack
Powered by
Regex question: come all who are ready for a chall...
# cfml-general
s
Regex question: come all who are ready for a challenge! i need to be able to select this entire string skipping over escaped characters
Copy code
'select *, 1 as ''top'', testCol as "top2" from mytable'
or
Copy code
"select *, 1 as ""top"", testCol as 'top2' from mytable"
it also can have line breaks in it
Copy code
"select *, 
        1 as ""top"", 
        testCol as 'top2' 
 from mytable"
I have tried googling it and creating a few additional solutions but none have proven to be the right solution. The reward for will be a new cfml vscode extension to format SQL syntax within cfc’s and cfm pages
w
what do you mean by this: i need to be able to select this entire string skipping over escaped characters
you mean you do or don't want to include double quotes? it's not clear
s
Copy code
//from this part of the code
queryExecute('select *, 1 as ''top'', testCol as "top2" from mytable');

//i need to extract 
'select *, 1 as ''top'', testCol as "top2" from mytable'
w
sec
s
I need it to not use the parens because the sql string might not be in a function call, it may be just set to a variable
w
sorry, what do you mean not use the params?
s
not params, parenthesis
r
You'd have to use a parser for that then.
w
i for one am totally ok with a sql statement assigned to a variable, in quotes, as being highlighted as a string and not sql per se
but sec, i'll do a variation
s
and since its in node, you can also use back-refrences and lookaheads if that is necessary
you can use this for testing https://regexr.com/
r
What are you trying to do? Find all SQL in a code base?
s
well all SQL strings in a file, so basically
w
to syntax highlight them or for some other purpose?
s
the goal is that i am writing a vscode extension to find and format all SQL within a cfml page
r
Okay. It'd probably be better to ask how do I extract all SQL strings from a file then what regex expression gives me blah.
t
also, i feel like the cfml extension does a pretty good job of this.
s
well i have a second check after strings are grabbed to test if it is in fact SQL
@Tim the Adobe one? because the vscode-cfml does not do formatting
t
true, vscode-cfml does not do formatting, but it does a pretty good job at highlighting.
s
Agreed, and i love that about the extension, truthfully i would love to some how integrate cfformat into the vscode-cfml plugin to make it complete and fast. but for now that is a bit over my head
w
r
Fat fingers today.
w
have to say honestly that i think this will be a near impossible undertaking
it's not the same as parsing the inside of a cfquery tag
and you're wanting to format text inside string vars
if you just want to highlight reserved words, that's pretty easy, but grabbing entire sql statements, especially when they're assembled from multiple statements, yikes
r
I only have a problem with highlighting when I do something like 
sql = "SELECT * FROM " & table & " WHERE id = :id"
w
if you just want highlighting of sql reserved words, i can give you js i wrote years ago, works pretty good (sql server specific but easy enough to generify if required)
for example, what you're seeing here is a sql string with the highlighting applied entirely via js:

https://www.websolete.com/wp-content/uploads/2019/09/7_procschema.png▾

but it's not 'formatted' per se
just wraps some spans around reserved words, comments, etc
s
Sorry just getting back to this
I think we are getting ideas mixed here, and let me shed some light to help understand my plan. vscode-cfml does and will continue to handle the Syntax highlighting for CFML pages which includes SQL in the document. I am trying, with decent success to add a companion extension to vscode to “on save” or by running a command. Format SQL inside your CFC/CFM from something like this
Copy code
SELECT CASE WHEN supplimental.ogc_fid=1 THEN 'Supplimental' ELSE 'Primary' END AS TYPE,round(SUM (apn_acres) :: NUMERIC,2) FROM apn LEFT JOIN supplimental ON st_intersects (ST_SetSRID (supplimental.wkb_geometry,3857),apn.wkb_geometry) GROUP BY supplimental.ogc_fid
to something like this
Copy code
SELECT
CASE
		
	WHEN
		supplimental.ogc_fid = 1 THEN
			'Supplimental' ELSE'Primary' 
			END AS TYPE,
		round( SUM ( apn_acres ) :: NUMERIC, 2 ) 
	FROM
		apn
		LEFT JOIN supplimental ON st_intersects (
			ST_SetSRID ( supplimental.wkb_geometry, 3857 ),
			apn.wkb_geometry 
		) 
GROUP BY
	supplimental.ogc_fid
using an NPM module called https://github.com/sql-formatter-org/sql-formatter so to accomplish this i need to find SQL strings in your current document and pass them to the formatter, then replace the unformatted code with formatted
Open in Slack
PreviousNext
Powered by