Poll the Audience

It has been awhile. I have started a new Job and things are going great... a little hectic but great nonetheless. Recently I have begun a project in my Software Engineering class that should prove to be rather interesting. The basic idea behind it is to create an ad hoc system that lets a Professor to issue a question and then allows the students to answer the question in a "Poll the Audience" fashion.

We plan to implement this using a web interface and ASP.net. We are planning to use C# for the back end code. The main obstacle we will face in the future is getting the application to interact with the client computers over the local area network.

Our goal is to have the professor create the question on their computer. The question will be saved in an XML format. When the student navigates to the website on their computer, it will pull the XML file from the professors hard drive and format it correctly in their browser. We are currently still in the planning stages but you can view our project wiki here.

I will try to post updates.

Until next time...

Updates

The last few weeks have been very hectic. I have numerous projects going on at school and I also underwent a job/career change. I have moved from a government job dealing with satellite imagery to a job in the banking industry.

Hopefully I will be able get back on track and begin posting more problems and solutions soon.

Until then...

Updates and more Pear MDB2 trouble

I know it has been awhile since I have last posted but I guess I have not run into any major errors lately. However I have run into one particularly tricky error that I can't seem to fix.

The main problem is that the code runs perfectly on my local webserver, its just when I move it to the external webserver that I run into the problem. I am using Pear MDB2 and am just trying to execute a Prepare statement. All of my select statements work great.

Here is the error I am encountering:

Catchable fatal error: Object of class MDB2_Error could not be converted to string in ... on line 917

From what I can tell it might have something to do with the particular version of PHP 5 that is being run on the webserver. It also may have something to do with the php.ini include path, but since I can connect using pear and pull information from the database using pear I don't think this is the issue.

If anyone has any recommendations let me know, otherwise I will keep everyone posted on what I figure out.

Shane

Timeout using SQL with Oracle

Yesterday I showed you how it was possible to "timeout" or make something disappear from your webpage after a certain number of days using PHP. I discussed briefly why that option was not necessarily a practical choice in that given situation. The better way to get the same result is to never have pulled the data from the database to begin with.

In Oracle you can easily perform arithmetic on dates in your SELECT statement to limit your results based on how old the entry is. For example, If I was going to pull out the usernames, messages, and dates from a database in which the posted date was within the last 7 days I could easily do that with a SQL SELECT statement.

In this example, POST_TABLE is my database table, and POST_DATE is the date of the post. I can use a SELECT statement similar to the one below to get all the messages within the last 7 days and order the results by the post date.

SELECT USERNAME, MESSAGE, POST_DATE FROM POST_TABLE WHERE (SYSDATE - POST_DATE < 7) ORDER BY POST_DATE

As you can see, it is a really simple solution that is much more elegant then the PHP based solution I discussed yesterday. Either way there are certain situations in which both solutions have their place. If you have any questions, comments, suggestions, or even complaints, leave them in the comments.

Shane

Timing out in PHP

Here is an easy way that I was able to make something disappear from a web page after a certain number of days using PHP. For example say I have a post or entry in a database that contains the date entered in MM/DD/YYYY format (02/21/1987 as an example). Also, imagine that I want that to display on the web page for 5 days and then be removed. Well I wrote a very simple function to do just that.


function timeout($date){
$curr_time = time(); //gets the current unix timestamp
$timeout = 432000; //time out in 432000 seconds or 5 days

$post_date = strtotime($date);

//if it has timed out, do not display, return false
if (($curr_time - $timeout) > $post_date){
return false;
}else{ //it has not timed out, return true
return true;
}

}


$timeout is calculated by taking 60 x 60 which produces the number of seconds in a minute. Then multiplying that value with 60 to get the number of seconds in an hour. Then multiplying that value by 24 to get the number of seconds in a day. Finally multiply the number of seconds in a day times the number of days before a timeout (in this case 5) to produce the timeout value in seconds.

You can find more information on the strtotime function here.

This solution does have some significant drawbacks however. In this situation you are forced to load extra data from the database that may never be used. In that situation there are probably more elegant ways to hide whatever you do not want to display (most likely by never pulling the date from the database in the first place). However if you are working with small amounts of data this solution can still be effective. This can probably be used for many other purposes as well. Enjoy.

