Skip to content. | Skip to navigation

Sections
Personal tools
You are here: Home Accelerate Articles U2 PDO Driver, Part 2: Write PHP applications to access U2 data

U2 PDO Driver, Part 2: Write PHP Applications to Access U2 Data

PHP is one of the most popular languages for Web applications, and now you can use it to access your U2 data. In part 1 of this series, you learned how to create a PHP Data Objects driver for U2. Now, learn how to put the driver you've created to use in a PHP application.

 PHP U2 data access using InterCall

Level: Intermediate

Double Xia ( sxia@rs.com), Software Engineer, Rocket Software

04 Jan 2007

Introduction

With the PDO driver for U2 written in part 1 of this series, your PHP applications can:

  • Connect to one or more servers
  • Retrieve marks
  • Access files and records
  • Execute database commands
  • Run U2 Basic programs

In this article, you'll develop a small project to demonstrate how to use the driver.

 

The demo project

In the demo project, you'll use asynchronous JavaScript and XML (Ajax) to write an application that retrieves a file list from a U2 account and loads file information when a file name is clicked. This application consists of two screens. The first is the logon screen, where you fill in the username, password, U2 server host name or IP address, and U2 account.

After successfully logging in, the file list from the specified account is presented in a table. When you click a file name hyperlink, the file information is retrieved in the background and displayed on the right side of the screen.

This demo project includes three types of code that run in a browser, Web server, or U2 server, respectively:

  1. Javascript code - Loaded from the Web server and run in a browser.
  2. U2 PDO driver and PHP scripts - Invoked by Apache Web server.
  3. U2 Basic subroutine - Called when PHP invokes a U2 call command.

Setting up the environment

