ADO / Database advice for AHK

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Joe Glines
Posts: 696
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

ADO / Database advice for AHK

15 Dec 2013, 08:23

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!
Regards,
Joe

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation
User avatar
tank
Posts: 2830
Joined: 28 Sep 2013, 22:15
Facebook: charlie.simmons.7334
Google: ttnnkkrr
GitHub: ttnnkkrr
Location: Irving TX
Contact:

Re: ADO / Database advice for AHK

15 Dec 2013, 09:48

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
http://msdn.microsoft.com/en-us/library/ms974559.aspx
It should require trivial almost no effort to convert the examples to ahk
We are troubled on every side‚ yet not distressed; we are perplexed‚
but not in despair; Persecuted‚ but not forsaken; cast down‚ but not destroyed;
https://www.facebook.com/ahkscript.org
If you have forum suggestions please submit a pull request
Check Out WebWriter
Thanks Tank :thumbup:
User avatar
Joe Glines
Posts: 696
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: ADO / Database advice for AHK

15 Dec 2013, 10:23

Thanks for the expert advice! That article looks spot-on!

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation
User avatar
VxE
Posts: 44
Joined: 30 Sep 2013, 10:35
Location: Simi Valley, CA

Re: ADO / Database advice for AHK

16 Dec 2013, 01:55

Here's my ADODB wrapper function: http://ahkscript.org/boards/viewtopic.php?f=6&t=74

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.
User avatar
Joe Glines
Posts: 696
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: ADO / Database advice for AHK

16 Dec 2013, 15:22

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.

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation
User avatar
Joe Glines
Posts: 696
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: ADO / Database advice for AHK

26 Dec 2013, 22:16

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.
Joe

Spoiler

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], boiler, Koopa8840, tatagi, Xtra and 33 guests