ahk script to select sql select script Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
usarian
Posts: 5
Joined: 25 Apr 2018, 09:24

ahk script to select sql select script

25 Apr 2018, 09:43

Mornin!

I'm a sql server developer, in our field it's common to compile many sql code statements into a single script, but if we want to test just one statement in the script, we can highlight it and click execute.

I'm trying to make an ahk script to select/highlight just one sql SELECT statement, hopefully targeting the statement that the cursor is currently closest to.. for example, if I add a column or a where criteria or a new join, it would be cool to hit a key combo and select the text of the sql statement that I just edited, without having to manually click and drag to select it.

I constructed a regular expression, but ahk doesn't seem to be able to select text based on regular expressions.

Code: Select all

i)select\s+.*\s+from\s+.*\s+(join\s+.*)*\s+(where(\s*.+\s*(AND|OR)?)+)?(\s*.+\s*)\s*(go|;)

here's some fake sql for testing

Code: Select all

use dealers
GO 

SELECT 
* 
from dbo.dealer_stone_price_sources join
where    1=1 and
2=2;

CREATE TABLE dbo.dealer_stone_price_sources
(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    dealer_id INT NOT NULL REFERENCES dealers(id),
    name nvarchar(50) NOT NULL,
    [description] nvarchar(255) NULL,
    [enabled] BIT NOT NULL DEFAULT (1),
    created_date SMALLDATETIME NOT NULL DEFAULT (GETDATE()),
    created_user_id INT NOT NULL,
    last_updated_date SMALLDATETIME NOT NULL DEFAULT (GETDATE()),
    last_updated_user_id INT NOT NULL
)
GO
Love to hear your suggestions the ideas!!

Usarian
MaxAstro
Posts: 557
Joined: 05 Oct 2016, 13:00

Re: ahk script to select sql select script  Topic is solved

25 Apr 2018, 10:56

So this is a weird and hacky solution, but it might actually work. I am assuming that a) You want to select everything from SELECT to GO, b) the statements you are talking about always begin with SELECT on a line of its own, c) they always end with GO, d) the words "SELECT" and "GO" do not appear alone like that anywhere inside the statement, e) the program uses `r or `r`n for newlines, and f) this is a text editor with the usual keyboard shortcuts.

Code: Select all

~^LButton::
	Send {Home}
	ClipText := ""
	while (!InStr(ClipText, "SELECT`r"))
	{
		Send +{Up}
		ClipSave := clipboard
		clipboard := ""
		Send ^c
		ClipWait
		ClipText := clipboard
		clipboard := ClipSave
	}
	Send {Up}
	Send {Down}
	while (!InStr(ClipText, "GO`r"))
	{
		Send +{Down}
		ClipSave := clipboard
		clipboard := ""
		Send ^c
		ClipWait
		ClipText := clipboard
		clipboard := ClipSave
	}
	Send +{Left}
	return
Note that in the example you posted there is a space after SELECT; if that is standard, you'll need to adjust this script to account for that. Otherwise remove the space. Tested on your example code after removing the space and it works fine. Kinda fun to watch it work, too. :)
usarian
Posts: 5
Joined: 25 Apr 2018, 09:24

Re: ahk script to select sql select script

25 Apr 2018, 13:41

I like it! it is definitely fun to watch, but it seems to highlight all the way to the top every time, and a lot of the time when I triggered it, nothing happened at all.

This definitely gets me going in a different direction than what I've been trying though, thank you so much!

I think I'm going to leave it open for now to see if any other responses trickle in later
MaxAstro
Posts: 557
Joined: 05 Oct 2016, 13:00

Re: ahk script to select sql select script

25 Apr 2018, 15:04

If it's highlighting all the way to the top, it's not finding the SELECT; probably because it's not seeing the newline it expects. Try changing "SELECT`r" to "SELECT`n" or even just "SELECT" (although that will have issues if the word SELECT appears anywhere in the block of text.)
usarian
Posts: 5
Joined: 25 Apr 2018, 09:24

Re: ahk script to select sql select script

25 Apr 2018, 15:54

