Welcome to the Blog of Mike Ross: November 2005 Archives

« October 2005 | Main | December 2005 »

November 29, 2005

Ebikin'

I'm so excited - someone (outside our UCS group here at WOU) actually posted a comment to one of my entries! Well, it made me realize I had been ignoring my Alternative Transportation category. We get so caught up in our work, don't we. Well, I'm still riding my electric bike, but it did finally give me a bit of trouble - a spring on the rear brake snapped, which caused the rear brake to shift all the way to the right and pressed one of the brake pads against the wheel all the time. Fortunately, this happened in my driveway. I emailed the guy I bought the bike from (Eric - info@ebikesnw.com) who has a nice electric bike shop in Seattle. He told me that I could get replacement brakes at any bike shop. Nope. The Giant LaFree uses a brake that's custom made - regular brakes are too long, and BMX style brakes are too short. After a bunch of fiddling around, I was able to use a combination of new brake parts and my old brake parts to get it working again. Actually, I just rode around for a couple weeks with only the front brake - no crashes, so it worked out OK, my apologies to the gal with the dog who was jogging and had to stop for me, though.

The Salem Statesman Journal newspaper had a neat article over the Thanksgiving weekend about a guy named Jim Brown, who has a bicycle with two small motors (not electric) on it. He's going to ride the thing across the country. I gather from the article that he is getting close to 100 mpg on his "custom" bike, and it goes about 25 mph. Here he is:

GO JIM!

There are a lot of alternatives out there, from small electric cars, golf carts, motorized skateboards, small motorcycles and mopeds (big motorcycles get worse gas mileage than many small cars - I have one, I know! My Goldwing burns super and gets about the same as our Toyota Camry). Let's use 'em!

Posted by rossm at 9:19 AM | Comments (0)

November 28, 2005

PHP and Oracle - Running a Stored Procedure

It occurred to me that, in order for PHP to be useful at all, we needed to be able to either put it inside a stored procedure or at least be able to call a stored procedure from PHP. After a bit of messing around we determined that putting PHP inside a stored procedure is not going to work. The Apache web server does not know what to do with the PHP code. On the other hand, it IS possible to call and run a stored procedure from PHP.

We have had success in connecting to our Oracle database, running simple SQL queries, and displaying the results. The connection to the database was pretty straightforward using the db.php include file and db::connect() built-in function.

There was not much in the way of resources out there that talked about this particular problem. We did find a post that got us started, and used the oci_connect and ocilogon built-ins. These gave us fits, and we never did get these functions to work for us. We experienced a problem with the database connection, where it just would not work. Apparently, this has happened before, and there is no apparent cause for it. The connection just never gets made, no error, no nothing. Need more work on this problem before we go to a production environment.

We are getting errors in using functions like this: oci_new_collection(), but no errors in functions like this: ocilogon(), so we may have some kind of versionitis working against us.

In summary, we have learned that PHP can not be embedded within our stored procedures using our current configuration. Stored procedures can theoretically be called from PHP, but we have not settled on a reliable way to do this yet. The following is the code we have to work from for now:

require_once 'DB.php';
$dboptions = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL,
);
$dsn='oci8://fic30:aeroace@aero.wou.edu';
$user="fic30";
$pwd="aeroace";
$sid="aero.wou.edu";
# $dbh= oci_connect($user,$pwd,$sid);
$dbh=ocilogon($user,$pwd,$sid);
$dbconn = DB::connect($dsn, $dboptions);
if (PEAR::isError($dbconn)) {die ($dbconn->getMessage());} #if we could not connect, quit the whole file and print message
else {echo "

Connection established

";}
$sql="BEGIN PHP2(:p_result_value); END;";
# $sql = "select * from um";
$stmt=OCIParse($dbh,$sql);
# $res=$dbconn->query($sql);
$result_values=oci_new_collection($dbh,"DALE_TABLE","FIC30");
OCIBindByName($stmt,"p_result_value",$result_values,-1,OCI_BNTY);
$rslt=OCIExecute($stmt);
echo "Number of elements in collection:";
print_r($result_values->size());
echo "";
echo"Results:";
print_r($result_values->getElem(0));
print_r($result_values->getElem(1));
?>

Posted by rossm at 1:13 PM | Comments (0)

November 17, 2005

November 17th = PHP Continues, Wilbur DB Link

This week Dale and I decided to press on with learning more PHP. We worked a bit more on our database connection code, and learned how to print out the results from a simple db query using an array. PHP stored the results from our query in an array (ums), and we were able to print out to the screen all of the different kinds of units of measure there are.

Next, we went to the Zend website and did a bunch of beginning tutorials there, such as include files, security, and file structure rules. Oh yes, we started on a tutorial on Regular Expressions, but decided that, um, it was getting mighty deep mighty fast and we, um, didn't want to get sidetracked from our pursuit of PHP. Later on the Regular Expressions!

I just found another tutorial which looks good - it's more than 11 parts, here .

Our time got cut short a bit because Brian W. came over and needed to do some priority work on Wilbur db links, so we helped him. We also had some good discussions about resurrecting our Unix/Solaris box and Oracle 10g install group as we all miss that one! I was able to talk with Mike Ellis and Ron S. about the pros and cons of PL/SQL, JS, PHP, and Pearl. Ron doesn't use PL/SQL, Mike E. doesn't use hardly anything BUT PL/SQL (because it's compiled on the DB, a notably good reason). I'm thinking we should be able to use a Merry Mix of all these things to optimize our applications.

Next week is Thanksgiving! (Yay!) so we will not meet again until December 1st. It looks like moving the last two schemas off of Wilb and on to Aero is becoming a heavy priority, so we may cool our jets on PHP for a week or two, gather together some UCS brainpower, and get those two schemas moved once and for all. They are tough ones as they are heavily intertwined with the Banner databases.

Posted by rossm at 3:29 PM | Comments (0)

November 15, 2005

LOOPing for Data - Part II

In Part I, we considered the subtle, but very useful cursor with a variable. We set up two cursors, with two corresponding loops and were able to display a list of projects for a given funding source. The following screen shot is from the SWAMP projects module, and used this structure to break out the display.

The outer loop displays the "Miscellaneous" and "Tier 1" funding source information, the inner loop displays the individual projects and their corresponding information. There are three projects under "Miscellaneous" and four projects under "Tier 1", at least for now. When new projects are added, or old projects removed from the database, then the display will change automatically.

OK, so that's all well and good, but how do you handle the HTML in each loop to make this work? And, by the way, didn't you promise to show us another use for the cursor-loop combo? Well, glad you are paying attention! Read on.....

Let's flesh out our loops a bit and put some real-world HTML in there. Remember our cursors look like this:

cursor fun_src is
select distinct fund_source
from proj_table;

cursor ind_projs(fc varchar2) is
select *
from proj_table
where fund_source = fc;

I hope you are getting more comfortable with the notion of passing in a variable to a cursor (which value actually comes from the first cursor). A happy combination.

Here's the outer loop:

for i in fun_src loop

Let's add some HTML in here. The projects form uses a new table for each fund type, then one table row for each project, like this (THANKS to Summer who showed me how to print out the HTML without having it interpreted by using lt; and gt; directives!):

< table width=95% border=1 class=graytable>
<tr><th>'||j.proj_type||'</th></tr>
<tr align=left>
<td width=22%>Project Name</td><td width=7% align=center>Est Cost</td>
<td width=7% align=center>Encum</td><td width=10% align=center>Proj Mgr</td>
<td width=12% align=center>lt;b>Start Date</td>
<td width=12% align=center>Est End Date</td>
<td width=7%>Last Status</td><td width=7%>Curr Status</td><td width=16%>Comments</td></tr>

The outer loop gives me a table, the Project Type (i.proj_type), and a table row containing the column headings such as Est Cost, Proj Mgr, etc.

Now we will start the inner loop:

for j in ind_projs(i.fund_source) loop

and place in it the individual project information, like this:

</tr align=left>
</td width=22%></a style="color:'||vcfont||';text-decoration: underline" href="http://www2.wou.edu:7777/pls/wou/fmm30.form_projects.get_totals?pproj_no='||i.proj_no||'">'||i.proj_name||'</td><td align=right width=7%>'||i.proj_est_cost||'</td><td width=7% align=right>'||i.encumbered||'</td><td> width=10% align=center>'||i.proj_manager||'</td>
<td width=12% align=center>'||i.proj_start_date||'</td><td width=12% align=center>'||i.proj_completion_date||'</td><td width=7% align=center><img src = "https://www2.wou.edu:443/images/swamp/'||vimagelast||'" /><td width=7% align=center><img src = "https://www2.wou.edu:443/images/swamp/'||vimage||'" /></td>
<td width=16%>'||i.comments||'</td>
</tr>

This gives you a good example of the syntax required to acquire the data values from the cursor. Also, for your specific situation you might need to add in PL/SQL if-then statements, and others, to get your output correct. But I think you can now get the idea of how to use these cursor-loop combinations.

The last thing I wanted to suggest was using a cursor-loop combination for the sole purpose of passing values into your form! That's right. Of course you can pass in any number of parameters into a procedure. Here's a good example of what I'd like to avoid:

procedure displaypage(vdisabled varchar2, vcomments varchar2, vvendor_no varchar2, vname varchar2,
vsales_rep varchar2, vvendor_account_no varchar2, vfax varchar2, vtelephone varchar2,
vmobile_phone varchar2, vemail varchar2, vprice_agreement varchar2,
vconfirm varchar2, vfob varchar2, ppreq_no varchar2, vpo_no varchar2);

That's a pretty hairy procedure call! Well, SOMETIMES, certainly not always, you can use the cursor-loop combo to simply fetch a set of values from the database, and just display them by going through the loop only once. This works for a situation where you can pass in one or two parameters, and then use those to query the rest of the data with a cursor. If the values you are passing are available from the database, give this one a try.

Well, that's it for LOOPing for Data. I hope you can see a way to use the power of the cursor-loop combination to improve your own forms!

Posted by rossm at 1:35 PM | Comments (0)

November 10, 2005

Out With the Solaris/Oracle Box, in With PHP

Well, our group project of building the Solaris box and installing Oracle 10g thereon has hit a major snag. Shaun got "pulled" to work on another project and so did the hardware! We were disappointed, but will take up with the Oracle install when we can.

In the mean time, Dale and I decided to press on with PHP. Using my primary programming strategy - stealing from someone else - I "borrowed" some code from Ron who is way ahead of us in PHP. In this way, Dale and I were able to connect to AERO through PHP, and run a simple select statement. Here's what the code looks like:

require_once 'DB.php';
//This is a call to the php module that does database communication - kind of like an include file.

$dsn = 'oci8://username:password@sid';
//Set a variable with the Oracle connection string information.

$dboptions = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL,
);
//A bit of fanciness Ron uses to implement portability. One of the main uses for this is to implement associative arrays (another new term).

$dbconn = DB::connect($dsn, $dboptions);
//do it - connect up using the variables we created.

if (PEAR::isError($dbconn)) {die ($dbconn->getMessage());} #if we could not connect, quit the whole file and print message

$sql = "select * from um";
$res = $dbconn->query($sql);
//Set the $sql string and run the query. Could also put the query inside the query() brackets.

if (PEAR::isError($res)) { die ($res->getMessage()); }
//If the query errs, capture the error and quit.

print "Num rows:" . $res->numRows();
//TaDa - this is what printed for us - Num rows: 39.

while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
$ums[$row['abbr']] = $row['description'];
#print "$row[abbr] - $row[description]
"; #debug
}
//What the heck, fill an array with the query results. Next time figure out how to use this.....

$res->free(); #get rid of the query now that we have the data into the $ums array

$dbconn->disconnect();
//close connection.

There it is. Enough time for Dale to eat a donut, too.

We all agreed that PHP is not going to be useful unless we can master DB tasks with it. In talking to Mike Ellis, he raised an interesting question - why would we use PHP, which is not compiled, rather than PL/SQL, which is compiled?? Good question. I don't know.

Posted by rossm at 12:51 PM | Comments (0)

A Little String FUN

One of the biggest challenges in writing DHTML,PL/SQL,JS combination code is handling characters in strings that also have meaning to the programming language. For example, the string "Joe's Bar and Grill" will give you fits because the ' has special meaning in both PL/SQL and JS - it means the end of the string. So, everything after the ' is just so much jibberish. Of course, this problem is so widespread that there's an easy fix, like this: "Joe\'s Bar and Grill". Will work.

But consider this little problem. We want to delete a vendor from a preq, and the vendor number just happens to be, guess what "G&SM01". We build our URL string (in JS) to call the delete vendor procedure like this:
//capture the vendor number from the form
var val = document.detail_form.pvendor_no.value;
//build the URL string Addy. ppreq_no is the preq number from the form
var Addy = "http://......?ppreq_no="+preq_no+"&pvendor_no="+val;
Do you see the problem?

