RealSQL-ODBC Driver

The RealSQL-ODBC Driver allows ODBC-compliant applications, such as Microsoft Word and Excel, to read and write data to a Reality database using SQL.

Prerequisites

• Refer to the Reality Release Information for details of which versions of Windows are supported.

• PCSNI software V2.3.1 Rev C or later.

• Any ODBC Level 1 or 2 compliant application.

• A Winsock compliant TCP/IP transport stack for TCP/IP connections.

• If you are connecting to a Reality database on a UNIX host, the host will require UNIX-Connect.

Installation

Windows

1. If you have not already done so, start the installation program as described for Windows in the section Starting the installation.

2. Select RealSQL-ODBC Driver from the SQL Software menu.

3. The default location is c:\Program Files\NorthgateIS\RealSQL-ODBC; you can override this if required.

UNIX

1. If you have not already done so, start the installation program as described for UNIX in the section Starting the installation.

2. Select option g) Install ODBC Driver, and follow the prompts given.

Set-up after installation

This section describes how to set up a connection to an SQL catalog on a Reality database for testing purposes. For more detailed information on setting up and maintaining a connection to an SQL catalog on a Reality database using ODBC or JDBC, refer to SQL for Reality.

On the Host System

SQL User-ids

• For each Reality user-id that will use SQL, use SSM option 2 to set the type (field 14) to 'S' or '*' so that it can be used as a server.

• Users that will be permitted to modify SQL catalogs must have this enabled in their security profile (set SSM option 3, field 21 to Y).

Setting up the Reality account

To make an account accessible to client ODBC or JDBC connections, log on to the account and enter SQLM at TCL.

Enter Y at the following prompt:

This Account is not configured or up to date for SQL.

Do you want to set it up or update it (Y/N):

Press RETURN at the SQLM prompt to return to TCL.

To confirm that the account has been set up correctly, enter SQL.DEMO at TCL. Continue at the first prompt, and all 15 rows of the EMP table should be displayed. Enter X at the end of any test, followed by Y to retain the sample tables used in the demonstration.

SQL Catalogs

If you are going to be creating your own SQL catalogs, rather than using an NEC-supplied catalog, you create the catalogs using the SQLM TCL command. For detailed information on how to set up and maintain an SQL catalog on a Reality database, refer to SQL for Reality.

On the Client

Configuring the Standard Network Interface

To connect to a Reality database using ODBC, you will need a WinSNI entry for direct connection to a Reality database (for details, see the description of how to set up PCSNI.

Setting up the SQL/ODBC Data Source

An ODBC data source stores information about how to connect to the indicated data provider.

To add a data source to an ODBC compliant application:

1. Select the 32-bit ODBC administration icon from the Control Panel. Note that on Windows 2000, Server 2003 and XP, the ODBC icon is in the Control Panel's Administrative Tools group (when in Category view on Windows, Server 2003 or XP, the Administrative Tools group is under Performance and Maintenance).

   Note

   • The ODBC administration icons have different names on different versions of Windows. Check that you have selected the correct icon.

   • On 64-bit versions of Windows, you can use both 32- and 64-bit data sources. To configure Reality ODBC data sources (32-bit) run the utility %windir%\syswow64\odbcad32.exe.

   The dialog that follows assumes that you are using ODBC 3 administration tools (the dialog title is ODBC Data Source Administrator). It provides instructions for setting up a System DSN (Data Source Name), but does not include details of User or File DSNs. For information about using ODBC 2 tools and setting up other types of DSN, refer to SQL for Reality.

2. Select the System DSN tab: (a System DSN is visible to all users of the host)

   

3. Click the Add button. This displays the following screen:
   
   

4. From the list select RealSQL Driver 32bit and click Finish.

   The following RealSQL Setup dialog box is then displayed.
   
   

5. Enter the following information:

   • Data Source Name - The name that is referred to by the application.

   • Description - A free text description of the data source.

   • System Name - The name of the appropriate entry in the PC's PCSNI configuration file.

   • Account - The Reality account or synonym account name that the connection will be made to and which holds the SQLSRVR MD entry and the SQL catalog.

   • Password - The Reality account password.

   • User ID - On UNIX, this must be the UNIX user-id that matches the Reality user-id. On Windows, it must be the Reality user-id.

   • Password - On a UNIX host, this must be the UNIX user-id password. On a Windows host, it must be the Reality user-id password.

When you have finished entering the data source setup parameters, click OK. The data source is now ready for use.

Testing a Connection to the Reality ODBC Data Source

A test program, ODBCTest, together with its original source file, is supplied as part of the driver installation. This program, stored in Program Files/NorthgateIS/RealSQL-ODBC/SampleApp, is a simple program designed to establish a connection to a Reality database, execute a supplied SQL statement and display the results.

To connect to a System DSN called DEMO:

odbctest -d"DEMO" -s"SELECT * FROM EMP"