Update data in OpenEdge using SQL and ODBC

Update data with SQL in OpenEdge

We will attempt to update data into OpenEdge database table similar to how we inserted data into OpenEdge. 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 update the last department we created into DEPARTMENT table. We last inserted department code of 900 with department name of Test2. We will update department name to Updated-Test2.

Update data in OpenEdge department table

The fields of DEPARTMENT table are:

  1. DeptCode
  2. DeptName

Let’s run this SQL query:

UPDATE pub.department
SET DeptName = 'First Try'
WHERE DeptCode = 900;

Execute this query. Then, select data out of department table and notice that department 900 has been updated.

SELECT *
FROM pub.department
WHERE deptcode = 900;

Update data with PHP and SQL in OpenEdge

Now that we update a department with SQL, we will attempt the same with PHP and SQL. Create a file called update-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');

$newDeptCode = 900;
$newDeptName = 'Updated-Test2';

// build SQL to create new department
$sql = sprintf(
		"update pub.department
		 set deptname = '%s'
		 where deptcode = %d",
		$newDeptName,
		$newDeptCode
	);

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

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

// ensure that a record was impacted
if ($stmt->rowCount() !== 1) {
	echo "Update executed cleanly but no record was updated\n";
	exit;
}

// print outcome
echo sprintf("%d department code updated\n", $newDeptCode);

Execute this script by typing php update-department.php

Note the additional error handling in this piece of code. We report error if we cannot prepare a statement OR execution of the SQL statement fails. After reporting an error, we exit. If all goes well, we show the department updated.

Risk

The risk with above method is that there is no error handling and no sanitization of department name before data is entered in the database. SQL Injection could result from such queries when users are the ones providing data. Let’s see ways to prevent SQL Injection.

Sanitize SQL with bound parameters in PHP PDO and OpenEdge

We will now bind parameters to our SQL statement instead of just passing verbatim string to department code and number. Create a file called update-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');

$newDeptCode = 900;
$newDeptName = "Test O'Brian";

// build SQL to create new department
// NOTICE: There are ? instead of actual values
$sql = 'update pub.department set deptname = ? where deptcode = ?';

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

// execute statement. Handle errors with execution
// NOTICE: execute now has an array that will replace the ?
$updated = $stmt->execute(array($newDeptName, $newDeptCode));
if ($updated === false) {
	echo sprintf("No record was updated. Error: %s\n", print_r($stmt->errorInfo(), true));
	exit;
}

// ensure that a record was impacted
if ($stmt->rowCount() !== 1) {
	echo "Update executed cleanly but no record was updated\n";
	exit;
}

// print outcome
echo sprintf("%d department code updated\n", $newDeptCode);
?>

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

You will notice that department 900 is updated. Notice that the SQL statement does not have numbers or strings to store. It just 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 updating department code check whether it is a valid integer or big integer and that its value is sane. Similarly, check whether department name is less than the max length of the column and has sane information.

Add commitment control

It is assumed that you are using READ COMMITTED transaction isolation. Create a file called update-department-parameters-commit.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');

$newDeptCode = 900;
$newDeptName = 'Test2';

// build SQL to create new department
// NOTICE: There are ? instead of actual values
$sql = 'update pub.department set deptname = ? where deptcode = ?';

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

// execute statement. Handle errors with execution
// NOTICE: execute now has an array that will replace the ?
// NOTICE: we are starting a new transaction and ending it with commit
$db->beginTransaction();
$updated = $stmt->execute(array($newDeptName, $newDeptCode));
$db->commit();

if ($updated === false) {
	echo sprintf("No record was updated. Error: %s\n", print_r($stmt->errorInfo(), true));
	exit;
}

// ensure that a record was impacted
if ($stmt->rowCount() !== 1) {
	echo "Update executed cleanly but no record was updated\n";
	exit;
}

// print outcome
echo sprintf("%d department code updated\n", $newDeptCode);
?>

Execute this script with php update-department-parameters-commit.php

You will notice that department 900 is updated. Edit the file and change from $db->commit(); to $db->rollback();, and change the department name to ‘Test2-rolling’ . Re-run the script and notice that the database has not updated any new data because the transaction was rolled back. This is the beauty and simplicity of transactions with SQL, PHP, ODBC and OpenEdge.

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