Understanding Data Sources | Mastering MySQL 4

A data source can be the path to a library of files, a server, or, in this case, a MySQL database. Connection information is associated with the data source—stored, for example, in the Windows Registry. To connect to the data source, the ODBC Driver Manager looks up the connection information associated with the DSN and connects using this. To connect via ODBC, it's not always necessary to have an existing DSN. You can specify the driver directly. All of the examples except for the DAO VB example later in this appendix connect without a preset DSN.

Setting Up a Data Source on Windows

The first step in getting a Windows application to connect to MySQL via ODBC is setting up a data source:

Select Start ® Settings ® Control Panel.
Depending on your version of Windows, you either select 32-bit ODBC or ODBC, or alternatively Administrative Tools and Data Sources (ODBC).
Click Add.
From the list that appears, select the MySQL driver you installed with MyODBC, and then click Finish.
The driver default configuration screen will appear. Complete the required details. The Windows DSN can be anything you want, and the host, database, username, password, and port details are the usual details required for connecting to MySQL. You can also specify a SQL command to run when you connect to the server.
You can click the Options button to be able to select various options to cater for the idiosyncrasies of each application (those that are not 100-percent ODBC compliant). For example, currently with Microsoft Access you should check the Return Matching Rows option. You may need to experiment to get things working smoothly, and you should look at the MySQL website as well, which will have the latest options for many common applications.
Click OK and the data source will be added.
Depending on your ODBC version, you may be able to test your connection by clicking Test Data Source. Check your connection details if the test fails.

Setting Up a Data Source on Unix

On Unix, you can edit the ODBC.INI file directly to set up your data sources.

For example:

 [ODBC Data Sources]   myodbc     = MySQL ODBC 3.51 Driver DSN    [myodbc]   Driver       = /usr/local/lib/libmyodbc3.so   Description  = MySQL ODBC 3.51 Driver DSN   SERVER       = localhost   PORT         =   USER         = root   Password     = g00r002b   Database     = firstdb   OPTION       = 3   SOCKET       =

To set the various options for non-ODBC-compliant applications on Unix, you set the OPTION value based upon Table H.1.

Table H.1: Connection Options
Bit	Description
1	Cannot handle receiving the real width of a column.
2	Cannot handle receiving the actual number of affected rows (the number of found rows will be returned instead).
4	Makes a debug log in C:\myodbc.log or /tmp/myodbc.log.
8	Sets an unlimited packet limit for results and parameters.
16	Does not prompt for questions.
32	Toggles dynamic cursor support (enabling or disabling it).
64	Ignores the database name in a structure such as databasename.tablename.fieldname.
128	An experimental option that forces the use of ODBC manager cursors.
256	An experimental option that disables the use of extended fetch.
512	CHAR fields are padded to the full length of the field.
1024	The SQLDescribeCol() function returns fully qualified column names.
2048	Uses compressed protocol.
4096	Causes the server to ignore spaces between the function name and the open bracket and makes all function names keywords as a result.
8192	Uses named pipes to connect to a server running on NT/2000/XP.
16384	LONGLONG fields are converted to INT fields.
32768	An experimental option that returns user as Table_qualifier and Table_owner from the SQLTables() function.
65536	Reads the MySQL configuration file for the client and odbc group's parameters.
131072	Adds extra safety checks.
262144	Disables transactions.
524288	In debug mode, enables query logging to the c:\myodbc.sql or /tmp/myodbc.sql file.

Setting Connection Options

If a graphical interface is not available, you can use the option values in Table H.1 to connect. To combine options, simply add the values together (so 3 is a combination of 1 and 2).