Yep, that worked. I took the `r out and just left select and it's working like a charm now.

Only It seemed to still be running the script even after it detected, sending control key presses. I had to kill autohotkey to get it to stop.. stuck in some sort of loop. I'll play with it later

Thank you so much again!
MaxAstro
Posts: 557
Joined: 05 Oct 2016, 13:00

Re: ahk script to select sql select script

25 Apr 2018, 16:17

I suspect it is also not finding "GO" for the same reason.
MaxAstro
Posts: 557
Joined: 05 Oct 2016, 13:00

Re: ahk script to select sql select script

26 Apr 2018, 10:12

Code: Select all

~^LButton::
	Send {Home}
	ClipText := ""
	Breaktest:= 0
	while (!InStr(SubStr(ClipText, 1, 9), "SELECT"))
	{
		Send +{Up}
		ClipSave := clipboard
		clipboard := ""
		Send ^c
		ClipWait
		ClipText := clipboard
		clipboard := ClipSave
		Breaktest++
		if (Breaktest > 50)
		{
			MsgBox % "No valid start point found."
			return
		}
	}
	Breaktest := 0
	Send {Up}
	Send {Down}
	while (!InStr(SubStr(ClipText, StrLen(ClipText)-6), "GO"))
	{
		Send +{Down}
		ClipSave := clipboard
		clipboard := ""
		Send ^c
		ClipWait
		ClipText := clipboard
		clipboard := ClipSave
		Breaktest++
		if (Breaktest > 50)
		{
			MsgBox % "No valid end point found."
			return
		}
	}
	Send +{Left}
	Send +{End}
	return
I thought of a better way to test for the SELECT and GO keywords that should be much more reliable. Also added a couple sanity checks; it will break the loop if it goes 50 lines without finding anything, rather than looping forever.
usarian
Posts: 5
Joined: 25 Apr 2018, 09:24

Re: ahk script to select sql select script

26 Apr 2018, 10:46

I'm very grateful for your interest, I guess since you're still working on it I'll give you a bit more detail

- it needs to be able to handle if the cursor is on the same line as the SELECT keyword without going up a line to begin selecting text
- "GO" and ";" are only two of the keywords that could indicate an end of the SELECT statement, these are normal statement separators but aren't required in all cases. For example if multiple SELECT statements are in the script, there won't usually be any separator, it will look more like:

Code: Select all

SELECT * from tableA
SELECT * from tableB
SELECT * from tableC
.. usually more complex and usually multi-line with JOIN and WHERE and also GROUP BY or whatever. But it shoudl select starting at whatever select statement the cursor is currently sitting on from beginning to end but not bleed into the next or the previous. These singl liners is easy, I have a thing to select from home to end, but it's when there's a lot of lines it gets complicated.

Is there a way to compile a list of keywords telling the script to stop search, and to not include the detected keyword in the selection -- oh, and also have it detect the end of the file. The end fo the file thing is what's getting me right now, there's nothing visibly happening when it is trying to go down but there's no more rows in the file


Alternatively, I do have that little regular expression I made earlier - is there a way to just have it select the text matched by the regex? That way I can simply tweak the regex the it will adjust the rest on it's own. plus it would actually be an instant grab (..well.. in theory..)
MaxAstro
Posts: 557
Joined: 05 Oct 2016, 13:00

Re: ahk script to select sql select script

26 Apr 2018, 11:01

I've been thinking of how to use the regex, and unfortunately I'm coming up blank, at least as far as instantly. You could Ctrl + A, Ctrl + C to select everything, the parse the clipboard for that regex, but I'm not sure where to go from there. Actually, I think there is a script to search the screen for a certain block of text, but I think it requires you to have a picture of the text in the first place, so that doesn't really help in this case...
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: ahk script to select sql select script

26 Apr 2018, 15:02

What application do you use? Oracle's SQLDeveloper allows you to do Ctrl + Enter which executes the statement that the cursor is currently within. Basically goes from the nearest SELECT to the next semi-colon.
usarian
Posts: 5
Joined: 25 Apr 2018, 09:24

Re: ahk script to select sql select script

26 Apr 2018, 15:50

This is Microsoft SQL Server, the prepackaged tool most people use is SQL Server Management Studio (SSMS). If you hit F5 or CTRL E or click EXECUTE without highlighting text it will execute the whole script, or you can use the keyboard or mouse to highlight an isolated statement in the script and then when you execute, only the highlighted text will be in effect
User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: ahk script to select sql select script

26 Apr 2018, 17:37

Hm that’s unfortunate. They should really add the feature I mentioned lol

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: No registered users and 142 guests