D3 ODBC I

Spectrum Sept/Oct '97

Nathan Rector

Natec Systems

nater@northcoast.com

http://www.northcoast.com/~nater/

One of the major trends that the computer industry is moving towards seems to be database connectivity and sharing. As the business world comes to expect more and more out of their computer systems and networks, they are also demanding that their unlike databases work together.

Only in the last few years has the Multi-value databases have been able to exchange information with other unlike databases. The most common connection tools found to allow unlike databases to connect and share information are ODBC and SQL.

In reality, ODBC is a shelled version of SQL. SQL is the actual transfer media between the applications and the database, whereas ODBC is the interface that allows applications to use the SQL functions without a programmer having to know the highly complex syntax of SQL.

ODBC gives users ability to share and manipulate data from D3, Universe, Unidata, Oracle, Sybase, Microsoft SQL Server, as well as many other databases using one application regardless of where the data is found. It allows your employee files to reside in Oracle and your sales data to reside in D3, but the application that is used to displaying and/or manipulate the information doesn't care where and how the data is actually stored.

ODBC hooks can also be found in just about all workstation operating systems. Since the most common workstation found in the work place is windows based, developers have a choice between several Window's programming languages that supports ODBC connectivity. Since Windows is the major workstation OS, I will be describing how to set up a ODBC client in the Windows environment.

There is one major decision that you need to keep in mind when you are looking at setting up an ODBC client in Windows. Are you going to be using 16-bit or 32-bit programs? If you are using 16-bit program such as those that run on Windows 3.1, you must load the 16-bit ODBC client. If you are using 32-bit programs such as programs written for Windows 95 or Windows NT, you must load the 32-bit ODBC client. Unlike other components of the Windows environment, there is no translation program between 16-bit and 32-bit ODBC calls.

Please Note: It is recommended you use the 32-bit ODBC client. Microsoft no longer supports changes to the 16-bit client so it's functionality has become static and won't support all the newer features that the 32-bit ODBC calls.

Once the drivers are installed, you need to setup the connection specifications. This gives the programs that work with ODBC the means to connect to your database. You'll need the following information to setup the connection specs:

Host - The host name or TCP/IP address for the machine the data is on.

Port Number - Use the default port number unless you have extensive knowledge of TCP/IP.

Virtual Machine - the virtual machine name. This is generally 'pick0'

User Id - the user ID to log in under. Use "DM" if you don't have a specific ID setup.

Version - the Version of D3 you are running.

User Password - any passwords that are required for the user ID

Account - Enter the account that the SQL tables for the data you want to extract are located. If you are unsure, then use 'sqldemo'

Account password - any password that is required to access this account

If passwords are required to login, but you wish them to be input by the user each time they access the database, then leave the password fields blank.

{show picture of the windows setup screen}

Once the client has been setup to access the database, you need to setup the D3 database to work with SQL and ODBC. Since ODBC requires the data you access to be in what's called a "normalized" database format (rows, columns, and tables), the multi-value structure of the D3 database must to be mapped to fit that style of database. In SQL, a Row would be a Multi-value record, a Column would be an attribute in the item, and a table is the dictionary of the file the items are in.

There are two command found in D3 to either map an existing D3 file or create a new D3 file that works with SQL and ODBC. These commands are SQL-CREATE-TABLE and CREATE TABLE command. They can be found as TCL commands. In the Nov/Dec Issue of Spectrum, I will cover SQL-CREATE-TABLE and the CREATE TABLE commands. These commands are used to map existing or new D3 file into tables that ODBC and SQL can work with.

D3 ODBC I

Spectrum Sept/Oct '97

Nathan Rector

Natec Systems

nater@northcoast.com

http://www.northcoast.com/~nater/

One of the major trends that the computer industry is moving towards seems to be database connectivity and sharing. As the business world comes to expect more and more out of their computer systems and networks, they are also demanding that their unlike databases work together.

Only in the last few years has the Multi-value databases have been able to exchange information with other unlike databases. The most common connection tools found to allow unlike databases to connect and share information are ODBC and SQL.

In reality, ODBC is a shelled version of SQL. SQL is the actual transfer media between the applications and the database, whereas ODBC is the interface that allows applications to use the SQL functions without a programmer having to know the highly complex syntax of SQL.

ODBC gives users ability to share and manipulate data from D3, Universe, Unidata, Oracle, Sybase, Microsoft SQL Server, as well as many other databases using one application regardless of where the data is found. It allows your employee files to reside in Oracle and your sales data to reside in D3, but the application that is used to displaying and/or manipulate the information doesn't care where and how the data is actually stored.

ODBC hooks can also be found in just about all workstation operating systems. Since the most common workstation found in the work place is windows based, developers have a choice between several Window's programming languages that supports ODBC connectivity. Since Windows is the major workstation OS, I will be describing how to set up a ODBC client in the Windows environment.

There is one major decision that you need to keep in mind when you are looking at setting up an ODBC client in Windows. Are you going to be using 16-bit or 32-bit programs? If you are using 16-bit program such as those that run on Windows 3.1, you must load the 16-bit ODBC client. If you are using 32-bit programs such as programs written for Windows 95 or Windows NT, you must load the 32-bit ODBC client. Unlike other components of the Windows environment, there is no translation program between 16-bit and 32-bit ODBC calls.

Please Note: It is recommended you use the 32-bit ODBC client. Microsoft no longer supports changes to the 16-bit client so it's functionality has become static and won't support all the newer features that the 32-bit ODBC calls.

Once the drivers are installed, you need to setup the connection specifications. This gives the programs that work with ODBC the means to connect to your database. You'll need the following information to setup the connection specs:

Host - The host name or TCP/IP address for the machine the data is on.

Port Number - Use the default port number unless you have extensive knowledge of TCP/IP.

Virtual Machine - the virtual machine name. This is generally 'pick0'

User Id - the user ID to log in under. Use "DM" if you don't have a specific ID setup.

Version - the Version of D3 you are running.

User Password - any passwords that are required for the user ID

Account - Enter the account that the SQL tables for the data you want to extract are located. If you are unsure, then use 'sqldemo'

Account password - any password that is required to access this account

If passwords are required to login, but you wish them to be input by the user each time they access the database, then leave the password fields blank.

{show picture of the windows setup screen}

Once the client has been setup to access the database, you need to setup the D3 database to work with SQL and ODBC. Since ODBC requires the data you access to be in what's called a "normalized" database format (rows, columns, and tables), the multi-value structure of the D3 database must to be mapped to fit that style of database. In SQL, a Row would be a Multi-value record, a Column would be an attribute in the item, and a table is the dictionary of the file the items are in.

There are two command found in D3 to either map an existing D3 file or create a new D3 file that works with SQL and ODBC. These commands are SQL-CREATE-TABLE and CREATE TABLE command. They can be found as TCL commands. In the Nov/Dec Issue of Spectrum, I will cover SQL-CREATE-TABLE and the CREATE TABLE commands. These commands are used to map existing or new D3 file into tables that ODBC and SQL can work with.