Midrange Programmer

Remember when you got your first PC at the office? You had a new fancy Telnet session to get at your AS/400 and probably felt like throwing that old 3197 CRT into the garbage. You now had all of those neat drag and drop functions at your disposal. But if you are like most people, unfortunately, many of your legacy applications still remain in the green screen dungeon. So today we'll spice up your applications with a graphical user interface.

We are going to build a Windows application on our PC that displays some fictitious customers. The customer file is on the AS/400 and will populate a ListView control in our Visual Basic program. From the ListView control, we will be able to manipulate the records in our customer file. To create our salad, we need a smattering of ingredients: Client Access, Visual Basic, an understanding of Microsoft's ActiveX Data Object (ADO), and the ListView control. We also need our main ingredient, the AS/400 database.

In this article, I will review the customer file, configure an ODBC data source name (DSN), and provide a quick lesson in SQL and ADO. You will then have some time to digest that before reading my next article, in which I will combine all of the things we've learned today and apply them in our GUI front-end application. We need to review the make up of our file on the AS/400. This will be critical, because we need to know the field names when we start modifying our records. Client Access needs to be installed, or at least the option Data Access for ODBC. We will configure an ODBC DSN using Client Access as the driver that provides the "pipeline" between our Visual Basic application and our database on the AS/400. Enough gabbing; let's get tossing.

First, let's talk about what our database looks like on the AS/400. You can download the file from the Web or create the physical file. Please note that all of the fields below are field type CHAR and length is 20 characters except for field fid, which is 12 characters in length.

When you create the file, make a note of which library you created it in. We will use that library when we create our DSN in the ODBC setup that is discussed next. The fields for the file are pretty self-explanatory, with the exception of FID. This field will hold a unique identifier for each record in our database, and its main purpose is to help provide a mechanism for finding a record for updating or deleting, which will be explained later on in the ADO section.

I mentioned that you need to have Client Access installed. This example is using Client Access Express V4R5M0, and the only component that needs to be installed is the Data Access – ODBC option. Once this is installed, you need to configure an entry in ODBC, so you can get at your data from the PC. I have included directions in the configure_ODBC file.

When we start coding our Visual Basic application, we will refer to the CustSrv400 DSN to get at the AS/400 data with our Connection object within ADO. Before we start displaying our AS/400 data, we need to know a little bit about SQL and Microsoft's ActiveX Data Object, and how we will use them.

SQL stands for "structured query language," the standard language used to communicate with most relational databases. The syntax for each SQL command may differ a bit for each relational database you are trying to connect to, but most SQL statements are the same. We will use SQL statements and combine them with ADO to manipulate our data.

Let's review the two biggest SQL statements that we will use to get at the data. First, when we need to select all of our data or a specific record to display in our ListView control, we will use the Select command:

In fields to display, we need to define which fields we wish to view from our table. We can define a single field, multiple fields, or use the wild card (*) to indicate that we want to view all of the fields defined for the file. The physical file from the statement represents which file we want to view. We don't need to specify a library for our file, because we coded that in the ODBC set up. The optional parameters order by and where allows us to sort by a specific field and to search by specific criteria, respectively. The following example selects all fields from the CSTSRVFL file and sorts it (order by) the flast field:

This is the syntax that we will use to fill the ListView control with AS/400 data. Let's take a look at another sample:

This will display the record where the fid field is equal to 100602081502. This syntax will be used when we want to get at a specific record and update its information. You can also use the order by and where clauses together, if the where clause is going to return more than one record and you want that information sorted. When we want to delete a record, we use the following syntax:

The physical file represents our file on the AS/400, and the where clause will contain an expression stating the data that you wish to delete. If we hadn't specified a where statement, then this would clear our entire file. The syntax we will use is something like this:

The key to this statement is that we will have a unique identifier for the FID field, so that we can delete this specific record. Those SQL statements above, plus a little bit of knowledge about ADO, will prepare us for when we build our GUI application.

ActiveX Data Object, or ADO, is Microsoft's newest high-level interface for data access. It was designed to eventually replace data access objects (DAOs) and remote data objects (RDOs), which were limited to accessing relational databases. ADO was designed to provide a consistent way of accessing data, regardless of how the data is structured. We will add this as a reference in our Visual Basic application, so we can get at all of the objects. The two objects I need to mention are Connection and Recordset.

An ADO Connection object is used to create an open connection to a data source. Through this connection, we can access our database. We will be going through our ODBC DSN to get at our AS/400, using the following code:

The dim statement defines what type of object our variable represents. The set mConn "builds" the object and now allows us to use all of the properties and methods for it. This code will open our connection to the AS/400 when we open our Visual Basic form, and it will close the connection when the program closes. Notice that with the Open method we are pointing to the data source that we configured in ODBC. This is how we grab onto that pipeline to the AS/400.

An ADO Recordset object is a set of records that we have fetched from our database. A record is a row of data from the result of a SQL statement that we will use. When we want to receive all of the records from our AS400 database, we will use the following code:

This code just says that I want to create an object called mRS, use it against my mConn Connection object, and fill my recordset with all of the records in CSTSRVFL file, sorting them by the FLAST field.

This article reminds me of a bland salad with fat free dressing, full of basic stuff that we need to know, like ODBC, ADO, and SQL. This stuff isn't very interesting yet, but in my next article we'll hop off the treadmill and get to the dessert. We'll take what you learned today and sprinkle these concepts into our Visual Basic application. I will show you the ListView control in Visual Basic and explain how to display, add, change, or delete records.

Bob Butcher is an AS/400 consultant with over 15 years' experience in the AS/400 arena. He specializes in client/server applications that deal with the AS/400 and Active Server Pages or Visual Basic. Bob can be reached at butcherb@samicsoft.com.