No more Labs.

	Possible new course for Fall: E-Business Communities, e-mail Chuck if you are interested.
	Help wanted, Teaching Assistants for Fall 00 E-Business Technology, e-mail Chuck.
	Want to keep you database online? E-mail Chuck with your request.  Please specify if this will be personal or business use.
	Keep in touch, please send me updated e-mail and job information.

Notes from 4/12: 

	In SQL, don't put a comma before the From or Where statements:
	SQL Round can be used to control the length of calculated values.  Use round(value, precision) in your Select statement.  See also tochar in ref-oracle.htm.  For example, to get a value in dollars and cents specify select round(average(sales),2) as avg_sales from foo
	TCL formatting uses the format command with formatting specification and value like this: format "%2.2f" $pure Format may be used inside the string for ns_adp_puts like this: ns_adp_puts "value is [format "%2.2f" $pure]% pure" If $pure equals 99.98765 the result will be: value is 99.99% pure For more information see ref-tcl.htm.  Note: use SQL tochar to format currency amounts with thousands separators.
	SQL Views are like tables but the data comes from other tables.  View may be used in queries but not updated.  If you find that you have a complex query you use frequently, or have trouble mixing aggregate functions with other values, consider creating a View.  Views are created, dropped, and shared (Grant) like tables.  For example:   create view spent as select budget_code, sum(amount) as total, count(amount) as n from actual group by budget_code;
	The Oracle NVL function can be used to substitute a value, like 0, if a value is Null.  For example:   select budget_code, nvl(sum(amount),0) as spent, from actual
	ns_returnredirect switches the user to a different page by doing a HTTP redirect.  Can be used on helper page if there no messages the user needs to see.  For example:   ns_returnredirect "helper-2.adp"

Notes from 4/05:

	The comma separated value file loader is working at http://usf-acs.rho.net/tech/csv-load.adp.  Please read the instructions provided on the page.  Apostrophes in data values are now handled automatically.
	Having trouble debugging your .adp page?  Use http://usf-acs.rho.net/tech/log-tail.adp to display error log information just after you try it.  It may contain a description of your error, along with other stuff.

Notes from 3/29:

AOL Server will not display Microsoft Word (.doc) or PowerPoint (.ppt) files correctly.  Here are two alternatives: Convert files to HTML using the Save As Web Page option, and update your links to point to the resulting .htm file (not the .doc or .ppt file). Store Word or Powerpoint files in you Apache Server directory (/home/usf/your-account) and link to them from your AOL server pages using the full address in the hyperlink.  For example: http://usf.rho.net/your-account/your-file.doc.

Make sure you don't have any spaces at the end of the following call: set db [ns_db gethandle] If you do, db will be set to "nsdb0 " when it should be "nsdb0" and you database operations will fail.  Certainly an obscure bug.

Notes from 3/22:

	Team Projects:  You should have completed the database design for your term project by now.   I want to review each team's database design.  Bring them to lab or arrange time with me. Several teams have not created team Web sites.  These were assigned on 2/23 and should have been completed by now. Please e-mail me your vendor evaluation assignments (Word and PowerPoint).
	Feedback please: let me know what you think of the course so far and e-mail comments I can use in my flyer for the Fall.

Notes from 3/08:

	Strings in SQL Insert statements should have apostrophe characters (') prefixed by a second apostrophe.  Check for two apostrophes (''), not a double quote character (").
	Personal Database I and II papers are being returned.  These are not graded but I have reviewed them and made suggestions.
	Spaces are important in both TCL and SQL.  Be sure to use spaces as shown in the examples.
	In the example .adp pages, the variable $sid is not enclosed in quotes in the embedded SQL because the Oracle data type for the sid column is Number, not Varchar.
	ACS Homepages that appear to be in http://usf-acs.rho.net/users/ do not allow the execution of TCL code, .adp pages will not work.

Notes from 3/01: 

	In your adp pages: The call to ns_db gethandle must be inside the same <% %> markers as the rest of your TCL code that does the database access. The <title> directive sets the title that appears in the blue bar at the very top of your browser window, not a title in the window. Suggested approach: copy the examples and get them to work in your Web pages, then change them for your user name, your table names, and your column names.
	Team assignments for 3/08 listed in the syllabus are not due.
	In SQL: You must do a Grant in SQL Plus before you may access your tables from TCL.  If you Drop a table and re-Create it you must do the Grant again.  The command is as follows (replacing tablename by the name of your table):  Grant all on tablename to ad;  SQL Plus commands may not include blank lines. SQL Plus may not be happy if you give it a large number of commands in one 'paste' operation.  Submitting commands in sections avoids problems and makes it easier to locate errors.  In a Select statement with an Order By part, the column names used in the Order By must also be in the list of columns returned.   This won't work: Select title, artist from cd_table order by cd_id This is will work: Select cd_id, title, artist from cd_table order by cd_id Sorted results that are not case sensitive, use Order By upper(column_name).  For example: Select title, artist from cd_table order by upper(title) Search for key words contained in a column using Like and % to match zero or more characters.  For example this select with find Elvis Presley, Elvis Costello, and Dancing Elvises: Select title, artist from cd_table where artist like '%Elvis%' Current date (without time) is returned by trunc(sysdate)

	To avoid filename problems (including having Notepad name files things like name.adp.txt), have Windows display all file extensions: Start Windows Explorer Select View, Folder Options from the menu. Select the View tab, clear checkbox in front of 'Hide file extensions for know file types' Click Apply and OK
	Lab on March 4 will be extended from 10 am to 2 pm in CO 225
	Your AOL Server Web pages (http://usf-acs.rho.net/student/) will give an HTTP 403 Error if you try to browse an empty directory.  We will put an index.adp page in your directory to stop this error message, you may replace this file.
	FYI: AOL Server, Apache Server, and Oracle are all running on the same Linux server.  AOL Server and Apache are using different file directories on the same server.
	AOL Server directory access via FTP: Connect to usf.rho.net using the same user-id and password you have used in the past.  This will take you to your 'old' directory /home/usf/your-account. Click the Change Directory button (ChgDir) on the right side to change directories to your directory within /home/web/usf-acs/www/student/your-account
	Team directory access, follow the procedure outlined above and change directories to your team directory (usf-tNN) under /home/usf/ for Apache server or  /home/web/usf-acs/www/student/ for AOL server.
	Example pages now contains the source code (HTML and TCL) for each of the .adp pages used in the lecture. Also, you can copy these files using WS_FTP, they are in directory: /home/web/usf-acs/www/tech
	A more robust test for a valid string is to trim blanks from the beginning and end of the string and then check the resulting string length: if { [string length [string trim $sname]] < 1 } { ns_adp_puts "name is blank"  }

Notes from 2/23:

	Dave Abercrombie's presentation, Scorecard.org, and Oracle Links.  Note: you don't have access to the error log or Cassandracle.
	Personal Web pages should demonstrate usable design with minimal graphics or special effects.  If you are thinking of using Frames, see Nielsen page 85.
	To see your Oracle data, start SQL Plus and use these commands: select * from cat;   gives a list of tables describe table s;   shows the definition of a table select count(*) from s;   displays the number of rows in a table select * from s;   shows the contents of a table
	When publishing files to the server with WS_FTP, be sure to use ASCII mode for transferring text files (.adp, .htm, .html, .tcl, .txt) and Binary mode for graphics files (.gif, .jpg, .png).  Use the check boxes under the file windows to specify the type of transfer.
	Corrupted files may result if you upload a text file in Binary mode and download it using ASCII mode.  This will put extra control characters (Carriage Returns) in the file and cause problems.  To check, open the file in Word, select the option to View Paragraph Marks (Tools, Options, View) and check for two paragraph markers (¶) at the end of each line.  Fix your file using the Word Find and Replace command to find ^p^p and replace with ^p.
	Can't see your changes to your Web site?  Click Refresh and reload each page.

Updates from 2/16:

	Starting SQL Plus, use the shortcut login shown below (logging in with user name and password does not work):   sqlplus /
	Be sure you log out from SQL Plus and Linux or your database changes will be lost.  Type exit twice as shown below.

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.5.0.2 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
[chuck@h chuck]$ exit

	Get a SQL error message about a missing right parenthesis?  Check your column names and make they don't contain spaces.
	Make your query results readable by displaying narrower columns. Use the Column command to set display format and width: Column column_A format a10;
	Display your results in alphabetical order by adding ORDER BY to the end of your query as shown below: select sname, ctitle from s, e, t where s.sid=e.sid and e.cn=t.cn order by ctitle;
	The Rollback command can be used to undo changes to the database.  This will undo everything since your last Commit command or the start of your SQL Plus session.  See Oracle.
	Data types: Use only the data types covered in the lecture, not CLOB or other large object types. Telephone numbers, credit card numbers, and other numbers that may contain leading zeros or hyphens should be stored as strings (varchar), not number, to preserve formatting.  Local telephone numbers are varchar(8) with the hyphen.

	The database should be for something that of interest to you.
	There will be a prize for the most creative application.
	This database need not serve any business purpose.
	Database contents do not have to shared with everyone on the Internet after you complete this course.  During class we will be talking about your data.  You can make the database password protected later on.
	This database is not required to have large numbers of records.
	The Personal DB Assignment has been divided into three parts.

 

	Check the class list and e-mail corrections and team information to chuck@ehrlichorg.com. If you have not filled out the online questionnaire, please do it now.
	The example data model for E-Business Information is being developed, take a look.
	When thinking about your projects, focus on user oriented Interactive Services.
	See References for info on Web tools, FTP, etc.
	Help wanted list. Includes internships, teaching assistants and real jobs.

Chuck Ehrlich

E-mail: chuck@ehrlichorg.com 
The Ehrlich Organization: 1-415-441-7140
Cell phone:  1-415-828-9001 (use when I'm at USF)
USF office: McLaren 210, Office hours by appointment.  I'm usually at USF Tuesday and Wednesday after 4:30 p.m.