AutoHotkey Community

It is currently May 24th, 2012, 5:54 pm

All times are UTC [ DST ]




Post new topic Reply to topic  [ 132 posts ]  Go to page 1, 2, 3, 4, 5 ... 9  Next
Author Message
 Post subject: ADO COM - Database Query
PostPosted: February 12th, 2007, 8:03 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
It queries the database through ADO.

NEED: COM Standard Library.

Code:
; Adjust sSource and sConnect appropriately
sSource  := "SELECT * FROM " . "atlas" ; . " WHERE country LIKE 'A%'"
sConnect := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . "C:\Database\atlas.mdb"

COM_Init()
prs :=   COM_CreateObject("ADODB.Recordset")
COM_Invoke(prs, "Open", sSource, sConnect)
Loop
{
   If   COM_Invoke(prs, "EOF")
      Break
   pFields   := COM_Invoke(prs, "Fields")
   Loop, %   COM_Invoke(pFields, "Count")
      pField:= COM_Invoke(pFields, "Item", A_Index-1)
   ,   sData .= COM_Invoke(pField, "Name") . ": " . COM_Invoke(pField, "Value") . "`r`n"
   ,   COM_Release(pField)
   COM_Release(pFields)
   COM_Invoke(prs, "MoveNext")
}
COM_Invoke(prs, "Close")
COM_Release(prs)
COM_Term()
MsgBox, % sData


Last edited by Sean on January 13th, 2009, 9:19 am, edited 6 times in total.

Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 12th, 2007, 10:53 am 
Offline

Joined: December 27th, 2005, 1:46 pm
Posts: 6837
Location: France (near Paris)
Beside the base functionality (which is useful), it seems you offer a good range of helper functions for Com support.
I guess it needs the latest version of AutoHotkey (v.1.0.46.08 at least), you should have mentioned it to avoid messages like "It doesn't work"...
Do you know a good online tutorial for Com? I always wanted to learn it, but never went very far, this could be a good new opportunity.

_________________
Image vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")


Last edited by PhiLho on February 12th, 2007, 1:08 pm, edited 1 time in total.

Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 12th, 2007, 11:36 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
PhiLho wrote:
Beside the base functionality (which is useful), it seems you offer a good range of helper functions for Com support.

Thanks. As a matter of fact, I originally planned to upload it as CoHelper.ahk. But, I was not sure if it had to be. BTW, I removed a few APIs to reduce the size of the script.

Quote:
I guess it needs the latest version of AutoHotkey (v.1.0.46.08 at least), you should have mentioned it to avoid messages like "It doesn't work"...

I was aware of it, but forgot to do it. Thanks for mentioning it.

Quote:
Do you know a good online tutorial for Com? I always wanted to learn it, but never went very far, this could be a good new opportunity.

Probably the most frequently opened file lately in my system has been Platform SDK Documentation. If I didn't get enough information from it, then I went to MSDN or googled it.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 12th, 2007, 11:54 am 
Offline

Joined: May 24th, 2006, 2:49 pm
Posts: 4511
Location: Belgrade
I know superb COM tutorial. I have read 4,5 of them already using different languages, but this one makes it crystal clear. It describes COM architecture in detail rather then explaining how to use it. It does so by creating simple COM object starting from blank C struct that at the end becomes valid COM class.

There are 6 articles (some form of mini book). Its made by the man who created cwebpage.dll. My recomendation is to go to tutorial 6 (last one) and download the source code for it. In that archive, the author made html with all 6 articles, and all 6 source archives in the form of book with the TOC.

This is the link to the first article:
http://www.codeproject.com/com/com_in_c1.asp

This is the link to the package containing all 6:
http://www.codeproject.com/com/com_in_c ... c6_src.zip

Its really extraoridnary set of articles. Don't miss it. Read every one of them as they are connected. Then you will see COM in its real lite.

I was learning this to understand Seans code better and to see what should I do to create COM interface in AHK. This article showed me that it is possible to create such thing in AHK to behave the very same as for instance in VBScript. TypeLibraries that are needed to decifer COMs data structures and interfaces are available as COM objects too, and those can be quieried in AHK to create VTable that can be used to invoke COM object inteface methods from the ahk script.