To implement this project, you need to set up a Web server. Here, use Apache http server 2.2.0. First, download Apache 2.2.0 (http://httpd.apache.org/) and install it.

Suppose you install the Apache http server under the directory $APACHE_DIR. You need to build a PHP shared Apache 2.0 Handler module. Go to $PHP_DIR and run the following commands:

  1. ./configure -prefix=$myprefix -with-pdo_u2
    -with-libxml-dir=$myprefix --with-apxs2=$ APACHE_DIR /bin/apxs
  2. make
  3. make install

Theoretically, this should install libphp5.so in $APACHE_DIR/modules and update $APACHE_DIR/conf/httpd.conf accordingly. You may need to manually change the configuration file to tell Apache to load the PHP module. To do so, add the following two lines to the httpd.conf file:

 
LoadModule php5_module        modules/libphp5.so

AddType application/x-httpd-php .php
 

Now restart Apache. To test if the PHP module can be loaded successfully, write a PHP script with the following content:

<?php
phpinfo();
?>
 

In a browser window, enter the address http://your_webserver_url/test.php. If the PHP module is loaded and U2 PDO driver is enabled successfully, you should see the following output:

 

Write U2 Basic subroutine

A Basic subroutine is used to service the requests of loading a file list and getting file information. This subroutine requires four arguments: two input arguments and two output arguments.

 
SUBROUTINE DEMOSUB(X_CODE, X_PARAMS, X_OUTDATA, X_ERROR)
 

Argument X_CODE is the operation code, followed by the parameters of the operation. Argument X_OUTDATA stores the output data and X_ERROR stores the error code of the operation.

An ON operation_code GOSUB statement is used to branch to specific code:

 
ON X_CODE GOSUB INITIALISE,
    GET_FILELIST,               ;* # 2
    GET_FILEINFO,               ;* # 3
    NOTUSED
RETURN
 

Operation GET_FILELIST expects three values in the parameters: file type, a starting number, and the number of file names. This operation first performs a selection on the VOC file and puts all file names in the first selection list. It then reads each file name from the select list and puts its details in the dynamic array X_OUTDATA:

GET_FILELIST:
    FTYPE=X_PARAMS<1>
    BEGNUM=X_PARAMS<2>
    ENDNUM=X_PARAMS<3>


    Do a select and put all file names in the first select list
    . . .
    LOOP
        READNEXT ID ELSE DONE = TRUE
    UNTIL DONE OR NUM >= ENDNUM DO
        READ Rec FROM FVOC, ID THEN
                X_OUTDATA<-1> = ID: @VM: Rec<1>: @VM :Rec<2>: @VM :Rec<3>
                NUM = NUM+1
        END

    REPEAT

RETURN
 

Operation GET_FILEINFO needs only one parameter: file name. It opens the file and calls FILEINFO to get detailed file information. It then composes an XML document segment in the output argument X_OUTDATA:

   
GET_FILEINFO:
    FNAME = X_PARAMS<1>

    OPEN "", FNAME TO FID ELSE
        X_OUTDATA = "Open ":FNAME:" failed"
        X_ERROR = 1
        RETURN
    END

    X_OUTDATA = "<FILEINFO>"

    FTYPE = FILEINFO(FID, 3)

    . . .
    X_OUTDATA := "<HashType>":HashAlg:"</HashType>"
    X_OUTDATA := "<SplitType>":SplitType:"</SplitType>"

    IF FILEINFO(FID, 22) THEN
        RFS = "Yes"
        END ELSE
        RFS = "No"
    END

    X_OUTDATA := "<Recoverable>":RFS:"</Recoverable>"

    MOD = FILEINFO(FID, 6)
    X_OUTDATA := "<Modulo>":MOD:"</Modulo>"

    BLKSZ = FILEINFO(FID, 7)
    IF BLKSZ < 0 THEN BLKSZ = 0
    X_OUTDATA := "<BlockSize>":BLKSZ:"</BlockSize>"


    X_OUTDATA := "</FILEINFO>"

RETURN
 

You can pass data from the U2 server to PHP in either dynamic array format or XML document format. Operation GET_FILELIST uses dynamic array format, while GET_FILEINFO passes XML format. This subroutine should be compiled and cataloged globally.


More PHP classes

Part 1 of this series discussed the U2 marks class u2marks.cls and the U2 data handle class u2dbh.cls. Another class used in the project is the U2 record class u2row.cls. This class uses a PHP multidimensional array to represent a U2 dynamic array. The class constructor has two parameters: the marks and the dynamic array string.

<?php

class u2row {
private $marks;
private $recstr;
private $recarray;

public function __construct($in_mk, $str) {
    $this->marks = $in_mk;
    $this->recstr = $str;
        

    $this->recarray = $this->dynToArray(u2marks::FM_IDX, $this->recstr);   
}

public function containMarks($mknum, $str) {
    $mk = $this->marks->getMark($mknum);
    if (strpos($str, $mk) !== false)
        return true;
    else if ($mknum < $this->marks->getMaxMarkNum()) {
        return $this->containMarks(1 + $mknum, $str);
    } else {
        return false;
    }
        
}

// convert dynamic array to php array
public function dynToArray($mknum, $str) {
    $mk = $this->marks->getMark($mknum);
    //echo "Current mark->".$mknum."->".$mk."<- str=".$str."\n";
    if ($this->containMarks($mknum, $str) === false) {
        return $str;
    } else {
        $tmpary = explode($mk, $str);

        if ($mknum < $this->marks->getMaxMarkNum()) {
            $newary = array();
            while (list($ky, $val) = each($tmpary)) {
                unset($tmpary[$ky]);

                $val = $this->dynToArray(1 + $mknum, $val);

                $newary[$ky] = $val;
            }

            $tmpary = $newary;
                
        }

        return $tmpary;
    }
}

...
?>
 

The class filelist.cls is developed to represent a list of files. The constructor requires three parameters: a U2 database handle object, a starting number, and the number of files you want to get. To actually retrieve the file list, operation GET_FILELIST of U2 subroutine DEMOSUB is called.

   
<?php
. . .

class filelist {
private $dbh;
private $rec;

public function __construct($in_dbh, $beg, $num) {
   $this->dbh = $in_dbh;
   $stmt = $this->dbh->prepare("CALL DEMOSUB :code :params :outdata :outcode");
   $stmt->bindParam(':code', $xcode);
   $stmt->bindParam(':params', $xparams);
   $stmt->bindParam(':outdata', $outdata,
       PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 102400);
   $stmt->bindParam(':outcode', $outcode,
       PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 12);
   $xcode = 2;

   $mks = $in_dbh->getMarks();
   $xparams = '' . $mks->getMark(u2marks::FM_IDX) . $beg .
       $mks->getMark(u2marks::FM_IDX) . ($num + $beg);

   /*  echo 'Params->'.$xparams; */
   $outdata="";
   $outcode="";

   if ($stmt->execute()) {
    $this->rec = new u2row($this->dbh->getMarks(), $outdata);
    print_r($rec);
   } else {
    $this->rec = null;
    echo "Failed to call sub\n";
   } 
}
    . . .
?>
 

Two input and two output parameters are passed to the subroutine DEMOSUB. The first parameter is the operation code 2, which means to get a file list. The second parameter is the starting number and the number of file names. Output parameter ":outdata" stores the result file list in dynamic array format. Output parameter ":outcode" stores the error code.

Private member $rec is a PHP array that contains values of the dynamic array. Member function outputFileListXML() in this class converts the PHP array to an XML document. It traverses the multidimensional array and puts each value in XML element "file":

public function outputFileListXML() {
    if (!is_null($this->rec)) {
         echo "<files>";
         $recary = $this->rec->getRecordArray();
         if (is_array($recary)) {
        while (list ($ky, $val)=each($recary)) {
             $recval = "<file fname=\"{$val[0]}\" ftype=\"{$val[1]}\"
fpath=\"{$val[2]}\" dictpath=\"{$val[3]}\"></file>\n";
            echo str_replace("&", "&", $recval);

        }
         }
        echo "</files>\n";
    }
}
 

Class fileinfo.cls is used to retrieve file information. The constructor has two parameters: U2 database handle object and a file name. It calls U2 subroutine DEMOSUB to retrieve the file information:

<?php
. . .
class fileinfo {
private $dbh;
private $rec;

public function __construct($in_dbh, $fname) {
   $this->dbh = $in_dbh;
   $stmt = $this->dbh->prepare("CALL DEMOSUB :code :params :outdata :outcode");
   $stmt->bindParam(':code', $xcode);
   $stmt->bindParam(':params', $xparams);
   $stmt->bindParam(':outdata', $outdata,
       PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 102400);
   $stmt->bindParam(':outcode', $outcode,
       PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 12);
   $xcode = 3;
   $xparams = $fname;
echo
   $outdata="";
   $outcode="";

   if ($stmt->execute()) {
    $this->rec = $outdata;
   } else {
    $this->rec = null;
    echo "Failed to call sub\n";
   } 
}
. . .
public function outputFileInfoXML() {
    echo $this->rec;
}
?>
 

Operation code 3 gets file information. The result is returned in XML format and is stored in ":outdata." Since the result from the Basic subroutine is already in XML format, function outputFileInfoXML() doesn't need to do any conversion.


 

Write Javascript code

Javascript files are stored in the Web server. They are loaded into the browser along with the HTML files that contain them. Javascript functions can be called as needed. Two Javascript files are essential to this demo project: xmlhttp.js and loadapp.js. The xmlhttp.js file hosts one function, makeRequest(), which creates an XMLHttpRequest instance and retrieves data from the Web server. The loadapp.js file contains the functions that call makeRequest() to load the file list and get file information.

  1. Create an instance of the XMLHttpRequest object.

    The XMLHttpRequest object is used to retrieve data from the Web server in the background. With this object, part of the screen can be updated without reloading the whole HTML page. Notice that there are different ways to create an instance of this object.



    Listing 7. Create XMLHttpRequest instance xmlhttp.js
    if (window.XMLHttpRequest) { // Mozilla, Safari,...
                http_request = new XMLHttpRequest();
                if (http_request.overrideMimeType) {
                    http_request.overrideMimeType('text/xml');
                }
            } else if (window.ActiveXObject) { // IE
                try {
                    http_request = new ActiveXObject("Msxml2.XMLHTTP");
                } catch (e) {
                    try {
                        http_request = new ActiveXObject("Microsoft.XMLHTTP");
                    } catch (e) {}
                }
            }
    

    After the XMLHttpRequest object is created, you must specify its action handler so the object can talk to the Web server.



    Listing 8. Load data from Web server xmlhttp.js
    function makeRequest(url) {
        http_request = null;
    
            create an XMLHttpRequest object
    
            .. .
            http_request.onreadystatechange = statusHandler;
            
            /* following line should disable IE caching */
            //http_request.setRequestHeader("If-Modified-Since",
                "Sat, 1 Jan 2000 00:00:00 GMT");
            
            http_request.open('GET', url, true);
            http_request.send(null);
    }
    

  2. Render the file list into HTML.

    The file list is transferred in XML format. For example, enter the address http://your_webserver_url/getfilelist.php?s=0&n=10 in a browser. You will see the XML document for the first 10 file names.



    <?xml version="1.0" ?>
    
    <files><file fname="&report&" ftype="F" fpath="_REPORT_"
        dictpath="D__REPORT_"></file>
    <file fname="AE_COMS" ftype="F" fpath="AE_COMS"
        dictpath="D_AE_COMS"></file>
    <file fname="AE_DOC" ftype="F" fpath="@UDTHOME/sys/AE_DOC"
        dictpath="@UDTHOME/sys/D_AE_DOC"></file>
    <file fname="AE_SCRATCH" ftype="DIR" fpath="AE_SCRATCH"
        dictpath="D_AE_SCRATCH"></file>
    <file fname="AE_XCOMS" ftype="F" fpath="@UDTHOME/sys/AE_XCOMS"
        dictpath="@UDTHOME/sys/D_AE_XCOMS"></file>
    <file fname="BP" ftype="DIR" fpath="BP" dictpath="D_BP"></file>
    <file fname="BP_SOURCE" ftype="DIR" fpath="BP_SOURCE"
        dictpath="D_BP_SOURCE"></file>
    <file fname="CATEGORIES" ftype="F" fpath="CATEGORIES"
        dictpath="D_CATEGORIES"></file>
    <file fname="CLIENTS" ftype="F" fpath="CLIENTS"
        dictpath="D_CLIENTS"></file>
    <file fname="COURSES" ftype="F" fpath="COURSES"
        dictpath="D_COURSES"></file>
    </files>
    

    The Document Object Model (DOM) needs to be used to read the XML document node tree and compose HTML elements that the user sees. This is achieved by calling the function parseFileList() to convert the file list XML document to an HTML table. This function knows the XML document structure; for example, it knows that "files" is the top-level element, and that it consists of a number of "file" elements. Each file element has four attributes: "fname", "ftype", "fpath" and "dictpath."



    Listing 9. Convert file list to HTML table loadapp.js
    function parseFileList(reqXML, listname) {
        var issueList = false;
    
        if (reqXML) {
            try {
                issueParent = reqXML.getElementsByTagName('files');
            issues = issueParent[0].getElementsByTagName('file');
            issueList = new Array();
            for (var x = 0; x < issues.length; x++) {
                    issueList[issues[x].getAttribute("fname")] =
                    new Array(issues[x].getAttribute("ftype"), 
                issues[x].getAttribute("fpath"),
                issues[x].getAttribute("dictpath"));
                }
            } catch (err) {
                alert("File list process error happened:");
            }
        } else {
            alert("null issue list XML");
        }
        
        if (issueList) {
            var t = "<table width=\"100%\" height=\"100%\"
            cellspacing=5 cellpadding=0 valign=top>";
            var cnt = 0;
            for (var x in issueList) {
                if ((cnt % 2) == 0) t += "<tr class=lineeven>";
                else t += "<tr class=lineodd>";
                        
                cnt++;
            t += "<td width=\"30px\"><a href='javascript:void(0)' 
    onClick=\"getfileinfo('"+x+"')\">&nbsp;"+x+"</a></td>";
                t += "<td>"+issueList[x][0]+"</td>";
                t += "<td>"+issueList[x][1]+"</td>";
                t += "<td>"+issueList[x][2]+"</td>";
                t += "</tr>";
            }
                
            t += "</table>";
            t += "<p>";
            if (beginNum > 0) {
                t += "<a href='javascript:void(0)' onClick=\"retrieveFilelist("
    +(beginNum-fetchNum)+","+fetchNum+")\">Previous</a>";
                t += "&nbsp;&nbsp;&nbsp;&nbsp;";
            }
            t += "<a href='javascript:void(0)' onClick=\"retrieveFilelist("
    +(beginNum+fetchNum)+","+fetchNum+")\">Next</a>";
                
            //alert("New issue list=>"+t);
            try {
                document.getElementById(listname).innerHTML=t; 
            } catch (err) {
            }
        }
    }
    

    If the conversion is successful, the result table is put into a block named "filelist." This is done by the following statement:



    document.getElementById(listname).innerHTML=t;
    

  3. Render file information into HTML.

    The file information is also transferred in XML format from the Web server. For example, "http:// your_webserver_url /getfileinfo.php?f=CLIENTS" should retrieve file information for CLIENTS:



    <FILEINFO>
    <FileType>HASHED</FileType>
    <HashType>0</HashType>
    <SplitType>KEYONLY</SplitType>
    <Recoverable>No</Recoverable>
    <Modulo/>
    <BlockSize>0</BlockSize>
    </FILEINFO>
    

    The function parseFileInfo() is used to convert the XML document to a table. This function assumes only that the file info XML document has elements and doesn't know the actual element names. It uses DOM APIs to traverse the child elements of FILEINFO to get data from the document, and composes a table.



    Listing 10. Convert file list to HTML table loadapp.js
    function parseFileInfo(reqXML, listname) {
        var t = "<table width=\"100%\" height=\"100%\" cellspacing=5 cellpadding=0>";
    
        if (reqXML) {
            try {
                infoParent = reqXML.getElementsByTagName('FILEINFO')[0];
                for (var x = 0; x < infoParent.childNodes.length; x++) {
                    thisnd = infoParent.childNodes[x];
                    valu = "";
                    if(thisnd.nodeType == 3) {  // text node
                        valu = thisnd.data;
                    }  else if (thisnd.nodeType == 1) { // element
                        if (thisnd.firstChild)
                        valu = thisnd.firstChild.data;
                    } 
                    t += "<tr>";
                    t += "<td>"+thisnd.nodeName+"</td>";
                    t += "<td>"+valu+"</td>";
                    t += "</tr>";
                }
            } catch (err) {
                alert("Fileinfo process error happened:");
            }
        } else {
            alert("Null fileinfo");
        }
        
        t += "</table>";
        //alert("New fileinfo=>"+t);
        try {
            document.getElementById(listname).innerHTML=t; 
        } catch (err) {
        }
    }
    

    If the conversion is successful, the result table is put into a block named "fileinfo." This is accomplished by the following statement:



    document.getElementById(listname).innerHTML=t;
    
Back to top

 


Put them together

With all ingredients ready, you can assemble them into a working application. Compile and globally catalog the U2 Basic subroutine DEMOSUB on the U2 server. You also need to place all PHP classes and Javascript files on the Web server. Several more pieces are needed to make the browser happy:

  1. Login screen.

    An HTML form is used to present the login screen.



    Listing 11. Login HTML file index.html
    <p>U2 data access demo using PDO Driver for U2
    <p><b>Sign in</b>:
    <form name="signin" action="demo.php" method="POST">
    <table id="form-table">
    <tr>
    <td>Username: </td><td><input type="text" name="usr" ></td>
    </tr>
    <tr>
    <td>Password: </td><td><input type="password" name="pwd"></td>
    </tr>
    <tr>
    </tr>
    <tr>
    <td colspan="2" align="center"><input type="submit" value="Submit">
    </td>
    </tr>
    </table>
    </form>
    

    PHP script demo.php is used to service the logon request. Demo.php should authenticate the username and password, but right now it just sets cookies for the username, password, host name, and account. It also draws an empty table for the file list. When it has loaded completely, it calls the Javascript function loadApp() to actually load the file list.



    Listing 12. Login servicing script demo.php
    <?php
    $usr = $_POST["usr"];
    $pwd = $_POST["pwd"];
    $svr = $_POST["svr"];
    $acct = $_POST["acct"];
    
    /*
     * put login and session control code here
     * right now, we assume you put the right username/password
     */
    setCookie("usr", $usr);
    setCookie("pwd", $pwd);
    setCookie("svr ", $svr);
    setCookie("acct", $acct);
    
    ?>
    <html>
    <head>
    <title>U2&trade; Data Access</title>
    <link rel="stylesheet" href="style.css" type="text/css">
    <SCRIPT language="JavaScript1.2" SRC="xmlhttp.js" type="text/javascript"></SCRIP
    T>
    <SCRIPT language="JavaScript1.2" SRC="loadapp.js" type="text/javascript"></SCRIP
    T>
    </head>
    
    <body onLoad="loadApp()">
    
    . ..
    
    <table class="fullWidth" cellspacing=5 cellpadding=0 width="100%" height="100%">
    <tr>
    <td id="leftpanel" width ="190px" valign="top">
       <div id="filelist" style="border-color: #84B0C7; border-width:
       3px 3px 3px 3px; border-style: outset">
       </div>
    </td>
    
    <td width ="20px" valign="top">
    </td>
    
    <td id="rightpanel" valign="top">
       <div id="fileinfo">
       </div>
    </td>
    </tr>
    
    </table>
    
    <br>
    
    
    </body>
    
    </html>
    

    The screen is divided into several blocks. The block named "filelist" is where you put the file list, and the block named "fileinfo" is where you put the file information.

  2. Get file list script.

    This PHP script is called by the loadApp() function to get a list of file names. This script is also called when the Next or Previous hyperlink in the browser is clicked. Basically this script instantiates an u2dbh object and a filelist object, and exports the file list as an XML document.



    Listing 13. Get file list getfilelist.php
    <?php
    header("Content-type: text/xml");
    echo "<?xml version=\"1.0\" ?>\n";
    . . .
    $dbh = new u2dbh($dsn, $usr, $pwd);
    
    $flist = null;
    
    if (!is_null($dbh->getDBH())) {
       $beg = $_GET["s"];
       $num = $_GET["n"];
       if (is_null($beg) || $beg < 0)
            $beg = 0;
       if (is_null($num) || $num < 0)
            $num = 20;
    
       $flist = new filelist($dbh, $beg, $num);
    
       $flist->outputFileListXML();
    }
    
    ?>
    

  3. Get file information script.

    This PHP script is called when a hyperlink for a file name is clicked in the browser. It instantiates an u2dbh object and a fileinfo object, and exports the file information as an XML document.



    Listing 14. Get file information getfileinfo.php
    <?php
    header("Content-type: text/xml");
    echo "<?xml version=\"1.0\" ?>\n";
    
    . . .
    
    $dbh = new u2dbh($dsn, $user, $pwd);
    
    /*echo "PRINT OUT DBH:";
    print_r($dbh);
    */
    $flist = null;
    
    if (!is_null($dbh->getDBH())) {
       $finfo = new fileinfo($dbh, $_GET["f"]);
    
    
       $finfo->outputFileInfoXML();
    }
    
    ?>
    

Put index.html, getfilelist.php, and getfileinfo.php on the Web server. Point your browser to http://your_webserver_url/index.html to see the login screen. Input a valid username and password, and a valid U2 server and account, and you should see a list of files. From there, you can click the hyperlink for a file name to check the file information.

Back to top

 


Conclusion

This article has covered how to write PHP applications using a U2 PDO driver. One PHP class u2row has been introduced. This class can be extended to fully support U2 dynamic array.

Special thanks

Special thanks to technical reviewers Shelley Thompson and Helen Beylkin.

Back to top


Downloads

Description Name Size Download method
Demo Project U2 Basic subroutine demosub.zip 1KB HTTP
Demo project PHP scripts u2pdo_php.zip 6KB HTTP
Demo project Ajax code u2pdo_ajax.zip 3KB HTTP

 

Resources

Learn

 

 

Get products and technologies

 

About the author

Double Xia photo

Double Xia works for Rocket U2 in Denver, Colorado. He has more than ten years of experience in developing U2 data servers. He covers an array of development areas, including locking mechanisms, XML, Web Services, and NLS. Most recently, he has focused on developing Eclipse-based tools for U2.

 

 

Document Actions