The SQL*Plus Buffer

Using SQL*Plus is the classic way of creating and managing Oracle databases. It is available with all installations on all operating systems. So, if you know how to use it, you'll be able to process an Oracle database on any operating system.

To use SQL*Plus, find the icon under the Start/Programs/Oracle.. and click it. Sign onto your database using the username and password. You should see a window similiar to the one shown below:

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 26 13:51:58 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>

Among the many features of SQL*Plus is a text editor. As you type into SQL*Plus, your keystrokes are placed into a buffer. When you press Enter, SQL*Plus will save what you typed into a line in the buffer and go to a new line, but it will neither finish the statement not execute it.

To see the contents of the buffer, type LIST. The line shown with an asterisk is the current line. You can change the current line by entering LIST followed by the line number such as LIST 1. To change the contents of the current line, enter change/astring/bstring, where astring is the string you want to change, and bstring is the replacement string.

The Change command is fine for making small changes, but it could be unworkable for editing longer expressions such as stored procedures. For such purposes, you can set up SQL*Plus to connect to your text editor. Before doing this, however, you should create a directory for your Oracle code, say C:\MyDirectory\OracleCode. Find the SQL*Plus icon your C drive or desktop and right click to reveal properties and enter the name of the new directory in the Start In text box. Click OK and restart SQL*Plus.

Next, click the Edit menu in the SQL*Plus window menu and then select Editor/Define Editor. You can enter the name of your editor here. Notepad is the default editor and works just fine for editing your SQL code. Click OK. Now you have defined your text editor for SQL*Plus and set it to point to your directory. Now whenever you type Edit, SQL*Plus will invoke Notepad or whichever editor you selected.

You can now create,save,and edit files of code in that directory. For example, if you enter the following:

SELECT Table_Name
FROM USER_TABLES;

After the contents appear, type Edit, SQL*Plus will bring up your editor with the contents of the buffer. Use SAVE AS to give the file a new name, such as EX1.txt. Close your editor and you will return to SQL*Plus. To edit the file you just created, type EditEX1.txt. and you will enter your editor with that file. When you exit your editor and return to SQL*Plus, EX1.txt will be stored in the SQL*Plus buffer. To cause the buffer contents to execute, enter the right-leaning slash(/).

Limiting Query Results in MySQL

When using MySQl databases, a SQL clause that can be used in queries is LIMIT. In a SELECT query, WHERE dictates which records to return, and ORDER BY decides how those records are sorted. The LIMIT clause states how many records to return. It is used like so:

SELECT * FROM tablename LIMIT x;
In this case, only the initial x records are returned from the query. So to return 5 records, you would write the query SELECT * FROM tablename LIMIT 5;

Using the format below, you can have y records returned, starting at x.

SELECT * FROM tablename LIMIT x,y;
SELECT * FROM tabename LIMIT 10,10;

This statement would return records 11 through 20. This is because like arrays, result sets begin at 0 when it comes to LIMIT, so 10 is the 11th record. To return the last records in a data set, you would write the query as follows:

SELECT * FROM tablename LIMIT 20,1;
Here the 20th record is returned.

The LIMIT x,y clause is used most frequently when paginating query results(showing them in blocks over multiple pages). However, the LIMIT clause does not improve query processing speed as the SQL engine still has to assemble the entire result set and then truncate the list. In addition, the LIMIT clause is not part of the SQL standard and is therefore not available in all databases.

Nonetheless, in MySQL, you can use LIMIT with most types of queries, not just with SELECT statements. You can also use the LIMIT with WHERE and/or ORDER BY clauses, but LIMIT must always come last in the query.

Using Output Buffering in PHP

By default, anything that a PHP script prints or any HTML outside of the PHP tags(even in include files) is immediately sent to the Web browser. Output buffering is a PHP feature that overrides this behavior. Instead of immediately sending HTML to the Web browser, that output is placed in a buffer-temporary memory. Then, when the buffer if flushed, it is sent to the Web browser. There can be performance benefits with output buffering, but the main benefit is that it eliminates those headers already sent error messages.

Some functions such as header(),setcookie(), and session_start() can only be called if nothing has been sent to the Web browser. Without output buffering, nothing is sent to the Web browser until the end of the page. As such, you can call these functions at any point in a script.

To begin output buffering, use the ob_start() function. This should be at the beginning of your script right after the opening PHP tag. Once this function is called, every echo() and similiar function will send data to a memory buffer rather than to the Web browser. And, HTTP calls such as header() and setcookie() will not be buffered and operate as usual.

At the end of your script, call the ob_end_flush() function to send the accumulated buffer to the Web browser. This has the effect of turning off the output buffering.

