Innovative Systems, LLC

I recently had a requirement of being able to get the current date and time from the system and use that value in a .Net program. After trying a couple of approaches I chose to call the “Retrieve System Value” API and convert the system date/time to a .Net DateTime type.

First, you will need to add a reference to the ActiveX control that allows your program to call programs on the system. Right click the “References” section under your project and under the “COM” tab on the “Add Reference” dialog look for “IBM AS/4400 iSeries Access for Windows ActiveX Object Library”. Add this to your solution. Make sure to also add “using cwbx;” to your program.

Now the code to get the date/time and convert to .Net DateTime:

        private DateTime GetDB2SystemDateTime()
        {

            DateTime sysDateTime = new DateTime();
            string wrkString;

            cwbx.AS400System AS400 = new cwbx.AS400System();
            cwbx.Program pgm = new cwbx.Program();
            cwbx.StringConverter strCnv = new cwbx.StringConverter();
            cwbx.LongConverter longCnv = new cwbx.LongConverter();
            cwbx.Structure ERRC0100 = new cwbx.Structure();
            cwbx.Structure SysValRcv = new Structure();
            cwbx.Structure SysValInfo = new Structure();

            // Define the connection and the program to call
            AS400.Define(db2Conn.DataSource);
            AS400.UserID = Properties.Settings.Default.iSeriesUser;
            AS400.Password = Properties.Settings.Default.iSeriesPwd;
            pgm.system = AS400;
            pgm.LibraryName = "*LIBL";
            pgm.ProgramName = "QWCRSVAL";

            // Connect to the remote command server
            try
            {
                AS400.Connect(cwbcoServiceEnum.cwbcoServiceRemoteCmd);
            }
            catch (Exception as400E)
            {
                fsw.WriteLine(LogFileDateTime() + "Error occured during AS400 logon: " + as400E.Message);
                fsw.Flush();
                return sysDateTime;
            }
            if (AS400.IsConnected(cwbcoServiceEnum.cwbcoServiceAny) == 0)
            {
                fsw.WriteLine(LogFileDateTime() + "Could not connect to AS400 remote command server");
                fsw.Flush();
                return sysDateTime;
            }

            // Create a parm structure for the system value receiver variable
            SysValInfo.Fields.Append("SystemValue", 10);
            SysValInfo.Fields.Append("TypeOfData", 1);
            SysValInfo.Fields.Append("InfoStatus", 1);
            SysValInfo.Fields.Append("LengthOfData", 4);
            SysValInfo.Fields.Append("Data", 20);

            // Create a parm structure for the system value data
            SysValRcv.Fields.Append("NumSysValsRtn", 4);
            SysValRcv.Fields.Append("SysValOffset", 4);
            SysValRcv.Fields.Append("SysValInfo", SysValInfo.Length);

            // Standard error reporting structure
            ERRC0100.Fields.Append("BytesProvided", 4);
            ERRC0100.Fields[1].Value = longCnv.ToBytes(272);
            ERRC0100.Fields.Append("BytesAvail", 4);
            ERRC0100.Fields[2].Value = longCnv.ToBytes(0);
            ERRC0100.Fields.Append("ExceptionID", 7);
            ERRC0100.Fields.Append("Reserved", 1);
            ERRC0100.Fields.Append("ExceptionData", 272);

            // Define the parms for the API call
            ProgramParameters parms = new ProgramParameters();
            parms.Append("RcvVar", cwbrcParameterTypeEnum.cwbrcOutput, SysValRcv.Length);
            parms.Append("RcvVarLen", cwbrcParameterTypeEnum.cwbrcInput, 4);
            parms["RcvVarLen"].Value = longCnv.ToBytes(parms["RcvVar"].Length);
            parms.Append("NumSysVals", cwbrcParameterTypeEnum.cwbrcInput, 4);
            parms["NumSysVals"].Value = longCnv.ToBytes(1);
            parms.Append("SysVals", cwbrcParameterTypeEnum.cwbrcInput, 10);
            parms["SysVals"].Value = strCnv.ToBytes("QDATETIME ");
            parms.Append("ErrorCode", cwbrcParameterTypeEnum.cwbrcInout, ERRC0100.Length);
            try
            {
                pgm.Call(parms);
            }
            catch (Exception callE)
            {
                fsw.WriteLine(LogFileDateTime() + "An error occured during API call to QWCRSVAL: " + callE.Message);
                fsw.Flush();
                AS400.Disconnect(cwbcoServiceEnum.cwbcoServiceAll);
                return sysDateTime;
            }
            ERRC0100.Bytes = parms["ErrorCode"].Value;
            SysValRcv.Bytes = parms["RcvVar"].Value;
            SysValInfo.Bytes = SysValRcv.Fields["SysValInfo"].Value;
            wrkString = strCnv.FromBytes(SysValInfo.Fields["Data"].Value);
            try
            {
                sysDateTime = DateTime.Parse(wrkString.Substring(0, 4) + "-" +
                                        wrkString.Substring(4, 2) + "-" +
                                        wrkString.Substring(6, 2) + "T" +
                                        wrkString.Substring(8, 2) + ":" +
                                        wrkString.Substring(10, 2) + ":" +
                                        wrkString.Substring(12, 2) + "." +
                                        wrkString.Substring(14));
            }
            catch (Exception dtE)
            {
                fsw.WriteLine(LogFileDateTime() + "Error occured extracting AS400 date/time: " + dtE.Message);
                fsw.Flush();
            }
            AS400.Disconnect(cwbcoServiceEnum.cwbcoServiceAll);
            return sysDateTime;

        }

