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

[ 132 posts ]

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

Print view | E-mail friend

Previous topic | Next topic

Author

Message

beardboy

Post subject:

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

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

Is there not a specific command to test if the connection is still open?

thanks,
beardboy

Top

Sean

Post subject:

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

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

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.

Top

beardboy

Post subject:

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

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

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

Top

Sean

Post subject:

Posted: July 9th, 2009, 5:35 am

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

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.

Top

beardboy

Post subject:

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

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

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

Top

Sean

Post subject:

Posted: July 9th, 2009, 7:07 am

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

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.

Top

infogulch

Post subject:

Posted: February 2nd, 2010, 12:33 am

Joined: March 27th, 2008, 2:14 pm
Posts: 700

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

Top

Sean

Post subject:

Posted: February 2nd, 2010, 1:37 am

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

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

Top

infogulch

Post subject:

Posted: February 2nd, 2010, 4:52 pm

Joined: March 27th, 2008, 2:14 pm
Posts: 700

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

Top

Sean

Post subject:

Posted: February 3rd, 2010, 1:18 am

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

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.

Top

infogulch

Post subject:

Posted: February 9th, 2010, 4:32 pm

Joined: March 27th, 2008, 2:14 pm
Posts: 700

Yeah, that fixed it. Thanks Sean.

_________________
Scripts - License

Top

Toxey

Post subject:

Posted: March 1st, 2010, 10:36 pm

Joined: December 13th, 2007, 11:41 pm
Posts: 31

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.

Top

Sean

Post subject:

Posted: March 2nd, 2010, 1:08 am

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

Search for msado15.dll in your system, and if found it, go to the directory and register it through regsvr32.exe msado15.dll.

Top

Toxey

Post subject:

Posted: March 2nd, 2010, 10:44 pm

Joined: December 13th, 2007, 11:41 pm
Posts: 31

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.

Top

Sean

Post subject:

Posted: March 3rd, 2010, 1:16 am

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

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

Top

Page 7 of 9

[ 132 posts ]

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

Board index » AutoHotkey » Scripts

All times are UTC [ DST ]

Who is online

Users browsing this forum: Bing [Bot], notsoobvious, Relayer and 11 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