Oracle 5.0, Справочное руководство

Страница: 1827 / 3232

Connector/ODBC Notes and Tips
1807
• Tips for Optimizing Queries on Attached SQL Tables
• 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.
20.1.7.2.1.2. Microsoft Excel and Column Types
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()
[887] function in your queries. The use of
CONCAT()
[887]
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.
20.1.7.2.1.3. Microsoft Visual Basic
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.
20.1.7.2.1.4. Microsoft Visual InterDev
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.
20.1.7.2.1.5. Visual Objects
Select the
Don't optimize column widths
option.
20.1.7.2.1.6. Microsoft ADO
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.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long
myconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCount
myrs.Close
myconn.Close