Select data with SQL from OpenEdge

We will attempt to select data from OpenEdge database table. Let’s assume that you have a client such as DBeaver set up. Connect to your OpenEdge database using ODBC. In my examples, I am using Sports2000 database. Let’s select departments from DEPARTMENT table.

The fields of DEPARTMENT table are:

1. DeptCode
2. DeptName

Let’s run this SQL query:

SELECT *
FROM pub.department;

Execute this query. You will see all data from the table DEPARTMENT.

Select a particular department

SELECT *
FROM pub.department
WHERE deptcode = 800;

Select data with PHP and SQL from OpenEdge

Now that we know how to get all departments and one particular department with SQL, we will attempt the same with PHP and SQL. Create a file called select-department.php with content as follows:

<?php

putenv('ODBCINI=/etc/odbc.ini');
putenv('ODBCINST=/etc/odbcinst.ini');
putenv('LD_LIBRARY_PATH=/usr/dlc/odbc:/usr/dlc/odbc/lib');

// build SQL to create new department
$sql = 'select * from pub.department';

// establish connection and prepare statement. Handle errors with preparing
$db = new PDO('odbc:Sports', 'sysprogress','test');
$stmt = $db->prepare($sql);
if ($stmt === false) {
        echo sprintf("Error: %s\n", print_r($db->errorInfo(), true));
        exit;
}

// execute statement. Handle errors with execution
echo "Displaying departments\n";
$result = $stmt->execute();
if ($result === false) {
        echo sprintf("Error: %s\n", print_r($stmt->errorInfo(), true));
        exit;
}

// fetch data
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
        echo sprintf("DeptCode: %d\tDeptName: %s\n",
                $row['DeptCode'],
                $row['DeptName']
        );
}

?>

Execute this script by typing php select-department.php

You will see the department codes and names printed out once executed.

Sanitize SQL with parameterization in PHP PDO and OpenEdge

We will now select department information for only a given department. Create a file called select-department-parameters.php with the following text:

<?php

putenv('ODBCINI=/etc/odbc.ini');
putenv('ODBCINST=/etc/odbcinst.ini');
putenv('LD_LIBRARY_PATH=/usr/dlc/odbc:/usr/dlc/odbc/lib');

$code = 900;

// build SQL to create new department
$sql = 'select * from pub.department where deptcode = ?';

// establish connection and prepare statement. Handle errors with preparing
$db = new PDO('odbc:Sports', 'sysprogress','test');
$stmt = $db->prepare($sql);
if ($stmt === false) {
        echo sprintf("Error: %s\n", print_r($db->errorInfo(), true));
        exit;
}

// execute statement. Handle errors with execution
echo "Displaying departments\n";
$result = $stmt->execute(array($code));
if ($result === false) {
        echo sprintf("Error: %s\n", print_r($stmt->errorInfo(), true));
        exit;
}

// fetch data
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
        echo sprintf("DeptCode: %d\tDeptName: %s\n",
                $row['DeptCode'],
                $row['DeptName']
        );
}

?>

Execute this script with php select-department-parameters.php

You will notice that data for deptcode 900 is extracted. Notice that the SQL statement only has a placeholder in the form of a question mark. Those question marks are replaced with actual values during execution. This method of parameterization allows us to send to the database data that could otherwise need unnecessary manual cleansing.

Validation and cleansing of parameters

There is no substitute to performing your own validation. Please absolutely perform validations. For example, before selecting department code check whether it is a valid integer or big integer and that its value is sane.

Selecting data with various methods

We will cover examples of JOINs, GROUP BY, WHERE clause etc. in other articles. This article helps you select data from OpenEdge using SQL and ODBC with any modern programming language.

Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP