« June 2005 | Main | September 2005 »
August 31, 2005
Welcome to the DHTML, PL/SQL, and Javascript Coding Category
Welcome! This category will be used to demonstrate the blending of DHTML, PL/SQL, and Javascript for some pretty slick web applications. Here at WOU we use PL/SQL Developer, and, currently, an Oracle 9i database. It is a great platform for web development, but there are very few resources that show you how to create applications incorporating all of these technologies into one package.
And I do mean "package". All of our applications consist of one or more packages stored on our Oracle database. When combined with an Apache web server, the database has the ability to serve up web pages from these packages! If you don't think that is really cool, well, I can't help you.
I am going to assume you know the basics of writing an Oracle package using PL/SQL, that you are familiar with basic web coding in HTML and DHMTL, and that you at least know what Javascript is. If you have questions about the basics in any of these areas, give me a post, and I'll try and help you out.
The most trying aspect of this coding environment is moving back and forth between them in your package. For example, let's say we have a table "mike.test_web" (mike is the schema, or owner, test_web is the name of the table). test_web has two columns, msg, a varchar2(40), with some text in it (like "Hello, world."), and num, a number column, which is a primary key, like 1.
So, if I want to display the message in a browser window, here is what my package would look like:
Create or replace package mike.test_msg is
procedure display_msg is
vtext varchar2(40);
begin
select msg into vtext
from mike.test_web
where num = 1;
htp.print('
H2 Yes indeed, this really works!/H2>br>
table>tr>td>'||vtext||'/td>/tr>/table>
'); end display_msg;
end test_msg;
I had to leave off all the "<"s so the blog server did not interpret the HTML code, but you get the idea. Don't forget your header file for the package, and compile. Then you can call your package from a browser window.
(NOTE: Your database and web browser must be configured to accomplish this task. It needs DAD's and such which are beyond the scope of this entry.)
Here it is:
You can see the web server stuff, then the package name and procedure name in the URL. Pretty cool!
Note the htp.print(' opening "tag" and '); closing "tag". This is how you move between PL/SQL code and the web code. It is pretty easy here, but it quickly gets complicated! What can you make of this line: td>'||vtext||'/td> ?? What is going on here?
If you can see that you are calling a PL/SQL variable from within your HTML, you've got it!
You should be thinking of all the possibilities this opens up for you. You can add in any HTML or DHTML (and soon PHP!) or Javascript to make applications that are dynamic and very nice looking....
Posted by rossm at 10:36 AM | Comments (0)
Processing Multiple Row Check Boxes - Part I
You can see a screen shot of this topic here on my blog under the Physical Plant category, More SWAMP Fun.... entry. The idea is to display a list of records with a checkbox for each record. You can then process the records, determine which one(s) were checked, and perform most any action on the selected group.
Let's start with an easy case. I want to simply insert a 'C' in the status column of my work request main table to indicate that this record is closed. For Part I, let's get started by setting up the display!
The first step is to display the records onto your form. We will display all open records so that the user can select which ones need to be closed. I built an HTML table and created rows like this (I replaced the <'s with _'s so the blog server would not interpret the HTML - does anyone know how to get around this?):
for i in open_req loop
htp.print('
_tr>
_td align=center>_input type=text name=reqno value='|| i.request_no||'>_td>'||substr(i.description,1,50)||'
_td>'|| i.submit_date||'_td>Open
_td align=center>_input type=checkbox name=checker>
_input type=hidden name=marker>
_/tr>
');
end loop;
open_req is a cursor that fetches all work requests that are "open", that is, all of them that have null in the status column. You can see that each table row will display the work request number, the first 50 characters of the description, submit date, a default status of Open, and a checkbox. [marker is a hidden value whose use will be seen in Part II.]
The end result is a table with all open work requests displayed - one work request per row, with an empty checkbox at the end of each row. This is a great start. Next time, we'll figure out which records should be closed, and, finally, perform the actual table update and re-display the form with a corrected list of open work requests.
Posted by rossm at 10:29 AM | Comments (0)
August 30, 2005
More SWAMP Fun - Closing Work Requests
Who doesn't like a good challenge? This week Tom Neal requested a modest modification to the campus-wide Work Request system. He wanted an easy way to track open vs. closed work requests. The goal was to spend a minimal amount of time to keep track of the status of work requests to make sure none of them fall between the cracks.
So, I slapped a new column on the main table - "status", and marked all but a few work requests as 'C' (for closed). In no time, I had a nice view of work requests that were still '' (null = open). Nice.
My idea was to display a list of all open work requests, one record per line, on a form. Each record would have a check box at the end of the record. To close one or more work requests you could check the appropriate box(es) and press a button. Easily said, but boy did I have a time with making it happen. But now it works, and maintaining the "status" should be a breeze. Thanks Will for your advice on handling the array!
Here's a screen shot.
One button click will close the 5 selected work requests.
If you would like to see the code that makes it happen, look for my forthcoming entry under the DHTML and PL/SQL Coding category. I will place some code snippets out there for your review. By adding new code on a regular basis I'll eventually get a nice list of useful code, plus some feedback on improvements or alternative coding ideas from others. Sounds good - look for it!
By the way - did you know that Troy got PHP up and running on our test instance of Oracle 10g? Sweet!
Posted by rossm at 4:23 PM | Comments (0)
August 25, 2005
History is Made: MBS Meets CBORD!!
After years of testing, fiddling, upgrading, cursing, and anything else we could think of, it finally happened. MBS met up with CBORD, they talked together nicely, and about 6 transactions from the bookstore's cash register to the CBORD server were successfully completed. Many thanks to Mike Ellis and Bill Nicks for pulling this one together!
This makes way for further development of the "Wolf" card, whereby students will have separate accounts for their meal plans and bookstore needs on the same card. Funds can be added to a student's Wolf card for purchasing books and/or meals, and the Wolf card will work much like a credit card. The funds may not be comingled, that is, you can not use your meal plan money at the bookstore, or vice versa, but the same card tracks both accounts.
There are a number of details that need to be ironed out, but the technical framework is finally in place to allow development of the Wolf card!
Posted by rossm at 1:14 PM | Comments (0)
Movin' Along from Wilb to Aero
The move for our friends in public safety is in its last testing stages. Will, Mike Soukup and I have fixed the errors found by Nancy and Cheri so far, and it looks like we have nearly 100% functionality. Once we are more confident with the stability of the application on Aero, Will and I will move the table data and complete the move. By the way, we are going to try a new method of moving the table data - truncating the Aero test tables (not dropping them) and using a control file for the exp/imp functions which will append the data onto the existing tables. "Why?", you ask. Because these particular tables are a bear to move! In the first place, they need to be moved to a new tablespace on Wilb. Also, we've got the table permissions, data constraints, primary keys, and indexes set up very well, so we don't want to destroy that work by dropping the tables. We'll see if this method helps or is just more work.
Will also discovered errors with the four sequences - not bringing over the correct "next number", which fouls things up when new vehicles are added, or new permits are issued. We are going to just monitor those by hand, and set the "next number" manually since there are only four of them.
One last piece to work out is coordination between public safety's users, and the Banner users in administration. Several Banner packages will need to be modified to point to Aero's tables. Thanks to Brian W. and Summer for handling this portion of the move.
Posted by rossm at 9:09 AM | Comments (0)
August 18, 2005
SWAMP Projects Module Gets a Facelift
Two new areas of functionality are being added to the SWAMP Projects module. The first upgrade is a form that sorts current projects by building, rather than the historical practice of sorting projects by funding source. The new form is 90% done(!), and works - in a beta kind of way. We might add a front page that lets the user select the kind of sort they want on the projects. The front page would allow a user to select the kind of sort they want, and also permit filtering, such as a list of only projects with a given Project Manager, etc. Yes, things can always be made more complex! Right now, the beta version of the building sort works, and the front page portion is just an idea, but a good idea.
The second Projects project is an exerted effort to compile all project financial data under a given funding source, with output being a very good guess of the spending of those funds to date plus the "encumbered" amount of those funds. Then, a compilation of all projects and all funding sources would give a good bird's eye view of the current financial status of all project funding. This has proven to be a tricky devil as it is relatively easy to generate data that is not accurate, and relatively difficult and time consuming to generate data that is accurate. For example, the townhouse project required a 29 page spread sheet to accurately show all the expenditures. Such items as contract expenditures, invoices, materials, in-house labor, planning fees, 1% for art costs, and so on must all be accounted for, and this must be accomplished for all projects. This upgrade is nowhere near even a beta yet, but it's coming along nicely. When done, it will provide a valuable planning tool. This project is an expansion of the management tool described in an earlier post.
Posted by rossm at 11:30 AM | Comments (0)
August 12, 2005
Wilbur Applications "Moving" Along
Will and I tackled the move of a couple of the toughest schemas from Wilb over to Aero. We're about 90% done! These schemas proved to be quite a challenge as they directly interact with Banner data. The processes for connecting to Banner data are quite different on the two databases. I did manage to "move" a schema that someone else had already moved, thereby stomping all over their work, and breaking a perfectly good application. Brian was actually pretty nice when he called me considering I had just blown away a bit of his handiwork! (I'm now thinking of writing a small app that gives us a bit of information about the myriad applications UCS has written to prevent this kind of error in the future.)
The good news is that Will and I were able to put the added infrastructure in place to enable these schemas to get at the data they need to work. One application is being tested by its users to see if any hidden bugs are lurking there, and we will tackle the final schema next week.
Once all applications are safely moved to Aero, we will be in a position to shut down the Wilb database, and move forward with our ultimate goal of providing Oracle 10g to the campus community.
Posted by rossm at 11:34 AM | Comments (2)
10G Install Successful
Earlier this week Troy and I installed Oracle 10g on a Sun server that Troy had set up. It went pretty easily once we replaced some kernel parameters that the Oracle documentation said were outdated. The Oracle installer erred out when they weren't present, though. We did discover that the Apache web server is no longer installed as part of the Enterprise DB install. No, Apache is now contained on a separate installation CD and requires its own Oracle Home and SID. We're not quite sure why or what the benefit is of this setup, but we will see!
Unlike Oracle installations of old, this install was pretty EASY. Once we got the kernel paramaters straightened out, the DB pretty much installed itself.
Stay tuned for more information on 10g and its benefits! Our goal is to eventually install 10g on the "cluster" and migrate AERO over as our production DB. Sweet!
Posted by rossm at 11:21 AM | Comments (0)
August 5, 2005
We're Moving...........
Will Drury and practically every other programmer in UCS have been working to move all our web applications from Wilbur to Aero. This is a long process as many of these applications are used a lot! Also, some of them were written by students and UCS staff who don't work here any more. That makes moving them more of a challenge. What does this all mean? Why bother?
Well, for one thing Aero is on the Sun cluster and Wilbur is a stand alone server, so all the advantages of a cluster will be available to every application once moved to Aero. Aero has superior horsepower, RAM memory, and storage capabilities as well. It will need them in order to keep up with all these applications!
There is a standing joke among programmers that a particular project is "90%" done. When they report that a project is 90% done, in reality, this often means a project is about half done, but that a host of unforeseen problems haven't cropped up yet. So you can now appreciate that the move from Wilbur to Aero is about 90% done! By the way, once this move is accomplished all will be set for the jump to Oracle 10g (well, that and actually getting the database installed, of course). Stay tuned as we work to keep WOU on the cutting edge of technology for everyone's benefit!
Posted by rossm at 11:44 AM | Comments (0)
HOW DO YOU GET TO WORK?
Gas prices spiral upward, the air quality continually gets worse and worse as population increases. So, what are you doing about it?
Nearly every day, my transportation medium is an "electric bike". What the heck is that? Here's the one I ride - a Giant Lafree Sport. It's pretty much a regular bicycle with a 3" longer wheelbase, a battery, and an electric motor! Maybe you've seen me riding it around campus? I've been riding it for nearly three years now (about 2500 miles), so let me tell you about the pros and cons.
Mechanically, this thing is really bulletproof! It does wear out the brake pads a bit faster than a normal bike because you tend to ride around faster. The batteries don't really require maintenance, but they do wear out. They have a usable life of about one year - no more than that (I don't care what the manufacturers say).
Replacement batteries cost either $50 for the cheapies, or $100 for the YUASA brand, which offer a lot better performance, but don't last one minute longer. With the Brand X batteries, you can travel around 12-13 miles, with the YUASA's maybe 17-18 miles before you need to recharge. BUT, recharging is so simple! I have a timer with an extension cord on it in my garage. When I pull in and the batteries are anywhere near low, I just plug in the extension cord and it charges the batteries, then shuts off. In the morning you unplug and whiz off, fully charged. It takes literally seconds. So guess what, I'm running the Brand X batteries and recharging more often. Big deal.
Pros: Really easy to maintain. Cost me $850 used (but like new). No insurance, no license, no parking permit required. Top speed 20 mph (motor quits helping at that speed - a Federal requirement). You can ride on the sidewalks. Burns NO gas, and costs maybe a dollar a YEAR for electricity. It's FUN!
Cons: You get wet when it rains. No good for distances above about 5 miles as a commuter - too slow. Batteries must be replaced about once a year.
OK, this is getting too long. I'll write more about the electric bike later. So, what other devices/tricks do people use to get to campus? Let me know!
Posted by rossm at 10:15 AM | Comments (2)
SWAMP Gets Another Facelift
Another project management module is under development for SWAMP.
Funding Status will display a collection of all projects under a particular funding source such as "Capital Repairs 03-05". Managers will have a bird's eye view of total spending for each funding source. This is a necessary, but very tricky, bit of information. Any particular project that comes in over or under budget affects the bottom line. Also, a manager will be able to "drill down" and see the monies spent on any particular project. (This is a very cool feature, but easy to implement since it already exists in the project management module.)
Once again, the flexibility of SWAMP allows for the creation of new modules that build upon the core functionality!
Posted by rossm at 9:13 AM | Comments (0)
WOU - Powered by Oracle
You probably didn't know this, but almost every facet of student life on campus is impacted by our Oracle database (we actually have more than one). A quick count shows about 112 different local applications that use the database. Examples of these applications include:
SWAMP - physical plant's purchasing,accounting, and postage systems
WEBDB - the university's web page
TIMESHEET - tracks student work hours
SAFETY - parking permits and, yes, citations
REDWOLF - residence hall network and internet monitor
LIBRARY - many library services such as reservations, periodicals
KEYS - key checkout data for the entire campus
JUKEBOX - a huge music collection for the entire campus
CAMERAS - security cameras
BOOKSTORE - a portion of the bookstore's web store
BANNER - student grades, financial records and a lot more
Oracle databases are not new to Western, we have used them here for years. They are widely considered to be the best databases available for reliability and speed. When installed on Unix servers, you get truly awesome performance. WOU currently runs Oracle version 9i housed on a Sun/Solaris cluster. Plenty of horepower!
Oracle databases are also packed with features, such as the ability to output HTML. This means you can use the database for web-based applications such as those listed above, and the database can give you data in the form of a web page. A great example of this is the student/faculty/staff search page on the WOU website. Type in a name or part of a name and you are performing a search of data on an Oracle database, reading the results in the form of a web page! Cool!
Stay tuned as University Computing is not letting any grass grow under our feet! Oracle has a new product known as 10g (for grid computing), and we will be making it available for use to the campus!
Posted by rossm at 8:08 AM | Comments (0)