Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
Most programs should work with Connector/ODBC, but for each of those listed here, there are specific notes and tips to improve or enhance the way you work with Connector/ODBC and these applications.
With all applications, ensure that you are using the latest Connector/ODBC drivers, ODBC Manager and any supporting libraries and interfaces used by your application. For example, on Windows, using the latest version of Microsoft Data Access Components (MDAC) will improve the compatibility with ODBC in general, and with the Connector/ODBC driver.
The majority of Microsoft applications have been tested with Connector/ODBC, including Microsoft Office, Microsoft Access and the various programming languages supported within ASP and Microsoft Visual Studio.
To improve the integration between Microsoft Access and MySQL through Connector/ODBC:
For all versions of Access, enable the Connector/ODBC Return matching rows option. For Access 2.0, also enable the Simulate ODBC 1.0 option.
Include a TIMESTAMP column in all tables that you want to be able to
update. For maximum portability, do not use a length specification in the column declaration
(which is unsupported within MySQL in versions earlier than 4.1).
Include a primary key in each MySQL table you
want to use with Access. If not, new or updated rows may show up as #DELETED#.
Use only DOUBLE float fields. Access fails when comparing with
single-precision floats. The symptom usually is that new or updated rows may show up as #DELETED# or that you cannot find or update rows.
If you are using Connector/ODBC to link to a table that has a BIGINT column, the results are displayed as #DELETED#. The work around solution is:
Have one more dummy column with TIMESTAMP as the data type.
Select the Change BIGINT columns to
INT option in the connection dialog in ODBC DSN Administrator.
Delete the table link from Access and re-create it.
Old records may still display as #DELETED#, but newly
added/updated records are displayed properly.
If you still get the error Another user has changed
your data after adding a TIMESTAMP column, the following trick may help you:
Do not use a table data sheet view. Instead, create a form with
the fields you want, and use that form data sheet view. Set the
DefaultValue property for the TIMESTAMP column to NOW(). Consider hiding the TIMESTAMP column from view so your users are not
confused.
In some cases, Access may generate SQL statements that MySQL cannot
understand. You can fix this by selecting "Query|SQLSpecific|Pass-Through" from the Access menu.
On Windows NT, Access reports BLOB columns as OLE OBJECTS. If you
want to have MEMO columns instead, change BLOB columns to TEXT with ALTER TABLE.
Access cannot always handle the MySQL DATE column properly. If you have a problem with these, change
the columns to DATETIME.
If you have in Access a column defined as BYTE,
Access tries to export this as TINYINT instead of TINYINT
UNSIGNED. This gives you problems if you have values larger than 127 in the column.
If you have very large (long) tables in Access, it might take a very long
time to open them. Or you might run low on virtual memory and eventually get an ODBC Query Failed error and the table cannot open. To deal with
this, select the following options:
Return Matching Rows (2)
Allow BIG Results (8).
These add up to a value of 10 (OPTION=10).
Some external articles and tips that may be useful when using Access, ODBC and Connector/ODBC:
Read
Optimizing Access ODBC Applications
For a list of tools that can be used with Access and ODBC data sources, refer to http://www.mysql.com/portal/software/convertors/ section for list of available tools.
If you have problems importing data into Microsoft Excel, particularly numeric, date, and time values, this is probably because of a bug in Excel, where the column type of the source data is used to determine the data type when that data is inserted into a cell within the worksheet. The result is that Excel incorrectly identifies the content and this affects both the display format and the data when it is used within calculations.
To address this issue, use the CONCAT() function in your queries. The use of CONCAT() forces Excel to treat the value as a string, which Excel will
then parse and usually correctly identify the embedded information.
However, even with this option, some data may be incorrectly formatted, even though the source data
remains unchanged. Use the Format Cells option within Excel to change the
format of the displayed information.
To be able to update a table, you must define a primary key for the table.
Visual Basic with ADO cannot handle big integers. This means that some queries like SHOW PROCESSLIST do not work properly. The fix is to use OPTION=16384 in the ODBC connect string or to select the Change BIGINT columns to INT option in the Connector/ODBC connect screen. You
may also want to select the Return matching rows option.
If you have a BIGINT in your result, you may get the error [Microsoft][ODBC
Driver Manager] Driver does not support this parameter. Try selecting the Change
BIGINT columns to INT option in the Connector/ODBC connect screen.
When you are coding with the ADO API and Connector/ODBC, you need to pay attention to some default
properties that aren't supported by the MySQL server. For example, using the CursorLocation
Property as adUseServer returns a result of –1 for the RecordCount Property. To have the right value, you need to set this
property to adUseClient, as shown in the VB code here:
Dim myconn As New ADODB.ConnectionDim myrs As New RecordsetDim mySQL As StringDim myrows As Longmyconn.Open "DSN=MyODBCsample"mySQL = "SELECT * from user"myrs.Source = mySQLSet myrs.ActiveConnection = myconnmyrs.CursorLocation = adUseClientmyrs.Openmyrows = myrs.RecordCountmyrs.Closemyconn.Close
Another workaround is to use a SELECT COUNT(*) statement for a similar
query to get the correct row count.
To find the number of rows affected by a specific SQL statement in ADO, use the RecordsAffected
property in the ADO execute method. For more information on the usage of execute method, refer to .
For information, see .
Select the Return matching rows option in the DSN.
For more information about how to access MySQL through ASP using Connector/ODBC, refer to the following articles:
A Frequently Asked Questions list for ASP can be found at .
Some articles that may help with Visual Basic and ASP:
by Mike Hillyer (<mike@openwin.org>
).
by
Mike Hillyer (<mike@openwin.org>
).
With all Borland applications where the Borland Database Engine (BDE) is used, follow these steps to improve compatibility:
Update to BDE 3.2 or newer.
Enable the Don't optimize column widths option in
the DSN.
Enabled the Return matching rows option in the
DSN.
When you start a query, you can use the Active property or the Open method. Note that Active starts by
automatically issuing a SELECT * FROM ... query. That may not be a good
thing if your tables are large.
Also, here is some potentially useful Delphi code that sets up both an ODBC entry and a BDE entry for
Connector/ODBC. The BDE entry requires a BDE Alias Editor that is free at a Delphi Super Page near you.
(Thanks to Bryan Brunton <bryan@flesherfab.com>
for this):
fReg:= TRegistry.Create;fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True);fReg.WriteString('Database', 'Documents');fReg.WriteString('Description', ' ');fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll');fReg.WriteString('Flag', '1');fReg.WriteString('Password', '');fReg.WriteString('Port', ' ');fReg.WriteString('Server', 'xmark');fReg.WriteString('User', 'winuser');fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True);fReg.WriteString('DocumentsFab', 'MySQL');fReg.CloseKey;fReg.Free;Memo1.Lines.Add('DATABASE NAME=');Memo1.Lines.Add('USER NAME=');Memo1.Lines.Add('ODBC DSN=DocumentsFab');Memo1.Lines.Add('OPEN MODE=READ/WRITE');Memo1.Lines.Add('BATCH COUNT=200');Memo1.Lines.Add('LANGDRIVER=');Memo1.Lines.Add('MAX ROWS=-1');Memo1.Lines.Add('SCHEMA CACHE DIR=');Memo1.Lines.Add('SCHEMA CACHE SIZE=8');Memo1.Lines.Add('SCHEMA CACHE TIME=-1');Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');Memo1.Lines.Add('SQLQRYMODE=');Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE');Memo1.Lines.Add('ENABLE BCD=FALSE');Memo1.Lines.Add('ROWSET SIZE=20');Memo1.Lines.Add('BLOBS TO CACHE=64');Memo1.Lines.Add('BLOB SIZE=32');AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);
The following information is taken from the ColdFusion documentation:
Use the following information to configure ColdFusion Server for Linux to use the unixODBC
driver with Connector/ODBC for MySQL data sources. You can download Connector/ODBC at .
ColdFusion version 4.5.1 lets you use the ColdFusion Administrator to add the MySQL data source. However,
the driver is not included with ColdFusion version 4.5.1. Before the MySQL driver appears in the ODBC data
sources drop-down list, build and copy the Connector/ODBC driver to /opt/coldfusion/lib/libmyodbc.so.
The Contrib directory contains the program mydsn- which lets you build and remove the DSN registry
file for the Connector/ODBC driver on ColdFusion applications. xxx.zip
For more information and guides on using ColdFusion and Connector/ODBC, see the following external sites:
.