This tutorial aims to teach you how you can connect to a Remote Database Management System (RDBMS) using LANDP. You will learn how to connect to and update a database using SQL.
LANDP provides two servers for connecting to a RDBMS, the Query server and the ODBC Query server. The Query server is available on OS/2 only and the ODBC Query server is available on Windows only. The Query server is a DB2 program whereas the ODBC Query server uses the Open Database Connectivity standard (ODBC) to connect to RDBMS systems that support ODBC.
The Query server and the ODBC Query server share the same 'query mode' interface. The query mode interface allows the server to handle requests via Structured Query Language (SQL). The Query server also offers a Shared File mode, which mimics the interface to the LANDP Shared File server.
We based this tutorial on the ODBC Query server using the query mode interface. Therefore, you can use this as a guide to using the Query server since the two servers share the common interface.
The ODBC Query server needs to connect to the database using a user ID and a password. You provide these to the ODBC Query server by using the GF (Grant Function), OS (Open Session) or CD (Change database) functions. These function also take the Data Source name. You can not make a Data Source name, user ID or password longer than 20 bytes.
Before you can begin with this tutorial you will need to:
You will need to configure your ODBC driver to talk to your RDBMS. You do this by creating a Data Source. On Windows you will find a Data Source Administrator program in the Control Panel. Use this program to create your Data Source.
If you plan to run the ODBC Query server as a Windows Service (this is the norm), you will need to create a System Data Source (System DSN). System Data Source's are available to any user of the system whereas a User DSN is only available to the user logged on. Since Windows Service can run under different users you need to create a System Data Source.
In this tutorial we will use DB2 and the Data Source, MYDATA. We mapped this Data Source to the LDPDEMO database.
If you are using the Query server you do not create Data Sources as these are part of ODBC. The Query server only supports DB2 and you can use DB2's Control Centre to create a database to use with the Query server. You must use the same database name during LANDP customization as you have with DB2.
The ODBC Query Server and the Query server allow programs to have multiple sessions. This enables a program to have multiple concurrent transactions. The ODBC Query server enables a program to change the Data Source that it is using for a session. Therefore, your program can use more than one Data Source. This feature is not available in the Query server.
Because we will use the ODBC Query server with SQL, you need to understand SQL and relational database systems.
There are no COMMON vectors that you need when customizing LANDP for the ODBC Query Server or the Query server. You only need to define the workgroup definition.
You need to define the workgroup:
SERVER=(EHCODB01)
PAR&ODB=(MYDATA,4,14,10,15)
The SOFTPACK vectors allow you to distribute your own software to each workstation along with the LANDP files. We will distribute files for the SVPCPRBN utility for our workstation.
The PAR&ODB keyword contains 5 optional parameters, as mentioned above. The Data Source parameter, also called the Configuration Name, refers to the name of the Data Source that the ODBC Query Server will use. Because you can change which Data Source a ODBC Query server session can use, the Data Source named in this parameter is the default data source.
The ODBC Query server uses worker threads to help increase the throughput. When passing a request to the RDBMS the thread of execution is blocked until the RDBMS replies. In a single threaded environment, your programs can only pass requests one at a time to the ODBC Query server. We made the ODBC Query server a multithreaded server. We call these extra threads worker threads. The ODBC Query server passes to these worker threads any request that needs the RDBMS. Therefore, only the worker thread is blocked until the RDBMS replies but your other programs can make use of the other worker threads.
Because there is an overhead when creating worker threads and a drain on resources, you can configure the ODBC Query server to create an initial number of worker threads which can grow to a maximum. If your program needs a service from the RDBMS and no worker threads are available then the ODBC Query Server will create a new thread if the maximum number has not already be reached. Otherwise, it return the return code 'SE.'
You can restrict the number of sessions that a single workstation can obtain. A session enables a program to do a piece of work. Programs may have multiple sessions or may just use a single session. A workstation may run multiple programs that use multiple sessions and you can end up with a single workstation using all available sessions. Therefore, by restricting the number of sessions available to a workstation you can control your workgroup session usage.
The request time-out refers to how long the ODBC Query server should wait for a response from the RDBMS. This should be less than your LANDP request time.
The validation procedure, VALSPEC, reads edited .SPC files and checks the data for consistency. If the data is valid it then generates data to update the internal repository to the same level as the .SPC files. After typing
you will see similar messages to those of the previous utilities. If all the changes you made are correct then VALSPEC will exit with a zero return code after updating the internal repository. If warning messages or errors exist then the internal repository will not be updated and the COMMON.SPC file will have to be checked and re-edited before we can proceed. To assist you in finding the problem you can look at the file CUSPARM.LST, which contains a log of the last customization step that was performed.
To view it type
type cusparm.lst
or open it in any text editor.
The final stage of customization before we are able to start LANDP itself is the distribution of the run-time files. This consists of two steps, the generation of the files using GENRUN, and the actual distribution.
To generate the run time files type
at the command prompt. This will read the internal repository and generate all the run-time files required based on the data we have edited and validated.genrun \ldp1
We can now distribute the run-time files to our workstation. For this example, this process is quite straightforward as we are distributing to the same workstation that we used to customize. In a larger customization process the distribution of files might involve copying files across a network or transfer by diskette.
We wish to place our example LANDP installation in the directory C:\LDP. In order to do this we use the GETTING utility. The command line
getting \ldp1 WS=AA c:\ldp\
will copy the run-time files for the workstation AA to the correct directory. If we had specified A: or B: as the target drive then we would be prompted to insert a formatted diskette. When specifying a target on a fixed disk as we did above then it is important that the path ends with a backslash character as above. You could also leave out the workstation specification and copy all the files for a workgroup.
With this step complete we have now completed the customization process and can start to use LANDP.
To begin using LANDP change to the directory into which we copied the files (C:\LDP). We can now start LANDP by entering
at the command line. This will load the LANDP Supervisor program and any services that we defined during customization. If this is successful then you are now running LANDP. Type
ehcinfo
to see what LANDP services are running.
Start SVPCPRBN from the command line and the select the 'Send CPRBs' menu item from the Verify menu. Register this instance of the SVPCPRBN program with the supervisor, using the IN function.
Prepare The ODBC Query server for business
You need to issue a GF function to the ODBC Query server before you can perform any useful function. The ODBC Query server expects that the Data Source name, User ID and password that you want to use to connect to the database exist in the Request Data Area. The format of the Request Data Area is:
The Request Data Length should be set to 60. You can specify a Request Data Length of 1 and place the letter 'O' in the Request Data Area. But you will need to provide the Query Server with the Data Source, User ID and Password using the OPENODB utility (see Chapter 7 of the LANDP Servers and System Management manual).
The Query server does not expect this as you are logged on to OS/2 as the correct user.
In our example we are connecting to the MYDATA Data Source, using the user LANDPID and a password of LANDPID.
The connection to the database does not happen until you issue a OO (Open On-line) function. The ODBC Query server stores this data provided in the GF function for use with the OO function.
Create And Populate A Table
At this time, we have an empty database. You will need to create a table to work on. The ODBC Query server can create this table using the SQ (Structured Query) function. The SQ function passes an SQL statement to the RDBMS for processing. You can provide the SQ function with any SQL statement that your RDBMS supports.
Before you can issue any function to the RDBMS you need to open the ODBC Query server in Query mode. To do this issue an OQ function. The first time you issue an OQ function, you may experience a little delay because the ODBC Query server 'logs on' to the RDBMS using the data provided in the GF, OS or CD functions.
Note, when issuing a a function to the ODBC Query server it is useful to give a Reply Data Area. The ODBC Query server passes the message returned from the RDBMS back to the application in the Reply Data Area.
We are now ready to create a table. Our table is simple and consists of three columns:
Use the SQ function to create a table that defines the above table, you can see an example below.
The RDBMS contains a table called EXTABLE. We want to add records to this table so that we can see how the other ODBC Query server functions work. Use the SQ function to insert a number of records into the table, you will need to use the SQL statement, INSERT. We suggest that you use these SQL INSERT statements:
INSERT INTO EXTABLE VALUES ('Smith, Tommy', '0178234', 12304.98)
INSERT INTO EXTABLE VALUES ('Brown, Andy', 'AB9834120000', 100.45)
INSERT INTO EXTABLE VALUES ('Jones, Brain', '00182AS93', 124.78)
INSERT INTO EXTABLE VALUES ('Trotter, Derek', '2301784', 0.98)
What happens if you input the character strings in double quotes rather than single quotes?
What does the LANDP server return code mean?
Can you see the error message in the Reply Data Area?
The return code QE indicates a 'Query Error.' The ODBC Query server returns a message in the Reply Data Area, if there is enough room. The RDBMS returned this message to the ODBC Query server when it completed the request. The ODBC Query server then adds the native database error number, in hexadecimal, to the front of the message. The native database error number is found in the first 4 bytes of the Reply Data Area.
At this point we have not committed the data, which means that we can rollback the requests that we have made or we can lose the data if we had a power failure or some other problem. Therefore, we must commit the data to the database. The ODBC Query server allows you to commit your work in a number of methods. You can use:
You may recall that the SQ function can submit any SQL statement that the RDBMS supports. Most, if not all, RDBMS support the SQL statement COMMIT or COMMIT WORK.
The CQ function logs the program off the ODBC Query server. This is the closing function that matches the opening function OQ. If your program logs out of the query mode, in other words issues a CQ function, and it has not committed its work then the ODBC Query server performs the commit on your behalf.
The CW function issues the SQL statement COMMIT WORK to the RDBMS. Therefore, it has the same effect as requesting an SQ function with the Request Data Area being 'COMMIT WORK.'
Commit your database.
Manipulating The Data Using SQL
Databases hold valuable information that we want to retrieve, update or delete from time to time. What follows, is a set of exercises that show you how to perform these tasks using the SQ function. SQL provides you with the SQL statements SELECT, UPDATE, and DELETE amongst others. We aim to show you how these work with the SQ function.
The SELECT statement retrieves data from the database. If the ODBC Query server receives an SQ function with a SELECT statement it opens a query. A query can exist for more than one function call and is identified by a query handle. An application can open many queries. The ODBC Query server returns the query handle in the Reply Parameter Area in bytes 0 and 1. You use the handle to perform other operations on the same query. The ODBC Query server allows you to close the query handle without committing, rollbacking or closing the connection. The EQ (End Query) function closes the given query handle.
If you want to view the contents of our table you issue a SQ function with a SELECT statement. However, the SQ function returns the first row only. You get to the remaining rows by using the query handle with the FR (Fetch Row) function, you repeat this until you retrieve all the rows in the table.
An example SELECT statement is:
SELECT NAME, BALANCE FROM EXTABLE
What happens if you issue the above statement?
Turn the SVPCPRBN output to hexadecimal using the Hex button. Look at the first 2 bytes of the Reply Parameter Area. What happens if you issue the above statement again?
The data in Reply Data Area looks familiar, but you can see some other characters mixed into the data stream. Below is the SVPCPRBN window that you should see.
The RDBMS returns a stream of bytes which you need to interpret. The data in the Reply Parameter Area describes the format of the data stream. It is best to work in hexadecimal for us to illustrate how you can interpret the data.
If you convert the Reply Parameter Area into hexadecimal it will look like:
02 00 02 00 c1 01 19 00 e1 01 08 00
The Reply Parameter Area is group into fields that are 2 bytes in length, called the short descriptor. The first two bytes show the query handle, in our case the query handle is 2 (your may be different). The fields are byte-reversed. The next two bytes indicate how columns (fields) make up the Reply Data Area, in our case the Reply Data Area holds data from 2 columns. This makes sense, as we SELECTed the columns NAME and BALANCE in the SELECT statement. The remaining bytes describe the format of the Reply Data Area.
The ODBC Query server describes each column using 2 2-byte fields. The first field indicates the data type used to represent the data. The second field indicates the length, in bytes, of that columns (as defined in the CREATE table statement).
Our two columns are described as:
NAME (COLUMN 1) | BALANCE (Column 2) | |
Field 1 : Data Type | 0x01c1 | 0x01e1 |
Field 2 : Column Length | 0x0019 | 0x0008 |
Note that in the table we have reverse the bytes to represent their true values.
In Chapter 16 of the LANDP Programming Reference you will find a table that details the data types supported by the ODBC Query server. This is Chapter 15 for the Query server. The table details the field data type in decimal and hexadecimal. These values are those used by the underlying RDBMS, LANDP is not making these values up. You will find two values for each decimal and hexadecimal, for example 01c0/01c1. The first value indicates that the column does not have a null indicator, whereas the second indicates that there is a null indicator.
Using the table found in Chapter 16, you can see that the NAME column has a data type that is Varying Character String with a 2 byte length field and that the column length is 25. This matches our definition of:
NAME VARCHAR(25).
You can see that the BALANCE column has a choice of data type, Float (double precision), Real and Float. This columns data type is Float double precision. This matches our definition of:
BALANCE DOUBLE.
Also each columns has a null indicator. This is important to remember when we interpret the Reply Data Area.
Now we know and understand the format of the Reply Data Area we can interpret the byte stream. The first column, NAME, has a null indicator and is a Varying Character String. Varying Character String columns are different to all other columns in that in the Reply Data Area they are preceded by a length field. The short descriptor defines the maximum number of bytes that can be used for that column. The NAME columns uses a Varying Character String with a length maximum of 25 bytes. The length of the actual data stored for this record is found in the Reply Data Area, in the first 2 bytes before the actual data. In addition, you also find a 2-byte null indicator field before the actual data for columns that allow NULLs. If your column is a Varying Character String and it allows NULLs, like the NAME column, the null indicator field comes before the variable length field.
Our initial view of the Reply Data Area format is:
You will notice that this format does not match the one returned by the ODBC Query server. Now we know about null indicators and variable length fields our view is:
If a column has the NULL value the indicator is set to 0xFFFF.
We should convert the Reply Data Area to Hexadecmial, to make it easier for us to interpret the data. The null indicator and variable length data in the Reply Data Area is byte reversed. We can interpret the Reply Data Area as:
Bytes | Hex (as is in Reply Data Area) | Interpretation |
0 -1 | 00 00 | NAME column data is valid, it is not NULL. |
2 -3 | 0c 00 | Useful column data is 12 bytes in length. |
4 -28 | 53 4d 49 54 48 2c 20 54 4f 4d 4d 59 00 00 00 00 00 00 00 00 00 00 00 00 00 | SMITH, TOMMY |
29 - 30 | 00 00 | BALANCE column data is valid, it is not NULL. |
31 -38 | 0a d7 a3 70 7d 08 c8 40 | Machine representation, double type, of 12304.98 |
Use the FR function and the query handle to retrieve the remaining rows from the table. The FR function requires the query handle to occupy bytes 0 and 1 of the Request Parameter Area. What happens if you attempt to retrieve another record after you retrieved the last row in the table?
What happens if you attempt to issue the FR function for a second time after you have retrieved the last row?
Try opening another query, for example, SELECT * FROM EXTABLE. What can you see in the Reply Parameter Area and the Reply Data Area?
Become Familiar With The SQ Function
You should now experiment with the SQ function by issuing different SQL statements. Use your knowledge of retrieving records to view the changes to database. We suggest:
The first suggestion updates the balance of account number AB9834120000 by 100.00. The second removes any records in the database where the name is equal to 'Trotter, Derek.' The third suggestion should retrieve one row only, that of Brain Jones. The final one should fail. What does the return code mean? Can you correct the statement to make it successful?
Before leaving this section we will under all the operations and close the connection to the ODBC Query server. Issue an RW and CQ, as below:
How To Delete Or Update A Record
You have already seen that you can delete and update a record using the SQ function. In this section we aim to show you the DL and RR functions of the ODBC Query server. These functions use the query handle that the SQ function returns. This is a useful tool for search through your database a record at a time and then updating or deleting the current record.
To start with, you will need to open a connection to the ODBC Query server using the OQ function. We will first show you how to update (replace) a record, by updating the ACCNUM column of Brain Jones'. First, we need to open a query that will search retrieve the rows from the table. We use the SQL statement:
SELECT * FROM EXTABLE FOR UPDATE
The FOR UPDATE clause creates a modifiable cursor for the query handle. The ODBC Query server returns a query handle that will enable us to fetch each record until we find Brain Jones' record.
Using the query handle we can issue the FR function to the ODBC Query server, until we reach Brain Jones' record.
When we find Brain Jones' record we need to copy the data in the Reply Data Area to the Request Data Area, you press the 'Rep->Req' button in SVPCPRBN to do this. Then we can modify the ACCNUM field to a new number of '0018AS98b.' Do not forget to set the Request Data Length to the value in Replied Data Length.
Using the CW function to commit the change and release the query handle.
Now view the contents of the EXTABLE using the SQ and FR function. When you have read the update row, use the EQ function to save you from reading all the rows.
We will now use the same process as above to delete Brain Jones' entry from the table. Use the SQ and FR functions to fetch Brain Jones' record. Do not forget to use the FOR UPDATE clause on the SELECT statement. When the ODBC Query server receives a DR (Delete Row) function, it deletes the row that the cursor associated to the query handle is pointing at. Issue the DR function.
Fetch each row of the table using a new query. You can do this by issuing the SQ function with the SELECT * FROM EXTABLE statement. The use the FR function to obtain all the rows, do not forget to copy the query handle (should be 0x0002) to the Request Parameter Area. Can you find Brain Jones' record?
Now issue a RW (Rollback function). Again, fetch each row from the database. What do you see?
Insert A Record With Out Using the INSERT statement
Can you guess what the IR (Insert Row) function does? Can you guess what the format of the Request Data Area is?
The IR function offers you two ways of adding a record to the table, the first is by using the query handle and the other is by explicitly stating the table name. In this example, we will explicitly state the table name.
The Request Data Area contains the row to insert and is just a stream of bytes. The IR function expects the row data to match the format returned by the SQ function when it has processed a SELECT * FROM statement. The data that we will insert is:
Remember the impact that the null indicator and variable length fields have. The format we need to use is:
Note, ACCNUM does not allow NULL values and hence there is no null indicator. This column uses the type CHARACTER which is a fixed length string and therefore must contain a valid value in each byte.
Enter the data using the Hexadecimal mode. The Request Data Area should look like (in Hexadecimal mode):
Bytes 0-4 | 00 00 0d 00 4a |
Bytes 5-9 | 6f 6e 65 73 2c |
Bytes 10-14 | 20 42 72 61 69 |
Bytes 15-19 | 6c 00 00 00 00 |
Bytes 20-24 | 00 00 00 00 00 |
Bytes 25-29 | 00 00 00 00 30 |
Bytes 30-34 | 31 32 59 54 38 |
Bytes 35-39 | 39 20 20 20 20 |
Bytes 40-44 | 20 00 00 00 00 |
Bytes 45-50 | 00 00 00 00 00 |
You need to supply a null query handle (oxFFFF), as returned from a DQ or DT function. The Request Parameter Area for the IR function needs to have the byte 2 set to the letter 'T' to indicate that the table name is specified at byte 12 of the Request Parameter Area. The Request Parameter Length should be set to 12 plus the length of the table name, in order case 19. The completed function should look like:
What happens if you do not specify the correct length on the Request Parm Length field?
What happens if you do not specify a null query handle in the Request Parameter Area?
In this practical tutorial you learnt: