View topic - ADO COM - Database Query • AutoHotkey Community

View unanswered posts | View active topics

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

ADO COM - Database Query

Page 4 of 9

[ 132 posts ]

Go to page Previous 1, 2, 3, 4, 5, 6, 7 ... 9 Next

Print view | E-mail friend

Previous topic | Next topic

Author

Message

Sean

Post subject:

Posted: August 8th, 2007, 12:33 am

Joined: February 12th, 2007, 7:54 am
Posts: 2462

daonlyfreez wrote:

Can anybody create a simple script that demonstrates how to get Excel contents, a single cell, or a range?

It's similar to set/put a value into a cell:

Code:

Value11 := Invoke(Invoke(pcls, "Item", 1, 1), "Value")
Value22 := Invoke(Invoke(pcls, "Item", 2, 2), "Value")

Compare it with Put (:notice the suffix = in the function name "Value"):

Code:

Invoke(Invoke(pcls, "Item", 1, 1), "Value=", "Test11")
Invoke(Invoke(pcls, "Item", 2, 2), "Value=", "Test22")

Top

daonlyfreez

Post subject:

Posted: August 8th, 2007, 8:33 am

Joined: March 16th, 2005, 10:33 pm
Posts: 968
Location: Frisia

Great! Thank you.

Here is an example script. Could you check what is needed (what I am doing wrong) to get a range of cells? I do get some value...

Code:

#Include %A_ScriptDir%\CoHelper.ahk

testFile := A_ScriptDir . "\test.xls"

MsgBox Step 1: Writing to Excel file

CoInitialize()

pxl := ActiveXObject("Excel.Application")

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

Invoke(Invoke(pcls, "Item", 1, 1), "Value=", "Test11")
Invoke(Invoke(pcls, "Item", 1, 2), "Value=", "Test12")
Invoke(Invoke(pcls, "Item", 1, 3), "Value=", "Test13")

Invoke(pawb, "SaveAs", testFile)
Invoke(pxl, "Quit")
Release(pxl)

MsgBox Step 2: Getting individual cells from Excel file

pxl := ActiveXObject("Excel.Application")

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

pawb := Invoke(Invoke(pxl, "Workbooks"), "Open", testFile)
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")

Value11 := Invoke(Invoke(pcls, "Item", 1, 1), "Value")
Value12 := Invoke(Invoke(pcls, "Item", 1, 2), "Value")

MsgBox % "Value11: " . Value11 . "`nValue12: " . Value12

MsgBox Step 3: Getting a range of cells from Excel file

ValueR := Invoke(Invoke(pcls, "Range", "A1", "C1"), "Value")
;MsgBox % ErrorLevel . "`n" . A_LastError

Invoke(pxl, "Quit")
Release(pxl)

CoUninitialize()

MsgBox % "ValueR: " . ValueR

FileDelete % testFile

And, I tried shortening the code by removing the "duplicates":

Code:

pxl := ActiveXObject("Excel.Application")

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

pawb := Invoke(Invoke(pxl, "Workbooks"), "Open", testFile)
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")

Invoke(pxl, "Quit")
Release(pxl)

But, I guess I need to repeat them, for else AHK/Excel hangs/crashes... Could you shed a light on that too?

_________________

mirror 1 • mirror 2 • mirror 3 • ahk4.me • PM or

Top

Sean

Post subject:

Posted: August 8th, 2007, 9:35 am

Joined: February 12th, 2007, 7:54 am
Posts: 2462

daonlyfreez wrote:

Here is an example script. Could you check what is needed (what I am doing wrong) to get a range of cells? I do get some value...

The syntax is correct. What you missed is that if you try to retrieve multiple cells/range, then the result will be returned in an array. So, what you saw is the address of the returned array.
As needs extra steps to have the actual values, I'd rather retrieve the values one-by-one.

Quote:

And, I tried shortening the code by removing the "duplicates":

But, I guess I need to repeat them, for else AHK/Excel hangs/crashes... Could you shed a light on that too?

You don't need the step "Add" when you open an existing file. Examine the following code:

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", 1)
pcls := Invoke(paws, "Cells")

Invoke(Invoke(pcls, "Range", "A1:C3"), "Value=", "Test")
;Invoke(Invoke(pcls, "Range", "A1", "C3"), "Value=", "Test")
Invoke(pawb, "SaveAs", A_Temp . "\test.xls")
Invoke(pawb, "Close")


pawb := Invoke(Invoke(pxl, "Workbooks"), "Open", A_Temp . "\test.xls")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")

