============================================================ WELCOME TO BORLAND 32-BIT SQL LINKS 3.5 FOR WINDOWS 95/NT --------------------------------------------------------- This file contains important, late-breaking information about Borland SQL Links for Windows, including revisions to the documentation. Information in this file supersedes information in the SQL Links documentation. TABLE OF CONTENTS ----------------- 1. DELPHI 2.0 NOTES 2. INSTALLSHIELD EXPRESS NOTES 3. TIPS FOR USING SQL LINKS 4. KNOWN PROBLEMS 5. INTERBASE SQL LINK DRIVER 6. ORACLE SQL LINK DRIVER 7. SYBASE SQL LINK DRIVER (DB-Lib) 8. SYBASE SQL LINK DRIVER (CT-Lib) ** NEW ** 9. MICROSOFT SQL LINK DRIVER 10. DB2 SQL LINK DRIVER ** NEW ** 11. INFORMIX SQL LINK DRIVER ** NEW ** 1. DELPHI 2.0 NOTES ------------------- DATABASE EXPLORER UPDATE This version of SQL Links includes a revised DLL file for the Database Explorer that adds support for the new SQL Links drivers. This Database Explorer DLL requires the updated Delphi 2.0. To install it, copy DBX.DLL from the \DBX directory of your installation CD to DBX.DLL in the Delphi 2 executable directory (usually \Program Files\Borland\Delphi 2.0\BIN). 2. INSTALLSHIELD EXPRESS NOTES ------------------------------ UPDATING INSTALLSHIELD EXPRESS You must update the InstallShield Express included with Delphi, Borland C++, and Paradox to redeploy the latest version of SQL Links and the Borland Database Engine. To update InstallShield Express, 1. Copy *.CNF from the \CNF directory of the installation CD to the InstallShield Express redist directory, which is typically \Program Files\InstallShield\IS Express Delphi\redist \Program Files\InstallShield\IS Express BC\redist \Program Files\InstallShield\IS Express PDX\redist 2. Copy *.* from the \ISX directory of the installation CD to the InstallShield Express directory, which is typically \Program Files\InstallShield\IS Express Delphi \Program Files\InstallShield\IS Express BC \Program Files\InstallShield\IS Express PDX 3. Add the following lines to the [SQL Link Drivers] section of ISDEPEND.INI in your InstallShield Express directory: Driver5=Informix Driver6=DB2 Driver7=Sybase (CT-Lib) Then add the following sections to ISDEPEND.INI: [SQL Link Drivers - Sybase (CT-Lib)] Src=HKEY_LOCAL_MACHINE\SOFTWARE\BORLAND\Database Engine\DLLPATH Uses1=sqlssc32.dll Uses2=sql_ssc cnf file [SQL Link Drivers - Informix] Src=HKEY_LOCAL_MACHINE\SOFTWARE\BORLAND\Database Engine\DLLPATH Uses1=sqlinf32.dll Uses2=sql_inf cnf file [SQL Link Drivers - DB2] Src=HKEY_LOCAL_MACHINE\SOFTWARE\BORLAND\Database Engine\DLLPATH Uses1=sqldb232.dll Uses2=sql_db2 cnf file [sql_ssc cnf file] Src= SrcSub=redist Uses1=SQL_SSC.CNF [sql_inf cnf file] Src= SrcSub=redist Uses1=SQL_INF.CNF [sql_db2 cnf file] Src= SrcSub=redist Uses1=SQL_DB2.CNF If you have the 16-bit BDE installed and in use on your machine, you won't see all the available SQL Links drivers when you try to set up aliases in InstallShield Express. To correct this, 1. Start the 32-bit BDE Configuration Utility. 2. Note the name of the default .CFG file being used (listed in the title bar). 3. Choose Options|Configure WIN3.1 to uncheck it. 4. Choose File|Save to update the Windows Registry. 5. Choose Options|Configure WIN3.1 again. 6. Choose File|Save As and type TEMP.CFG. When the Non-system Configuration File dialog box appears, choose No. 7. Close the BDE Configuration Utility. 8. Copy TEMP.CFG over your default .CFG file. 3. TIPS FOR USING SQL LINKS --------------------------- WHERE TO START For information about configuring and connecting to SQL servers, see the Help file SQLLNK32.HLP. USING ALIASES WITHOUT SPECIFYING A LANGUAGE DRIVER If no language driver is specified, the default language driver is ASCII. Language-specific processes like character translation, sorting and table name validation will default to English rules. For further information, see "Common SQL Links driver settings" and your driver's "Connecting to..." sections of the SQLLNK32.HLP. WORKING WITH LARGE TABLES If you create data entry forms on large tables you might find some performance degradation, particularly if the user is allowed to view unrestricted record sets. To help cut down the load on your server and optimize server performance try to write applications that access relatively few records at a time. One way to do this within a form-based application is to create forms that order the data by index and set range criteria to limit the record set. (For example, instead of creating a form that displays all orders for all customers, write your application to limit the user's working selection by criteria such as customer name, state, and area code.) WORKING WITH "SNAPSHOTS" (TABLES WITHOUT A UNIQUE INDEX OR ROW ID) It is always better to use a table with a unique index if possible. This allows for better control and performance. If you insert records into a "snapshot" table, you might need to close and reopen the table to see your additions. SYNONYM AND CROSS DATABASE / CROSS SERVER TABLE ACCESS Oracle and Informix synonyms can now be accessed using DbiOpenTable and Live Queries. Tables on different databases or servers can also be accessed in the same way. For example, you can now open "SCOTT"."CUSTOMER"@"DBLINK" on Oracle and pubs.dbo.authors on Sybase. UPDATING SNAPSHOTS--TABLES WITHOUT UNIQUE INDEX OR ROW ID When working with a table that does not have a unique index or row ID, keep the following in mind: - The SQL Links do not support record modifications and deletions on "snapshots" within an explicit client transaction. Emptying tables is supported within an explicit client transaction, but rolling back such an operation does not update the internal record cache. - To update the record cache, close the table and then open it again. 4. KNOWN PROBLEMS ----------------- DB2 ON WINDOWS 95 In Windows 95, you should catalog DB2 nodes using TCP/IP on the client, such as CATALOG TCP/IP NODE MYNODE ... If you use the Microsoft IPX/SPX protocol, the protocol will hang when metadata is retrieved. IBM reports that this is a Microsoft problem. The DB2 SQL Links driver can only access servers in the Windows 95 environment that are connected using TCP/IP. INFORMIX-REFERENTIAL INTEGRITY The Informix SQL Links driver does not currently support referential integrity. Support is planned for a future release of SQL Links. ROLLBACKS AND INFORMIX LOGGING TABLES Rolling back a transaction on an Informix Logging database invalidates the cursor. To avoid this, use ANSI databases or close the cursor after rolling back the transaction and reopen it. SYBASE CT-LIB STORED PROCEDURES When ENABLE BCD is TRUE, stored procedures with MONEY fields fail to retrieve MONEY output parameters. INFORMIX - DECIMAL AND MONEY PRECISION DECIMAL and MONEY data types are rounded to two decimal places when getting data from the server. For example, DECIMAL 123.4567 is rounded to 123.45. This is a vendor issue. To prevent this, set ENABLE BCD to TRUE in the BDE Configuration Utility. INFORMIX - APPLYING FILTERS Applying a filter on DECIMAL and MONEY fields in Informix will fail, because the data types are all mapped to the BDE logical type fldFLOAT. To avoid this, use passthrough SQL. UPDATABLE SQL QUERIES Tables owned by another user must be qualified by owner name. Use quotation marks around the entire qualified name, for example "OWNER"."TABLE". INFORMIX - USING VERSION 7.2 WITH BLOB FIELDS If you open a BLOB field on an Informix 7.2 server, trying to move to another record causes the server to crash. This is caused by an Informix problem. Reference case number 55155 when contacting Informix. INSTALLSHIELD TEMPORARY DIRECTORIES IN WINDOWS NT When installing the SQL Links under Windows NT, InstallShield does not remove temporary directories named _ISTMPx.DIR, where x is a number from 0 to 9. You must delete them manually. 5. INTERBASE DRIVER ------------------- INTERBASE SQL LINK and LOCAL INTERBASE SERVER InterBase SQL Link installs a subset of the InterBase vendor DLLs necessary for Local InterBase Server. If you have an earlier version of Local InterBase Server on your computer when you install InterBase SQL Link, be sure to upgrade with the latest version. STORED PROCEDURE AND PARAMETER BINDING Trying to bind parameters to a SQL statement after a stored procedure has been run results in a "Column Unknown" error when DbiQPrepare is called. SUPPORTED DATA TYPES The BDE now supports the InterBase 4.0 data types of NUMERIC and DECIMAL. INTERBASE INSTALLATION CHANGES When installing over existing copies of IB_LICEN.DAT and INTRBASE.MSG, the SQL Links installation program copies the files being overwritten to IB_LICEN.BOR and INTRBASE.BOR. If the SQL Links are uninstalled, these files are NOT restored automatically; you must restore them. 6. ORACLE DRIVER ---------------- SUPPORT FOR QUOTED OBJECT NAMES This release of SQL Links supports the creation of Oracle tables with quoted object names. Object names enclosed in double quotation marks may contain spaces, special characters, reserved words, or mixed case. When querying a table with special object names, be sure to enclose the name in double quotation marks. For example: SELECT * FROM "Customer" For more information on object naming rules, see your Oracle server documentation. USING LIVE QUERIES WITH QUOTED FIELD NAMES Live queries for Oracle tables require a table qualifier or an alias name if the field names are quoted. For example : SELECT * FROM someTable WHERE "fieldname" = someValue will not work, the workaround will be to use either SELECT * FROM someTable T1 WHERE T1."fieldname" = someValue or SELECT * FROM someTable WHERE "someTable"."fieldname" = someValue SETTING ORACLE DRIVER FLAGS Using mixed-case object names can result in incompatibility with earlier versions of SQL Links for Oracle. Earlier versions of SQL Links used uppercase object names only. To use uppercase object names only, set the DRIVER FLAGS option to 1 using the BDE Configuration Utility. Specifying a Repeatable Read transaction isolation level can result in incompatibility with applications using earlier versions of SQL Links for Oracle. Earlier versions of SQL Links supported Read Committed only. To maintain transaction support compatibility with earlier versions of SQL Links, use the BDE Configuration Utility to set the DRIVER FLAGS option to 512. To use uppercase object names only AND maintain transaction support compatibility with earlier versions of SQL Links, use the BDE Configuration Utility to set the DRIVER FLAGS option to 513 (representing 512+1). ORACLE UPDATABLE QUERIES All names, both column and table, are case sensitive (as if they were quoted explicitly). ORACLE BLOCK WRITE SUPPORT Support for Oracle array binding in this release shows improved performance for block writes when using DbiBatchMove and DbiWriteBlock. CONFIGURING BDE FOR USE WITH ORACLE SQL*NET V2.0 OR GREATER Use the BDE configuration utility (or alias manager) to set the "Net Protocol" parameter for Oracle aliases to TNS. 7. SYBASE DRIVER (DB-LIB) ------------------------- ADDITIONAL TRANSACTION ISOLATION LEVEL SUPPORT An error (DBIERR_NOTSUPPORTED) is returned if Repeatable Read is specified. USING DEFAULT SYBASE DRIVER FLAGS SETTING For Sybase SQL Servers prior to System 10, if your SQL Server faults while executing BLOB reads or BLOB writes, check the SQL Link driver's DRIVER FLAGS parameter. If DRIVER FLAGS is set to 0 (the Sybase default): 1. Call Sybase Customer Support and describe the problem, referencing Case #125046. 2. Set the DRIVER FLAGS parameter to 1. Use this DRIVER FLAGS setting until you receive the necessary software fix from Sybase. Setting DRIVER FLAGS to 1 will enable you to execute BLOB reads and BLOB writes without causing a server fault. This may cause the client to receive SQL Server timeouts during BLOB writes. MULTIPLE RESULT SETS The Sybase SQL Link driver does not support the use of stored procedures that return multiple answer sets. SYBASE INDEX HINT FOR UPDATABLE QUERIES This release of SQL Link has added support for Sybase's forceindex hint for query optimization when performing updatable queries. For example, the following query is now a valid updatable query: SELECT * FROM CUSTOMER (2) SYBASE URGENT PARAMETER AND TABLE CLOSE PERFORMANCE The use of the URGENT parameter is supported by the named pipes driver, the Novell NetWare networking software (SPX/IPX) as well as many TCP/IP networking packages: (Note: the dll's specified in the following examples are network and network transport specific.) myNLMsrvr=C:\SQL\BIN\WDBNOVSP,04200020,1,83BD,URGENT myUNIXsrvr=C:\SQL\BIN\WDBNOVTC, 128.0.168.245, 1425, URGENT myNamedPipesrvr=C:\SQL\BIN\DLL_NAME,\\MY_SRVR\ROOT, URGENT The Sybase Open Client documentation erroneously notes that Novell's LanWorkPlace does not support the URGENT parameter. You actually need a LanWorkPlace version later than 4.0, Lan WorkPlace for OS/2 is compatible only with version 3.0. URGENT requires Out of Band Data (OOBD) support and is necessary for Sybase's dbcancel() to work effectively. Dbcancel() is called to cancel the retrieval of un-read result sets. In order to properly support OOBD, your Solaris-based server may need a call to ndd to set its tcp_rexmit_interval_max parm. The call is: /usr/sbin/ndd -set /dev/tcp tcp_rexmit_interval_max nnnn where nnnn is a number of milliseconds. You might see a delay when clicking on the master grid of a master-detail form - the query to retrieve the data for the detail might not return for several minutes. By default, the TCP protocol will wait until a packet is full before sending it. This will generally help packet transmission over large networks because it cuts down on the number of packets sent. This can often slow the performance of client applications. If the server needs to send 750 bytes to an application and is using 512-byte packets, the client will see a delay while the server waits on 274 bytes of additional data. It might be possible to disable this on the SQL Server itself. This can be done by adding a trace flag to the RUN_SERVER file, -T1610. UPDATE/DELETE QUERIES AND DBIGETRELATIVERECORD Running the SQL statements UPDATE or DELETE on a table with a unique index, closing and reopening the table, switching to an existing index, then calling DbiGetRelativeRecord results in the server error "Attempt to initiate a new SQL operation with results pending." STORED PROCEDURES AND DBIQSETPROCPARAMS If a stored procedure with a result set and either an output parameter or a return value is executed and then rebound using a call to DbiQSetProcParams, the output parameter or return value will not work correctly. SPECIFYING THE SYBASE QUERY API By default, SQL statements sent to MS SQL and Sybase servers using DB-Lib are now sent using the synchronous query submission API. Earlier versions of SQL Links used the asynchronous query submission API. To use the asynchronous query submission API, add 2048 to the current value of DRIVER FLAGS or set it to 2048 if it's blank. 8. SYBASE DRIVER (CT-LIB) ------------------------- DBIERR_MULTIPLEUNIQRECS ON DEAD TABLES If the error DBIERR_MULTIPLEUNIQRECS occurs while using a dead table (no index at all), then the cursor for that table should be closed and reopened, regardless of whether DbiBeginTran had been called to start a transaction. This is true only for the CT-Lib version of the Sybase driver. SUPPORTING OLDER SYBASE SQL SERVER VERSIONS The Sybase CT-Lib driver does not support older versions of the Sybase SQL Server. If you intend to use Sybase SQL Server versions 4.x and below, you must use the BDE Configuration Utility (on the Sybase Driver page) to change the name of the DLL32 parameter from SQLSSC32.DLL (the default) to SQLSYB32.DLL. If you cannot find the SQLSYB32.DLL file then you will need to run the Install program and select the Sybase DBLIB driver to install. You will also need to set the environment variables PATH, SYBASE, INCLUDE and LIB so that they do not refer to the CT-Lib directory. MULTIPLE ACTIVE STORED PROCEDURE SUPPORT The Sybase CT-Lib SQL Link driver can now perform row fetches from multiple stored procedures simultaneously using the new property stmtEXECASCURSOR. The stmtEXECASCURSOR property allows users to ask for a CT-Lib cursor instead of a CT-Lib command. CT-Lib cursors let the user have multiple cursors open and fetch rows from those simultaneously. With CT-Lib commands, all pending results must be processed before executing the next operation. This property is mainly for users who execute Sybase stored procedures that return a result set. They can pass the SQL string as "EXECUTE proc_name" or "EXECUTE proc_name :1, :2, :3 ..." (if there are parameters), prepare the statement, then set stmtEXECASCURSOR to True. This makes the driver open cursors on the stored procedure instead of a command. Some limitations to this property: 1. The stored procedure should not have any BDE output parameters or return status. 2. Input parameters must be place holders in the SQL string and must be bound before execution. (Literal substitution of parameter values in the string doesn't work). 3. The stored procedure body should contain a single SELECT statement returning a single result set. EXAMPLE: int main () { // Initialize engine // Connect to database //Prepare a statement DbiQPrepare(hDb, .., .. , phStmt); // Set the property DbiSetProp(hStmt, stmtEXECASCURSOR, TRUE); // Bind parameters if any //DbiQExec(hStmt, phCur); . . . return 0; } 9. MICROSOFT SQL DRIVER ----------------------- USING DEFAULT MICROSOFT SQL DRIVER FLAGS SETTING If your Microsoft SQL Server faults while executing BLOB reads or BLOB writes, check the SQL Link driver's DRIVER FLAGS parameter. If DRIVER FLAGS is set to 0 (the Microsoft SQL default) set the DRIVER FLAGS parameter to 1. Setting DRIVER FLAGS to 1 will enable you to execute BLOB reads and BLOB writes without causing a server fault. This may cause the client to receive SQL Server timeouts during BLOB writes. SPECIFYING THE MS SQL QUERY API By default, SQL statements sent to MS SQL and Sybase servers using DB-Lib are now sent using the synchronous query submission API. Earlier versions of SQL Links used the asynchronous query submission API. To use the asynchronous query submission API, add 2048 to the current value of DRIVER FLAGS or set it to 2048 if it's blank. 10. DB2 DRIVER -------------- DBCLOB DATA TYPE The DB2 physical type DBCLOB is not currently supported within the BDE even though schema information indicates that it is an available type. It is suggested that you use either CLOB or BLOB physical types. STORED PROCEDURES The SQL Links driver for DB2 translates the logical BDE string type (fldZSTRING) to a physical DB2 SQL_CHAR data type when it is passed as a parameter to a stored procedure. This can cause problems for stored procedures that are hard-coded to expect a physical SQL_VARCHAR as a parameter. Programmers should write stored procedures that examine the sqltype member of the SQLDA structure (for example, input_sqlda->sqlvar[i].sqltype) to determine which data type the client has actually bound, instead of expecting a specific data type. CONNECTING TO AS/400 SERVERS Due to a problem with IBM server software, remote connections to DB2 on the AS/400 platform do not work properly when version 2.1.0 or earlier of the DB2 client/server library is used. Version 2.1.1 of the client/server library corrects the problem. CREATING INDEXES ON AS/400 SERVERS Due to a problem with the IBM server software version 2.1.1, if you create a new index by specifying an index name that includes lowercase characters and is enclosed in double quotes (""), the index cannot be accessed by the BDE. For example, the following statement creates an accessible index, CREATE INDEX CustNdx ON .... but the following statement creates an index that the BDE can't access, CREATE INDEX "CustNdx" ON .... If creating an index through a call to DbiAddIndex(), the szName member in the IDXDesc Parameter should only be uppercase. CREATING BLOB OR CLOB COLUMNS WITH DBICREATETABLE() When using DbiCreateTable(), BLOB or CLOB columns created in a DB2 table by the function are set by default to a size of 1MB. To create BLOB columns of different sizes, use Passthrough SQL. VARBINARY OUTPUT PARAMETERS IN STORED PROCEDURES In a stored procedure parameter description (SPParamDesc.uFldType), normally a corresponding BDE logical data type is specified. There is one exception. A varbinary output parameter should specify fldBYTES instead of fldVARBYTES. CALLING BY NAME IN STORED PROCEDURES DB2 only supports calling by number in stored procedures. Always use calling by number instead of calling by name. CREATING SCHEMA OBJECTS ON IBM MVS There is an optional entry, within the alias settings of the BDE configuration, called REMOTE DBN. This entry can be used for setting the user's preferred database on IBM MVS. For creating Schema objects using APIs like DbiCreateTable() this entry should be set to the user's preferred database name. This entry is only used for IBM MVS. For example; REMOTE DBN = BORLAND will create objects in database BORLAND and the query generated by BDE will be "CREATE TABLE TABNAME ( ... ) IN DATABASE BORLAND". Ignoring this entry will create objects in the user's default database. 11. INFORMIX DRIVER ------------------- SOFTWARE REQUIREMENTS The Informix SQL Link driver requires the latest client software from Informix. LIMITATIONS ON OPENING TABLES This edition of the Informix SQL Link driver does not support opening a table with a column name that is an Informix keyword or function name. Some examples include MONTH, DAY, YEAR, AVG, COUNT, ALL, and UNIQUE. For a complete list of keywords and function names, see your Informix documentation. COPYING INFORMIX TABLE WITH INTERVAL COLUMN When an Informix table with an interval column is copied, the destination table is created with an interval that has a qualifier of year-to-month. If this qualifier does not match the source table's interval qualifier, you will receive an error during the copy. To work around this problem, use passthrough SQL to create the destination table with the correct interval qualifier. Then append the contents of the source table to the destination table. STORED PROCEDURE SUPPORT The Informix driver supports stored procedures. Please note the following points: 1. Informix stored procedures have input parameters but no output parameters. 2. DbiOpenSPParamList returns all input parameters and sets SPParamDesc.uParamNum and SPParamDesc.szName starting from 1. SPParamDesc[0].uParamNum = 1 SPParamDesc[0].szName = "1" and SPParamDesc[0].eParamType = paramIN; . . . The rest of the information for SPParamDescs (such as uFldType, usubType, iUnits1, iUnits2, uOffset, uLen, and uNullOffset) must be set by user. RETRIEVING SQLCA INFORMATION The Informix SQL Link driver includes an improved passthrough property that contains native SQLCA information. Users can use the drvNATIVESQLCA property with DbiGetProp() to retrieve SQLCA information. SQLCA information gives detailed data on Informix server errors and exceptions. When an Informix error occurs, the Informix Global SQLCA information for that error is retrieved and retained by the SQL driver until the next time the database server is accessed. General SQLCA information is returned whenever an error hasn't occurred. The following table shows the information that is made available. *ppropValue *pilen Informix SQLCA sizeof(struct sqlca_s) EXAMPLE // Informix SQLCA structure from informix sqlca.h header file struct sqlca_s { long sqlcode; char sqlerrm[72]; /* error message parameters */ char sqlerrp[8]; long sqlerrd[6]; /* 0 - estimated number of rows returned */ /* 1 - serial value after insert or ISAM error code */ /* 2 - number of rows processed */ /* 3 - estimated cost */ /* 4 - offset of the error into the SQL statement */ /* 5 - rowid after insert */ struct sqlcaw_s { char sqlwarn0; /* = W if any of sqlwarn[1-7] = W */ char sqlwarn1; /* = W if any truncation occurred or database has transactions */ char sqlwarn2; /* = W if a null value returned or ANSI database */ char sqlwarn3; /* = W if no. in select list != no. in into list or turbo backend */ char sqlwarn4; /* = W if no where clause on prepared update, delete or incompatible float format */ char sqlwarn5; /* = W if non-ANSI statement */ char sqlwarn6; /* reserved */ char sqlwarn7; /* reserved */ } sqlwarn; }; struct sqlca_s mySqlca; int main() { // Initialize engine // Connect to database. //get the sqlca (on no exception) unsigned int len; DbiGetProp(hDb,drvNATIVESQLCA, &mySqlca, sizeof(sqlca_s), &len); //get the sqlca (on an exception) DbiOpenTable(hDb, "non existing table", ...) DbiGetProp(hDb,drvNATIVESQLCA, &mySqlca, sizeof(sqlca_s), &len); return 0; } SYSTEM TABLE STORAGE OF OBJECT NAMES Before storing object names in the system tables (such as table names, index names, field names, and so on) the Informix driver converts them to lowercase. RETRIEVING INFORMIX DATABASE INFORMATION The Informix SQL Link driver has three properties for determining the type of database the BDE is connected to Property Type Description --------------------------------------------------------- dbONLINE BOOL TRUE if the database connected is ONLINE type otherwise FALSE dbTRANALLOWED BOOL TRUE if the database connected permits transactions otherwise FALSE dbANSI BOOL TRUE if the database connected is ANSI type otherwise FALSE You can access these properties using the BDE function DbiGetProp. ============================================================