Using The Tenary Operator in PHP

The tenary operator provides a more concise way to structure conditional statements. Instead of if-else or case statements, you could write it as follows:

(condition) ? value1 : value2

The condition in parentheses is evaluated; if it is true, the first value will be returned (value1). If the condition is false, value2 is returned. Because the tenary operator returns a value, the entire structure is often used to assign a value to a variable or used as an argument for a function. For example, in PHP the line below will print out SET or NOT SET, depending upon the status of the variable $var.

echo (isset($var)) ? 'SET' : 'NOT SET';

Another example of using the tenary operator would be to alternate the background colors on table rows. Here is an example:

$bg = '#eeeeee'; //set the initial background color
$bg = ($bg == '#eeeeee' ? '#ffffff' : '#eeeeee'); //switch the background color
echo '

Here the initial row background color is assigned to the variable $bg and then the tenary operation, alternating colors, is assigned back into $bg. The result is a table with rows of alternating background colors.

Creating Files Using PHP

When working with files, PHP has several useful functions such as fopen(),fread(), fwrite() and fclose(). Fopen() opens a file or url and the sytax is fopen(string $filename, string $mode). If filename is of the form "scheme://...", it is assumed to be a URL and PHP will search for a protocol handler (also known as a wrapper) for that scheme.

If PHP has decided that filename specifies a local file, then it will try to open a stream on that file. The file must be accessible to PHP, so you need to ensure that the file access permissions allow this access.

If PHP has decided that filename specifies a registered protocol, and that protocol is registered as a network URL, PHP will check to make sure that allow_url_fopen is enabled. If it is switched off, PHP will emit a warning and the fopen call will fail.

Here is a simple PHP script that opens an existing text file, creates and writes to a new file and then closes the file.

?php
//create and open a new file
$newfile = fopen("c:\\xampp\\htdocs\\philduhe.txt", "a+");
fwrite($newfile, "This is Phil's new file.");
fclose($newfile);
echo "All done!";
?>

The mode parameter specifies the type of access or stream to the file. It may be any of the following listed below. In my script, I used the mode of +a to read and write to to the file.

'r' Open for reading only; place the file pointer at the beginning of the file.
'r+' Open for reading and writing; place the file pointer at the beginning of the file.
'w' Open for writing only; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.
'w+' Open for reading and writing; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.
'a' Open for writing only; place the file pointer at the end of the file. If the file does not exist, attempt to create it.
'a+' Open for reading and writing; place the file pointer at the end of the file. If the file does not exist, attempt to create it.
'x' Create and open for writing only; place the file pointer at the beginning of the file. If the file already exists, the fopen() call will fail by returning FALSE. If the file does not exist, attempt to create it.
'x+' Create and open for reading and writing; place the file pointer at the beginning of the file. If the file already exists, the fopen() call will fail. If the file does not exist, attempt to create it.

On the Windows platform, be careful to escape any backslashes used in the path to the file, or use forward slashes.

Using control flow operators in MySQL

There are several control flow operators available in MySQL. This post will show how to use the CASE operator. One version of this operator employs the following syntax:

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result...] [ELSE result] END.

In this version, the CASE statement returns the result value when value=compare_value. Here is an example:

mysql> SELECT CASE 1 WHEN 1 THEN 'apple'
-> WHEN 2 THEN 'orange'
-> ELSE 'more' END;
+--------------------------------+
CASE 1 WHEN 1 THEN 'apple' WHEN 2
THEN 'orange' ELSE 'more' END
+--------------------------------+
| apple |
+--------------------------------+
1 row in set (0.00 sec)

Another version of the CASE statement is:

CASE WHEN [condition] THEN result [WHEN condition THEN result...] [ELSE result] END.

Here is how this version would work as follows:

mysql> SELECT CASE WHEN 2>1 THEN 'true'
-> ELSE 'false' END;
+-------------------------------------------+
| CASE WHEN 2>1 then 'true' ELSE 'false' END
+-------------------------------------------+
| true
+-------------------------------------------+
1 row in set (0.00 sec)

In this example, the result value is returned for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

The default return type of a CASE expression is the compatible type of all return values. If used in a string context, the result is returned as a string. If used in a numeric context, then the result is returned as a decimal or integer value.

The syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement which does not allow an ELSE NULL clause, and it is terminated with END CASE instead of END.

MYSQL User Defined Variables

In MySQL, you can define your own variables to use in queries. You can store user defined variables in one statement and refer to it in another statement. User defined variables are specfic to a connection session. That is, a user variable defined in one session cannot be seen or used in another session. All variables for a given client connection are automatically freed when that client exits.

