MySQL query function - having problems creating an object.

[JRS]

Hello All,

I'm new to MySQL but I have established a database and I've
successfully run queries against it in a command prompt window.
I've also (seemingly) successfully installed the MySQL ODBC 3.51
Driver and when I run the a test it comes back successful.

I'm trying to implement the the function I've taken from this
forum which I modified somewhat but right out of the
blocks I'm getting the following Macro Scheduler error dialog:

Microsoft VBScript runtime error :429
ActiveX component can't create object: 'myodbcobject'
Line 19 column 0
[OK] [Abort]

(I always choose abort)

I understand other aspects of what I did with the function may be
in error as well but I think the first order of business is figuring
out what I've done/doing wrong that I can't create an object.

Any help or insight by any of you fine people here on this forum would
be graciously and greatly appreciated. Joel S.


VBSTART
Function GetCustomerName(MemberName)
Dim SQLString
set MyDB = CreateObject("myodbcobject")
SQLString = "SELECT * FROM mainlang WHERE colB='4994'"
set rsCustomers = MyDB.Execute(SQLString)
If Not rsCustomers.EOF then
GetMemberName = rsCustomers.Fields("MemberName")
Else
GetMemberName = "Not Found"
End if
MyDB.Close
End Function
VBEND
Input>MemberName,Enter Member Last Name:
VBEval>GetCustomerName("%MemberName%"),CustName
MessageModal>The Member Name Is : %CRLF% %CRLF% %MemberName%

[Marcus Tettmar]

Not sure what myodbcobject is. Is this an ActiveX object I don't know about? With ANY ODBC connection you should be using ADODB.Connection. You then use Open with the connection string relevant to the database in use. Assuming you have the MySQL ODBC drivers installed correctly you should use something like:

Code: Select all

ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=servername; DATABASE=databasename; USER=username; PASSWORD=password; OPTION=3;"

Set MyDB = CreateObject("ADODB.connection")
MyDB.Open ConnectionString

//Now you should have a connection.  So now you can do your SQL:
SQLString = "SELECT * FROM mainlang WHERE colB='4994'"
set rsCustomers = MyDB.Execute(SQLString)

Modify the connection string as required. Double check on the MySQL website that I have the connection string format right. You may also need to specify the port number.

[JRS]

Thank you very much for your reply! Forget all about the
weird CreateObject string ...

I tried your fix and whatever I plug into 'SERVER= ' I get a Macro
Scheduler unknown server host error dialog.

I did a MySQL status of my MySQL installation from the command
prompt (following) and tried all kinds of combinations:

SERVER=jrs@localhost
SERVER=5.0.21-community
SERVER=local
SERVER=MYSQL.EXE Ver 14.12 Distrib 5.0.21, for Win32

etc. to no avail.

Is there some kind of option that will return my server that your
ConnectionString would recognize?

Thanks very much for your patience. I really appreciate your help




mysql> \s]
--------------
C:\PROGRA~1\MYSQL\MYSQLS~1.0\BIN\MYSQL.EXE Ver 14.12 Distrib 5.0.21, for Win32
(ia32)

Connection id: 5
Current database:
Current user: jrs@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.21-community
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 24 min 14 sec

Threads: 1 Questions: 19 Slow queries: 0 Opens: 0 Flush tables: 1 Open tabl
es: 7 Queries per second avg: 0.013
--------------

->


VBSTART
Function GetCustomerName(MemberName)
Dim SQLString
ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost via TCP/IP; DATABASE=languages; USER=jrs; PASSWORD=nottherealpassword; OPTION=3;"
Set MyDB = CreateObject("ADODB.connection")
MyDB.Open ConnectionString
SQLString = "SELECT * FROM mainlang WHERE colB='4994'"
set rsCustomers = MyDB.Execute(SQLString)
If Not rsCustomers.EOF then
GetMemberName = rsCustomers.Fields("MemberName")
Else
GetMemberName = "Not Found"
End if
MyDB.Close
End Function
VBEND
Input>MemberName,Enter Member Last Name:
VBEval>GetCustomerName("%MemberName%"),CustName
MessageModal>The Member Name Is : %CRLF% %CRLF% %MemberName%
Remark>Let>SQLString=select * from mainlang where colB = '4994'
Remark>VBRun>UpdateDB("%SQLString%"),result
MessageModal>uid
MessageModal>result
MessageModal>%SQLString%

[Marcus Tettmar]

I'm afraid only you know the server your MySQL database is on. I can't tell you that. You need to specify the server name and possibly port number if it is not the default. The server needs to have that port open. For help with MySQL and the MySQL connection string you should check out the MySQL documentation and web site.

[adroege]

I believe I've found the information from your posted output of the \s command as follows:

mysql> \s
-----------------------------
Connection: localhost via TCP/IP
TCP port: 3306

Based upon this information you should set your SERVER like so:

Code: Select all

SERVER=localhost
    OR
SERVER=127.0.0.1

(If port number is required then use the number 3306)

localhost is usually a synonym for the IP address of 127.0.0.1 which refers to the local computer.