sql Test Page

People often confuse the term "object" and "class." Originally, the term "object design" simply meant grouping procedures and data into a cohesive structures called "objects." The "class" contruct was created for an implementation of Object Oriented Programming called "C for Classes" by Bjarne Stroustrup. C++ was optimized for creating GUI interfaces on desktop computers.

The World Wide Web is not a desktop computer. The rigid class structure adopted by PHP impedes development; So, I created a free form object which I placed in the sql.php file. I call this approach The Resource Model.

This is a test page for sql.php. Use the Code Viewer to see the test. The Test Results are below the dotted line.

Test Results

These are tests results for the free form sql object. NOTE: this object requires the msg Notification Object.

sqlValue Test

The first test will use sqlValue to draw this sites name from the main database. I will format the link in sqLite3:

My Site Name is: Internet Rivers. I was born in The Denver Metro. I am currently serving time in The Salt Lake Valley.

I often make crazy typos when I am programming. I will make a bad SQL statement and use msgNote() to display the error. You should see an error message followed by debug note.

In the next test, I will attach the code database and see how many times people have viewed the sql Code Viewer Page. It's been viewed 16418 times.

Test sqlAll() Against a Romote Database

The next code will test sqlAll(). This funnction selects an entire array. To make the test interesting, I created a remote postgres database with elephantSQL.com. This data is stored several hundred miles aways.

test_idtest_nmhit_cnt

Updating with sqlExec()

Wow, that was fun. I can access data locally and across the country with the same driver. I know. In this next test, I will use sqlExec() to increment the counter in row two.

msgNote() will tell us if the operation was successful. I love msgNote. I run it after every sql statement when I am writing new code. It should say we updated one row.

sqlValue() says the value of row two is now: .

The DB_ONE_MAX directive tells sqlExec to rullback if it updates more than one entry. I try to update three rows. It should rollback. The test should scream a warning at me, then a debug message showing my buggy script.

I will try to rollback and rollback without an active transaction. I will give a warning if the rollback fails and a debug comment if the commit fails.

dbConn() as a PDOStatement Factory

The sqlExec() function is not really necessary. A more efficient approach is to draw a prepared PDOStatement directly from dbConn(). The following pseudo code uses two PDOStatements concurrently in a complex loop structure:

$sql = 'UPDATE Table ...'; // some SQL Statement
$sql2 = 'INSERT INTO Table ... '; // a statement for an inner loop
$arr = array(); // appropriate data for SQL.
$stmt = dbConn('main',$sql);
$inner = dbConn('main',$sql2);
while ($condition) {
  // I create message for outer loop.
  $arr = [ data for outer loop ];
  msgToggle($stmt->execute($arr,'Success Message','Failure Message');
  while (inner condition) {
    $innerArr = [ data for inner loop ];
    $inner->execute($innerArr);
  }
}

I do not believe that functions are superior to objects. The problem I face is simply that database connection needs to be global; So, I encapsulate the PDO object in a function; so that I can generate PDOStatement Objects at will throughout the program.

In Closing

PHP closes all objects at the end of the script; However it is good practice to close connections.

I will now close the connections. This test session executed 13 sql commands. The next block shows script execution times. The calls to the remote server took up most of the time.

BreakScript TimeDiff
Finish Setup0.00669693946838380.0066969
sqlValue Test0.00859785079956050.0019009
Typo Test [rem]0.00866699218750.0000691
Attach DB Test0.00921988487243650.0005529
Remote Database Test0.00935387611389160.0001340
DB_ONE_MAX & Rollback Test0.00950884819030760.0001550

blog ~ Community Color