2 Sean
If you want to create COM interface in AHK for AHK then I suggest you to create new topic on AHK Wiki so all interested can colaborate. I have pretty good understanding about the topic now, seems that PhilHo and foom are willing to help too... This is good oportunity for all of us to learn more about COM and do something useful for the community along the way.

_________________
Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 12th, 2007, 12:39 pm 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
majkinetor wrote:
2 Sean
If you want to create COM interface in AHK for AHK then I suggest you to create new topic on AHK Wiki so all interested can colaborate. I have pretty good understanding about the topic now, seems that PhilHo and foom are willing to help too... This is good oportunity for all of us to learn more about COM and do something useful for the community along the way.

I wish I could. Unfortunately, however, I can't as I'm not a programmer, have no programming experience at all.

Anyway, looks like you're now better prepared than me for this job, even if I have a programming skill. I stopped reading the fabulous articles you mentioned around article 3 or 4.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 12th, 2007, 1:11 pm 
Offline

Joined: December 27th, 2005, 1:46 pm
Posts: 6837
Location: France (near Paris)
Sean wrote:
Probably the most frequently opened file lately in my system has been Platform SDK Documentation. If I didn't get enough information from it, then I went to MSDN or googled it.
That might be a good reference, but hardly a good tutorial! ;-) Or I might have missed something.

majkinetor wrote:
I know superb COM tutorial.
Many thanks, I will look into it.

_________________
Image vPhiLho := RegExReplace("Philippe Lhoste", "^(\w{3})\w*\s+\b(\w{3})\w*$", "$1$2")


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 12th, 2007, 1:42 pm 
Offline

Joined: May 24th, 2006, 2:49 pm
Posts: 4511
Location: Belgrade
Sean wrote:
I wish I could. Unfortunately, however, I can't as I'm not a programmer, have no programming experience at all.

ha ha... you are strange guy Sean. Why the hell did you read COM articles if you are not a programmer ? I mean, its hard core programming topic....

Quote:
Anyway, looks like you're now better prepared than me for this job, even if I have a programming skill. I stopped reading the fabulous articles you mentioned around article 3 or 4.

I wish I have all the time to spend on this. However life in Serbia is not so relaxed. Our parlament just brought the new law witch basicly states that none in government institutions will have salary rise in next 6 years ! :lol: I will probably have to change my job soon and switch to private sector, witch will leave even less time for this. I will try to do something, nevertheless. I am at article 4 now :) and cumulating information from previous 3 wasn't easy. I still have to sort things in my head about COM and why are some things done that way, and not some other...

_________________
Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 18th, 2007, 2:06 pm 
Can you please give an example of the instruction to read an Excel sheet, please ? Would it be possible to implement the reading of float values ?
Thanks for your answer.


Report this post
Top
  
Reply with quote  
 Post subject:
PostPosted: February 19th, 2007, 1:08 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
washboard wrote:
Can you please give an example of the instruction to read an Excel sheet, please ? Would it be possible to implement the reading of float values ?

Unfortunately I can't as MS Office is not installed on my system. I hope other users help.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 19th, 2007, 1:37 pm 
Offline

Joined: May 24th, 2006, 2:49 pm
Posts: 4511
Location: Belgrade
all ado things work the same.

Just look on the net to see excell connection string then quiery the sheet
using

Select *
from [Sheet$]

_________________
Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 19th, 2007, 4:57 pm 
@ majkinetor
Thanks. I will search for this "excel connection string", but ...
@ Sean
As in Excel there are float values, would it be possible to retrieve them (you said taht for the moment they are not managed) ? In your example you access to an mdb database file. May be there can be float values in it, too ? Thanks for your work.


Report this post
Top
  
Reply with quote  
 Post subject:
PostPosted: February 20th, 2007, 1:20 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
washboard wrote:
As in Excel there are float values, would it be possible to retrieve them (you said taht for the moment they are not managed) ? In your example you access to an mdb database file. May be there can be float values in it, too ? Thanks for your work.

I've been trying to avoid using floating numbers until recently (:there was a reason). So, you must confirm this to me:
nType returns 4 for a floating number (and 5 for a double)?
If not, what value does it return?

If I get the info about it, I may be able to incorporate it.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: February 20th, 2007, 4:58 pm 
Offline

Joined: May 24th, 2006, 2:49 pm
Posts: 4511
Location: Belgrade
you can get float values as strings.

This option also resides in excell connection string.

This is my C# function

Code:
private string XLS_GetExcellConnectionString( string excelFileName )
{
   return   "Provider=Microsoft.Jet.OLEDB.4.0" +
            ";Data Source=" + excelFileName +
            ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO;\";";
}


I think IMEX is responsible for returning strings for any value (IMport EXport)

_________________
Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 22nd, 2007, 7:36 pm 
Offline

Joined: May 20th, 2007, 7:48 pm
Posts: 48
Here's a verbatim re-write of the original script at the top of this thread using the most recent CoHelper.ahk. It's simpler now thanks to the Invoke() function. Starting from this example it's been pretty easy to build scripts that manipulate Access databases, though some knowledge of ADO is required.
Code:
#NoEnv
#Include CoHelper.ahk

; Adjust sSource and sConnect appropriately
sSource  := "SELECT * FROM " . "atlas" ; . " WHERE country LIKE 'A%'"
sConnect := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . "C:\Database\atlas.mdb"

CoInitialize()
prs := ActiveXObject("ADODB.RECORDSET")

If (Invoke(prs, "Open", sSource, sConnect, 1, -1, -1))    ;Open
  MsgBox % "Connecting the database FAILED!", ExitApp

BOF := Invoke(prs, "BOF")      ; BOF
EOF := Invoke(prs, "EOF")      ; EOF
If BOF && EOF
  MsgBox % "No Match Found!", ExitApp

Invoke(prs, "MoveLast")            ; MoveLast
nRecordCount := Invoke(prs, "RecordCount")   ; RecordCount
Invoke(prs, "MoveFirst")           ; MoveFirst

Loop, %nRecordCount%
{
   pFields     := Invoke(prs, "Fields")   ; Fields
   nFieldCount := Invoke(pFields, "Count")   ; Count
   sData       := ""

      Loop, %nFieldCount%
      {
          pField%A_Index% := Invoke_(pFields, "Item", 3, arg:=A_Index-1)      ; Item   
          sName  := Invoke(pField%A_Index%, "Name")   ; Name
          sValue := Invoke(pField%A_Index%, "Value")   ; Value
          sData .= sName . ":  " . sValue . "`r`n"
          Invoke(pField%A_Index%, "Release")           ; Release
      }

   MsgBox % sData

   Release(pFields)          ; Release
   Invoke(prs, "MoveNext")  ; MoveNext
;   Invoke(prs, "MovePrevious")   ; MovePrevious
}

Invoke(prs, "Close")   ; Close
Release(prs)   ; Release
CoUninitialize()


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 8:58 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
tfcahm wrote:
Starting from this example it's been pretty easy to build scripts that manipulate Access databases, though some knowledge of ADO is required.

I completely forgot about this... Thanks for the script.
BTW, it seems to be easier to work with Excel too:

Code:
#Include CoHelper.ahk

CoInitialize()
pxl := ActiveXObject("Excel.Application")
;Invoke(pxl, "Visible=", "True")

pawb := Invoke(Invoke(pxl, "Workbooks"), "Add")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", "Sheet1")
pcls := Invoke(paws, "Cells")

Invoke_(pcls, "Item=", 3, 1, 3, 2, 8, "Test12")
;Invoke(Invoke_(pcls, "Item", 3, 1, 3, 2), "Value=", "Test12")
Invoke_(pcls, "Item=", 3, 2, 3, 1, 8, "Test21")
;Invoke(Invoke_(pcls, "Item", 3, 2, 3, 1), "Value=", "Test21")

Invoke(pawb, "SaveAs", A_Temp . "\test.xls")
Invoke(pxl, "Quit")
Release(pxl)
CoUninitialize()


Report this post
Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 132 posts ]  Go to page 1, 2, 3, 4, 5 ... 9  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: Yahoo [Bot] and 15 guests


You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Powered by phpBB® Forum Software © phpBB Group