Insert data in OpenEdge using SQL and ODBC

Create/Insert data with SQL in OpenEdge

We will attempt to insert data into 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 create a new department into DEPARTMENT table.

Insert data into OpenEdge department table

The fields of DEPARTMENT table are:

  1. DeptCode
  2. DeptName

Let’s run this SQL query:

INSERT INTO pub.department (DeptCode, DeptName)
values (800, 'Test1');

Execute this query. Then, select data out of department table and notice that our new department has been added.

SELECT *
FROM pub.department
WHERE deptcode = 800;

Create data with PHP and SQL in OpenEdge

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

// build SQL to create new department
$sql = sprintf(
		"insert into pub.department (deptcode, deptname)
		values (%s, '%s')",
		$newDeptCode,
		$newDeptName
	);

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

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

// print outcome
echo sprintf("%d record(s) inserted\n", $inserted);

?>

Execute this script by typing php create-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 number of records inserted.

Try running this script again. You will notice that an error is generated like so: [DataDirect][ODBC 20101 driver][20101]Cannot insert duplicate key in object “PUB.DEPARTMENT” with unique index “DeptCode”. This error indicates that DeptCode has a unique index on it and we cannot insert department code of 900 again. Awesome!

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 create-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;
$newDeptName = "Test O'Brian";

// build SQL to create new department
// NOTICE: There are ? instead of actual values
$sql = 'insert into pub.department (deptcode, deptname) values (?, ?)';

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

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

// print outcome
echo sprintf("%d record(s) inserted\n", $inserted);
?>

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

You will notice that a new department is created. 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 inserting 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 create-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;
$newDeptName = 'Test2';

// build SQL to create new department
// NOTICE: There are ? instead of actual values
$sql = 'insert into pub.department (deptcode, deptname) values (?, ?)';

// 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();
$inserted = $stmt->execute(array($newDeptCode, $newDeptName));
$db->commit();

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

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

// print outcome
echo sprintf("%d record(s) inserted\n", $inserted);
?>

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

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