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

[ 132 posts ]

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

Print view | E-mail friend

Previous topic | Next topic

Author

Message

Sean

Post subject:

Posted: March 8th, 2009, 1:46 am

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

tank wrote:

Ether way your exampled didnt work either :wink:

OK, I uploaded a new COM.ahk to support dot syntax also in COM_Invoke_(). Now, use like:

Code:

COM_Invoke_(Excel, "Sheets['newName'].Copy", VT_ERROR:=10, DISP_E_PARAMNOTFOUND:=0x80020004, VT_DISPATCH:=9, COM_Invoke(Excel,"Sheets",1))
; COM_Invoke_(Excel, "Sheets['newName'].Copy", VT_ERROR:=10, DISP_E_PARAMNOTFOUND:=0x80020004, VT_DISPATCH:=9, COM_Invoke(Excel,".Sheets[1]"))

Top

daroc

Post subject:

Posted: March 8th, 2009, 1:55 am

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

My solution to duplicate a worksheet in output window.

Code:

    input:=COM_Invoke(oExcel,"workbooks.open",input_path)
    output:=COM_Invoke(oExcel,"workbooks.open",output_path)
    COM_Invoke(oExcel,"Windows(2).Activate") ; activate input window (which has a macro recorded)
    COM_Invoke(oExcel,"Run","Makro2") ; run Makro2 in input window

In input_path document I have a macro:

Code:

Sub Makro2()
    Windows(2).Activate ' activate output window
    Sheets(1).Copy Before:=Sheets(1) ' duplicate the first sheet in output window
End Sub

I don't know why Windows(2) is the input window for com and simultaneously Windows(2) is output window in macros...

Then I change sheet's name...

Code:

COM_Invoke(output,"worksheets.item[1].name",sheetname)

Top

Sean

Post subject:

Posted: March 8th, 2009, 11:39 am

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

daroc wrote:

I don't know why Windows(2) is the input window for com and simultaneously Windows(2) is output window in macros...

The indices aren't fixed ones. Test this.

Code:

sFile1:=A_Temp . "\test1.xls"
sFile2:=A_Temp . "\test2.xls"

COM_Init()
pxla :=   COM_CreateObject("Excel.Application")
COM_Invoke(pxla,"Workbooks.Open",sFile1)
COM_Invoke(pxla,"Workbooks.Open",sFile2)
MsgBox % "1: " COM_Invoke(pxla,"Windows[1].Caption") "`n2: " COM_Invoke(pxla,"Windows[2].Caption")
COM_Invoke(pxla,"Windows[2].Activate")
MsgBox % "1: " COM_Invoke(pxla,"Windows[1].Caption") "`n2: " COM_Invoke(pxla,"Windows[2].Caption")
COM_Invoke(pxla,"Quit")
COM_Release(pxla)
COM_Term()

Top

Leonidas225

Post subject:

Posted: April 26th, 2009, 3:43 am

Joined: January 30th, 2009, 5:43 am
Posts: 12

nice script! i tried out the script from the first post. works fine but i have some performance issues: if i make a query with a larger result 150 by 15 fields it takes forever. the connection to the .db is ok becouse if i have just a few matches it works fast enough. about the same time with a ~44MB .db and a ~350MB one.
anyone has simular experiences?

Top

Sean

Post subject:

Posted: April 26th, 2009, 8:44 am

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

Leonidas225 wrote:

if i make a query with a larger result 150 by 15 fields it takes forever.

Certainly concatenating all the results into one should be replaced with other means in this case. Replace the following with your own one

Code:

sData .= COM_Invoke(pField, "Name") . ": " . COM_Invoke(pField, "Value") . "`r`n"

Top

Leonidas225

Post subject:

Posted: April 26th, 2009, 3:13 pm

Joined: January 30th, 2009, 5:43 am
Posts: 12

Quote:

Replace the following with your own one

Code:

sData .= COM_Invoke(pField, "Name") . ": " . COM_Invoke(pField, "Value") . "`r`n"