User variables are written as @var, where the variable name "var" consists of alphanumeric characters from the current character set, “.”, “_”, and “$”. One way to establish user variables is through a Set statement as follows:

SET @var = expr [, @var = expr] ...
For SET, either = or := can be used as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements. Here is a simple example of how user-defined variables could be applied:

mysql> set @var1=2,@var1=4,@var3=6;
Query OK, 0 rows affected (0.00 sec)
mysql> select @var1,@var2,@var3,@var4:=@var1+@var2+@var3;

The above select statement will return values of 2,4,6,12.

Using MySql Prepared Statements

Prepared Statements were introduced in MySQl 4.1 and provide the option to set up a SQL statement query once and execute it many times with different parameters.  Prepared Statememts replace ad hoc query strings, are executed on the server, and in doing offer increased security and performance.

A typical prepared statement would look something like:
 SELECT * FROM employee WHERE code = ?

The ? is a placeholder. When you execute the above query, you would need to supply the value for it, which would replace the ? in the query above.  I have prepared(no pun intended..) a sample from a database I created to keep track of job applications.  The syntax is depicted below:

mysql>PREPARE fa FROM 'SELECT * FROM job WHERE title=?';
Query OK, 0 rows affected (0.00 sec)
Statement Prepared
mysql>SET @fa_param='Financial Analyst';
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE fa USING @fa_param;

+--------+-------------------+--------+-------------------------------------+
| job_id | title             | salary | requirements | requisition | company_id |
+--------+-------------------+--------+-------------------------------------+----+
|     11 | Financial Analyst |      0 | excel;pivot tables | NULL |       11 |
|     17 | Financial Analyst |      0 | Crystal Reports    | NULL |       24 |
..........
|    117 | Financial Analyst |     24 | nonprofit experience | NULL |     78 |
|    141 | Financial Analyst |      0 | monitoring and reporting |NULL | 108 |
+--------+-------------------+--------+-------------------------------------+----+

13 rows in set (0.00 sec)
mysql>DEALLOCATE PREPARE fa;
Query OK, 0 rows affected (0.00 sec)
mysql>

Prepared Statements work with DML(INSERT,DELETE,UPDATE, and REPLACE), CREATE TABLE and SELECT queries. The increased security comes from separating data  being supplied from SQL logic.  In addition,  the separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack.

Normally when you are dealing with an ad hoc query, you need to be careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.


Second, the increase in performance comes from the MySQL engine parsing the query only once. When you initially prepare the statement, MySQL will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to decreased processing time if you need to run the same query many times, such as when doing many INSERT statements.

As a side note, PHP has an API to Prepared Statements through its "mysqli" extension. You can read more about it in the mysqli section of the PHP Manual.

Uploading Files With PHP

The process of uploading a file has two dimensions. First, the HTML form must be displayed with the proper code to allow for uploads. Then, upon submission of the form, your PHP script must copy the uploaded file to its final destination.

However, for this process to work, a few things must be in place:
-PHP must run with the right settings
-A temporary storage directory must exist with the correct permissions
-The final storage directory must exist with the correct permissions.

There are several settings in the PHP configuration file(php.ini) that dictate file uploads. In the file uploads section of this file, you will see the following three lines:

file_uploads=On
;upload_tmp_dir =
upload_max_filesize = 2M

The first line dictates whether or not uploads are allowed. The second line states where the uploaded files should be temporarily stored. On most systems, this setting can be left commented out. On Windows, you would create a temporary directory, set to a value such as C:\temp, making sure the line is not preceded by a semicolon.

Finally, a maximum upload file size is set. The "M" is shorthand for megabytes in configuration settings. Save the php.ini file and restart your Web server. You can confirm the settings by running the phpinfo()script.

Next prepare your HTML upload form:

<html>
<head>
<title>File Upload Form</title>
</head>
<body>
This form allows you to upload a file to the server.<br>
<form enctype="multipart/form-data" action="upload.php" method="post">
Please choose a file: <input type="file" name="uploaded" /><br />
<input type="submit" value="Upload" />
</form>
</body>
</html>

And a PHP script which receives the HTML form is depicted below:

<?php
$target = "upload/";
$target = $target . basename( $_FILES['uploaded']['name']) ;
if(move_uploaded_file($_FILES['uploaded']['tmp_name'],$target))
{
echo "The file ". basename( $_FILES['uploaded']['name']). " has been uploaded";
}
else {
echo "Sorry, there was a problem uploading your file.";
}
?>

Hope this helps someone out there!