PHP HelpPHP Help

Online Community that helps beginners learn PHP,
and webmasters solve PHP coding problems

since 1999

Using MyODBC to Connect to a Remote Database

MyODBC Compatibility and Options

ODBC Compatibility

Here are a few notes on making your data compatible with ODBC. They are from the MySQL manual.

The "zero" values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values '0' or 0, which are easier to write. "Zero" date or time values used through MyODBC are converted automatically to NULL in MyODBC 2.50.12 and above, because ODBC can't handle such values.

MyODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.

The special date '0000-00-00' can be stored and retrieved as '0000-00-00'. When using a '0000-00-00' date through MyODBC, it will automatically be converted to NULL in MyODBC 2.50.12 and above, because ODBC can't handle this kind of date.

String functions now return VARCHAR rather than CHAR and the column type is now VARCHAR for fields saved as VARCHAR. This should make the MyODBC driver better, but may break some old MySQL clients that don't handle FIELD_TYPE_VARCHAR the same way as FIELD_TYPE_CHAR.

Understanding MyODBC Options

MyODBC allows the user to specify several options affecting the behavior of the ODBC connection. I don't know what all of the options do, but will present those I have used or can find a description of. I will be updating these in the future.

MySQL Driver Configuration

Don't optimize column width.

Return matching rows.

Trace MyODBC. This option activates logging of SQL statements sent to the MySQL server. It is useful when you encounter difficulties connecting to the database. The log is written to the file "myodbc.log" on the C:\ drive. (Note that you must use MYSQL.DLL and not MYSQL2.DLL for this option to work!) Once you have generated a log file, check the queries that MyODBC sends to the MySQL server; You should be able to find the queries by searching for the string ">mysql_real_query" in the "myodbc.log" file.

Allow BIG results. SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will have many rows. In this case, MySQL will directly use disk based temporary tables if needed. MySQL in this case will prefer to do a sort instead doing a temporary table with a key on the GROUP BY elements.

Don't prompt on connect. When initiating an ODBC connection, do not ask the user to manually enter username and password (and possibly other information).

Simulate ODBC 1.0. Behave like older ODBC.

Ignore # in #.table.

Use manager cursors (experimental).

Don't use setlocale.

Pad CHAR to full length.

Return table names in SQLDescribeCol.

Use compressed protocol.

Ignore space after function names.

Don't optimize column width.

Force use of named pipes.

Change BIGINT columns to INT.

No catalog (experimental).

Read options from C:\my.cnf.

Safety (use this when you have problems).

Pages:  1   2   3   4   5Related Forum Topics   |   More Tutorials »

Copyright © 2017