Welcome to the world of Stored Procedures- Oracle, MS SQL,MySQL

Welcome to Stored Procedures

A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database .Programmers and administrators can execute stored procedures either from the SQL Server Management Studio or from within an application as required.
Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again.
Stored procedures improve performance by reducing network traffic and CPU load.

Benefit	Explanation of benefit
Modular programming	You can write a stored procedure once, then call it from multiple places in your application.
Performance	Stored procedures provide faster code execution and reduce network traffic. Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimze your SQL code every time it runs. Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.
Security	Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.

 You can find detailed stored procedures of popular database systems here:
a. Oracle  : http://codingbasics.blogspot.com/2011/07/stored-procedures-in-ms-sql.html
b. MSSQL : http://codingbasics.blogspot.com/2011/07/stored-procedures-in-oracle.html
c. MySQL : http://codingbasics.blogspot.com/2011/07/stored-procedures-in-mysql.html

Stored Procedures in MySQL

Working with Stored Procedures in MySQL

Writing the first stored procedure

The first stored procedure is very simple. It retrieves all products from products table. First let’s take a look at the stored procedure source code below:

1	DELIMITER //
2	CREATE PROCEDURE GetAllProducts()

3	BEGIN
4	SELECT *  FROM products;

5	END //
6	DELIMITER ;

The first command you see is DELIMITER //. This command is not related to the stored procedure. DELIMITER statement in MySQL is used to change the standard delimiter (semicolon) to another. In this case, the delimiter is changed from semicolon(;) to //, so you can have multiple SQL statements inside stored procedure which can be separated by the semicolon. After the END keyword we use delimiter // to show the end of the stored procedure. The last command changes the delimiter back to the standard one (semicolon).
more

Stored Procedures in Oracle

Working with Stored Procedures in Oracle

Procedures are written in Oracle's PL/SQL programming language.
Sample Procedure
In Notepad, type:
CREATE OR REPLACE PROCEDURE sample
IS
BEGIN
  NULL;
END;
Save your file as sample.sql.
Let’s go through the procedure line by line. The lines are numbered for your convenience:
1 CREATE OR REPLACE PROCEDURE sample
2 IS
3 BEGIN
4   NULL;
5 END;
Line 1:
CREATE OR REPLACE PROCEDURE is an SQL statement that instructs Oracle to create a procedure called sample, and to overwrite it if it exists.
Line 2:
The IS keyword signals that a PL/SQL body will follow.
Line 3:
The BEGIN keyword signals the start of a PL/SQL body.
Line 4:
The NULL PL/SQL statement indicates that no action should be performed. We cannot just leave it out, because at least one statement is required in a PL/SQL body.
Line 5:
The END keyword signals the end of the PL/SQL block.
Now let's move on to creating a procedure.


Read more

JSP Tutorial -2 : Connect to MYSQL database and basic operations

This is detailed java code to connect a jsp page to mysql database
and create a table of given name in the specified database.

Steps to Follow:

1. create a database named 'usermaster' in mysql and create table "user_master".
2. Create an application directory named 'user' in the Tomcat.
3. Before running this java code you need mysql connector jar
   in the Tomcat-6.0.16/webapps/user/WEB-INF/lib directory.

Creating JSP pages:
In this example, we have created two JSP pages.
First page is to provide link to create table and the next page for processing the table creation.

1. Hello_database_query.jsp
2.create_table.jsp

1. Hello_database_query.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<html>
    <head>
        <title>Create table in mysql database using jsp</title>
    </head>
    <body>
        <TABLE style="background-color: #ffffcc;">
            <TR>
                <TD align="center"><h2>To create
                    table click on the link given below</h2></TD>
            </TR>
            <TR>
                <TD align="center"><A HREF="create_table.jsp">
                <font size="+2" color="blue">create table</font>
            </TR>
        </TABLE>
    </body>
</html>
Save this code as a .jsp file named "Hello_database_query.jsp" in the directory Tomcat-6.0.16/webapps/user/.
You can run this jsp page typing http://localhost:8080/user/Hello_database_query.jsp url in address bar of the browser.



2.create_table.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<html>
    <head>
        <title>display data from the table using jsp</title>
    </head>
    <body>
        <%
            /* Create string of connection url within specified
                 format with machine name, port number
            and database name. Here machine name id localhost
              and database name is usermaster. */
            String connectionURL = "jdbc:mysql://localhost:3306/usermaster";
            // declare a connection by using Connection interface
            Connection connection = null;
            // declare object of Statement interface that is used for
            // executing sql statements.
            Statement statement = null;
            try {
                // Load JBBC driver "com.mysql.jdbc.Driver".
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                /* Create a connection by using getConnection() method
                   that takes parameters of string type connection url, user
                   name and password to connect to database. */
                connection =
                   DriverManager.getConnection(connectionURL, "root", "root");
                /* createStatement() is used for create statement object
                  that is used for sending
                sql statements to the specified database. */
                statement = connection.createStatement();
                // sql query to retrieve values from the secified table.
                String QueryString =
                 "create table user_master(id int not null auto_increment,name " +
                        "varchar(25),city varchar(20), primary key(id));";
                // execyteUpdate() mothod execute specified sql query.
                statement.executeUpdate(QueryString);
        %> <TABLE border="0" style="background-color: #ffffcc;">
        <TR>
            <TD align="center" ><font size="+3" color="green">Congratulations !
                </font></TD>
            <tr><TD align="center" ><font size="+2">Table of specified name is
                created successfully.</font>
            </TD></tr>
        </TR>
        <% } catch (Exception ex) {%>
        <TABLE border="0" style="background-color: #ffffcc;">
            <TR>
                <TD align="center" ><font size="+3" color="red">Some problems
               to create table.</font></TD>
            </TR>
            <% } finally {
                // close all the connections.
                statement.close();
                connection.close();
            }
            %><tr><td align="center" >
                    <A HREF="Hello_database_query.jsp">
                <font size="5" color="blue">back to home page</font></A></td>
        </tr> </TABLE>
    </body>
</html>
Save this code with name "create_table.jsp" in the directory Tomcat-6.0.16/webapps/user/.
This jsp code creates table of specified name in the mysql database and shows a response page.
If table exists already or found any error to make connection with database then shows an error page.

PHP MYSQL Tutorial 2: Insert, Update, Delete records in a Table.

This is second of PHP MYSQL basics tutorials. If you have not read the first one, please do it here :
http://codingbasics.blogspot.com/2011/01/php-mysql-tutorial-1-install-php-mysql.html
where each and every step is very detailed explained with code.

Insert rows into table


Insert.php


<html> 
<body> 
<? 
if ($UserName) 
{ 
mysql_connect (localhost, username, password) or die ("Problem connecting to DataBase"); 
$query = "insert into cust values ('$cid','$UserName','$LastName','$Details')"; 
$result = mysql_db_query("eshop", $query); 


echo "Data inserted. new table:<br><p></p>"; 
$query = "SELECT * FROM cust"; 
$result = mysql_db_query("eshop", $query); 


if ($result) { 
echo "<table width=90% align=center border=1><tr> 
<td align=center bgcolor=#00FFFF>idx</td> 
<td align=center bgcolor=#00FFFF>User Name</td> 
<td align=center bgcolor=#00FFFF>Last Name</td> 
<td align=center bgcolor=#00FFFF>Free Text</td> 
</tr>"; 


while ($r = mysql_fetch_array($result)) { 
$cid = $r["cid"]; 
$user = $r["UserName"]; 
$last = $r["LastName"]; 
$det = $r["Details"]; 


echo "<tr> 
<td>$idx</td> 
<td>$user</td> 
<td>$last</td> 
<td>$det</td> 
</tr>"; 


} // end of while loop 
echo "</table>"; 
} else { 
echo "No data."; 
} // end of if ($result) 
} else { 
echo "No UserName Entered. Please go back and reenter UserName"; 
} // end of if ($UserName) 
echo "<p></p>"; 
include ('sitelinks.txt'); 
?> 
</body> 
</html>

Read more for update and Delete records from the table.

PHP MYSQL - Tutorial 1 : Install PHP, MYSQL , connect and view the contents of a table

Most of you already experienced by now, its not that easy to install apache, php and mysql
and get started with PHP quickly. you may face more problems especially on windows.
For this, there is wonderful free tool called XAMPP.

XAMPP is an easy to install Apache distribution containing MySQL, PHP and Perl
THis is available for Win,Linux, Solaris, Mac OS at : http://www.apachefriends.org/en/xampp.html

When I used Xampp, the only issue I faced was "apache was not started" , but it was due to a the port 80
was already in by other service in my system.In that case, change the port in httpd.conf
or stop the service( if you do not need it now like tomcat etc.,)

Okay, let me assume that PHP is installed.
This tutorial series is all about connecting to MYSQL Database , and performing varios database operations

First step obviously is to create a table.
You can install SQLYOG( excellent MySQL client) to do the qureying on the MYSQL Database Server installed by Xampp.
or since this is a basic tutorial , you can even type the commands in mysql command line.

Lets get into code now :

Submit Form with JQuery , PHP, HTML,MYSQL

Let us understand with a simple example of how to submit a form to a database with JQuery, PHP, HTML, MySql.

This is a direct working code with explanation given whereever required.


<div class="container">  
<form id="submit" method="post">  
<fieldset>  
              <legend>Enter Employee Information</legend>  
  
              <label for="fname">Employee First Name:</label>  
           <input id="fname" class="text" name="fname" size="20" type="text">  
  
              <label for="lname">Employee Last Name:</label>  
              <input id="lname" class="text" name="lname" size="20" type="text">  
  
            <button class="button positive"> <img src="../images/icons/somepic.png" alt=""> 
                 Add Employee </button>  
  </fieldset>  
</form>  
<div class="success" style="display:none;">Employee has been added.</div>  
</div>  


// It is relatively easy to make ajax calls with jquery. we need to pass type( GET or POST),
// URL ( of the destination), data,  success( what to be done post successful execution).

$(document).ready(function(){  
    $("form#submit").submit(function() {  
    // we want to store the values from the form input box, then send via ajax below

    var fname     = $('#fname').attr('value'); 

// We are storing it in the variable called “fname” and we are taking it from the input
       //  field with the id of “fname” and the “.attr(‘value’) tells jQuery to take the value from
       // the attribute of the value from the input field

    var lname     = $('#lname').attr('value');  
        $.ajax({  
            type: "POST",  
            url: "ajaxcalls.php",  
            data: "fname="+ fname +"& lname="+ lname,  
            success: function(){  
                $('form#submit').hide(function(){$('div.success').fadeIn();});  
  
            }  
        });  
    return false;  
    });  
});  



Add this in ajaxcalls.php File:



<?php  
  
    include ("../../inc/config.inc.php");  
  
    // Employee INFORMATION  
    $fname        = htmlspecialchars(trim($_POST['fname']));  
    $lname        = htmlspecialchars(trim($_POST['lname']));  
  
    $addClient  = "INSERT INTO Employees (fname,lname) VALUES ('$fname','$lname')";  
    mysql_query($addClient) or die(mysql_error());  
  
?>  


Hope you like it.