A full set of online books is included on the SQLBase 8.1 media. However, to view the most recent versions of online books, please visit our web site at: http://www.guptaworldwide.com/tech/books_online.asp

Migrating Databases to SQLBase 8.1

No migration is required from version 8.0 to 8.1. However, SQLBase 8.1 contains significant structural incompatibilities with versions 7.x and earlier of SQLBase databases. Therefore a “conversion process” must occur in these cases. This process can be accomplished in one of two ways:

1. By performing an “UNLOAD” of your current database in its existing version, install SQLBase 8.1, create a new database, and perform a “LOAD” into the new SQLBase 8 database (See SQL Language Reference & DBA Guide for more information about Load/Unload.)

2. SQLBase 8.1 does contain the facility to automatically convert your databases for you. This conversion occurs during the first user connection to the database being serviced by the SQLBase 8.1 Server.

a) Special Considerations for Auto Conversion

The auto-conversion has the following restrictions and will irreparably fail and harm your database if these restrictions are violated:.

The database must be a version 6.0.01 or greater.
The database must be in a consistent state. This means that it cannot be in a corrupted state or have any log file dependencies.
The database cannot have any user-created dependencies upon SQLBase’s system tables or views (see DBA Guide for list of system tables and views.) This includes user-created views, indexes, and public synonyms.
Encrypted databases MUST be the same encryption level as the SQLBase 8.1 Server.
A backup (BACKUP DATABASE or BACKUP SNAPSHOT) cannot be “RESTORED” with the SQLBase 8.1 Server.
Partitioned databases cannot be automatically converted.

b) Gupta recommended steps for “auto-converting” databases to SQLBase 8.1

1. Ensure the existing database is in a consistent state – with your current version of SQLBase, disconnect all users from the database, connect to the database as the user SYSADM and perform a CHECK DATABASE. (optionally, you can perform an “UNLOAD DATABASE” and skip step 2.)

2. Disconnect and perform a “SET SERVER” and a “DEINSTALL DATABASE” of your existing (older version) database. Disconnect your Server connection (“SET SERVER OFF”) to close the SQLBase Server gracefully.

3. Ensure a backup of the database – with the SQLBase Server exited, copy the database to a backup location. (If in Step 1 you performed an “UNLOAD” then copy the “UNLOAD” file to the backup location.

4. Backup your existing sql.ini file and uninstall your current version of SQLBase Server. ( After the uninstall, ensure that you do not have copies of sqlwntm.dll and sqlngci.dll in your %windir%\system32 or anywhere in your Windows PATH.)

5. Install SQLBase 8.1 Server into a target directory different then that of your previous version. After installation use either Windows Services Manager or SQLBase Management Console, to stop the SQLBase 8.1 Server (and SQLBase Resource Manager.)

6. Copy your sql.ini from Step 3 into your SQLBase 8.1 installation target directory. Using Connectivity Administrator (or notepad) change your dbdir (database directory) to the desired location. Move your database (folder & file) to the dbdir location.

7. Start SQLBase 8.1 Server (by using either Windows Service Manager, SQLBase Management Console, or Windows “Start” menu.

8. Using SQLTalk, perform a “SET SERVER” and an “INSTALL DATABASE” of your database. Disconnect your Server connection (“SET SERVER OFF”.) Connect to your database as the user SYSADM (the database will be converted and may take several minutes.) Perform an “UPDATE STATISTICS” and “COMMIT” your changes.

9. Your database is now converted.

What’s new in SQLBase 8.1

(See also the following section, “What’s new in SQLBase 8.0”.)

.NET data provider

The new .NET data provider has simplified data access and good performance, allowing easy use of SQLBase databases with both new and existing .NET applications. It includes a set of classes that implement nearly all of the applicable .NET framework interfaces.

Third-party tools integration

This version of SQLBase has greatly improved integration with a wide variety of third-party development tools and end-user tools. SQLBase databases will work seamlessly with the various explorers and wizards in these tools. SQLBase has been tested for integration with the following platforms:

· Microsoft Visual Studio 6.0

· Microsoft Visual Studio .NET

· Crystal Reports Writer 8.0.1.0/9.0

· Microsoft Access 9.0/2002

· Microsoft Excel 2000/2002

· DTS 8.0(SQL Server 2000)

· Delphi 6.0/7.0

· Enterprise Manager 8.0(SQL Server 2000)

SQLBase Management Console (SMC) enhancements

New features in SQLBase Management Console include:

· View SQLBase Server activity.

· View database information.

· View process information.

· View cursor information.

· View Database, Process, Cursor and Lock information per database.

SQL Server Enterprise Manager compatibility

SQLBase databases can now be used in SQL Server Enterprise Manager to create linked databases and distributed queries. (Compatibility tested against SQL Server 2000 version only.)

JDBC Driver upgrade

The SQLBase JDBC Driver is now compatible with, and built with, JDK version 1.3.1.

ODBC driver enhancements

Improved integration with third party tools.
Added support for SQLSetPos() with operations of SQL_ADD, SQL_UPDATE and SQL_DELETE in addition to SQL_POSITION.
Better backwards compatibility for ODBC 2.x applications.
Support for blob handling in large chunks. SQLGetData() and SQLPutData() can be called to operate on chunks larger than 64 KB. In theory 2 GB is the limit for a single get or put.
Support for SYNONYMS through SQLTables() and SQLColumns().
The driver now implements the SQLProcedureColumns API function. Your applications can now retrieve more complete information about SQLBase stored procedures and their parameters.

OLE DB data provider enhancements

The provider now implements the IRowsetChange interface. IRowsetChange has methods for updating the columns of existing rows, deleting existing rows, and adding new rows. The provider cannot update Rowset objects derived from multiple tables. It does not support updating, deleting or inserting rows for a Schema Rowset. The updates are immediate.
The provider now implements the schema GUID for Procedure Parameter information. Your applications can now retrieve more complete information about SQLBase stored procedures and their parameters.

What’s new in SQLBase 8.0

Maximum identifier name size is doubled

The maximum number of characters allowed for database object identifiers is doubled in SQLBase 8. In earlier releases, it was 18. Now identifiers are 36 characters long. The objects affected by this change are:

· Table name

· Column name

· View name

· Index name

· Bind variable name (SQLMBNL)

· Cursor name

· Command name

· Procedure name

· Synonym name

· Trigger name

· Correlation name

· Event name

· Referential Constraint Name (SQLMCNM)

Updated OLEDB Provider

In addition to modifying the provider to make it support COM+ transactions, several defects have been fixed.

Gupta ODBC Driver

Gupta now provides their own ODBC driver, rather than licensing the Merant ODBC driver. Some obsolete connection attributes that were used by the Merant driver are preserved by the Gupta driver for purposes of backward compatibility. For details on attributes, see chapter 10 of the Database Administrator’s Guide, or press F1 for online help during setup of an ODBC datasource.

The Gupta ODBC driver, similarly to its predecessor, cannot be used with Team Developer to connect to SQLBase, because it makes multiple recursive calls into the SQLAPI.

Updated SQL/API

In addition to changes to support COM+ transactions, another new API function has been added: sqlmls (Remote Long Seek into file on server), for Windows platforms only.

Maximum database name size is doubled

It has increased from 8 to 16. It must still be a valid operating system dataset name.

Load/Unload with encryption

When doing an UNLOAD DATABASE command, you may now use the clause “ENCRYPTED key” in the same position that you would otherwise use the COMPRESS clause. Use this option to encrypt the resulting data file. You may specify a key for the encryption, or you may omit the key. Up to 16 characters may be used in the key value. If there are embedded blanks in the key value you specify, it must be delimited by double quotes. The same key that you specified, if any, must also be used later if you reference the data file in a LOAD command.

More information about this clause is available in Gupta Books Online, in the SQL Language Reference Manual under the LOAD and UNLOAD commands.

Support for temporary files larger than 2GB

For database engines running on Windows operating systems, temporary files are no longer limited to 2 gigabytes in size.

New functions

@SOUNDEX(string1)

Returns a four-character string (in the format of one letter plus three digits) that represents the sound of the characters in string1. The resulting four-character string can be useful for purposes of indexing strings based on how they sound. An entry in SQL.INI controls the exact SOUNDEX algorithm used – choices are SQLBase (the default), SQL Server, or Oracle.

@DIFFERENCE(string1,string2)

Compares two strings. Returns an integer that indicates how similar the two sounds are, based on their @SOUNDEX values. Return values range from 0 to 4. Zero means no similarity or an error; higher integers mean better matches; a 4 means a perfect match.

More detailed information about these two functions is available in Gupta Books Online, in the SQL Language Reference Manual.

Support 64-bit integer binding from C programs

SQLPSLL and SQLPULL program data types have been added to SQL.H. This introduces support for _int64 C type. The ODBC driver will also benefit from this change. Also, SQL.H file was modified to introduce b8 (byte8) and ub8 (ubyte8) data types.

COM+ Support

Support for COM+, new in SQLBase 8, includes the following changes:

A new component, SQLBase Resource Manager (SQLBRM.EXE).
A new communication protocol, SQLMPIPE.DLL.
Modifications to the OLE DB Provider (support IJoinTransaction interface, change registry settings to work with auto-enlistment, make the provider free-threaded to work with ATL-based COM servers, and support aggregation).
New samples (read COM+ Samples Guide in the Samples subfolder)
Updated documentation.

Current limitation: COM+ transactions will not work if you are using the Transmission Security features of SQLBase (the secureapi keyword value in SQL.INI is greater than zero.)

Using SQLBase Resource Manager (SQLBRM)

SQLBRM is the component that enables COM+ transactions against SQLBase. It is notified by the Microsoft Distributed Transaction Coordinator (DTC) when a COM+ component initiates a transaction against the SQLBase server. When an application running under COM+ issues a command to commit or abort a transaction, DTC notifies SQLBRM, which causes the command to be executed in SQLBase. If the SQLBase server fails during a transaction, SQLBRM notifies DTC, and later plays back and commits the transaction when the SQLBase server restarts.

SQLBRM.EXE is installed by default as a Windows service. You can run the executable directly instead, but ordinarily you would use it as a service. It takes three parameters:

SQLBRM /ListenPort=2156 /LogDir=c:\windows\temp /Autostart=yes

The values shown are the default values used when SQLBRM is installed as a service (presuming that “windows” is the directory where your Windows operating system is installed.)

The Autostart parameter controls whether SQLBRM will start SQLBase if it's not already running. If SQLBRM is a service, it will start SQLBase only as a service, presuming that SQLBase has been configured to allow this.

To use SQLBRM with COM+, you must specify the SQLMPIPE protocol in the section of SQL.INI that relates to the database engine:

[dbntsrv]

dbname=ISLAND,sqlmpipe

servername=server1,sqlmpipe

…

[dbntsrv.dll]

comdll=sqlmpipe

…

The SQL.INI settings related to clients require that you use SQLWS32 as the communication protocol when running against database engines using SQLMPIPE:

[win32client.dll]

comdll=sqlws32

[win32client.ws32]

serverpath=server1,yourhostname,2156/*

…

Note the use of 2156 as the default listen port for SQLBRM, as contrasted with the default listen port of 2155 for SQLBase itself.

Shutting down SQLBase Resource Manager

When running as a service, Resource Manager can be shut down from the Windows Services administrator, or from SQLBase Management Console (described below). When Resource Manager receives a request to shut down, it waits until any existing users have disconnected from SQLBase.

SQLBase Management Console (SMC)

This component, SQLBMMC.DLL, is a “snap-in” designed to integrate with the Microsoft Management Console (MMC). It is a replacement for SQLBase Server Monitor in older versions of SQLBase.

SQLBase Management Console can be started from the Gupta program group.

Your SQLBase server installation appears as a tree structure of nodes in the left pane of the MMC window, and various status displays appear in the right pane, depending on the node that is currently selected. The online help file contains documentation on all SMC capabilities. Some of them are summarized here. By using right-click context menus, you can invoke the following general functionality:

· Display all database servers, their related databases, and listening protocols.

· Display the database path, current number of cursors, current number of users, database size, and database volume for a specific database.

· Start or stop a particular database server.

· Change the Startup Type, Event Logging, and Server Console properties of a database server.

· Change the currently active database server.

· Start and stop SQLBase Resource Manager, and display its statistics. (Note that the “Connects” and “Disconnects” statistics represent the total number of cursors connected and disconnected to the server, not the number of actual connect and disconnect requests, which will be smaller.)

Connectivity Administrator

In earlier versions of SQLBase, the Connectivity Administrator filename was CFGWI10.EXE. It has changed to GPTCONFIG.EXE. A new node is visible in the Protocols section of the server tab, “MTS Connection”. This represents the new SQLMPIPE protocol to support COM+ transactions and SQLBase Resource Manager. Note that there is no corresponding protocol node on the Connectivity (client) tab. Clients interact with the Resource Manager using the TCP/IP protocol, as shown in the SQL.INI examples above, in the Resource Manager section of this document.

Compatibility and system requirements

The SQLBase 8 server and client have important considerations regarding backward compatibility. An older SQLBase client (SQLBase 7.x) can connect to SQLBase 8. Certainly, no new features of SQLBase 8 would be available to applications still using the SQLBase 7.x client.

Version 8.x of SQLBase requires (and installs) MDAC 2.7 to support the OLE DB provider. MDAC, in turn, requires Internet Explorer 4.01 or higher to be already installed prior to the SQLBase installation.

This version of SQLBase also requires Microsoft Management Console (MMC) version 1.1 or higher be installed for the SQLBase Management Console (SMC) snap-in to work. MMC is not a part of the Windows 98 and Windows ME operating systems; however, users of those operating systems can download MMC from the Microsoft web site.:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com/support/mmc/mmcdown.asp

To properly use the SQLBase 8.1 .NET data provider, you must have installed Service Pack 1 for the .NET Framework SDK. For Windows XP machines, you should have installed Service Pack 1 for Windows XP. You can download the most current .NET framework redistributable components at:

http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/829/msdncompositedoc.xml

The installer checks for free space to use for temporary files during installation. It requires 25MB on the SYSTEM drive (where the operating system is installed) and 100MB on the TEMP drive (where the %TEMP% environment variable is pointing).

To deploy applications that use the SQLBase 8.1 .NET data provider, you must copy the provider DLL (Gupta.SQLBase.Data.DLL) into either the same folder as the application, or into the Global Assembly Cache that is part of the .NET Framework. For information on how to install that DLL into the Global Assembly Cache, visit the Microsoft web site that explains the cache and methods of installation:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinstallingassemblyintoglobalassemblycache.asp

Issues

Versions of SQLBase prior to 8.x placed SQLWNTM.DLL and SQLNGCI.DLL in the WINDOWS\SYSTEM32 folder. SQLBase 8.x does not place those DLLs there. Normally those DLLs would be removed by the uninstall of an earlier version, but if they still exist in that folder for some reason, they will interfere with the version 8.x DLLs. If your SQLBase 8.x installation appears to be acting like an earlier version, check for those DLLs in that folder, and remove them.

If you uninstall SQLBase 8.1, two files are deliberately retained in your program directory: SQL.INI and SQLINI.SAV. These files contain valuable client and/or server configuration information, and they can save you time and effort in the case of a reinstallation or an installation on a different machine. If you truly wish to uninstall the product permanently, you will need to manually delete these two files.

The Gupta SQLBase ODBC Driver and OLEDB Provider require sqlwntm.dll and sqlngci.dll to register properly with ODBC and OLEDB. If at the time of installation these files are not available the registration will fail. To manually register these components:

· ODBC Driver

regsvr32.exe /n /i:"c:\program files\gupta AddDefaultDSN" sqlbaseodbcsetup.dll

· OLEDB Provider

regsvr32.exe sqlbaseoledb.dll

A shortcut for launching the “SQLBase Management Console” (a snap-in for Microsoft Management Console) is being created in the Gupta program group during installation. In the case where the SMC snap-in, SQLBMMC.DLL, has not been registered properly, an error message will be thrown by MMC stating that the “snap-in failed to initialize”.

Solution: Register the SMC snap-in SQLBMMC.DLL using the REGSVR32 command.

If COM+ support for SQLBase is selected at install time for a Windows NT system on which the NT Option Pack has not been installed, these messages will be posted during the install: "the dynamic link library SHWAPI could not be found. An error occuured while loading the odbcconf library." and "MSDTC doesn't exist on this machine. The SQLBase Resource Manager has been copied but has not been registered ..." . In addition, the SQL.INI file that supports connection through COM+ will be implemented but attempts to connect to SQLBase will fail because in fact the COM+ connection protocol (SQLMPIPE) has not been satisfactorily installed.

Solution: Copying SQLSTD.INI to SQL.INI should implement connection using the non-COM+ (SQLAPIPE) protocol.

When using the OLE DB data provider and Microsoft ADO objects such as DataGrid Control 6.0 and Data Control 6.0, using the default server-side cursors will result in errors.

Solution: Explicitly specify client-side cursors with code such as “gRS.CursorLocation =adUseClient” when using ADO objects.

When using the OLE DB data provider and Microsoft SQL Server Query Analyzer, you get a data conversion error if the SQLBase table being queried contains a DATETIME column with a value outside of the range of years 1753 to 9999. Query Analyzer’s internal date/time datatype can only handle that range of years.

Solution: Revise the query to omit that column from the result set.

If you initially install SQLBase Server as a program (not a Windows service), the option to start SQLBase as a service is not immediately available to you after installation.

Solution: Use SQLBase Management Console (SMC). Select the current active server (usually SERVER1), right-click, and choose Start as Service from the context menu. Thereafter, SQLBase will be registered as a Windows service, and SMC will allow you to start and stop it as either a program or a service.

When using the OLE DB data provider, the result sets generated from SQLBase do not support the Bookmark property in ADO Recordset objects.

With Cursor Context Preservation on, a DROP TABLE command will now return an error if there is a reference to that table in a different cursor in the same transaction. This is a change in behavior compared to earlier versions of SQLBase; in earlier versions the table would be dropped without an error.

The time portion of the DATETIME datatype has resolution to the second and microsecond. The time portion of TIMESTAMP has resolution several decimal places past microsecond; the exact precision depends on the operating system. Because of this difference in resolution, it is possible to have very slightly different values in two columns that were initially assigned the same value. For this reason, you should use caution when running equality tests or using date arithmetic between two columns when one is DATETIME and one is TIMESTAMP.

In some cases, a connect request to the new ODBC driver may return a status of SQL_SUCCESS_WITH_INFO rather than SQL_SUCCESS. This behavior differs from the older Merant driver, and your applications must check for it. An example of this difference occurs when InMessageSize is set to 1 – Merant will return SQL_SUCCESS, and Gupta will return SQL_SUCCESS_WITH_INFO.

The Gupta ODBC driver always returns zero as the result of function SQLNumParams, regardless of the actual number of INTO parameters used in the statement. This is a change in behavior from the Merant driver.

Function SQLGDI has been changed to return simply a column name instead of qualified name. In prior versions of the SQL API, the name returned by SQLGDI was sometimes simply the column name, but was sometimes in the form table.column or user.table.column.

When creating a new linked server inside Enterprise Manager using SQLBase OLEDB provider, you need to always select the "Allow InProcess" option after choosing the provider.

When Crystal Reports 9.0 uses the SQLBase ODBC driver and Smart Linking to generate an inner join between two tables, the query will fail because the generated query is in SQL-92 syntax but SQLBase itself is not fully compatible with SQL-92.

When clients connect to the SQLBase OLE DB data provider, the connection string must not contain unsupported properties, or the connection request will fail. There are several properties in the OLE DB spec that are not supported by SQLBase, including:

DBPROP_CATALOGLOCATION (DBPROPSET_DATASOURCEINFO)

DBPROP_CATALOGTERM (DBPROPSET_DATASOURCEINFO)