Right. Here's the Addy string we just built:
"http://......?ppreq_no=PRQ123456&pvendor_no=G&SM01"
ERROR: No value given for variable SM01. "=" expected.
The "&" character means a new variable is being passed. There is no way for the browser to know that it's just part of the vendor number. Well, the easy answer is, don't allow vendor numbers to contain "&"'s. OK, but a quick check of the vendor table reveals we have 43 vendors with an "&" in their vendor number. You tell John he has to change them. Me, I'm going to think up something else.

And here it is. A simple cure with some tricky code syntax problems. But, once you figure those out, it's a snap.

First off, both JS and PL/SQL have Replace functions that will seek out a character in a string and replace it with something else.

JS: var x = val.replace(/&/,"~");
//changes G&SM01 to G~SM01 - there are no ~'s in vendor numbers.
//This is the exact format required within JS.

Now I build my URL with the x string and the error goes away. The browser is happy, but nothing happens. That is because my delete procedure needs to "reverse engineer" my string back to the way it was in order to know what record to delete from my data.

PL/SQL Replace is used as follows (pv is the passed in value x):

vvend := replace(pv, '~','&');
//pv = G~SM01, vvend = G&SM01
Now all that's left is to perform my delete based upon vvend.

That's it! We used the JS replace() function to remove the offending character(s), called our PL/SQL procedure and passed the modified vendor number, then converted the vendor number back to the way it was using the PL/SQL replace() function. A much better solution than requiring our users to modify their data to remove the offending characters!

Posted by rossm at 9:03 AM | Comments (0)

November 3, 2005

PHP 101

Today Dale and I worked on a beginning PHP tutorial. We believe that our applications will be more robust if we learn to code up some PHP. Ron has gotten PHP enabled on AERO, so we were all set to go.

The first thing I noticed was how easy it was to get a simple page up. I literally saved a notepad file with a .php extension, and that's all it took.

Next we learned a bit about super-global variables in PHP. The $_SERVER super-global returns quite a bit of information about the browser being used. This code:
_?php echo $_SERVER['HTTP_USER_AGENT']; ?>
returns this kind of information:

Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR
1.0.3705; .NET CLR 1.1.4322)

You can then look through this output for the string 'MSIE' to determine whether a person is using Internet Explorer. The code looks like this:
if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== FALSE) {
echo 'You are using Internet Explorer.
';
}
?>

and outputs the phrase after the echo above if you are using IE.