Shane

Oracle PL/SQL Update Procedure

Here is a short Oracle stored procedure with an Update function. It took me longer then it probably should have to discover the correct syntax to get the Update working correctly. One thing to be sure of is to make sure your variable names are not the same as your column names, even if the case of the letters is different. For example if you have a column named FIELD_ONE, you do not want to have a variable named field_one. Here is the update example:

Here is the Package Header:


create or replace
PACKAGE PACKAGEUPDATE
IS
-- Procedure that updates values in the table
PROCEDURE ProcUpdate
(
id IN TABLE_NAME.FIELD_ID%TYPE,
in_field_one IN TABLE_NAME.FIELD_ONE%TYPE,
in_field_two IN TABLE_NAME.FIELD_TWO%TYPE
);

END PACKAGEUPDATE;


Here is the Package Body:


create or replace
PACKAGE BODY PACKAGEUPDATE
IS

PROCEDURE ProcUpdate
(
id IN TABLE_NAME.FIELD_ID%TYPE,
in_field_one IN TABLE_NAME.FIELD_ONE%TYPE,
in_field_two IN TABLE_NAME.FIELD_TWO%TYPE
)
IS
BEGIN
UPDATE MODAL_REQUEST
SET
FIELD_ONE = in_field_one,
FIELD_TWO = in_field_two
WHERE
FIELD_ID = id;

EXCEPTION
WHEN OTHERS THEN
PACKAGEERR.ERR_STOP;
RAISE;

END ProcUpdate;

END PACKAGEUPDATE;

The update procedure takes in 3 parameters, an id, and two fields. The procedure updates the columns FIELD_ONE and FIELD_TWO with the values passed in in_field_one and in_field_two respectively. It does this where the FIELD_ID column equals the value passed in to id.

The EXCEPTION is just a call to a general exception procedure (PACKAGEERR.ERR_STOP), if anyone wants to see an example let me know. Also, if anyone has any other questions or concerns feel free to leave me a comment.

Shane

Pear "executeStoredProc" function with Oracle 10g

My last post I showed how I was able to eventually connect to an Oracle database using Pear MDB2. This post I am going to show you how I am able to call an Oracle stored procedure using Pear MDB2.

Initially I attempted using the Pear function executeStoredProc() but after looking at the documentation in the Pear oci8 driver, it became clear that this function does not do what it is intended to do(at least by the way I interpret it). The executeStoredProc function should be able take in the database procedure name and an array of parameters, and use that information to call the stored procedure. The executeStoredProc function also has other options that can be sent in as parameters, more info on executeStoredProc can be found in the MDB2_Driver_Function_oci8 documentation.

Instead of using this function I wrote a similar function that I included in my connect script. This way I am able to call this function anytime I am connected to the Oracle database. The function below is specific for my purposes only, but it should be easily modifiable to fit in other situations. It is modeled after the executeStoredProc function.

function &executeDatabaseProc($name, $params, $db){
$query = "BEGIN ".$name;
$query .= $params ?
"('".implode("', '", $params)."')" : "()";
$query .= ";end;";

return $db->query($query, null, true, false);
}

This function takes the database procedure name, an array of parameters, and the current database connection. It creates a string called $query that is formatted similar to:

"BEGIN database_proc_name('param1', 'param2');end;"

After creating $query, the Pear MDB2 function query is called. Like I stated previously I did not need the last 3 parameters so I just hard coded them into the call (they will never need to change).

Below I briefly will show you how to make a call to this function in php code:

$mdb2 = connect();
if ($mdb2->supports('transactions')) {
$mdb2->beginTransaction();
}

//array must match the number of parameters
//in the procedure you are calling
$data = array("param1","param2");

$result =& executeDatabaseProc(
"database_proc_name",$data,$mdb2);

if (PEAR::isError($result)) {
if ($mdb2->in_transaction) {
$mdb2->rollback();
}
} else {
if ($mdb2->in_transaction) {
$mdb2->commit();
}
}
$mdb2->disconnect();

That is just one example of how this function can be used. If you have any questions or are having any trouble let me know in the comments and I will see if I can help you out. Also, if anyone else knows a better alternative I am always open for new suggestions.

Hope this helps.

Shane