Value11 := Invoke(Invoke(pcls, "Item", 1, 1), "Value")
Value22 := Invoke(Invoke(pcls, "Item", 2, 2), "Value")
Value33 := Invoke(Invoke(pcls, "Item", 3, 3), "Value")

Invoke(Invoke(pcls, "Item", 4, 4), "Value=", "Test44")
Invoke(pawb, "Save")
Invoke(pawb, "Close")

Invoke(pxl, "Quit")
Release(pxl)
CoUninitialize()

Top

daonlyfreez

Post subject:

Posted: August 8th, 2007, 11:22 am

Joined: March 16th, 2005, 10:33 pm
Posts: 968
Location: Frisia

Great! Thanks again. Will use looping to get ranges then.

_________________

mirror 1 • mirror 2 • mirror 3 • ahk4.me • PM or

Top

BoBo¨

Post subject:

Posted: April 9th, 2008, 2:02 pm

On July 27, 2007 Sean (is he alive? :shock:

) has promised to shed some light on [this].
Well, meanwhile we're running out of time :wink:

, and therefore request the community (in general) to get support on this. Any help will be much appreciated.

Top

silveredge78

Post subject:

Posted: April 9th, 2008, 5:26 pm

Joined: July 25th, 2006, 7:37 pm
Posts: 490
Location: Midwest, USA

I reviewed this post and it looks very promising. I'm not too great at DB things. However I have need to pull data from MS SQL 2000 databases for clients. I currently use t4esql to do it. It pulls incorrect information when there is NULLs so I had to write code to filter it accordingly. It seems to work, but if I could do this purely with AHK that would be what I prefer.

