in reply to Using DBI to make connection to a database

The syntax for a DBI connection string is 'dbi:DRIVERNAME:stuff' where DRIVERNAME is the name allocated to the DBD (database driver you want to use) in DBI. Whatever, you put for DRIVERNAME will cause DBI to attempt to load DBD::DRIVERNAME and hence when you used 'dbi:DriverName' you got "you may need to install the DBD::DriverName error. For DBD::ODBC, the DRIVERNAME should be 'ODBC'. So, your connection string should start 'dbi:ODBC'.

After the 'dbi:ODBC' you add a further ':' and then usually follow it with a series of name=value pairs separated by semi colons ';'. So now your connection string looks like 'dbi:ODBC:attr1=value;attr2=value;'. DBD::ODBC (actually in this case ODBC more precisely) defines the attribute names you can use so basically, after 'dbi:ODBC:' you add whatever attributes ODBC and your ODBC driver define to connect to your ODBC data source. The most common and most simple thing to do is as you did and create a DSN (data source name) in the ODBC administrator and then you'd use 'dbi:ODBC:DSN=name_of_my_dsn' as the connection string (DBD::ODBC also supports for this case only 'dbi:ODBC:name_of_my_dsn' as back in time ODBC did not support attributes).

If you don't want to create a system or user ODBC DSN you can use so called DSN-less connection strings. In this case you have to tell the ODBC driver manager which ODBC driver to use )in much the same way you tell DBI which DBD to load) and then you tell the ODBC driver which database to connect to e.g., 'dbi:ODBC:DRIVER={MS SQL Server Driver};Server=mydatabaseserver;Database=mydatabase;' like the example already provided in this thread. In this case you should consult your ODBC driver documentation for driver specific attributes and the ODBC documentation for what ODBC defined connection attributes there are (e.g., DSN, DRIVER, FILEDSN). NOTE: a) I made up "MS SQL Server Driver", you need to use the string listed in the ODBC administrator under the drivers tab b) {} are used in ODBC connection strings to delimit values which may contain spaces or other punctuation.

Lastly, you need to be aware that since you are on Windows you might have a 64bit machine. As the ODBC driver is really a DLL (code wrapped up in a way that allows it to be dynamically loaded into a program) you cannot mix 32 bit apps with 64bit DLLs and vice versa. If your perl is 64 bit then you need to create your DSNs with the 64 bit ODBC driver manager setup dialogues and if your perl is 32 bit you need to use the 32 bit ODBC driver manager. If you hit this issue do a search for where to find the 2 different ODBC driver managers.

Hope this helps although I imagine by now you've read some documentation or searched online which would probably have been quicker all round than posting here for a "quick" answer.

  • Comment on Re: Using DBI to make connection to a database