Page 1 of 1

ADO / Database advice for AHK

Posted: 15 Dec 2013, 08:23
by Joe Glines
I work a lot with CSV files and was going to write a script that opened, manipulated, deduped,etc. files (both for me and colleagues) when I realized that I could use Microsoft's ADO to handle much of the heavy lifting. I know virtually nothing about ADO however, from my testing, it seems most Win 7 computers have it by default. (I tried Win 8 and received some errors, my quick searching sounds like it would need some tweaking but should be available)

I've seen quiet a few different posts on the other forum providing various AHK libraries for using ADO however I have no way to compare/evaluate one over the other. (This is where some sort of "like" button would be handy)

Does anyone have a recommendation for a particular AutoHotKey library I should use? I will need basic functions of
1) read in CSV / Tab delimited Unicode files
2) run sql code to select rows (using basic code like: Distinct, Count, Like)
3) return counts of above / Export rows found above.

If anyone has any links to suggested readings on ADO I'd love that as well. I saw ADO is on w3 Schools but am not sure that is going to be helpful.

thank you for your time!

Re: ADO / Database advice for AHK

Posted: 15 Dec 2013, 09:48
by tank
there are libraries yes but i have never used or wanted to use any of them. much like my own iweb library it became nonsense in the face of integrated COM in 1.1
ADO is more or less the cornerstone of client interaction with DB. it isnt going anywhere
recommend that you configure a user dsn for text files

any way here is the best advise i can offer read this article
It should require trivial almost no effort to convert the examples to ahk

Re: ADO / Database advice for AHK

Posted: 15 Dec 2013, 10:23
by Joe Glines
Thanks for the expert advice! That article looks spot-on!

Re: ADO / Database advice for AHK

Posted: 16 Dec 2013, 01:55
by VxE
Here's my ADODB wrapper function:

The example in that thread shows SQL querying a CSV file like a database table. I don't think you can UPDATE or DELETE using text file drivers.

Re: ADO / Database advice for AHK

Posted: 16 Dec 2013, 15:22
by Joe Glines
Thanks! This is the actual post I'd reviewed a while ago that made me realize I could perform sql queries on text files. :) I was just curious if there was a "better" approach. One reason is that I tested this script by searching through ~100K records. While the timing wasn't bad, I often work with much larger files (and have much more complicated queries) thus I was hoping for a way to speed it up. I know I can add SetBatchLines -1 to my script but I'm wondering if there are other, obvious, things that could be done that would speed it up.

Re: ADO / Database advice for AHK

Posted: 26 Dec 2013, 22:16
by Joe Glines
So after hours upon hours of playing I was able to get the text driver to open a UTF-8 file using a schema.ini file and tweaking the connection string. I also configured it to open a tab delimited file with a .tsv extension (I would not have thought that would have been difficult but only specific extensions are enabled by default)

I've got an okay-looking gui that will show headers on the top allowing for selecting specific columns then adding where clauses and return the results in a ListView. Unfortunately I can't seem to get the LIstview to re-create itself. Everything runs fine the first time through. Then when I change something and re-submit, the LV_Delete() command removes all the rows (and I remove the columns) but it will not redraw the ListView. When I put a msgbox in-place of the listview , the query results are returning, just nothing is being shown.

If anyone could offer up some help I'd be extremely grateful!!! I've spent several dozen hours building this script and am just plain STUCK :oops:

While I'm specifically looking for help on the LIstView, any other feedback is welcome. I am faking everything I'm doing here and am sure there are much, much better approaches to this.