Reading the hmail DB gives "Error: 800A01B6 "

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

I've got a script that I would like to do a lookup on a custom table I added to the hmail DB. Pretty simple, looks like this:

Code:

' we need to grab a DB connection so we can validate this domain
Dim obDatabase
Set obDatabase = obApp.Database
    
' which subdomain is this message from? //does oMessage.To ONLY contain the address?
tDomain = Split(oMessage.To,"@")
EventLog.Write("Looking For: " + tDomain(1))
    
' ask the DB if we should post this message somewhere
sSQL = "select * from mailhop where mh_Domain = '" & tDomain(1) & "'"
oRS = obDatabase.Execute(sSQL)
EventLog.Write("Query recordcount: " + oRS.recordcount)

Error that gets thrown is:

Code:

“ERROR” 2224 “2010-05-11 21:03:48.174” “Script Error: Source: Microsoft VBScript runtime error – Error: 800A01B6 – Description: Object doesn’t support this property or method: ‘obDatabase.Execute’ – Line: 58 Column: 4 – Code: (null)”

I'm fresh out of ideas, looks like it should work fine. Then again this is my first crack at VBScript. Perhaps a more trained set of eyes wouldnt mind throwing me a bone?

Bill48105 · **Posted:** 2010-05-19 03:23

Hi,
The line # is your clue. It doesn't like:
Set obDatabase = obApp.Database

Did you define obApp & authenticate with hmail earlier in the script? Usually something like:
Set obApp = CreateObject("hMailServer.Application")
Call obApp.Authenticate("**USER**", "***PASSWORD***")
Bill

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

Earlier in the script I called:
Set obApp = CreateObject("hMailServer.Application")
Call obApp.Authenticate("administrator", "XXXX")

The line number it is barfing on is: oRS = obDatabase.Execute(sSQL)

Gotta be something small. Is there soe way of telling if my onApp.authenticate() worked?

Bill48105 · **Posted:** 2010-05-19 04:24

Odd. When I used your code snippet the obDatabase line was the one that puked for me until I added the authenticate stuff ahead of it & then that went away but tough for us to know for sure without seeing your entire script.

As far as I know you tell by trying to access an hmail object.

Odds are start with something simple like trying one of the example scripts that works then make changes to that so you have a working base to catch your mistakes. Tis how I always do it.
Bill

mattg · **Posted:** 2010-05-19 08:45

ievolve wrote:

The line number it is barfing on is: oRS = obDatabase.Execute(sSQL)

http://www.hmailserver.com/documentatio ... t_database
Try

obDatabase.ExecuteSQL(sSQL)

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

Thanks Matt, that was what I was doing wrong.

However I am now getting a different error, it is saying "Object Required" for oRS, here is the full source, real easy, jut query the DB and pull back a recordset.

Is anyone familiar with any other scripts that read the DB that I could look for an example of how to do this?

Code:

Dim tDomain, sSQL, oRS
      
      ' we need to grab a DB connection so we can validate this domain
      Dim obDatabase
      Set obDatabase = obApp.Database
      
      ' which subdomain is this message from? //does oMessage.To ONLY contain the address?
      tDomain = Split(oMessage.To,"@")
      EventLog.Write("Looking For: " + tDomain(1))
      
      ' ask the DB if we should post this message somewhere
      sSQL = "select * from temptable where mh_Domain = '" & tDomain(1) & "'"
      oRS = obDatabase.ExecuteSQL(sSQL)
      EventLog.Write("Query recordcount: " + oRS.recordcount)

Full error is:

Code:

“ERROR” 2224 “2010-05-21 00:17:10.596” “Script Error: Source: Microsoft VBScript runtime error – Error: 800A01A8 – Description: Object required: ‘oRS’ – Line: 59 Column: 4 – Code: (null)”

mattg · **Posted:** 2010-05-21 09:01

There is a database 'insert' function in this script - viewtopic.php?f=20&t=13890

I've not queried the database directly, but I suspect that you are not setting the select statement properly. Perhaps the file name, or some escaping of quotes is required...

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

Note, I also tried to set oRS like this (alas, to no avail):

Code:

Set oRS = CreateObject("ADODB.RecordSet")

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

Yea, I saw that script and based my stuff off it.

I am beginning to wonder if ExecuteSQL() only lets you insert stuff and doesnt actually return anything.

mattg · **Posted:** 2010-05-21 09:07

What database do you use?

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

How would a guy properly escape the quotes in that query? Sorry I'm a total newb to vbscript

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

This is the mysql DB that hmail uses, I just added a table to the hmail DB to store some config I would like to read.

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

Note I tried a simple select statement with no special chars and got the same error back. So I dont see this as an issue of escaping characters.

mattg · **Posted:** 2010-05-21 10:10

ievolve wrote:

This is the mysql DB that hmail uses, I just added a table to the hmail DB to store some config I would like to read.

This means that you originally had a version of hMailserver prior to ver 5. Is that correct?

From Ver 5 hmailserver uses MS SQL not MySQL as the built in database. There will be different commands...

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

We are using 5.3.3-B1829 with MySQL

mattg · **Posted:** 2010-05-22 02:51

And is that an earlier version with the built in MySQL database that was upgraded, or have you installed MySQL yourself and manually connected your hMailserver to it?

I just really want to be sure before I help with the SQL code...

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

This is a fresh install of 5.3.3-B1829, we installed MySQL fresh ourselves and pointed hmail at it. The mail server itself works great otherwise.

mattg · **Posted:** 2010-05-22 07:54

(Remembering that I don't know MySQL at all, I'd normally use a query builder with something like PHPMyAdmin...)
Just having a look at a few MySQL queries on the net...

Why the single quotes in this line?

ievolve wrote:

sSQL = "select * from temptable where mh_Domain = '" & tDomain(1) & "'"

The examples that I found when googling just have table name un-quoted.

Try...

ievolve wrote:

sSQL = "select * from temptable where mh_Domain = " & tDomain(1)

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

Even better, I tried this to no avail:

Code:

sSQL = "select * from temptable"

The error (Object required: ‘oRS’) seems to be telling me that my variable oRS needs to be some sort of an object to receive the results of the executeSQL()?

In the sample script that inserts stuff to the DB, they just do a "Call" to executeSQL() what I want to do that is different is store that result set in a variable that I can loop over.

I think the trick lies in trying to figure out what data type that the ExecuteSQL() method is returning and it doesnt appear to be documented anywhere? I'd be happy to pay for support to figure this out, but I dont really know where to start.

Bill48105 · **Posted:** 2010-05-22 18:16

This post from martin help any?
"You can't. The Execute is purely ment for executing statements. It's not a complete access layer for the database.

"
viewtopic.php?p=33548#p33548

Perhaps hMail has changed since because that post is old but makes it sound like you get no results, it just runs the SQL statement you supply..

ievolve · **Joined:** 2010-04-05 05:17 **Posts:** 24

Nice find Bill. That somewhat answers my question, bummer though, it would be so much more efficient to use the DB object that is already there than to build up and tear down database connection objects for each and every email.

Anyone aware of any other lighter weight key/value stores in VBScript that I can use to do a simple config lookup?

Bill48105 · **Posted:** 2010-05-23 03:47

No problem, glad I was able to help.
I agree but may need to use what is available & possibly put in a feature request.

Reading the hmail DB gives "Error: 800A01B6 "

Who is online