There are several troubleshooting techniques you'll need to use to get to the root of the problem.

[*] Instead of using .echo -q on each $com() call, save the results in a variable, such as %Successful. Then you can check its value at each stage and do some error reporting (which I did not include - for brevity - in the original example).

%Successful = $com(mysql.Database, Open, 1, *bstr, %Connection.String)
if (!%Successful) {
  • echo -ti * ERROR: Database didn't open. | comclose mysql.Database | halt
}

[*] You should check to see if a $com() object is opened before attempting to create it.

if ($com(mysql.Database)) .comclose mysql.Database
.comopen mysql.Database ADODB.Connection

[*] You can check the .State proper of a Command, Connection, Record, Recordset or Stream object.

alias State {
  • [color:#006600]; If no valid COM object is supplied, simply return $null.
    ;
    if (!$com($1)) return

    ; Make sure we're checking on an ADODB object.
    ;
    if ($gettok($com($1).progid,1,46) != ADODB) return

    ; Make sure we're checking the right kinds of objects.
    ;
    if (!$istok(Command Connect Record Recordset Stream, $gettok($com($1).progid,2,46),32)) return

    ; Otherwise, get the state of the object.
    ;
    .echo -q $com($1, State, 2)

    ; Check for a specifically named state property
    ;
    if (($prop == Closed) && ($com($1).result == 0)) return 1
    if (($prop == Open) && ($com($1).result == 1)) return 1
    if (($prop == Connecting) && ($com($1).result == 2)) return 1
    if (($prop == Executing) && ($com($1).result == 4)) return 1
    if (($prop == Fetching) && ($com($1).result == 8)) return 1

    ; If some other state is requested, return 0 rather than $null, meaning Unknown state.
    ;
    if ($prop) return 0

    ; If we make it this far, then no property was specified on a valid COM object. This means
    ; we will want to grab the entire list of states which are ANDed together.
    ;
    var %State.List
    if ($com($1).result == 0) return Closed
    if ($com($1).result & 1) %State.List = Open
    if ($com($1).result & 2) %State.List = %State.List Connecting
    if ($com($1).result & 4) %State.List = %State.List Executing
    if ($com($1).result & 8) %State.List = %State.List Fetching

    ; And return them.
    ;
    return %State.List
}
[/color]
Examples:

if ($State(mysql.Database).Open) {
  • ; then the database connection is open and active
}

or

if (Open Executing == $State(mysqlDatabase)) {
  • ; wait for a bit, still executing a statement
}

The same can be done for the recordset object at any time.

[*] Make as full an error reporting mechanism as you require to troubleshoot each step along the way. You might want to echo out every dynamic variable after it gets set (such as a dynamically-generated SQL statement) and/or halt after each step, till you find the problem step.
  • As you can see, there are a lot of ways for you to troubleshoot exactly where the problem lies. Once you localize that, then you get to figure out what broke when you changed something.

    For instance, if your connection string isn't quite right, then you can change it to something else until you get it working exactly as you need it to. I use 2 different ones for MySQL, both work for me.

    DRIVER={MySQL ODBC 3.51 Driver};Port=3306;Option=16384;Stmt=;Database=[color:#840017]dbname    ;Server=12.34.56.78;User ID=username;Password=passWORD
    [/color]
    or

    Provider=MySQLProv;Data Source=[color:#840017]dbname    ;Server=localhost;User ID=username;Password=passWORD
    [/color]
    You can use the IP for the server or (if it's on your machine) localhost, depending on how you have your permissions set. And sometimes, you have to fiddle with those settings to get it working exactly right, as I said.

    • DALnet: #HelpDesk and #m[color:#FF0000]IR[color:#EEEE00]C