Which part should i replace?

Code:

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

Top

silveredge78

Post subject:

Posted: May 21st, 2009, 8:45 pm

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

Sean, I am trying to adopt your script to Write data into an existing database. I do not understand ADO COM enough to adapt the base code to do this. I can read it fine and dandy, but I don't know what command structure to use to update this database. Any examples would greatly appreciated!

_________________
SilverEdge78

Top

Sean

Post subject:

Posted: May 22nd, 2009, 12:46 am

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

silveredge78 wrote:

I can read it fine and dandy, but I don't know what command structure to use to update this database. Any examples would greatly appreciated!

Exactly what would you like to do? Here is a link to the MSDN on ADO, you may find what you want there:
Microsoft ActiveX Data Objects (ADO)

Top

beardboy

Post subject:

Posted: July 9th, 2009, 12:14 am

Joined: March 2nd, 2004, 10:10 pm
Posts: 443
Location: SLC, Utah

I want to be able to open a connection and then not close it until my application is closed, but I have noticed that the Database will close the connection if it stays idle for too long, or if the network cable gets disconnected for a while. If I then run another query on the DB it pops up the following error:

Code:

COM Error Notification
---------------------------
Function Name:   "Execute"
ERROR:   Unspecified error

   (0x80004005)
PROG:   Microsoft OLE DB Provider for ODBC Drivers
DESC:   [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionWrite (send()).
HELP:   ,0

Will Continue?
---------------------------
Yes   No

Is there a way that I can test to see that the connection is still open? That way if it isn't I can re-open a connection? I have tried using the following code but it lists the connection still as open even after I manually kill the connection:

Code:

MsgBox % "Connection State = " . Com_Invoke(pConnect, "State")

thanks,
beardboy

Top

silveredge78

Post subject:

Posted: July 9th, 2009, 12:48 am

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

Why not close and then reopen the connection if it's going to be a certain amount of time in between? That way you're not keeping unnecessary traffic to the server, and you free up memory in your program I would think.

_________________
SilverEdge78

Top

beardboy

Post subject:

Posted: July 9th, 2009, 12:53 am

Joined: March 2nd, 2004, 10:10 pm
Posts: 443
Location: SLC, Utah

Speed. Disconnecting and reconnecting to the DB for every query slows down the speed. The user is going to be doing queries all day long, sometimes seconds apart, other times hours apart. They will leave the app open all day long, but if the user goes to lunch and the connection times out I will want to be able to see that the connection isn't open any more and open a new connection.

thanks,
beardboy

Top

silveredge78

Post subject:

Posted: July 9th, 2009, 12:55 am

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

Why possibly somehow determine the length of time for time-out, and then add a timer for just under that to close it, flag a variable accordingly, then that way you'll know.

_________________
SilverEdge78

Top

beardboy

Post subject:

Posted: July 9th, 2009, 12:59 am

Joined: March 2nd, 2004, 10:10 pm
Posts: 443
Location: SLC, Utah

I can program around idle time outs, but where I first noticed the issue was when testing myself. I hibernate my laptop between work at home, and even when using my app on my laptops DB, due to hibernating and my IP address changing my connection to the DB was terminated. I would rather the user / client not see an error if there is a way I can just check to see if I have an active connection still.

thanks,
beardboy

Top

silveredge78

Post subject:

Posted: July 9th, 2009, 1:01 am

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

That makes more sense then. If someone helps you in PM, please post it as I might be able to make use of this.

_________________
SilverEdge78

Top

Sean

Post subject:

Posted: July 9th, 2009, 1:20 am

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

beardboy wrote:

In that case, the straightforward way may be to turn off the Error Notification via

Code:

COM_Error(0)

then handle the errors yourself. It may look like, assuming it's inside the function

Code:

Global COM_HR
...
If  COM_HR <> 0  ; error occured!
...

Code:

...
If  COM_Error() <> 0  ; error occured!
...

Top

Page 6 of 9

[ 132 posts ]

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

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

Who is online

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