Note the ERRC0100, SysValRcv and SysValInfo variables.  These are a type of cwbx.Structure and you can think of these are creating data structures programatically.  Also note the "fsw" object...this is basically a text file write that I'm using to log errors during the conversion process and is defined in the main section of my class as a type of StreamWriter.

When this routine is called it returns the system date/time converted to a .Net DateTime value or a DateTime value with the default for the object type.

Hopefully no one has gotten the impression that I’m clever enough to figure out this .Net stuff on my own. I did get a book from Borders to start off with but for the iSeries specific stuff I’ve had some help along the way. My sensei (unbeknownst to him) has been Colm Byrne from his Don Net Fluke blog. He’s got examples AND some videos for instruction.

In this installment of the .NET journey we’re going to create a small but useful program to query the DB2 database. Later we’ll add code to export the data to Excel! How cool is that?

Creating the Project

In Visual Studio 2005, create a new Windows form project. To do this, start your version of Visual Studio and click the “Create project…” link in VS “Start Page”. You’ll then be prompted for the type of application to created. Select “Windows Appliction”.

Adding Controls

Once the project is created on the right hand side will be a window titled “Solution Explorer – yourproject name”. In the middle will be a “Form1.cs [Design]” tab with a small blank window. Make the window a little bigger by dragging the corner of the window. For this example we’ll add the following controls to the window:

• A text box for the host name
• A text box for the SQL statement to execute
• A data grid view
• A button to execute the query

On the left hand side of VS should be a box with the word “Toolbox” orientated on it’s side. Let your mouse hover over this and a toolbox should appear. If you don’t have this select “View | Toolbox” from the menu. Now select the “Label” tool from the toolbox and drop it on your form. Move the label so it’s in the upper left hand corner of the window. Now select a “Textbox” tool and drop it on your form. Move this so it’s to the right of the label control you dropped earlier. You’ll notice that as you get close to the label VS presents aligment guides to help you place the control on the same X axis as the label.

Now we need to change the visible text for our label control. Select the label control and on the left under the “Toolbox” there should be a box labeled “Properties”. Let your mouse hover over this and the properties should appear. If it doesn’t select “View | Properties Window” from the menu. Locate the “Text” property and change this from “label1″ to “System”. Also make sure the text box you added next to the label is large enough (drag the right side of the control to change).

Now we’ll add controls for the SQL statement. Add another label under the system label you added earlier and change the text to “SQL Statement”. Add a text box to the right of this label and make the control wider. Since we’re typing an SQL statement we want to have more than one line of text in the text box so we need to make it multi-line. Open the properties window for the text box and change “Multiline” to true. Now you can also change the height of the text box.

