MySQL UDFs

[cdkid]

Ok, got it...it seems COM errors can't be handeled the way we handle other errors

Guess I need to look for some other method to handle the error.

I think it may help you to look on the first page at my second or third post. it has a thing to help you track down errors.

[kclteam]

The MySQL syntax is: SHOW DATABASES;

But then you would need to write a separate COM for it as the MySQL UDFs require database to be specified beforehand.

Lemme see what I can come up with.

Edit: Here is the solution:

#include 'MySQL.au3'
$sql = _MySQLConnect($user, $pass, $anydatabase, $host)
$query = "SHOW DATABASES"
$result = _Query($sql, $query)
With $result
    While NOT .EOF
    MsgBox(0, '', .Fields("Database").value)
    .MoveNext
    WEnd
EndWith
_MySQLEnd($sql)

Remember this will only show those databases that $user has privileges to see.

Edited March 9, 2006 by kclteam

[cdkid]

UPDATE:

added _GetDBNames

Returns an array

$array[0] = DB Count

$array[n] = Nth database name.

~cdkid

Edited March 9, 2006 by cdkid

[theguy0000]

cool thanks!

[theguy0000]

wait, then how do you select a different database if the connection makes you select one already?

Edited March 9, 2006 by theguy0000

[cdkid]

I'm working on functions to change the connection string right now, it'll be just a li'l bit longer.

~cdkid

[cdkid]

UPDATE:

Added _ChangeCon()

USAGE:

$m = _MySQLConnect($username, $password, $database, $server)
$m = _ChangeCon($m, '','',$newdatabase, '', $newserver);This will change the connection to a new server
;and database, but keep the same driver & username/password

~cdkid

[kclteam]

Thanks cdkid,

I was looking for something like this for a while.

[cdkid]

DEAD END ALERT!!! ( i figured this would be the best place to ask this)

I'm writing my functions in COM and it works great with autoit, so here's the au3 code

Dim $cdobj, $m, $l, $q
$l[0] = "this"
$l[1] = "is"
$m[0] = "a"
$m[1] = "test"
$cdobj = ObjCreate("CdSQL.MySQL")
$q = $cdobj.addrecord("sometable", $m, $l)
msgbox (0, '', $q);I have addrecord returning the SQL command for debugging purposes

Now, all that works great, but this doesnt (vbscript code)

dim cdobj, m, l, q
l = Array("this", "is")
m = Array("a","test")
set cdobj = WScript.CreateObject("CdSQL.MySQL")
q = cdobj.addrecord("sometable", m, l)
msgbox q

I get an "Invalid argument" error at "q = cdobj.addrecord..."

but this works fine

dim cdobj, q
set cdobj = WScript.CreateObject("CdSQL.MySQL")
q = cdobj.addrecord("sometable", array("this","is"), array("a","test"))
msgbox q

And here is the source code for my AddRecord function (set up for debugging, not executing query yet.)

Public Function AddRecord(ByVal table As Object, ByVal Col() As Object, ByVal Value() As Object) As Object
        'AddRecord overload #1 --> Variant array for Columns
        'Variant array for  Values
        Dim str As Object, i As Integer
        str = "INSERT INTO " & table & "("
        For i = 0 To UBound(Col, 1)
            If i = UBound(Col, 1) Then
                str = str & Col.GetValue(i)
            Else
                str = str & Col.GetValue(i) & ","
            End If
        Next
        i = 0
        str = str & ") VALUES("
        For i = 0 To UBound(Value, 1)
            If i = UBound(Value, 1) Then
                str = str & Value.GetValue(i)
            Else
                str = str & Value.GetValue(i) & ","
            End If
        Next
        str = str & ");"
        Return str
    End Function

If anyone could help me figure out the problem with this, i'd really appriciate it.

~cdkid

[jvanegmond]

CDKid I YOu

Is it ok if i use some of this for my project at school?

[cdkid]

Heh, I'm developing it for what ever you want to use it for, so I guess. But dont try to take credit for it or i will hunt you down!!!!!! lol anyway, yeah sure.

--Still wondering about this COM problem in my last post....

~cdkid

[erebus]

Congratulations!

I have been looking for this functionality for such a long time.

I really thank you, you are a lifesaver mate.

[erebus]

Just a quick question:

How can I add an error check for the ODBC driver's existance in the script?

The @error macro didn't work after the _MySQLConnect function. I think you should add such functionality in your UDF.

Thanks in advance,

Edited March 17, 2006 by erebus

[kclteam]

What I do is check the registry for installed softwares...if it contains MySQL ODBC Connector, then OK else error.

[cdkid]

Can you post the registry key where that is so i can add it to the _MySQLConnect UDF?

Edit: Also, I fixed the problem in my post earlier, for it to work with VBScript i had to remove the () from the param names.

~cdkid

Edited March 17, 2006 by cdkid

[erebus]

@cdkid: I saw in your UDF that you have @error support. However it doesn't work (and I don't know why).. Can you tell?

Edit: about the registry key, I think he means the 'HKLM\Software\MySQL AB\MySQL Connector/ODBC 3.51' one. Also 'HKLM\Software\ODBC\ODBCINST.INI\ODBC Drivers' would do the job. However I don't like this method much.. I prefer the @error macro instead.

Edited March 17, 2006 by erebus

[cdkid]

Well, what I'd do is check the registry key & set @ERROR if it doesnt contain MySQL ODBC Connector

[cdkid]

[uPDATE]

If the driver is not found _MySQLConnect should set @Error to 2 and return 0.

~cdkid

[erebus]

[uPDATE]

If the driver is not found _MySQLConnect should set @Error to 2 and return 0.

~cdkid

But for some reason it doesn't...

Can anyone explain why this happens?

[kclteam]

that's becoz the error is COM error...so script stops abruptly thereafter...no @error is set watsoever...

i read it in the help files...but not quite sure if my concepts of COM are clear...

I use COM error handeler which cdkid has mentioned in 3rd or 4th post in the very first page of this thread...and I guess that's the only way

P.S.: I was talking about

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

Scan the content of this key for MySQL ODBC Connector.

Edit: There is a thread in the forums about a script which checks for all installed softwares on a compute. I use that actually.

Edited March 17, 2006 by kclteam