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 5 of 9

[ 132 posts ]

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

Print view | E-mail friend

Previous topic | Next topic

Author

Message

tank

Post subject:

Posted: January 13th, 2009, 2:14 am

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

i dont see "movelast" is it in the stored proc?

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

Top

Sean

Post subject:

Posted: January 13th, 2009, 6:32 am

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

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

Top

namebran

Post subject:

Posted: January 14th, 2009, 7:54 am

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

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()

Top

namebran

Post subject:

Posted: January 19th, 2009, 12:58 pm

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

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

Top

Sean

Post subject:

Posted: January 20th, 2009, 1:00 am

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

Read this page first:
http://www.autohotkey.com/forum/viewtopic.php?t=22923

Top

namebran

Post subject:

Posted: January 20th, 2009, 4:53 am

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

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

Top

HugeHugh

Post subject: Call stored procedure and get recordcount

Posted: February 6th, 2009, 9:56 pm

Joined: February 6th, 2009, 7:31 pm
Posts: 3

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 

Top

wtg

Post subject:

Posted: February 19th, 2009, 8:21 pm

Joined: October 4th, 2006, 2:15 am
Posts: 250
Location: Louisville, KY

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.

Top

Sean

Post subject:

Posted: February 20th, 2009, 2:38 am

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

Thanks. I'm delighted to hear that you adopt the existing codes and able to write your own working code so easily.

Top

daroc

Post subject:

Posted: March 7th, 2009, 3:18 pm

Joined: March 5th, 2009, 7:59 pm
Posts: 23

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 :shock:

"workbooks.item[1].worksheets.items[1].name"
"workbooks.item[1].worksheets.items[1].name", "newname"
But how to duplicate?

Top

tank

Post subject:

Posted: March 7th, 2009, 5:46 pm

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

daroc wrote:

"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

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

Top

Sean

Post subject:

Posted: March 7th, 2009, 6:37 pm

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

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_().

Top

daroc

Post subject:

Posted: March 7th, 2009, 7:06 pm

Joined: March 5th, 2009, 7:59 pm
Posts: 23

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 March 7th, 2009, 11:35 pm, edited 2 times in total.

Top

tank

Post subject:

Posted: March 7th, 2009, 7:45 pm

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

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 :wink:

So far it seems to beyond my skill to do it outside vba

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

Top

daroc

Post subject:

Posted: March 8th, 2009, 12:18 am

Joined: March 5th, 2009, 7:59 pm
Posts: 23

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?

Top

Page 5 of 9

[ 132 posts ]

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

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

Who is online

Users browsing this forum: Yahoo [Bot] and 16 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