Executing a DB2 Stored Procedure from Excel with Parameters

Recently I had to help someone by creating a small piece of VBA code that would fetch rows returned by an ISeries stored procedure. The stored procedure however required two parameters to be set; a from date and a to date. Since this took some time to research I thought I’d post a simplified version on here for myself as well as anyone who needed the reference.

Note: It is highly recommended that you use parameterized queries and not simply a concatenation of values. https://support.microsoft.com/en-us/kb/181734

Code Comment

The following code gets an user input start date (in YYYYMMDD format) from cell B1 and an end date from cell B2. It then places those dates into the stored procedure as parameters. Any resulting rows (with the exception of headers) are displayed starting from cell A6.

An important thing to note here is the use of

QUERYTIMEOUT=0

in the connection string. Using this keeps your procedure call from timing out.

Additionally note how instead of using a loop, rows returned are simply placed from the recordset to cells A6+ using

Range("A6").CopyFromRecordset Rs

The Code

Sub UpdateQuery()
    
    If IsNumeric(Range("B1").Value) Then
        intStart = Range("B1").Value
    Else
        Exit Sub
    End If
    
    If IsNumeric(Range("B2").Value) Then
        intEnd = Range("B2").Value
    Else
        Exit Sub
    End If
    
    Dim ConnectionString As String
    ConnectionString = "Driver=iSeries Access ODBC Driver;System=ISERIES.COMPANY.COM; QUERYTIMEOUT=0;UID=Username;pwd=Password;"
    
    Dim Cn
    Set Cn = CreateObject("ADODB.Connection")
    Cn.ConnectionTimeout = 0
    Cn.Open ConnectionString
    Set Geti5Connection = Cn
    
    Dim Cm
    Set Cm = CreateObject("ADODB.Command")
    Set Cm.ActiveConnection = Geti5Connection
    Cm.CommandText = "call mylib.myproc(" & intStart & "," & intEnd & ")"
    Set Geti5Command = Cm
    Set Rs = Cm.Execute
    
    Range("A6").CopyFromRecordset Rs
    Set Cm = Nothing

End Sub

How to Make a Quick and Dirty ArrayList in PHP

At work I usually work with .NET. My own projects however, tend to be PHP for the most part. Tonight I needed to mimic .NET’s ArrayList object. Since PHP does not have such an object (that I know of) I decided to cook one up for myself. My example depicts a book (object) and a shelf (array) scenario for easier comprehension.

<?php
// Holds all our books
Class Shelf {
	private $ar = array();
	public function add($book) {
		array_push($this->ar, $book);
	}
	public function listBooks() {
		return $this->ar;
	}
}
// Book class, kept simple
Class Book {
	public $title;
	public $author;
}

// Declare a new book and add details
$book1 = new Book;
$book1->title = "Death of a Salesman";
$book1->author = "Arthur Miller";

// Declare another book and add details
$book2 = new Book;
$book2->title = "Fight Club";
$book2->author = "Chuck Palahniuk";

// Add the books to our book shelf
$shelf = new Shelf;
$shelf->add($book1);
$shelf->add($book2);

// Take a peek inside the book shelf
print_r($shelf->listBooks());
// TIP: JSON output anyone?
echo json_encode($shelf->listBooks());
?>