I know this refers to ADO, which I believe is what Access can use. But can this script set be used in MS SQL 2000 (I don't know what access standards can be used). If so, can someone provide a quick example comparable to the one used for the Access database.

Thank you very much in advance if you can help!

_________________
SilverEdge78

Top

tank

Post subject:

Posted: May 25th, 2008, 8:36 am

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA

the use of com library still finds me in utter amazement
in some ways it has actually come to feel more natuaral to me then using vb kinda sur real as it were
i suspect that even tho this library has been out now couple years well almost that it may be yet years to come before the true depth of its benifet is fully seen

on another note i had totally forgoten you could silently interact with an excell document
till i saw sean comment it out
truly a freaking genious

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene

Top

namebran

Post subject:

Posted: January 11th, 2009, 5:52 am

Joined: October 13th, 2008, 2:54 am
Posts: 9

I've run into a problem using the what i've learned from this topic. I have been working on a script in which i have to access a SQL Server and its Databases often for maintenance reasons. Anyway, everything has worked great when running queries using ADO, but when i try to use a Stored Procedure instead of say a SELECT statement, the RecordSet is empty. Is there someone that could please help me with executing a Stored Procedure using ADO.

Thank you in Advance.

Top

Sean

Post subject:

Posted: January 11th, 2009, 11:28 am

Joined: February 12th, 2007, 7:54 am
Posts: 2462

namebran wrote:

when i try to use a Stored Procedure instead of say a SELECT statement, the RecordSet is empty.

Post your code.

Top

drmurdoch

Post subject: I want to write my schedule to text files

Posted: January 11th, 2009, 7:04 pm

Joined: November 10th, 2006, 5:10 am
Posts: 110

Sean,

Would this allow me to read a MS Access Database ?

Top

namebran

Post subject:

Posted: January 11th, 2009, 8:43 pm

Joined: October 13th, 2008, 2:54 am
Posts: 9

Here is the code that matters, i am using almost exactly what is on the first page of this topic with some minor changes. Even using that code exactly as it is, with my connection string of course, i still couldn't get a SQL built in stored procedure to return any result. And just to let you know i am no expert at any of this. This is my 2nd post ever and i've only been using AHK or done any scripting for like 3 or 4 months now.

One thing i noticed when i run this the nRecordCount variable is "-1". I'm not sure what that means but i was hoping it might be helpful.

Thanks!

Code:

sConnect := "Driver={SQL Server};Server=" . dbConnectB . ";Uid=" . loginUser . ";Pwd=" . loginPass . ";"
prs := ActiveXObject("ADODB.RECORDSET")

NEXquery := "exec sp_databases"

qErr := adoQuery(prs, NEXquery, sConnect, qData)
If qErr
{
   MsgBox % "Error connecting to SQL Server"
   Return
}

MsgBox % "Result = " . qData

adoQuery(xprs, sQry, sCon, ByRef qData)
{
   Invoke(xprs, "open", sQry, sCon, 1, -1, -1)  ;Open
   sState := Invoke(xprs, "State")   ;State
   If !sState
      Return "1"
   BOF := Invoke(xprs, "BOF")      ; BOF 
   EOF := Invoke(xprs, "EOF")      ; EOF 
   If BOF && EOF 
      MsgBox % "No Match Found!"
   Invoke(xprs, "MoveLast")            ; MoveLast 
   nRecordCount := Invoke(xprs, "RecordCount")   ; RecordCount
   Invoke(xprs, "MoveFirst")           ; MoveFirst 
   qData := ""
   Loop, %nRecordCount% 
   { 
      pFields     := Invoke(xprs, "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 
      } 
      If nRecordCount > 1
      {
         If A_Index = %nRecordCount%
            qData := qData . sValue
         Else
            qData := sValue . ";" . qData
      }
      Else
         qData := sValue
      Release(pFields)          ; Release 
      Invoke(xprs, "MoveNext")  ; MoveNext 
   } 
   Invoke(xprs, "Close")   ; Close 
   Return
}

Top

Sean

Post subject:

Posted: January 12th, 2009, 1:06 am

Joined: February 12th, 2007, 7:54 am
Posts: 2462

First just specify the stored procedure name like:

Code:

NEXquery := "sp_databases" ; Stored Procedure name

Then, specify the CommandType when opening the recordset:

Code:

Invoke(xprs, "open", sQry, sCon, 1, -1, adCmdStoredProc := 4)

I hope it work. BTW, if you need to specify parameters to the stored procedure, should use the Command object ADODB.Command.

Top

namebran

Post subject:

Posted: January 12th, 2009, 5:03 am

Joined: October 13th, 2008, 2:54 am
Posts: 9

Thanks for your advice Sean, unfortunately i wasn't able to get it to work. I must be missing something although i have been looking at msdn and w3schools info and it looks like it should work. I have tried the Command Object also with the same results. If you think of anything else let me know but i dont wont to take up too much of your time. Of course i will keep playing with it but i 'll be looking for a work around with just using standard queries too.

Top

Sean

Post subject:

Posted: January 12th, 2009, 6:30 am

Joined: February 12th, 2007, 7:54 am
Posts: 2462

namebran wrote:

Thanks for your advice Sean, unfortunately i wasn't able to get it to work.

Need error messages. For that I suggest to use COM Standard Library. Just include it into the script and change all the function call Invoke to COM_Invoke, and see if it outputs an error message.

Top

namebran

Post subject:

Posted: January 13th, 2009, 1:44 am

Joined: October 13th, 2008, 2:54 am
Posts: 9

Okay i used the code below this time, basically the same thing (the ADO.ahk script). I first plugged in the stored procedure with my connectin string to see if it worked and it didn't. So then i did as you said and used the Com Library. Here is the output error message i received:

Code:

; Adjust sSource and sConnect appropriately
#Include Com.ahk

sSource  := "sp_databases" 
sConnect := "Driver={SQL Server};Server=XXXXXX;Uid=sa;Pwd=abc123;"

Com_CoInitialize()

prs := ActiveXObject("ADODB.Recordset")
Com_Invoke(prs, "Open", sSource, sConnect, 1, -1, 4)

If Com_Invoke(prs, "BOF") && Com_Invoke(prs, "EOF")
{
   MsgBox,   No Match Found!
   ExitApp
}

Com_Invoke(prs, "MoveLast")
nCount := Com_Invoke(prs, "RecordCount")
Com_Invoke(prs, "MoveFirst")

Loop, %   nCount
{
   pFields := Com_Invoke(prs, "Fields")
   Loop, %   Com_Invoke(pFields, "Count")
        pField%A_Index% := Com_Invoke(pFields, "Item", A_Index-1)
      , sData .= Com_Invoke(pField%A_Index%, "Name") . ": " . Com_Invoke(pField%A_Index%, "Value") . "`r`n"
      , Com_Release(pField%A_Index%)
   Com_Release(pFields)
   Com_Invoke(prs, "MoveNext")
;   Com_Invoke(prs, "MovePrevious")
   MsgBox, % sData
}

Com_Invoke(prs, "Close")
Com_Release(prs)
Com_CoUninitialize()

[/img]

Top

Page 4 of 9

[ 132 posts ]

Go to page Previous 1, 2, 3, 4, 5, 6, 7 ... 9 Next

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

Who is online

Users browsing this forum: No registered users and 17 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