Next we’ll add a data grid view to the form. Go back to the toolbox and drop a “DataGridView” onto the form. Since the intent of this project is a simple read-only query tool, we don’t want users to type into this grid. Locate the “AllowUserToAddRows” and “AllowUserToDeleteRows” in the object’s properties and change those to False.

For the last control we’ll add an “Execute” button. Drop a “Button” object onto your form and move it to the left under the grid. Change the text for the button to “Execute”.

The First Test

Just for grins, let’s run the program. Click the “Run” button (green triangle) on the toolbar and your window should look something like this:

Start Your Connection!

Okay, now we need to add controls that let us connect to our iSeries.

First we need to add a reference to the IBM .Net data provider. On the right hand side of VS under the “Solution Explorer” window there should be an outline of your project represented by a tree view. Locate the “References” node, right click and select “Add Reference” from the popup menu. Locate “IBM DB2 UDB for iSeries .NET Provider, highlight and click “Okay”. Note that if you can’t find this provider then you need to install from the iSeries Access CD.

We need to make VS aware of the data controls available in the IBM .NET provider. From the menu, select “Tools | Choose Toolbox Items”. Under the “.NET Framework Components” tab you’ll find a list of items available for selection. To find the ones we’re intersted in, type “IBM” in the filter underneath the list. You should then see four items from the IBM.Data.DB2.iSeries namespace. Select all four and click “Okay”. These are now available on the “Toolbox” window and will be there for each subsequent project you create in VS 2005.

Now go to the “Toolbox” window and drag/drop a “iDB2Connection” object to your window. Once you do, you’ll notice that VS shows the object in a space underneath the window you created earlier. That’s because these components are not “visible”. Now go back to the “Toolbox” and drag/drop a iDB2Command and iDB2DataAdapter to your window.

We need to link some of these together so click the “iDB2Command” object and under the “Properties” window locate the “Connection” property, click into the value and click the down arrow. The small popup window should show only one available value and it should be the iDB2Connection object we dropped earlier. Go ahead and select it.

Now select the “iDB2DataAdapter1″ and locate “InsertCommand” in the object’s properties. Click into the value, click the down arrow and select the iDB2Command object in the list.

We could also set the system name to connect to in the iDB2Connection object but since we’re allowing the user to enter a system name we’ll set it later in the program.

One more object to drop onto our form. Locate a “DataSet” object and drop onto the form. You’ll then be prompted to choose either a Typed or Untyped dataset. Select Untyped and click Okay.

Now we need to link the data grid view to the data set we just added. Click the data grid and locate “DataSource” in the object’s properties. Click the down arrow which will present a window of data sources. Expand “Other Data Sources” and there you will find “Form 1 list instances” (if you’ve changed the name of your form it will show differently). Expand your form’s instances and you should find the dataset you just added. Click the data set to select.

Just A Little Code

Now that all of our objects are present we can code!

Double-click the “Execute” button you added earlier and add the following code:

            dataSet1.Clear(); // Clears the data in the data set
            try
            {
                dataSet1.Tables[0].Columns.Clear(); // Clears column information
            }
            catch { };
            // This code tells the iDB2Connection object which machine we're connecting to
            iDB2Connection1.ConnectionString = "DataSource=" + textBox1.Text;
            // Set the SQL statement to be executed
            iDB2Command1.CommandText = textBox2.Text;
            try
            {
                try
                {
                    // Fill the data set with our results
                    iDB2DataAdapter1.Fill(dataSet1);
                    // The data grid view will set column information automatically
                    dataGridView1.AutoGenerateColumns = true;
                    // Point the data grid view to the first (and only) table in the data set
                    dataGridView1.DataMember = dataSet1.Tables[0].TableName;
                }
                // This will catch communication type errors
                catch (IBM.Data.DB2.iSeries.iDB2CommErrorException CommError)
                {
                    MessageBox.Show(CommError.MessageDetails, CommError.Message);
                };
            }
            // This will catch SQL syntax errors
            catch (IBM.Data.DB2.iSeries.iDB2SQLErrorException SQLError)
            {
                MessageBox.Show(SQLError.Message);
            };

Note that if you changed any of the control’s names you may have to adjust the code.

Time For A Test Drive

Now let’s try it out! Click the “Run” button in VS and if everything goes as planned, you should see your application running. Test it by entering:

• Your iSeries name/IP address
• An SQL statement that selects records from a small table. To test, I use “select * from qgpl.qauoopt”.
• Click “Execute”

The data grid should now be populated with the data from the selected table. A word of caution here…don’t issue a select statement that returns millions of rows. Every row in the result set would be returned to your program before the data is displayed and that would not be good.

The data grid works much like the data grid in Excel. You can resize rows and columns, double-click a column to fit the widest data, etc. Play around with your new toy!

In the next installment of the .NET journey, we’ll add some bells/whistles to our example program.

So…you remember this?

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using IBM.Data.DB2.iSeries;

namespace iSeriesTester
{
    class Program
    {
        static void Main(string[] args)
        {
            String cnnString = "DataSource=xxxxxx";
            bool connected = false;
            iDB2Connection cnn = new iDB2Connection();
            iDB2Command cmd = new iDB2Command();
            cmd.Connection = cnn;
            cnn.ConnectionString = cnnString;
            cmd.CommandText = "select * from qgpl.qauoopt";
            try
            {
                cnn.Open();
                connected = true;
            }
            catch (iDB2ConnectionFailedException)
            {
                MessageBox.Show("Could not connect to system!");
            }
            catch (iDB2CommErrorException e)
            {
                MessageBox.Show(e.MessageDetails);
            }

            if (connected)
            {
                iDB2DataReader reader;
                try
                {
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        try
                        {
                            Console.WriteLine(reader.GetString(0) + " - " + reader.GetString(1));
                        }
                        catch
                        {
                            MessageBox.Show("An error occured retrieving data!");
                        }
                    }
                    reader.Close();
                }
                catch (iDB2SQLErrorException sqlE)
                {
                    MessageBox.Show(sqlE.Message);
                }
            }

            cnn.Close();
            cmd.Dispose();
        }

    }
}

So let’s look at this program in detail. First off is the “using” statement. In .NET a using means which namespace in the .NET framework you’ll be using in your program. It’s not really an “include” like C/C++ or Java. AAMOF, if you search your computer for “System.Windows.Forms” you’ll only find a reference to a .DLL and .TLB.

So how do you know which namespace to include? In the “Microsoft .NET Framework SDK v2.0″ program group there’s an option for “Documentation”. If you open the documentation and search for “String Class” you’ll find that it’s a part of the “System” namespace. So if you want to use the “String” class you’ll need to have a “using System;” line. Also note the “IBM.Data.DB2.iSeries” using statement. If you search your computer for matching files you’ll find a reference to IBM.Data.DB2.iSeries.dll in “C:\Program Files\IBM\Client Access”.

The next line:

namespace iSeriesTester

Defines the namespace for the program. This can be anything but it’s suggested that you organize your program’s namespace into a hierarchy. For example, I could have used the namespace:

namespace InnovativeSystems.NETJourney.iSeriesTester

The “class Program” line is required in C# but doesn’t merit much discussion now. In C# (as in C or C++) there must be a “main” function in each program. In C# your main function will receive an array of strings which equate to the command line options, if any, supplied to your program.

Notice the iDB2Command and iDB2Connection objects declared in the “main” function. These are defined in the “IBM.Data.DB2.iSeries” namespace. The iDB2Command stores the actual SQL statement to be executed and iDB2Connection defines which system we’ll execute the statement against. Once the command is created we attach the connection to the command via “cmd.Connection = cnn;” statement.

The next two lines:

cnn.ConnectionString = cnnString;

cmd.CommandText = 

				

Okay, so you’ve decided that you want to learn .NET and techniques related to connecting to the iSeries. So do I! For this blog, we’re going to concentrate on C#. Why C# over the over available .NET languages? It’s because I have more experience with C/C++ then I do with Visual Basic or Java. I’m sure that the examples presented in this blog can be easily modified to work with any .NET language.

