Delete data from OpenEdge using SQL and ODBC

Delete data with SQL from OpenEdge

We will attempt to delete data from OpenEdge database table similar to how we updated 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 had inserted department code of 900 with department name of Test2. We will delete department 900.

Delete data from OpenEdge department table

The fields of DEPARTMENT table are:

  1. DeptCode
  2. DeptName

Let’s run this SQL query:

DELETE FROM pub.department
WHERE DeptCode = 900;

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

SELECT *
FROM pub.department
WHERE deptcode = 900;

Delete data with PHP and SQL from OpenEdge

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

// build SQL to create new department
$sql = sprintf(
		"delete from pub.department
		 where deptcode = %d",
		$newDeptCode
	);

// 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
$deleted = $stmt->execute();
if ($deleted === false) {
	echo sprintf("No record was deleted. Error: %s\n", print_r($stmt->errorInfo(), true));
	exit;
}

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

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

Execute this script by typing php delete-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 that the department was deleted.

Risk

The risk with above method is that there is no error handling. 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. Create a file called delete-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 = 1000;

// build SQL to create new department
// NOTICE: There are ? instead of actual values
$sql = 'delete 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
// NOTICE: execute now has an array that will replace the ?
$updated = $stmt->execute(array($newDeptCode));
if ($updated === false) {
	echo sprintf("No record was deleted. Error: %s\n", print_r($stmt->errorInfo(), true));
	exit;
}

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

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

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

You will notice that department 1000 is deleted. 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 deleting department code check whether it is a valid integer or big integer and that its value is sane.

Add commitment control

It is assumed that you are using READ COMMITTED transaction isolation. Create a file called delete-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 = 1200;

// build SQL to create new department
// NOTICE: There are ? instead of actual values
$sql = 'delete 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
// 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($newDeptCode));
$db->commit();

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

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

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

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

You will notice that department 1200 is deleted. Edit the file and change from $db->commit(); to $db->rollback();, and change the department code to a valid existing code. Re-run the script and notice that the database has not deleted that code 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