DSNless connection to a MySQL Database

Posted by C on Sat 30 Aug 2003 04:43 PM — 10 posts, 43,105 views.

C Sat 30 Aug 2003 04:43 PM #0

Hey,

I have searched the net a bit looking for the connection string to connect without a DSN to a MySQL database. Anyone know what it might be? At the moment I am just using the following code:

Set objConn = CreateObject("ADODB.Connection")
strDSN = "DSN=dsnname;UID=;PWD="
objConn.Open strDSN

That works fine, but a DSNless connection is a little quicker and speed is important to me :)

Thanks for any help.

Shadowfyr USA Sat 30 Aug 2003 07:27 PM #1

You probably can't from in a script. Scripts are inherently reliant on COM access methods, so you are forced to use slower access methods. You could build a COM program that uses a direct way to talk the the database, but then you would have to have the script calling the COM program and it would still be slow. Assuming Python has some sort of library for MySQL, then you could probably use it to directly access the database, but you would also lose some portability with other Mushclient users. This assumes though that there is even a library wrapper to support MySQL in Python.

I am almost 100% certain though that no more direct method exists for VBScript, JScript or PerlScript. They just where not designed to provide such a thing.

Hmm.. Here we are, though not sure how well it works so far:

http://sourceforge.net/projects/mysql-python/

---
If you can't find a fast or rational way to do something, you are probably using a Microsoft product. ;)

Nick Gammon Australia Forum Administrator Sun 31 Aug 2003 07:18 AM #2

Yes, you can do that. See this post:

http://www.gammon.com.au/forum/?bbsubject_id=1552

Here is an example:



sub AddRecordTable (first, last, phone, notes)
dim db
Set db = CreateObject ("ADODB.Connection")

 ' Open the connection
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=c:\mushclient\mydb.mdb"

db.Execute "INSERT INTO Contacts (FirstName, LastName," & _
      "Phone, Notes) VALUES (" & _
          """" & first & """, " & _
          """" & last & """, " & _
          """" & phone & """, " & _
          """" & notes & """ );"         

db.Close
Set db = Nothing
end sub

Shadowfyr USA Sun 31 Aug 2003 06:22 PM #3

Ah hah.. This time I have got you Nick, unlike the prior post I responded to without looking for the date. He was asking if it was possible to access databases some way 'other' than DSN. ;)

Meerclar USA Sun 31 Aug 2003 07:58 PM #4

Sure it is, if he feels like writing a custom application for it. As far as accessing thru a script, DSN is pretty well the only viable option since scripting wasn't really designed to accomodate anything else.

Shadowfyr USA Sun 31 Aug 2003 08:22 PM #5

Actually Meerclar, building a custom application wouldn't do anything for you. The reason DSN is slow is because it doesn't use direct library access to a database functions, but instead relies on COM. If you use your own app, then you are just shifting the COM support from the DSN server to one of your own, with the same resulting drop in speed. The alternative method of using Python may solve this, since the script itself would use a Python library to access the MySQL database. This 'should' be faster, but I can't be sure how much faster or even if it will work from inside Mushclient. All in all though, Python seems to be the best bet for doing anything that the MS Windows Scripting system, can't, won't or does do, but inefficiently.

Nick Gammon Australia Forum Administrator Sun 31 Aug 2003 09:11 PM #6

Muahahaha!

Unless I misunderstood the original post which said "looking for the connection string to connect without a DSN" I have done that.

The DSN (data source name) I took to be the thing you set up in the ODBC control panel. This is the relevant part of his original post:

DSN=dsnname

My response was to allow a connection by using the actual file name, that saves going through the DSN to file name mapping, and also avoids the user having to make a DSN entry into the ODBC control panel.

However until "C" responds we won't know if this is what he wanted or not. He didn't say he wanted another way than scripting.

C Sun 31 Aug 2003 10:41 PM #7

Sorry to leave everyone hanging :p

Sorry Shadowfyr, Nick is indeed correct in his interpretation of what I am looking for. The code posted:

sub AddRecordTable (first, last, phone, notes)
dim db
Set db = CreateObject ("ADODB.Connection")

 ' Open the connection
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=c:\mushclient\mydb.mdb"

db.Execute "INSERT INTO Contacts (FirstName, LastName," & _
      "Phone, Notes) VALUES (" & _
          """" & first & """, " & _
          """" & last & """, " & _
          """" & phone & """, " & _
          """" & notes & """ );"         

db.Close
Set db = Nothing
end sub

This works fine for an access database, but my database is a MySQL database, so the Provider and Data Source would be different. I have seen this done for SQL Server [Although this is another MS product], so maybe it can't be done with MySQL and I will just have to make do with what I have.

Just had a look around there and according to a post on Experts exchange you can only access SQL Server, Jet and Oracle via DSNless connections.

[e.g. ConnStr = "driver={SQL SERVER};server=localhost;uid=user;pwd=pass;database=somedatabase" ]

Thanks anyway guys! :)

Poromenos Greece Sun 31 Aug 2003 11:39 PM #8

Forum wars?

Shadowfyr USA Mon 01 Sep 2003 12:17 AM #9

Ok.. I didn't realize there was a significant difference there. Oh well. lol