First you need to get the tools. To start with, click here to download Visual Studio 2005 Express C# Edition. As of this writing, VS 2008 is available but all my examples will be created with VS 2005. Feel free to try VS 2008 but be aware that there will be differences and I won’t be able to explain why. Then download and install the .NET 2.0 SDK from here. The SDK has command line tools and documentation that wil be extremely helpful.

Also make sure your Windows version is up to date, especially concerning the .NET framework.

Now for the iSeries stuff. You’ll need to be on at least V5R3 of OS/400 and make sure you have the latest service pack for your version of iSeries Access. I know with V5R4 of iSeries Access that you will need to have the latest service pack as a bug in the .NET provider will cause your .NET app to crash upon termination. IBM’s web site doesn’t guarantee that their provider will work with .NET 2.0 but so far I haven’t had any problems.

Once you have all your goodies installed, start up Windows notepad and paste the following code. The only change you’ll need is to change the connection string (“DataSource=xxxxxx”) replacing “xxxxxx” with your system name or IP address.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using IBM.Data.DB2.iSeries;

namespace iSeriesTester
{
    class Program
    {
        static void Main(string[] args)
        {
            String cnnString = "DataSource=xxxxxx";
            bool connected = false;
            iDB2Connection cnn = new iDB2Connection();
            iDB2Command cmd = new iDB2Command();
            cmd.Connection = cnn;
            cnn.ConnectionString = cnnString;
            cmd.CommandText = "select * from qgpl.qauoopt";
            try
            {
                cnn.Open();
                connected = true;
            }
            catch (iDB2ConnectionFailedException)
            {
                MessageBox.Show("Could not connect to system!");
            }
            catch (iDB2CommErrorException e)
            {
                MessageBox.Show(e.MessageDetails);
            }

            if (connected)
            {
                iDB2DataReader reader;
                try
                {
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        try
                        {
                            Console.WriteLine(reader.GetString(0) + " - " + reader.GetString(1));
                        }
                        catch
                        {
                            MessageBox.Show("An error occured retrieving data!");
                        }
                    }
                    reader.Close();
                }
                catch (iDB2SQLErrorException sqlE)
                {
                    MessageBox.Show(sqlE.Message);
                }
            }

            cnn.Close();
            cmd.Dispose();
        }

    }
}

Then save the file to a location on your PC. For this example I’m going to name the file “iSeriesTester.cs” and place the file in “My Documents”.

Now we’re going to compile and test the application. If you installed the .NET 2.0 SDK correctly then you should find the following option:

Start – All Programs – Microsoft .NET Framework SDK v2.0 – SDK Command Prompt

Once you select this option a DOS command prompt will appear. This prompt sets certain environment attributes for you to easily compile .NET code. Use the “CD” command to change to the directory where you saved the above .cs file. Now enter the following on the command prompt:

csc /r:"C:\Program Files\IBM\Client Access\IBM.Data.DB2.iSeries.dll" iSeriesTester.cs

If everything goes according to plan, the DOS prompt should appear again without any error messages. Now type “iSeriesTester” on the command prompt and press Enter. You should see the contents of QAUOOPT displayed in the prompt window.

Congratulations! You’ve just compiled your first .NET program!

In the next installment of the journey I’ll go into more detail about the program code and the command line option.

As you probably are aware, I enjoy writing PC applications. If you’re reading this, you’ve probably heard of or used FROG. FROG was created with a programming language called Euphoria and uses the database connectivity APIs from iSeries Access. As much as I enjoy programming in Euphoria I realized that in order to keep up with the latest technology available from IBM for connecting to the iSeries, I was going to have to learn .NET. (I know, Java is another choice but I’m going down the .NET road). Also, IBM will probably discontinue the database APIs in the future.

So, the “.NET Journey” is going to be a blog of my own experiences with .Net and the IBM iSeries geared toward helping others learn the same. I’ve already developed some applications with .NET but I am by no means an expert. Some of the journey will be my own learning experiences so if you’re interested, you can learn along with me.

The next installment of the journey will outline where to get the development tools and the requirements for your iSeries.