 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
beardboy
Joined: 02 Mar 2004
Posts: 443
Location: SLC, Utah
|
 Posted: Thu Jul 09, 2009 1:24 am Post subject: |
 |
|
Is there not a specific command to test if the connection is still open?
thanks,
beardboy |
|
| Back to top |
|
 |
Sean
Joined: 12 Feb 2007
Posts: 2219
|
| Posted: Thu Jul 09, 2009 1:32 am Post subject: |
|
|
| beardboy wrote: | | Is there not a specific command to test if the connection is still open? |
Actually the network connection is the problem, right? I don't think ADO objects have a way to query about the network connection. |
|
| Back to top |
|
|
beardboy
Joined: 02 Mar 2004
Posts: 443
Location: SLC, Utah
|
| Posted: Thu Jul 09, 2009 1:47 am Post subject: |
|
|
Thanks for the info. Here is what I ended up doing and it seems to be working great.
| Code: | QueryDB(statement)
{
Global pConnect,COM_HR
COM_Error(0)
Loop
{
pQuery := COM_ActiveXObject("ADODB.Command")
COM_Invoke(pQuery, "ActiveConnection", "+" . pConnect)
COM_Invoke(pQuery, "CommandText", statement)
prs := COM_Invoke(pQuery, "Execute" )
if COM_HR <> 0 ; error occured!
OpenDBConnection("{SQL Server}", "MySQLServer", "MyDatabase", "myUsername", "myPassword")
else
break
}
COM_Error(1)
data := ShowResults(prs)
COM_Release(prs)
COM_Release(pQuery)
return data
}
|
thanks,
beardboy |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2219
|
| Posted: Thu Jul 09, 2009 5:35 am Post subject: |
|
|
| beardboy wrote: | | Thanks for the info. Here is what I ended up doing and it seems to be working great. |
One thing. You better release the old objects pQuery/pConnect before obtaining a new connection. |
|
| Back to top |
|
|
beardboy
Joined: 02 Mar 2004
Posts: 443
Location: SLC, Utah
|
| Posted: Thu Jul 09, 2009 5:48 am Post subject: |
|
|
Part of my OpenDBConnection() is the following:
| Code: | if pConnect <>
{
COM_Invoke(pConnect, "Close")
COM_Release(pConnect)
}
if pQuery <>
COM_Release(pQuery)
if prs <>
COM_Release(prs)
|
Also since my last post I have found a problem with turning off the errors and assuming all errors are connection problems. If for some reason my client sends a bad SQL statement it just tries to re-connect endlessly. I have changed it a little bit as follows:
| Code: | if COM_HR <> 0 ; error occured!
{
if a_index > 1 ; If still getting error after attempting to re-connect to DB
{
COM_Error(1) ; Turn Errors back on to inform the user of the Error
prs := COM_Invoke(pQuery, "Execute" ) ; Invoke the Error
return
}
OpenDBConnection("{SQL Server}", "MySQLServer", "MyDatabase", "myUsername", "myPassword")
}
|
Let me know what you think.
thanks,
beardboy |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2219
|
| Posted: Thu Jul 09, 2009 7:07 am Post subject: |
|
|
| beardboy wrote: | | Part of my OpenDBConnection() is the following: |
I could imagine that with pConnect, but not with pQuery as pQuery wasn't declared as Global. |
|
| Back to top |
|
|
infogulch
Joined: 27 Mar 2008
Posts: 378
|
| Posted: Tue Feb 02, 2010 12:33 am Post subject: |
|
|
Sean: I tried making a simple AutoHotkey_L wrapper for an SQL query, but this code fails at "rs.Open()" with "No COM Dispatch Object!" error:
| Code: | ;
; SqlQuery(): Executes a SQL query and returns its results in a two-dimentional object
;
SqlQuery(sql, connStr) {
rs := COM_CreateObject("ADODB.Recordset")
rs.Open(sql, connStr)
while !rs.EOF
{
pFields := rs.Fields
i := A_Index
loop % pFields.Count
ret[i,A_Index] := pFields.Item(A_Index-1).Value
if !ret.Name
loop % pFields.Count
ret.Name[A_Index] := pFields.Item(A_Index-1).Name
rs.MoveNext()
}
pFields := ""
rs.Close()
rs := ""
return ret
} |
The same thing in normal ahk works:
| Code: | ;
; SqlQuery(): Executes a SQL query and returns its results in a CSV format
;
SqlQuery(sql, connStr) {
rs := COM_CreateObject("ADODB.Recordset")
COM_Invoke(rs, "Open", sql, connStr)
while !COM_Invoke(rs,"EOF")
{
pFields := COM_Invoke(rs,"Fields")
i := A_Index
loop % COM_Invoke(pFields, "Count")
{
pField := COM_Invoke(pFields, "Item", A_Index-1)
ret .= COM_Invoke(pField, "Value") ","
COM_Release(pField)
}
if !names
loop % COM_Invoke(pFields,"Count")
{
pField := COM_Invoke(pFields, "Item", A_Index-1)
names .= COM_Invoke(pField, "Name") ","
COM_Release(pField)
}
ret .= "`n"
COM_Release(pFields)
COM_Invoke(rs, "MoveNext")
}
COM_Invoke(rs, "Close")
COM_Release(rs)
return names "`n" ret
} |
_________________
Scripts - License |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2219
|
| Posted: Tue Feb 02, 2010 1:37 am Post subject: |
|
|
| infogulch wrote: | | Sean: I tried making a simple AutoHotkey_L wrapper for an SQL query, but this code fails at "rs.Open()" with "No COM Dispatch Object!" error: |
I think you said the opposite. The one for normal AHK requires an explicit call to COM_Init(). |
|
| Back to top |
|
|
infogulch
Joined: 27 Mar 2008
Posts: 378
|
| Posted: Tue Feb 02, 2010 4:52 pm Post subject: |
|
|
| Sean wrote: | | I think you said the opposite. The one for normal AHK requires an explicit call to COM_Init(). |
No, I just double checked. The vanillaAHK+COM works just fine, but the AHK_L+COM_L gives that error i mentioned. (Note: I do call COM_Init() in the vanilla ahk version)
Btw, I'm using AutoHotkey_L Rev. 43 that has been merged with AutoHotkey_U.
_________________
Scripts - License |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2219
|
| Posted: Wed Feb 03, 2010 1:18 am Post subject: |
|
|
| infogulch wrote: | | Btw, I'm using AutoHotkey_L Rev. 43 that has been merged with AutoHotkey_U. |
OK, I guess you're using the Unicode build. Then, you have to use COM_U. The difference between COM_L and COM_U lies in ANSI or UNICODE. |
|
| Back to top |
|
|
infogulch
Joined: 27 Mar 2008
Posts: 378
|
| Posted: Tue Feb 09, 2010 4:32 pm Post subject: |
|
|
Yeah, that fixed it. Thanks Sean. 
_________________
Scripts - License |
|
| Back to top |
|
|
Toxey
Joined: 13 Dec 2007
Posts: 10
|
| Posted: Mon Mar 01, 2010 10:36 pm Post subject: |
|
|
Beautiful and wonderful, I can't say enough how thankful I am that this thread is here. I think it's Sean I should be thanking for most of this, so thank you thank you thank you.
I've used this with great success with XP and Vista, but the same program won't connect on one machine with Windows 2000 Professional. No idea why.
With this code snippet I should be reading from an mdb query (that incidentally links to some dbf files) saved on my network:
| Code: | ;*********** GET THE PARTS AND STORE *******************
; Adjust sSource and sConnect appropriately
pData =
sSource := "SELECT * FROM " . "qryOnlineDailyActionParts"
sConnect := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . "S:\HOTKEYS.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)
, pData .= COM_Invoke(pField, "Value"). "¢"
, COM_Release(pField)
pData .= "`r`n"
COM_Release(pFields)
COM_Invoke(prs, "MoveNext")
}
COM_Invoke(prs, "Close")
COM_Release(prs)
COM_Term()
;*********** END OF PARTS GET AND STORE *****************
|
This works great on XP, Vista, and even on a single machine with Windows 2000, but when I run it on this one system with 2000 Pro, I get either one of the the following errors:
---------------------------
COM Error Notification
---------------------------
Function Name: "Open"
ERROR: The COM Object may not be a valid Dispatch Object!
First ensure that COM Library has been initialized through COM_Init().
()
Will Continue?
---------------------------
Yes No
---------------------------
-- or --
---------------------------
COM Error Notification
---------------------------
Function Name: "Open"
ERROR: (0x800A0E7F)
PROG:
DESC:
HELP: ,0
Will Continue?
---------------------------
Yes No
---------------------------
I've got the library in the same place as every other machine. I've got an up to date Msjet40.dll (relatively) in this machine, and my service pack is 4. There's some update rollup for 2000, but I can't for the life of me figure out how to actually download it.
I'm at a loss to figure this out. It should work. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2219
|
| Posted: Tue Mar 02, 2010 1:08 am Post subject: |
|
|
| Search for msado15.dll in your system, and if found it, go to the directory and register it through regsvr32.exe msado15.dll. |
|
| Back to top |
|
|
Toxey
Joined: 13 Dec 2007
Posts: 10
|
| Posted: Tue Mar 02, 2010 10:44 pm Post subject: |
|
|
I was so excited when I read this, I tried it immediately, but it had no effect. I tried it from the directory the file was in and also from C: prompt with an absolute path. (This was the msado15.dll in ADO folder of common files). I did get a confirmation message that the dll was registered.
Thinking along these lines, I also installed latest MDAC stuff from: http://msdn.microsoft.com/en-us/data/aa937729.aspx
It was MDAC 2.8 SP1 (which does NOT update Jet).
Still no luck, but I am ever hopeful and grateful. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2219
|
| Posted: Wed Mar 03, 2010 1:16 am Post subject: |
|
|
I haven't took a careful look at your second error message, now it appears really strange. Anyway, your first error message implied (almost certainly) that creating ADO object was failed, and the second error message implied that creating ADO object was succeeded but it failed at the stage of opening the database. That's all I can tell as I don't have access to Win2K. The only thing I can think of is to suggest to try to separate connecting and querying the database.
| 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()
pcn := COM_CreateObject("ADODB.Connection")
prs := COM_CreateObject("ADODB.Recordset")
COM_Invoke(pcn, "Open", sConnect)
COM_Invoke(prs, "Open", sSource, "+" pcn)
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_Invoke(pcn, "Close")
COM_Release(pcn)
COM_Term()
MsgBox, % sData
|
|
|
| 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
