Support Center » Knowledgebase » DSN and DSN-less Connection Strings

DSN and DSN-less Connection Strings



DSN or DSN-less?

Connection to your database can be done using either a DSN (Data Source Name) or without DSN aka DSN-less.

DSN stores all the information required to connect to your database, including your database type, server / file location, username and password as well as connection options is stored in the DSN itself. Your script simply refers to the connection using the DSN name.

On the contrary, when using DSN-less method, your script would have to specify all the above details in the connection string itself. See examples below.

Should you use DSN or DSN-less method?

Some people tend to think that DSN-less is faster because it saves the time to look up the DSN information in the registry. Some people think DSN is faster based on several tests.

The main consideration really is this:

With DSN method, you will have to maintain two sets of settings: your script and the DSN itself. With DSN-less method, all you need to maintain is the script / connection string.

For portability reasons, it would be better to use DSN-less method. However depending on your circumstances, DSN method might be more appropriate / convenient.

For MS Access Database connection strings, you MUST use the DSN-less OLEDB driver. ODBC driver for MS Access causes instability on the server.

DO NOT USE ODBC (DSN or DSN-less) for MS Access!

 

Example of DSN Connection:

MS SQL / MySQL / PostgreSQL

"DSN=dsnname"

 

The DSN itself can be created / configured through the control panel - ODBC section.

Please note that there is normally a prefix that is enforced upon the DSN name which is based on your account to avoid name clashes with other users. Be sure to use the full DSN name as displayed on the control panel, including the prefix.

 

Examples of DSN-less connection:

MS Access

With OLEDB:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:somepathmyDb.mdb;"

MS SQL

With ODBC:

"Driver={SQL Server}; Server=MyServerName; Database=myDatabaseName; UID=myUsername; PWD=myPassword"

With OLEDB (Recommended):

"Provider=sqloledb; Data Source=myServerName; Initial Catalog=myDatabaseName; User Id=myUsername; Password=myPassword"

MySQL

With ODBC:

"Driver={MySQL ODBC 3.51 Driver}; Server=servername; Database=dbname; UID=username; PWD=password; Option=3"

MySQL (When connecting from Windows 2008)

With ODBC:

"Driver={MySQL ODBC 5.1 Driver}; Server=servername; Database=dbname; UID=username; PWD=password; Option=3"

PostgreSQL

With ODBC:

"Driver={PostgreSQL}; Server=servername; Database=dbname; UID=username; PWD=password"

 

Microsoft's Knowledge Base provides more in-depth information on DSN and DSN-less connections.



Article Details
Article ID: 23
Created On: 07 Aug 2004 05:00 AM
 Back
 Login [Lost Password] 
Email:
Password:
Remember Me:
Please note that the login and password to the support area is NOT the same as your hosting control panel login and password
 
 Search
 Article Options
Home | Register | Submit a Ticket | Knowledgebase | Downloads | Control Panel User's Guide | Server Status
Language: