Access mysql from perl using odbc

Question

I need to access MySQL from perl.

Can I connect using the odbc driver?

If so, how do I do that?

I just can't seem to get the connection string right!

On windows, where DBD:MySQL is available, this is how I connected:

DBI->connect("DBI:mysql:$database", 
    $user, $password, {RaiseError => 1});

This is what I've tried:

DBI->connect("DBI:ODBC:$database", $user, $password, {RaiseError => 1});'

This results in the following error:

DBI connect('database','username',...) failed: [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (SQL-IM002) at -e line 1

This is a standard connection on a local machine -- that is, both the perl app and the MySQL server are on the same machine.

I'd like to use odbc because DBD:mysql doesn't build for ActiveState perl on Mac OS X, and it's much easier for us when we stick with packages that can be obtained through ppm.

Axeman · Accepted Answer · 2011-12-08 21:12:41Z

2

Have you tried including the port?

http://www.connectionstrings.com/mysql#p0cs1

Also, for ODBC you might need to specify the driver, if you're not going against a previously set up DSN

DBI->connect( join( ';'
    , 'DBI:ODBC:driver={MySQL ODBC 5.1 Driver}'
    , "Server=$SQL_SERVER"
    , "Database=$SQL_DATABASE"
    , "UID=$SQL_USERID"
    , "PWD=$SQL_PASSWD"
    ));

edited Dec 8, 2011 at 21:12

answered Dec 8, 2011 at 20:52

Axeman

29.9k2 gold badges50 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Fenwick Over a year ago

No, I haven't. I'll try that. I've updated the OP to show what I have tried.

bohica · Accepted Answer · 2011-12-09 14:22:18Z

Your $database is not a database it is a Data Source Name (DSN). 'dbi:ODBC:xxx' should be read as 'dbi:ODBC:DSN=xxx' and the iODBC driver manager will look for a DSN called 'xxx' in its config files. As you are on a mac there should be a data sources GUI app you can use to create DSNs or alternatively, you'll have to find iODBC's ini files and insert them yourself (iodbc.ini and iodbcinst.ini I think or perhaps without the 'i').

"Data source name not found and no default driver specified" normally means the xxx data source cannot be found in the ini files (there are user and system ones) however that trailing "Driver could not be loaded (SQL-IM002)" kind of suggests the mysql ODBC driver library could not be loaded and hence your DSN does exist but it points to either an invalid mysql ODBC driver lib or one which is broken. Here again the GUI app allows you to add/edit drivers or you'll need to find the iodbcinst.ini file and see what the Driver attribute points to for the mysql driver.

Collectives™ on Stack Overflow

Access mysql from perl using odbc

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related