[Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

Post your working scripts, libraries and tools
User avatar
VxE
Posts: 44
Joined: 30 Sep 2013, 10:35
Location: Simi Valley, CA

[Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

01 Oct 2013, 22:16

This function uses an ADO COM object to connect to a database (e.g: SQL Server, MySQL, Access, or CSV file), executes a SQL query, then returns the query results (for SELECT statements).

The connection string is probably the most vexing part of SQL. Fortunately, Google is your friend, you just need to search for "connection string" along with the brand name of your RDBMS.

Although not canon, this function accepts up to two additional options appended to the connection string. RowDelim and ColDelim will tell ADOSQL to return a delimited string instead of an object. This is demonstrated in the example below.

                Download: AHK-Lib-ADOSQL.zip

                Also on GitHub

Here's an example showing the use of microsoft's ODBC text driver with a csv file.

Code: Select all

SetWorkingDir, %A_ScriptDir%
IfNotExist, products.csv
	FileAppend,
(
P_CODE,P_DESCRIPT,P_INDATE,P_QOH,P_MIN,P_PRICE,P_DISCOUNT,V_CODE
"11QER/31","Power painter,15 psi.,3-nozzle","03-NOV-2011",8,5,109.99,0.00,25595
"13-Q2/P2","7.25-in. pwr. saw blade","13-DEC-2011",32,15,14.99,0.05,21344
"14-Q1/L3","9.00-in. pwr. saw blade","13-NOV-2011",18,12,17.49,0.00,21344
"1546-QQ2","Hrd. cloth,1/4-in.,2x50","15-JAN-2012",15,8,39.95,0.00,23119
"1558-QW1","Hrd. cloth,1/2-in.,3x50","15-JAN-2012",23,5,43.99,0.00,23119
"2232/QTY","B\&D jigsaw,12-in. blade","30-DEC-2011",8,5,109.92,0.05,24288
"2232/QWE","B\&D jigsaw,8-in. blade","24-DEC-2011",6,5,99.87,0.05,24288
"2238/QPD","B\&D cordless drill,1/2-in.","20-JAN-2012",12,5,38.95,0.05,25595
"23109-HB","Claw hammer","20-JAN-2012",23,10,9.95,0.10,21225
"23114-AA","Sledge hammer,12 lb.","02-JAN-2012",8,5,14.40,0.05,NULL 
"54778-2T","Rat-tail file,1/8-in. fine","15-DEC-2011",43,20,4.99,0.00,21344
"89-WRE-Q","Hicut chain saw,16 in.","07-FEB-2012",11,5,256.99,0.05,24288
"PVC23DRT","PVC pipe,3.5-in.,8-ft","20-FEB-2011",188,75,5.87,0.00,NULL 
"SM-18277","1.25-in. metal screw,25","01-MAR-2012",172,75,6.99,0.00,21225
"SW-23116","2.5-in. wd. screw,50","24-FEB-2012",237,100,8.45,0.00,21231
"WR3/TT3","Steel matting,4""x8""x1/6",.5" mesh","17-JAN-2012",18,5,119.95,0.10,25595
), products.csv

; Connection strings are typically semicolon-separated lists of key/value pairs
connection_string =
( ltrim join;
	Driver={Microsoft Text Driver (*.txt; *.csv)}
	Extensions=asc,csv,tab,txt
	Persist Security Info=False
)

; Here, the "coldelim" option is added on the fly to tell ADOSQL to return a string
MsgBox % ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT P_CODE, P_PRICE, P_DESCRIPT
	FROM products.csv
	WHERE P_PRICE > ( SELECT AVG( P_PRICE ) FROM products.csv )
)")

filedelete, products.csv
User avatar
Joe Glines
Posts: 667
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

16 Dec 2013, 14:35

Thanks for the link! I'd stumbled upon this one earlier and played with it. (which is what made me realize this was possible in the first place) I was just hoping to find one that had a clearer look at what was going on (and not necessarily needing the library file). I think I will use it as my template and build some gui interface around it to submit sql code.

One question I do have is the sql code took a fair amount of time to process 100K rows with just 1 matching pattern. Frequently I'll be processing matching patterns (LIKE) commands and will be hitting rows often between 500K - 1 million. Is there any way I can speed this script up? I know I can add "SetBatchLines -1" to allow it more CPU time but I don't know if I could speed up anything else.

Regards,
Joe
User avatar
VxE
Posts: 44
Joined: 30 Sep 2013, 10:35
Location: Simi Valley, CA

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

16 Dec 2013, 22:02

SetBatchLines has no effect on the query execute time. "LIKE" is a very slow operation (it's similar to RegexMatch). If you're running a query like "...WHERE column LIKE 'pattern'..." against ~1 million records, expect it to take a while.

Of course, you can always install MySQL, import the records there, and enjoy the speed benefits of having a RDBMS facilitate your query. As long as you have the driver, you can use ADOSQL with MySQL using a connection string like this

Code: Select all

DRIVER={MySQL ODBC 5.2w Driver};Server=localhost;Port=3306;Database=mydb;Uid=admin;Pwd=12345;Option=3
User avatar
Joe Glines
Posts: 667
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

17 Dec 2013, 10:31

Thanks for pointing out the setbatch lines will not affect it.

Yes, I understand like commands take a while to execute, I was just wondering if there was a way to speed up anything. While installing mySQL is an option for me (which I still might do for my own benefit) trying to get my colleagues to install /use it isn't really an option for me. :(

I don't suppose anyone has tried to use ADO to access a Sharepoint database /List? I'm thinking if I can figure out my connection string I should be able to do this...

Thank you for your help & recommendations!
Regards,
Joe
User avatar
Joe Glines
Posts: 667
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

17 Dec 2013, 22:09

VxE, I'm leveraging your code to import, apply a filter, and save and am having a problem getting the path to the CSV file I'm opening to work.

When place the CSV file in the same folder as the script and then reference as you do in your example
FROM products.csv
everything works fine. I'm planning to build this to open csv files at any location and no matter how I pass in the path to the file, I can't get it to find the file.

At first I was using the Explorer_GetSelected() function but even when I inserted the path directly in the script it doesn't work. ie. put something like this in your code:
FROM C:\test\ADOSQL\Example breakout\products.csv

I can't get it to work. I've tried wrapping it with single & double quotes, etc. but can't figure it out. Any idea what I'm doing wrong?

thank you!
Regards,
Joe
User avatar
Joe Glines
Posts: 667
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

18 Dec 2013, 07:54

So I did some digging and found this link which seemed (somewhat) related. After playing around I got the following to work.

Code: Select all

File:="[D:\my test\sec level]\[my email.csv]"
MsgBox % Clipboard:=ADOSQL( connection_string ";coldelim=   `t", "
(
   SELECT distinct EMAIL,STAKEHOLDER_NUM,COUNTRY
   FROM " . File . "
   Where email like '%joe%'
   )")
I was very surprised to see that I could not have the folder path and the file name within the same brackets. That is the following did not work: File:="[D:\my test\sec level\my email.csv]"

While I can write some code to wrap the folder path and file name separately I'd love if anyone has a better approach.
User avatar
VxE
Posts: 44
Joined: 30 Sep 2013, 10:35
Location: Simi Valley, CA

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

18 Dec 2013, 20:58

Honestly, I just set the working dir to the folder with all the csv files, then use the file names like table names in the query.
User avatar
Joe Glines
Posts: 667
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

20 Dec 2013, 09:18

That will take care of having spaces in the directory however it will still break if the file name itself has a space in it. Anyway I've figured out how to wrap the path & file name to import correctly. I've also figured out how create a Schema.ini file to import tab delimited files however it looks like the Text driver can only handle Ansi or OEM encoding. I really need UTF8. :(

I've done a lot of googling and find several article talking about the issue but can't seem to find a work-around.

Does anyone know of a work around for the text driver? Or perhaps a way to use a different driver to import text files?
thank you
Joe
User avatar
Joe Glines
Posts: 667
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

30 Dec 2013, 09:19

I've figured out how to open UTF-8 files :) and am working on some further tweaks but I'm curious if you (VxE) or anyone knows if I can open and join two text files (or more). It seems conceivable since I can perform sql commands but i'd first have to open tow connections to different text files and then join them. I thought I'd see if you of a reason why it cannot be done before I go down that long path.
Regards,
Joe
User avatar
VxE
Posts: 44
Joined: 30 Sep 2013, 10:35
Location: Simi Valley, CA

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

30 Dec 2013, 17:18

Just use the file name as you would a table name in the JOIN clause.

Code: Select all

...
FROM [file1.csv] a
INNER JOIN [file2.csv] b
ON a.id = b.id
User avatar
Joe Glines
Posts: 667
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

01 Jan 2014, 21:38

Wow - really? I thought I'd have to "Open" it as I did the first one. Perhaps I'm confusing things and I'm actually opening an ADO DB "connection" (which has little to do with the file) Anyway, I'll play with it. Thanks for simplifying my world!
Regards,
Joe
newpie
Posts: 37
Joined: 01 Jun 2014, 07:50

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

01 Jun 2014, 08:51

Hello, I was wondering if someone could give me a simple ahk msgbox example of using this for a MySQL database. I appreciate the help.

I found two connection settings thru my provider:

Version: MySQL 5
Username: cpUsername_dbUserName
Database Name: cpUsername_dbName
Password: The password for cpUsername_dbUsername
Hostaddress: localhost
Port: 3306


Or

Host name = (use the server IP address)
Database name = (cpanelUsername_databaseName)
Database username = (cpanelUsername_databaseUsername)
Database password = (the password you entered for that database user)
MySQL Connection Port = 3306
User avatar
VxE
Posts: 44
Joined: 30 Sep 2013, 10:35
Location: Simi Valley, CA

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

08 Jun 2014, 19:27

You mention a provider, does that mean your mysql database is hosted by a company like amazon or rackspace? If so, ask your provider for detailed instructions for connecting to a mysql database on your instance.
User avatar
Gio
Posts: 865
Joined: 30 Sep 2013, 10:54
Location: Brazil

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

07 Mar 2015, 11:17

Hello there VxE :thumbup:

Your function has helped me greatly with DataBase managing from AHK scripts, so i thank you for it.

The user johnny256ahk asked one thing about the function that i could not answer, so i will replicate it here:
The issue I see here is I have to pass the connection-string every time, and keep opening/closing a connection. I don't know if the performance of keeping a connection open would be better, but being able to open it and have a handle would be neat so my logging functions don't have to know the connection string!

Any thoughts on that?
Original topic

Any answers would be of great help :thumbup:
User avatar
boiler
Posts: 3880
Joined: 21 Dec 2014, 02:44

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

08 Mar 2015, 01:20

Once I determine the connection string that works for me, would it also work for anyone else using the same RDMS brand on their machine? The script would know the user ID, password, and a database name for the database it is trying to access.

Specifically, I am using PostgreSQL, as will all other users of the script. The databases it will access will all have the same schema. I currently run psql command line queries with output directed to a file, and this seems like it should be faster since it would reduce the hard drive access significantly.
User avatar
VxE
Posts: 44
Joined: 30 Sep 2013, 10:35
Location: Simi Valley, CA

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

19 Mar 2015, 22:34

@Gio: You could boost performance by keeping an open connection, though whether you would notice the performance boost is anyone's guess. If your script creates an ADODB.connection object, you can configure it however you like and manually open/close the connection. With an open connection, you can submit queries through the 'execute' method and then handle the recordset. Just double check that the recordsets are cleaned up after you're done with them.


@boiler: Once you have a working connection string, it will work on other computers as long as 1) the HOST is valid and reachable and 2) the correct driver is installed ({PostgreSQL} in your case) and 3) the UID and PWD are correct.
User avatar
jNizM
Posts: 2583
Joined: 30 Sep 2013, 01:33
GitHub: jNizM
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK

20 Mar 2015, 02:01

Has someone experience with MSSQL OLAP Cube connections?
[AHK] 1.1.30.03 x64 Unicode | [WIN] 10 Pro (Version 1909) x64 | [GitHub] Profile
Donations are appreciated if I could help you
keiiz
Posts: 3
Joined: 24 Apr 2015, 19:20

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

22 Dec 2015, 05:23

Thanks VxE for an awesome function!

just a quick question someone can hopefully help with..! Is it possible to return the rows affected from the query? (and is this the best way to tell if inserts are successful?)

Looking at the ADO documentation on w3schools(won't let me post the url) w3schools / asp/met_conn_execute.asp

It would appear the ADO execute method should support this:

Syntax for row-returning

Code: Select all

Set objrs = objconn.Execute(commandtext,ra,options)
ra Optional. The number of records affected by the query

But editing the function and trying it with code below doesn't seem to work

Code: Select all

	If !( coer := A_LastError ){
		oRec := oCon.execute(Query_Statement,recordsAffected)
		msgbox %recordsAffected%
	}
or have I completely missed something?

Thanks in advance!
User avatar
VxE
Posts: 44
Joined: 30 Sep 2013, 10:35
Location: Simi Valley, CA

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

23 Dec 2015, 21:08

I imagine you'd need to pass the address of the output variable. Unfortunately I can't test it right now.

Code: Select all

	If !( coer := A_LastError ){
		oRec := oCon.execute(Query_Statement,&recordsAffected)
		msgbox %  NumGet(recordsAffected)
	}
keiiz
Posts: 3
Joined: 24 Apr 2015, 19:20

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

30 Dec 2015, 00:05

Hi VxE,
thanks for the reply and merry xmas!

Gave it a try, but no dice unfortunately - did some more reading and your thoughts of passing to the address seem to be correct.. found a thread where someone got it to work in vba
stackoverflow / questions/12676747/how-to-get-the-affected-rows-in-vba-ado-execute

Dim recordsAffected As Long
cn.Execute "SELECT * INTO someschema.sometable FROM someschema.anothertable", _
recordsAffected
MsgBox recordsAffected

I can't seem to get the execute command to modify the recordsAffected variable in ANY way..
I've tried to pass it both as an address and as a variable.. checking the variable and contents of the address before and after running the execute command, and haven't found one instance where the variable has changed..
bit stumped..

only other piece of information that I've found that may be of some interest is

RecordsAffected
Optional. A Long variable to which the provider returns the number of records that the operation affected.

I don't really do any work with address variables.. but not sure if the fact that it wants a Long variable is going to cause any issues? might have to shelve this till I have a stroke of inspiration, unless there's anymore ideas?

Cheers

Return to “Scripts and Functions”

Who is online

Users browsing this forum: dominicx and 52 guests