HINTS FOR USING STARSQL WITH ODBC ENABLED APPLICATIONS StarWare StarSQL 1.04 9/19/95 Copyright 1995, StarWare Connectivity Software, Inc MICROSOFT QUERY NOTES * MS Query may fail to create tables when connected to DB2 due to an authorization failure. See the "CreateTable" keyword under "Advanced Options" for a solution. * Query & updates: You can use MS Query to update fields in existing tables by selecting "Allow Editing" in the Records menu. You can also use it to create new tables with "Table Definition" in the File menu. However, using Query and StarSQL to populate a table with the "Save table as" menu item fails with a "Prepared Statement not found" error message. MICROSOFT ACCESS 2.0 NOTES * Field names in Microsoft Access 2.0 tables intended for export to AS/400 must be <=10 characters in length, and characters must all be upper-case. Otherwise, you will receive an ODBC error. * Microsoft Access 2.0 table names intended for export to OS/400 must be all upper-case. If they are not upper-case, the table name will be enclosed in quotes after export to OS/400. * Datatypes are converted when Microsoft Access 2.0 tables are exported to OS/400 tables. The data type conversions are as follows: Access 2.0 (source) OS/400 (destination) Counter Data Type Integer Date/Time TimeStamp (formatted as YYYY-MM-DD-hh.mm.ss) Currency Float Text VarChar * Add the following line to your C:\WINDOWS\MSACC20.INI file to significantly boost Microsoft Access performance: DisableAsync=1 * You can boost performance by creating a table called MSysConf in Microsoft Access and setting values for the three records in the MSysConf table Config field. The table has four fields: 1) Config - integer (2 bytes) 2) chValue - Text 255 bytes 3) nValue - long integer (4 bytes) 4) Comments - Text 255 bytes Config and nValue are the only two fields that need to be populated. The values you need to set in the Config field are: 101: Allow/Disallow local storage of password 102: Delay in seconds between retrieval 103: Number of rows to be retrieved during any retrieval * If you use Microsoft Access for query only (read only), you may want to add the keyword "SNAPSHOTONLY=1" to the [ODBC] section of MSACC20.INI for better performance. * For Access 2.0, Microsoft recommends the installation of the Access 2.0 Service Pack ACCSVC.EXE, released November 1994, which is available from CompuServe and ftp.microsoft.com. * MS Access may fail to create tables when connected to DB2 due to an authorization failure. See the "CreateTable" keyword under "Advanced Options" for a solution. * If Access times out while trying to connect to a host over a slow WAN, increase the LoginTimeout value (default is 20 seconds) in the [ODBC] section of the MSACC20.INI file (MS Access settings). * If Access times out while doing a query on a busy host or AS/400 system, increase the QueryTimeout value (default is 60 seconds) in the [ODBC] section of MSACC20.INI. * Tables can be uploaded to AS/400 databases using the 'Save Table As' feature if IsolationLevel=0 has been set in the ODBC.INI file. INTERSOLV Q & E NOTES * When selecting tables while building a query, the selected table will look like "rdb-name.table-name." Use the Expression field to modify and replace it with "rdb-name.collection-name.table-name." GUPTA v5.0.2 AND PRE-v5.0.2 Gupta v5.0.2: * If you will be using Gupta SQLWindows or QuestReporter v5.0.2, you are no longer required to add the Gupta1=1 flag to the ODBC.INI file. If you have used Gupta pre-v.5.0.2 and have added this flag, remove it before you use StarSQL with Gupta or QuestReporter v5.0.2 and later. You must specify during Gupta installation that ODBC will be installed in order to enable ODBC support. During installation Gupta creates a file in its install directory called \dcc.ini. On the StarSQL installation media you will find a subdirectory GUPTA which contains a file called dcc.sw; this file contains StarSQL- specific information for use by Gupta. Use a text editor such as Notepad and its cut and paste functions via the clipboard to append the contents of the StarWare-supplied \gupta\dcc.sw file to the existing \dcc.ini file in order to enable Gupta v5.0.2 to work with StarSQL. Gupta pre-v.5.0.2: * You must edit ODBC.INI and add the following flag to the datasource: Gupta1=1 NOTES FOR OTHER DEVELOPMENT APPLICATIONS (e.g. PowerBuilder, Visual Basic) * StarSQL supports all native scalar functions and a subset of ODBC scalar functions; SQLGetInfo returns a list of supported scalar functions for each supported server. * SQL to C and C to SQL Data Type Translations: StarSQL supports all default translations as well as most ODBC-supported conversions. * There are some known problems with using the SQL_BINARY type when sending data to DB2. * Loss of precision when comparing 370 Floating Point values may cause precise match criteria to not be resolved properly. * Query Timeout: In this release, query timeout is not enabled. However, Login timeout is enabled. * SQLCancel (Unpredictability): If SQLCancel is called while StarSQL is waiting for a response from the host on the network, the result is unpredictable. It is possible to encounter a race condition in which StarSQL attempts to cancel the host request just as the host completes the request and sends the reply. In this case, the host has already completed the request, but StarSQL does not see the reply (which is purged from the network connection during the cancel attempt). Applications that could be adversely affected by this can work around this unpredictability by not using auto-commit mode, and by issuing a ROLLBACK after each cancel. * Other application-specific notes (using StarSQL with Lotus 1-2-3, MapInfo, PowerBuilder, Access, FoxPro) are available from StarWare support. * A problem with StarSQL and Lotus Approach is currently under investigation. MICROSOFT JET ENGINE HINTS Microsoft Visual Basic 3.0 Professional uses the Microsoft JET (MSAJT110.DLL or MSAJT200.DLL) to perform many of the built-in data access functions. Limitations of the JET suggest that certain procedures be followed. Jet Version Microsoft Jet version 2.00 provides significant performance enhancements over earlier versions. Ensure that you have the latest version of the Microsoft Jet by downloading COMLYR.EXE from CompuServe or from the Microsoft bulletin board. Database Qualifier By default, SELECT statements used to create Visual Basic snapshots and dynasets will not use the default database qualifier in your ODBC setup. Instead, unqualified table names will be qualified with the current User ID. Snapshots and dynasets that do not use SQL Passthrough (option 64) cannot use qualified table names. MS Jet interprets a period in a table name to indicate that the string proceeding the period is the name of a local Microsoft Access format database. To use qualified table names in SELECT statements you must use the SQL Passthrough option (option 64) as shown in the following example: Set SNAP = DB.CreateSnapshot ("SELECT * FROM MYDATA.ORDERS", 64) Record sets created with SQL Passthrough are not updatable. While VB will allow you to use the CreateDynaset command with SQL Passthrough the dynaset you create will not be updatable. INSERTING, DELETING AND UPDATING DATABASE RECORDS If you choose to use SQL Passthrough when retrieving records, you will not be able to use the dynaset UPDATE method to modify the database. The most efficient way to modify the database is to create INSERT, DELETE and UPDATE SQL strings and execute them directly with the ExecuteSQL command as illustrated in the following example: x = db.ExecuteSQL ("INSERT INTO MYDATA.ORDERS (ORDNUM, DESCRIP, QTY) VALUES (1234, 'THIS IS AN ORDER', 100)") ExecuteSQL does not recognize the BeginTrans, CommitTrans and RollBack commands in VB. All transactions executed via Execute SQL will be auto-committed unless you turn autocommit off with an ODBC API function call. LOCAL MDB DATABASE WITH ATTACHMENTS Another way to access your remote database with StarSQL is to create a Microsoft Access format database to which the tables of your database are attached. The MDB file you create will carry table qualifiers, index information and a local copy of the data dictionary. Preliminary tests show there to be a significant performance gain with this method compared to accessing your remote database directly. You can create this MDB file using Microsoft Access or with your Visual Basic application. Your Visual Basic application can access and manipulate data via this local MDB file in the same way you would manipulate any other Microsoft Access format database. Your Visual Basic application will be unaware of the remote attachments. Any changes to the remote database or the ODBC driver on the client PC will require that the MDB attachment file be recreated.