The next concept we covered was that you can put HTML tags in with the PHP tags, and they will be interpreted correctly. You do not have to switch between PHP and HTML mode. This is not true of Javascript, HTML and PL/SQL. Very neat!

Next, we learned a bit about function calls in PHP. There are a number of built in functions (the strpos() call used above is a good example) and they are called pretty much like any other language.

The coolest thing we covered today was the implementation of a simple form like this (all <'s replaced with _'s to display):
_form action="action.php" method="post">
_p>Your name: _input type="text" name="name" />_/p>
_p>Your age: _input type="text" name="age" />_/p>
_p>_input type="submit" />_/p>
_/form>

The form looks like this:

We then created the action.php file, which looks like this:
Hi _?php echo $_POST['name']; ?>.
You are _?php echo $_POST['age']; ?> years old.

And gave us output like this:

Hi Mike. You are 34 years old.

Note that no variables are created at all. The $_POST built-in from PHP handles it. You just have $_POST display the value from the form text box. I thought the "submit query" button that is displayed is kind of like magic, where did it come from? It must also be built-in to PHP.

Unfortunately, we ran out of time here. My first imPHPressions are that PHP will simplify some aspects of our applications for us, and will be a powerful new tool to use to make our apps more robust. I thought we were able to cover a lot of territory pretty simply, and am anxious to expand my knowledge of PHP.

By the way, we were following a beginning tutorial from the official PHP site at: http://us2.php.net

Posted by rossm at 11:14 AM | Comments (1)

November 2, 2005

LOOPing for Data - Part I

Often, when beginning a DHTML page display, we will start with a PL/SQL cursor. The cursor gathers a set of values for us, which we can then loop through and display the resulting data on our DHTML page. This entry will show you not only how to accomplish this goal, but also how to use loops for grouping your output, and even using a cursor-loop combo just for displaying form values!

Let's get started with a simple PL/SQL cursor that fetches a project name, a project number, and a project cost, ordering by the project number, such as this:

cursor get_proj is
select proj_name, proj_number, proj_cost
from project_table
order by proj_number;

We can then loop through the get_proj cursor results and display whatever is there. This is quite handy for dynamic web pages - whatever is in the database that matches our query can be displayed.

So, we might have some code like this:

for i in get_proj loop
htp.print(' [do some cool html thing here] ');
end loop;

Note: Referring out to PL/SQL variables from HTML is a bit awkward - you must use this form:
'||i.proj_name||'
This will get you the proj_name from the i record. If you want to get fancy, you can also use pl/sql functions, such as:
'||to_char(i.proj_cost,'$9,999,999')||'
This will give you output such as $1,234 ...Ah, but I digress.

This cursor-loop combo is a common tool and is quite useful, but what I wanted to show you were two little twists to this scenario that can save you some time. They go hand in hand.

First, let's assume we want to display some projects, ordered by their funding source. Under each funding source there will be anywhere from 1 to n projects (if there are 0 we don't want to display anything). We can accomplish this by using two loops. The outer loop will pick the funding source, and the inner loop will pick all projects with that funding source. When we've looped through all the projects from the first funding source, we'll increment the outer loop to the next funding source, and so on until we're done. This is a great idea but requires one very subtle little trick to work - a cursor with a variable!

That's right - a variable. Consider these two PL/SQL cursors:

cursor fun_src is
select distinct fund_source
from proj_table;

cursor ind_projs(fc varchar2) is
select *
from proj_table
where fund_source = fc;

Yes, this does work!

Our outer loop will be like this:
for i in fun_src loop

And our inner loop will follow, like this:
for j in ind_projs(i.fund_source) loop

Can you see what's happening? The inner loop calls to the ind_projs cursor with the current value of the funding source! Ok, that's enough for you to chew on for Part I of this article. Consider this carefully, and you will see just how neat of a trick this is!

Next time we'll put in a bit more code and put these two loops to work for us. We'll also consider another use for these cursor-loop combo's.

Posted by rossm at 1:08 PM | Comments (0)