 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
tank
Joined: 21 Dec 2007
Posts: 2416
Location: Louisville KY USA
|
|
| Back to top |
|
 |
Sean
Joined: 12 Feb 2007
Posts: 2224
|
 Posted: Tue Jan 13, 2009 6:32 am Post subject: |
 |
|
| namebran wrote: | | So then i did as you said and used the Com Library. Here is the output error message i received: |
Now if you also prefix COM_ to ActiveXObject, then no need of CoHelper.ahk, i.e., the script is completely ported to COM.ahk.
Anyway, I expected an error message from somewhere else, but it seems a cursor type issue. First change the cursor type like:
| Code: | | Com_Invoke(prs, "Open", sSource, sConnect, 0, -1, 4) |
Then the script would look like:
| Code: | sSource := "sp_databases"
sConnect := "Driver={SQL Server};Server=XXXXXX;Uid=sa;Pwd=abc123;"
COM_Init()
prs := COM_CreateObject("ADODB.Recordset")
COM_Invoke(prs, "Open", sSource, sConnect, 0, 1, 4)
Loop
{
If COM_Invoke(prs, "EOF")
Break
Loop, % COM_Invoke(pFields:=COM_Invoke(prs, "Fields"), "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
|
|
|
| Back to top |
|
|
namebran
Joined: 13 Oct 2008
Posts: 9
|
| Posted: Wed Jan 14, 2009 7:54 am Post subject: |
|
|
I was able to get this to work. Thanks to your help Sean by recommending me to the Com Library i realized what was giving me a problem.
I was expecting i would be able to use the same script i think you wrote and just plug different queries in there and it would work properly but that isn't the case. At least for stored procedures it isn't.
The problem was that that script was based on being able to use the RecordCount Property, but in my case it would just return -1 and the script would have nothing to Loop.
I realized what wa going on when looking at the error i posted along with this Note about the property.
Note: This property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor.
Here is the changed code that retreives the info i need from the Procedure:
| Code: |
; Adjust sSource and sConnect appropriately
#Include CoHelper.ahk
sSource := "sp_databases"
sConnect := "Driver={SQL Server};Server=zzzzz;Uid=sa;Pwd=abcxyz;"
CoInitialize()
prs := ActiveXObject("ADODB.Recordset")
Invoke(prs, "Open", sSource, sConnect, -1, 1, 1)
If Invoke(prs, "BOF") && Invoke(prs, "EOF")
{
MsgBox, No Match Found!
ExitApp
}
nCount := Invoke(prs, "RecordCount")
Invoke(prs, "MoveFirst")
Loop
{
If Invoke(prs, "EOF")
{
MsgBox % "End of Record reached"
Break
}
pFields := Invoke(prs, "Fields")
amtFields := Invoke(pFields, "Count")
Loop, % Invoke(pFields, "Count")
{
If A_Index = 1
{
pField%A_Index% := Invoke(pFields, "Item", A_Index-1)
sData .= Invoke(pField%A_Index%, "Value") . ";"
Release(pField%A_Index%)
}
}
Release(pFields)
Invoke(prs, "MoveNext")
}
MsgBox, % sData
Invoke(prs, "Close")
Release(prs)
CoUninitialize()
|
|
|
| Back to top |
|
|
namebran
Joined: 13 Oct 2008
Posts: 9
|
| Posted: Mon Jan 19, 2009 12:58 pm Post subject: |
|
|
Next thing i am having a problem with. I ran into another stored procedure which now i need to include parameters. I know i need to use ADODB.Command, but i just can't get anything to work. Once i get a peice of code together that somewhat seems to be in the correct format i will post it but if someone could point me in the right direction on how i should be writing it that would be great.
| Code: |
#SingleInstance Force
#Persistent
#NoEnv
dropName := "xNAMEx"
sQuery := "sp_dropserver "
sConnect := "Driver={SQL Server};Server=xNAMEx;Uid=sa;Pwd=password;"
pc := Com_ActiveXObject("ADODB.Connection")
pcmd := Com_ActiveXObject("ADODB.Command")
Com_Invoke(pc, "Open", sConnect) ;Open
sState := Com_Invoke(pc, "State")
MsgBox % sState ;%
Com_Invoke(pcmd, "ActiveConnection", pc) ; This throws an error "Function Name ActiveConnection, Invalid number of parameters"
Com_Invoke(pcmd, "CommandText", sQuery)
Com_Invoke(pcmd, "CommandType", 4)
Com_Invoke(pcmd, "Execute", "", "", 4) ; Then this throws obviously that the connection is not open.
Com_Invoke(pc, "Close")
Com_Release(pc)
Com_CoUninitialize()
|
This Stored procedure doesn't need to return any data except if it was successful or not, it just makes a change on the server end. I didn't write the Parameters part yet because i can't even i guess associate the connection with the command. Once i get that to work i will tackle the next part.
Thanks for your help everyone.
Just to clarify where i think my problem is, i think it has to do with this line, i don't understand why this doesn't work.
| Code: |
Com_Invoke(pcmd, "ActiveConnection", pc)
|
Thanks Again |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2224
|
|
| Back to top |
|
|
namebran
Joined: 13 Oct 2008
Posts: 9
|
| Posted: Tue Jan 20, 2009 4:53 am Post subject: |
|
|
Excellent Sean you have helped me once again! I got it to work! Here is my code that works in case it helps anyone:
| Code: |
#Include CoHelper.ahk
#Include Com.ahk
Com_CoInitialize()
dropName := "SYSNAME"
sQuery := "sp_dropserver"
sConnect := "Driver={SQL Server};Server=CCCC;Uid=sa;Pwd=abca;"
objcon := Com_ActiveXObject("ADODB.Connection")
objcmd := Com_ActiveXObject("ADODB.Command")
Com_Invoke(objcon, "Open", sConnect) ;Open
MsgBox % "objCon State = " . Com_Invoke(objcon, "State")
Com_Invoke(objcmd, "ActiveConnection", "+" . objcon)
Com_Invoke(objcmd, "CommandText", sQuery)
Com_Invoke(objcmd, "CommandType", 4)
Com_Invoke(objcmd, "NamedParameters", "True")
qParam1 := Com_Invoke(objcmd, "CreateParameter", "@server", 129, 1, 6, dropName)
objparam := Com_Invoke(objcmd, "Parameters")
Com_Invoke(objparam, "Append", "+" . qParam1)
spExec := Com_Invoke(objcmd, "Execute")
MsgBox % "Result" . spExec
Com_Release(objcmd)
Com_Invoke(objcon, "Close")
Com_Release(objcon)
Com_CoUninitialize()
ExitApp
|
|
|
| Back to top |
|
|
HugeHugh
Joined: 06 Feb 2009
Posts: 2
|
| Posted: Fri Feb 06, 2009 9:56 pm Post subject: Call stored procedure and get recordcount |
|
|
I have adapted namebran's solution to allow for getting the recordcount from the stored procedure result. You need to set the Cursorlocation to 3. This is working great:
| Code: |
#Include Lib\CoHelper.ahk
#Include Lib\Com.ahk
Com_CoInitialize()
idParam := "25"
sQuery := "MyStoredProcedureName"
sConnect := "Driver={SQL Server}; Server=servername; Database=dbname; UID=username; PWD=mypassword"
objcon := Com_ActiveXObject("ADODB.Connection")
objcmd := Com_ActiveXObject("ADODB.Command")
Com_Invoke(objcon, "CursorLocation", 3) ; allows you to get RecordCount
Com_Invoke(objcon, "Open", sConnect) ;Open
Com_Invoke(objcmd, "ActiveConnection", "+" . objcon)
Com_Invoke(objcmd, "CommandText", sQuery)
Com_Invoke(objcmd, "CommandType", 4)
Com_Invoke(objcmd, "NamedParameters", "True")
qParam1 := Com_Invoke(objcmd, "CreateParameter", "@idParam", 72, 1, 36, idParam)
objparam := Com_Invoke(objcmd, "Parameters")
Com_Invoke(objparam, "Append", "+" . qParam1)
spExec := Com_Invoke(objcmd, "Execute")
nRecordCount := Invoke(spExec, "RecordCount")
Loop, % nRecordCount
{
pFields := Invoke(spExec, "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(spExec, "MoveNext") ; MoveNext
}
Com_Release(objcmd)
Com_Invoke(objcon, "Close")
Com_Release(objcon)
Com_CoUninitialize()
ExitApp
|
|
|
| Back to top |
|
|
wtg
Joined: 04 Oct 2006
Posts: 85
Location: Louisville, KY
|
| Posted: Thu Feb 19, 2009 8:21 pm Post subject: |
|
|
First off, let me add to the chorus of people thanking Sean for his incredible work enabling COM support in AHK, and then taking the time to post working examples that show off what can be done. Thank you, thank you, thank you. What an incredible contribution you make to the tool.
Thanks also for this example. It was just what I was looking for - a way to query SQL Server and Oracle from AHK. I have zero experience with COM, but with this and the other examples people have posted, and a little digging to figure out what connection info I needed for Oracle, I have things working and I'm going to post my revised example in case it might help someone else.
I've extended the example to illustrate executing multiple queries on one connection. Often one only wants to connect to the database once instead of incurring the overhead of opening and closing a connection with each query, so that's what this demonstrates. The simple routine that shows the results shows the RowCount followed by a tab delimited list of column names and the data the query returns.
| Code: |
COM_CoInitialize()
;---------------- Set up the connection ------------
; MSDAORA is Microsoft's Oracle OLE DB Driver; OraOLEDB.Oracle is Oracle provided
sConnect := "Provider=MSDAORA;Data Source=OracleDB;User Id=User;Password=Pwd;"
pConnect := COM_ActiveXObject("ADODB.Connection")
COM_Invoke(pConnect, "CursorLocation", 3) ; Client-side cursor, allowing retrieval of RecordCount before processing the results
COM_Invoke(pConnect, "Open", sConnect) ;Open
;---------------- Execute the 1st query ------------
sQuery := "select count(*) from ps_personal_data"
pQuery := COM_ActiveXObject("ADODB.Command")
COM_Invoke(pQuery, "ActiveConnection", "+" . pConnect)
COM_Invoke(pQuery, "CommandText", sQuery)
prs := COM_Invoke(pQuery, "Execute" )
ShowResults(prs)
COM_Release(prs)
COM_Release(pQuery)
;---------------- Execute the 2nd query ------------
sQuery := "select emplid, first_name From ps_personal_data where last_name = 'Jones'"
pQuery := COM_ActiveXObject("ADODB.Command")
COM_Invoke(pQuery, "ActiveConnection", "+" . pConnect)
COM_Invoke(pQuery, "CommandText", sQuery)
prs := COM_Invoke(pQuery, "Execute" )
ShowResults(prs)
COM_Release(prs)
COM_Release(pQuery)
;---------------- Close connection and exit --------
COM_Invoke(pConnect, "Close")
COM_Release(pConnect)
COM_CoUninitialize()
exitapp
ShowResults( prs )
{
If COM_Invoke(prs, "BOF") && COM_Invoke(prs, "EOF")
{
MsgBox, No Results
return
}
nCount := COM_Invoke(prs, "RecordCount")
sResults := "Record Count = " . nCount . "`r`n"
COM_Invoke(prs, "MoveFirst")
Loop
{
If COM_Invoke(prs, "EOF")
{
Break ; End of Record reached
}
nRow++ ; Keep track of what row in the result set we're processing
pFields := COM_Invoke(prs, "Fields")
nNumFields := COM_Invoke(pFields, "Count")
sRow := ""
Loop, % nNumFields
{
pField%A_Index% := COM_Invoke(pFields, "Item", A_Index-1)
if nRow=1
sHeadings .= COM_Invoke(pField%A_Index%, "Name") . "`t"
sRow .= COM_Invoke(pField%A_Index%, "Value") . "`t"
COM_Release(pField%A_Index%)
}
COM_Release(pFields)
if nRow=1
sResults .= sHeadings . "`r`n"
sResults .= sRow . "`r`n"
COM_Invoke(prs, "MoveNext")
}
MsgBox, % sResults
COM_Invoke(prs, "Close")
}
|
Thanks again to Sean and everyone else that have added their own examples. Now I gotta go do a bunch of reading on COM and ADODB.  |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2224
|
| Posted: Fri Feb 20, 2009 2:38 am Post subject: |
|
|
| Thanks. I'm delighted to hear that you adopt the existing codes and able to write your own working code so easily. |
|
| Back to top |
|
|
daroc
Joined: 05 Mar 2009
Posts: 20
|
| Posted: Sat Mar 07, 2009 3:18 pm Post subject: |
|
|
I can't find how to read the name of current worksheet, change it and how to create new worksheet. Could anyone tell me? [Actually I would like to duplicate current sheet]
Edit:
I guessed one:
COM_Invoke(oExcel,"workbooks.item[1].worksheets.add")
What about other?
Edit2:
Ok, I get almost all 
"workbooks.item[1].worksheets.items[1].name"
"workbooks.item[1].worksheets.items[1].name", "newname"
But how to duplicate? |
|
| Back to top |
|
|
tank
Joined: 21 Dec 2007
Posts: 2416
Location: Louisville KY USA
|
| Posted: Sat Mar 07, 2009 5:46 pm Post subject: |
|
|
| daroc wrote: | I can't find how to read the name of current worksheet, change it and how to create new worksheet. Could anyone tell me? [Actually I would like to duplicate current sheet]
Edit:
I guessed one:
COM_Invoke(oExcel,"workbooks.item[1].worksheets.add")
What about other?
Edit2:
Ok, I get almost all
"workbooks.item[1].worksheets.items[1].name"
"workbooks.item[1].worksheets.items[1].name", "newname"
But how to duplicate? |
the vba is | Code: | ;~ vba
;~ Sheets("newName").Select
;~ Sheets("newName").Copy After:=Sheets(1) |
but there seems to be no com equivilent way to execute this within COM except to simply use Run some VBA via excel
_________________
Basic Webpage Controls with JavaScript / COM - Tutorial by Jethrow
 |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2224
|
| Posted: Sat Mar 07, 2009 6:37 pm Post subject: |
|
|
| tank wrote: | the vba is | Code: | ;~ vba
;~ Sheets("newName").Select
;~ Sheets("newName").Copy After:=Sheets(1) |
but there seems to be no com equivilent way to execute this within COM except to simply use Run some VBA via excel |
I think I already said that this syntax will never be supported, even VBS/JS doesn't support it. But there is a way to do it. (Not tested)
| Code: | COM_Invoke(Excel,"Sheets['newName'].Select")
COM_Invoke(Excel,"Sheets['newName'].Copy","+0","+" COM_Invoke(Excel,"Sheets",1))
; COM_Invoke(Excel,"Sheets['newName'].Copy","+0","+" COM_Invoke(Excel,".Sheets[1]")) |
If it doesn't work, must use COM_Invoke_(). |
|
| Back to top |
|
|
daroc
Joined: 05 Mar 2009
Posts: 20
|
| Posted: Sat Mar 07, 2009 7:06 pm Post subject: |
|
|
I don't quite understand what "Sheets" is in terms of com_invoke
This is what I got after my modifications (where oExcel:=COM_ActiveXObject("Excel.Application"))
| Code: | | COM_Invoke_(oExcel,"workbooks.item[2].worksheets.item[2].Copy","+0","+" COM_Invoke(oExcel,"workbooks.item[2].worksheets.item[1]")) |
And it doesn't work.
It shows error:
function name: copy
error: the parameter is incorrect
| Code: | | COM_Invoke(oExcel,"workbooks.item[2].worksheets.item[2].copy") |
This copies the sheet to another window (but it is not what I want to get, I would rather like to duplicate sheet in current window/workbook)
Edit: I think I messed up
| Code: | | COM_Invoke_(oExcel,"workbooks.item[2].worksheets.item[2].copy", "workbooks.item[2].worksheets.item[2]") |
shows error:
Function name: workbooks.item[2].worksheets.item[2]
Error: unknown name
And
| Code: | | COM_Invoke(oExcel,"workbooks.item[2].worksheets.item[2].copy", "workbooks.item[2].worksheets.item[2]") |
shows error:
Function name: "copy"
Desc: unable to get Copy property of Worksheet class
| Code: | | COM_Invoke(oExcel,"workbooks.item[2].worksheets.item[2].Copy","+0","+" COM_Invoke(oExcel,"workbooks.item[2].worksheets.item[1]")) |
Function name: "Copy"
Error: incorrect parameter
| Code: | | COM_Invoke_(oExcel,"workbooks.item[2].worksheets.item[2].Copy","+0","+" COM_Invoke(oExcel,"workbooks.item[2].worksheets.item[1]")) |
Function name: workbooks.item[2].worksheets.item[2].Copy
Error: unknown name
Last edited by daroc on Sat Mar 07, 2009 11:35 pm; edited 2 times in total |
|
| Back to top |
|
|
tank
Joined: 21 Dec 2007
Posts: 2416
Location: Louisville KY USA
|
| Posted: Sat Mar 07, 2009 7:45 pm Post subject: |
|
|
| Sean wrote: | | I think I already said that this syntax will never be supported, even VBS/JS doesn't support it. |
you did thats why i said he would have to use vba from excel
Ether way your exampled didnt work either 
So far it seems to beyond my skill to do it outside vba
_________________
Basic Webpage Controls with JavaScript / COM - Tutorial by Jethrow
|
|
| Back to top |
|
|
daroc
Joined: 05 Mar 2009
Posts: 20
|
| Posted: Sun Mar 08, 2009 12:18 am Post subject: |
|
|
Ok, now I'm trying to make a macro that will do that for me, but there's something strange.
I open two windows. If I want to activate the first using com, I should use COM_Invoke(oExcel,"Windows(2).Activate"), but when I do the same using macro, window index is 1 like windows were sorted in reversed order. Why? |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
