Stan Seiler ([log in to unmask]) suggested that I upload the file in txt format. So, I did. If you are interested in IMAGE/SQL and Client/Server access, you will want to peruse it. It is Appendix B of the TCU 20 January 1994 Workbook. If you have questions or comments, do not hesitate to send them.. If you have criticism and it is constructive, great, send it on! If you have criticism and it is not constructive, great, send it to yourself! Kind regards, Denys. . . Client/Server with IMAGE/SQL Denys P. Beauchemin (713) 626-3842 Introduction In 1992 Hewlett-Packard introduced an SQL interface for IMAGE, under the name SQL for IMAGE. A few months later, HP renamed it to IMAGE/SQL and today, it is probably the hottest topic of the HP3000. This paper will adress all aspects of client/server with IMAGE/SQL. We will cover the latest developments in IMAGE, attach a TurboIMAGE database to an ALLBASE/SQL database environment (DBE) and use SQL to retrieve data in the IMAGE database. Next, we will configure a PC on a network to access the database with some of the new client/server PC tools, using both the GUPTA API and ODBC. Finally, we will look at some code examples of programs written on the HP3000 to access IMAGE through the SQL interface. ALLBASE and TurboIMAGE One can look at IMAGE/SQL as the end product of marrying two existing technologies, to wit TurboIMAGE and ALLBASE. We are all quite familiar with the IMAGE side, so let's spend a very short amount of time on the ALLBASE aspect. ALLBASE TurboIMAGE Concept equivalent DBE Collection of databases Objects of one OWNER DATABASE TABLE DATASET ROW or TUPLE IMAGE RECORD COLUMN FIELD or ITEM INDEX CHAIN or KEY GROUP/USERS PASSWORDS Figure 1. Database Objects. A DBE (Data Base Environment) is made up of various MPE files on the system and is created with an SQL command issued programmatically or through ISQL. The command, START DBE allows the creator (also known as the Data Base Administrator DBA by default, though more users can be granted DBA capabilities later) to specify the name of the DBECON, DBEFILEs and DBELOG files. The DBECON file's name is also the name of the DBE and this file contains the configuration of the entire DBE. One should view the DBEFILEs simply as repositories of data. The DBA can add more space to the repository by simply adding more DBEFILEs to the existing DBE. This is done via other SQL commands. Finally, the DBELOG files are used for, logging... Of course, all these files are privileged files on MPE/iX. The DBE in effect is a collection of databases which share the same logging and recovery mechanism. DATABASE is a collection of tables which belong to the same OWNER. More on th is a little later. A TABLE contains the data, like a stand alone detail dataset. A ROW or TUPLE is a collection of columns within a table. A COLUMN is a single storage entity within a ROW. An INDEX is the method used by SQL to streamline access to the data contained in various tables. Where IMAGE makes use of KEYs for masters and CHAINs for details, ALLBASE/SQL supports b-tree access to all tables and a form of HASHED INDEX to specifically designated tables. The GROUP/USER concept is used by SQL to control which user can perform which action to what data. A GROUP is made up of USERs and other GROUPs. USERs and GROUPs can be GRANTed various capabilities by the DBA. These same capabilities can be REVOKEd. A The mission is therefore to map the IMAGE database into the ALLBASE DBE and away we go. INTERLUDE Before going any further, it is important to realize a few things about ALLBASE/SQL and to keep in mind a few rules for this exercise. ALLBASE uses itself to store the directory of tables, indexes, columns, groups, and other objects. Entries regarding these objects are stored in a database which is made up of tables belonging to a user called SYSTEM. Thus if one wants to see which tables are in a DBE, one could retrieve this information from a table called SYSTEM.TABLE. Only one DBE can be opened by a session at any one time. Therefore we should plan on ATTACHing all related IMAGE databases to the same DBE. In our example, we will create the SALESDBE and we will ATTACH two IMAGE databases: ORDERS and INVTRY. With release F.0 of ALLBASE, a process can connect to up to 32 DBEs, but a transaction must still be limited to a single DBE. The command SET CONNECTION TO 'dbe'; is used to switch the connection to the various DBEs during the process. The concept of passwords does not exist in ALLBASE/SQL. The user name is used at CONNECT time to acquire the capabilities in the DBE. IMAGE/SQL follows all MPE restrictions so let us be aware of the various datab ase, DBEs, groups and accounts. In our example, the DBE and the IMAGE databases are in the same group and account and we are the creator for all of them. IMAGESQL will prompt for a maintenance word for both the DBE and the IMAGE database if the user is not the creator. So make sure that the maintenance words are set properly for both. CREATING A DBE The first thing to do is to create a DBE, in this case we will call it SALESDBE. We use the program ISQL (Interactive SQL) to issue the following SQL command: :ISQL isql=>START DBE 'SALESDBE' MULTI NEW (1) DBEFILE0 DBEFILE DBEFILE0 (2) WITH PAGES = 1000, NAME = 'SALESD0', LOG DBEFILE DBELOG0 (3) WITH PAGES = 1000, NAME = 'SALESLG0'; isql=>EXIT; Figure 2. Creating a DBE. (1) Here we specify the name of the DBE, the fact that it is new and the fact that is can be accessed by multiple users. (2) Here we specify the size and the names (internal and external) of the original DBEFILE DBEFILE0. The size is in pages which represent 4k bytes each. Here we request that the DBEFILE0 be called DBEFILE0 internally, that it be allotted 1000 pages of 4k and that it be created as MPE file SALESD0. (3) Here we specify the type, name (internal and external) and size of the log file. In our example with are not using DUAL logging and the logging will be circular and to the MPE file SALESLG0. Note the ; to end the command. We use SQLUTIL to switch settings and assign a maintenance word. :SQLUTIL >>SETDBEMAINT DBEnvironment name: SALESDBE Current maintenance word: <cr> New maintenance word : mword Retype New maintenance word: mword >>EXIT Figure 3. Using SQLUTIL. ATTACHING AN IMAGE DATABASE Once we have created the DBE, we want to attach the two IMAGE databases to it. For this we will use the program called ATCUTIL or IMAGESQL. By the way, ATCUTIL will create a DBE for you, if you wish. :IMAGESQL >> SET SQLDBE SALESDBE >> SET TURBODB ORDERS >> ATTACH various warnings and voila! >> EXIT Figure 4. Attaching a database. What just happened? Well, many things, let us examine closer. Each dataset in the IMAGE database has been added to SYSTEM.TABLE with the name of the IMAGE database as the owner. The default owner can be altered at ATTACH time by specifying OWNER=. However, remembering that in ALLBASE parlance a database is a collection of tables belonging to one owner, IMAGE/SQL fits very well within the concept. Each field in each dataset has been added to SYSTEM.COLUMN with the NOT NULL attribute as IMAGE currently does not allow valid null items. A file called ATCINFO is created which contains mapping information for each dataset in the IMAGE base. This ATCINFO file is created as a privileged file which accompanies the DBE, in essence, it is now part of the DBE and is declared in the DBECON file. Mapping involves transforming data items or dataset names, data types and security features. Specifically if item or set names contain special characters, IMAGESQL will transform these characters to an underscore. For example, IMAGE's CUSTOMER-MASTER will become CUSTOMER_MASTER, and MARKUP% will become MARKUP_. There are more things which will be added to this file in a few minutes. A DBE can be attached to multiple IMAGE databases, so the ATCINFO file will contain information about each and every attached IMAGE base. A file called dbnameTC is created which is a privileged file that accompanies the TurboIMAGE database. Since an IMAGE database can be attached to multiple DBEs, the accompanying DBTC file will contain entries for each DBE to which this IMAGE base is attached. MAPPING DATA TYPES If a data field in any of the datasets of the IMAGE base is a compound item, a message about compound items being SPLIT will be displayed at ATTACH time. This is because ALLBASE/SQL does not support compound items. What actually happened at ATTACH time, is that IMAGE/SQL generated (read mapped) a series of columns which correspond to the subitem count of the IMAGE item. For example, if the IMAGE item ADDRESS is defined as 4X30, IMAGE/SQL will map this item into 4 columns like so: IMAGE item IMAGE/SQL MAPPED column ADDRESS 4X30 ADDRESS_1 char(30) ADDRESS_2 char(30) ADDRESS_3 char(30) ADDRESS_4 char(30) Figure 5. Splitting compound items. But data type mapping goes further than splitting compound items into multiple columns. IMAGE/SQL allows the DBA to map a a non-compound item into multiple columns. IMAGE/SQL allows data type mapping so that the data contained in the item can be handled as a type differing from the IMAGE type. Let us first look at user mapping. There are many databases which have been designed with a single item, usually rather large, that actually (logically) encompasses many fields. This is prevalent in databases which came into being before TurboIMAGE. In pre-Turbo days, an IMAGE base could only have 255 distinct data items declared for use throughout the database. This presented problems for larger, more complex databases. The common solution was to declare a large item, like x200 or j50 and have the program actually interpret the data correctly. For users of 4GLs, this was a neat trick, as the various dictionaries handled these items with no problems. For example, an item defined as x200 could actually contain various integers and strings which add up to 200 bytes. In this case, the item is just used as a storage space and IMAGE as a storage manager with non-representative data items. However, this will not work for IMAGE/SQL in the SQL aspect. SQL has something known as data integrity constraint. This means that you cannot have the same X200 as above, to contain a mix of integers and characters and other types. SQL, when manipulating this column, will be very unhappy finding data which is not a valid character string. So IMAGESQL enables the DBA to create new columns which map onto this X200 field and actually represent the data properly. This mapping is done via the SPLIT command in IMAGESQL. It is important to know precisely what is contained in the sub- fields and the length of each sub-field. Let's use an example: In our x200 field above, which we will call CUST-INFO, contains the customer name, 4 address lines, country, telephone number and related customer number. The split command is >>SPLIT CUST_MASTER.CUST_INFO INTO CUST_NAME: X30: CHAR(30),& C_ADDR1: X30: CHAR(30),& C_ADDR2: X30: CHAR(30),& C_ADDR3: X30: CHAR(30),& C_ADDR4: X30: CHAR(30),& C_COUNTRY: X30: CHAR(30),& C_TELEPHONE:X16: CHAR(16),& C_RELATED: I2: INTEGER Figure 6. Using the SPLIT command The ATCINFO file would then be updated to contain this mapping and the newly specified columns would also be inserted in SYSTEM.COLUMNS. These new columns would now be available for SQL access. The last important concept of data type mapping has to do with the data type itself. IMAGE, while having several data types, does not have data constraint. Which means that the data is not checked for proper fit going in or out of IMAGE. In fact, the only common tool from HP which makes use of a data integrity constraint concept is QUERY. For example, QUERY will not allow the user to enter a non- numeric character when it is asking for customer-no. QUERY verifies that the input is valid for the corresponding IMAGE data type. SQL on the other hand, while having fewer and sometimes different data types, has strong data integrity constraints. This means the data must fit the data type or something untoward is going to happen at some point. During the original ATTACH, IMAGE/SQL will map the IMAGE data types to equivalent SQL types. But as is usual in cases of conversion, it is not 100%. SQL has SMALLINT, INT, DECIMAL and FLOAT data types to which IMAGESQL can map the following types: LOGICAL, REAL, ZONED and PACKED data. It is outside of the scope of this presentation to go into data mapping at this time, suffice it to say that IMAGESQL allows for alternate types in cases of problems. By using the UPDATE command, the various columns can have their data types changed to alternates which can better contain the data. The mapping is elementary for X and U fields into CHAR of corresponding length and I1 and I2 types map into SMALLINT and INT respectively. Again, all this mapping information is carried in the ATCINFO file and is of course, contained in SYSTEM.COLUMN for each column. It is recommended to perform the data mapping task before adding users (next step), since all views relating to the altered data are dropped if the data type is modified or the data is split. ADDING USERS After the ATTACH command, only one user is defined in IMAGE/SQL, the database creator or DBC. In order to allow other users, the DBC, having DBA capability in the DBE, has to declare additional users. As we said earlier, the concept of passwords is not implemented in SQL. Instead, it has USERs and GROUPs with capabilities. For our purposes, the USER is the MPE USER and ACCOUNT of the user CONNECTing to the database. For example if I had logged on as MGR.DENYS my CONNECT ID to IMAGE/SQL would be [log in to unmask] In IMAGE, the access to the data is decided at DBOPEN time, using a combination of the password and the DBOPEN mode. The password used, if valid, corresponds to a user class number between 1 and 63. Class 64 is reserved for the creator and class 0 is used for any invalid or nonexistent password. In the schema, the creator will have specified the READ and WRITE accesses to data items and datasets, by specifying which classes can do what action to what data. For IMAGE/SQL, we need to map USERs to MODE/PASSWORD combinations. This is done as we declare new users to IMAGE/SQL using the ADD USER command: >> ADD USER MGR@DENYS WITH PASS=PW, MODE=5 Figure 7. Adding a user. Now we get various messages. IMAGE/SQL will equivalence the password SUPERPW to a user class, for example 63 and will create a GROUP to represent this class. The GROUP name is made up of the IMAGE database name (the OWNER, remember?) and the user class. In our example this would be ORDERS_V63. The USER is added to the GROUP and then IMAGE/SQL creates a series of VIEWs which reflects the access that the MODE/PASSWORD combination allows. There is one VIEW created per each dataset which is accessible. The VIEW is named with the owner, the dataset name and the class number. Within the view, the accessible columns are represented. In our example, if class 63 permitted access to the dataset CUSTOMER-MASTER, the VIEW ORDERS.CUSTOMER_MASTER_V63 would be created and it would contain the names of the columns to which class 63 had access. A VIEW is nothing more than a stored SELECT command. ACCESSING IMAGE/SQL Now that the preparations are done, let us access the IMAGE data via the SQL environment. There are three ways to do this. One, write a program, two, using any of the neat 3rd party products, and finally, via ISQL. For the purposes of our discussion, we will limit ourselves to ISQL. Actually we have already used ISQL when we created the DBE originally. This shows us that ISQL is more than just a QUERY equivalent, in fact, ISQL, which stands for Interactive SQL, allows one to issue ALL SQL commands. For IMAGE/SQL tables, however, these commands are limited to the commands of the Data Manipulation Language (DML) portion of SQL. IMAGE/SQL does not support the Data Definition Language (DDL) commands. The core of the DML is the SELECT command: select-statement ::= SELECT [ ALL | DISTINCT] select-list INTO host-variable-reference, [,host-variable reference]... FROM table-reference [,table-reference]... [WHERE search-condition] [GROUP BY column-name[,column-name]...] [HAVING selection-condition] [ORDER BY column-name | column-number [ASC|DESC]] and don't forget the ';' at the end of the command. SELECT Figure 8. The command. SELECT-LIST: By using DISTINCT, the user prevents the retrieval of rows with duplicate column values. If the user wishes to retrieve all the columns, the asterisk can be used. Also, aggregate functions can be specified here, as well as expressions and constants. Aggregate functi ons are: MIN, MAX, AVG, COUNT and SUM. An expression is simply a mathematical expression enclosed in parentheses, eg: (1.10 * SALE_PRICE) would return all sales_price marked up by 10%. This is done by SQL not ISQL. ISQL permits the user to directly issue a SELECT command. Since ISQL is a program itself, the user cannot specify the INTO clause. Programmatically, the host-variables are the storage areas in the user program to which SQL will return the retrieved data. FROM: This instructs SQL as to which tables are to be retrieved from. Multipl e joins are easily executed, simply by specifying multiple tables. Note that if in the select list, there are duplicate column names due to multiple tables, these columns must be fully qualified by adding the table name in front or the column name. WHERE: This is how one controls the selection of rows during a retrieval. One or more search condition may be specified thus giving greater control over the retrieval. Of course, using KEY items and SEARCH items in the where clause accelerates the retrieval of the rows. If the where clause is not specified, or the selection criteria are not KEY or SEARCH items, the datasets will be scanned serially. GROUP BY: Using this clause, one identifies columns which are used for grouping for aggregate functions applied to a group of rows. HAVING: This is how one further controls the retrieval of groups defined by the GROUP BY clause. The GROUP BY has to be specified in order to use this feature. ORDER BY: This clause is used to sort the retrieved rows by the specified column or columns in either ascending or descending order. The columns can be specified by column name or column number in the select list. If the WHERE is used, it takes precedence over the GROUP BY, HAVING and ORDER BY. The rows are selected before the groups are formed. If the GROUP BY clause is used, SQL creates groups out of the retrieved rows. If the HAVING clause is used, the groups which do not qualify will be eliminated. The selection criteria are many and quite powerful, but one must pay attention lest a large select be sent improperly specified, against a large dataset. SQL supports partial keys, betweens, comparisons, exits, ins, nulls, quantifieds. SOME ISQL EXAMPLES 1- Retrieve all customer numbers and name of customers in Texas, sort them by customer name in ascending order: SELECT customer_no, customer_name FROM dbcust.cust_master WHERE state = 'TX' ORDER BY customer_name; Figure 9. Example 1 - A simple select. 2- Retrieve all customer numbers, customer names and year-to-date orders, and group them by state: SELECT customer_no, customer_name, ytd_sales FROM dbcust.cust_master GROUP BY state ORDER BY customer_name; Figure 10. Example 2 - Using GROUP BY in a select. 3- Now we are on a roll, let's retrieve the outstanding orders from the orders file and select only the customers which have orders outstanding for over $10,000. Further let's group the orders by customer and get the customer name as well. SELECT c.cust_master.customer_no, customer_name, SUM(total_value) FROM dbcust.cust_master c, dbcust.order_header o WHERE c.customer_no=o.customer_no, order_status ='OP' GROUP BY c.customer_no, customer_name HAVING SUM(total_value) > 10000 ORDER BY c.customer_name; Figure 11. Example 3 - A complex SELECT. We have seen that it is quite straightforward to make a TurboIMAGE database into an IMAGE/SQL database. Only in trying it out, and experiencing for one's self will one be able to get a glimpse at what is now possible with this new environment. CLIENT/SERVER Access to IMAGE/SQL IMAGE/SQL ushers in OPEN SYSTEMS for IMAGE users. Whilst not a Relational Data Base Management System (RDBMS) in the true sense, IMAGE/SQL enables relational-type access to IMAGE data, and for virtually all users this will be more than sufficient. For now... The 3rd parties, now able to access IMAGE through its OPEN SYSTEMS interface, are going to supply, in fact are supplying now, client server-based tools which make decision-support tasks a joy. There are going to be many more tools, and opportunities. Well, let s see how this works. ON THE PC We are now going to install a network for a PC to an HP3000, load some communication software on the PC, along with tools from the IMAGE/SQL Upgrade kit. We will download the APIs and Information access. At the end of this section, we will set up the database for Gupta and ODBC API access. Contructing the network We are now going to add our PC to the HP3000 over a network. Chances are very high that your HP3000 has a LANIC (LAN Interface Card) already on it. NS is most probably already installed on it. So what we want to do is prepare the PC and connect. We are going to map our steps on the OSI model. 1. Physical Layer: I am using Ethernet 802.3 protocol running on an thin co-ax . This layer is concerned only with ensuring that the raw data get from one node to the other, in whatever shape it s in. It addresses the the actual physical cable and the electrical characteristics of said cable. 2. Data-Link Layer: This layer is basically encompassed by the Ethernet protocol. This is the layer which packages the data into packets and puts them on the physical link. It is here that the network is managed in terms of packet creation and transfer, error detection and control and also flow control. It uses the CSMA/CD (Carrier Sense Multiple Access/Collision Detection) protocol. 3. Network Layer: I use IP, or Internet Protocol. This layer and the next one are usually talked about, and addressed together: TCP/IP. This layer is the one concerned with establising, maintaining and terminating network connections. It is at this layer that the work of translating logical addresses and /or node names into physical addresses. 4. Transport Layer: I use TCP, or Transmission Control Protocol. The other part of TCP/IP. This layer is the one which makes sure that the data gets to where it is supposed to go. If the data does not get to the target syste, this layer will initiate the retransmission. 5. Session Layer: This layer is basically encompassed in the TCP/IP standard. It is here that communication between two nodes are coordinated. This is accomplished by establishing a conversation between the programs on the two nodes. 6. Presentation Layer: I use NS/VT, or NS Virtual Terminal. This is proprietary to HP and this protocol also encompasses the next layer. This is where the data is reformated and any conversions which are necessary are also handled here. 7. Application Layer: Again, I use NS/VT. This layer is acts as the interface between the programs on the HP3000 and on the PC. It also has functions such as FTP, a File Transport Protocol; Telnet, a terminal access to a server; NFS, Network File System; SNMP, Small Network Management Protocol; and SMTP, Simple Mail Transfer Protocol. What I used: 1 co-ax cable with BNC connectors. 2 BNC T-Connectors 2 BNC Terminators. For the PC: 386 or better MS/DOS PC MS/DOS 3.3 or newer Miscrosoft Windows 3.1 About 10 megabytes of disc space. 1 LAN card for the PC with the driver. Remember the driver name, you will need it later. TCP/IP Software for the PC. I used WRQ Reflection Network Series, 3000 connection version 2.11 The IP address. The node, domain and organization names For the Server: The IMAGE/SQL Upgrade Kit MPE/iX 4.0 or newer ThinLAN 3000/XL and a LANIC card and ThinMAU. The IP address. The node, domain and organization names Setting up the hardware. Remember that a thin LAN connects to a card through a T-connector, not directly into the card. The thinlan must have a terminator at both end. So, at a minimum, you need two T-connectors, 2 terminators, and one co-ax cable with 2 connectors. On the HP3000, there is a ThinMAU where I plugged the T-connector and then one end of the co-ax. On the other side of the T, I placed a terminator. On the PC, I installed a LAN card. I used a KATRON ET- 16TB costing $89.00. I loaded the card driver that came with the card, onto my PC and installed the card but not before I placed the jumper JP1 to SOFT ;. I then placed a T-Connector at the appropriate spot and connected the co-ax coming from the HP3000. On the other side, I connected another co-ax which leads to an HP9000/300 and the rest of the network. If I did not have any other nodes, I would have placed the other terminator instead. The LAN card driver. The KATRON came with the driver I needed, A:\NDIS\E200PLUS.DOS, and a setup program TBSETUP. I used TBSETUP to configure my card for the I/O base address, 300; the interru pt or IRQ, 10; and the connector type, BNC. I then cycled the power on my PC so that the configuration took on the LAN card. TCP/IP software on the PC. WRQ has a comprehensive communications package that enables communications between the PC and the HP3000. This software is part of the Reflection Network Series, RNS. It can also be used to support Reflection 1 for Windows. RNS 3000 connection provides access to the following protocols: 1. Telnet-MGR: This provides access to 2 TCP/IP standard, Telnet and FTP. Since I have a 9000 on the network, and both the 3000 and the 9000 support FTP, I use this connection. 2. LAT: (Local Area Transport) This is a DECnet protocol governing the communications between terminals and hosts. It is required to emulate a termi nal on a VAX/VMS. I don t have a VAX, so I don t use this one. 3. VT-MGR (NS/VT): This is what we will use for our exercise. It handles the communications between the HP3000 and the PC. Step 1, create a directory on your PC to hold the files. C:\>MD HPPCAPI Step 2, download the file HPPCAPI.PUB.SYS and unpack it. Run Reflection and download the file HPPCAPI.PUB.SYS to HPPCAPI.EXE in the HPPCAPI directory. The