Google
 

Tuesday, June 17, 2008

Triggers

Main Index
o Creating a Trigger
o Modifying a Trigger
o Renaming a Trigger
o Viewing a Trigger
o Deleting a Trigger

A TRIGGER is a special type of stored procedure, which is 'fired' automatically when the data in a specified table is modified. It is invoked when an INSERT, UPDATE, or DELETE action is performed on a table.

TSQL Stored Procedures » Modifying a Stored Procedure

The stored procedure modification is usually done using the ALTER PROCEDURE statement. It can also be done by deleting the existing stored procedure and then creating a new one with the same name. If we are using the ALTER PROCEDURE statement any of the permissions associated with the stored procedure are retained. In the other case, however, the permissions will be lost.

We can alter a stored procedure so that only the parameter definition is changed and not the permissions that are set for the stored procedure. The parameters from the ALTER PROCEDURE statement are the same as the ones for the CREATE PROCEDURE statement.

Code:
CREATE PROCEDURE spGetAvgGrade
AS
SELECT AVG(Std_Grade) FROM Students
GO
EXEC spGetAvgGrade;
Output:
AverageGrade
2
(1 row(s) affected)
Language(s): MS SQL Server
Code:
ALTER PROCEDURE spGetAvgGrade (@Course INTEGER)
AS
SELECT AVG(Std_Grade) as AverageGrade FROM Students
WHERE Std_Course = @Course
GO

EXEC spGetAvgGrade 3;
Output:
AverageGrade
1
(1 row(s) affected)
Explanation:

This example demonstrates how to use the ALTER PROCEDURE command to modify a procedure and then execute it.
Language(s): MS SQL Server

T-SQL -->Stored Procedures --> Using Parameters

Parameters can be passed to the stored procedures. This makes the procedure dynamic.

The following points are to be noted:

* One or more number of parameters can be passed in a procedure.
* The parameter name should proceed with an @ symbol.
* The parameter names will be local to the procedure in which they are defined.

The parameters are used to pass information into a procedure from the line that executes the parameter. The parameters are given just after the name of the procedure on a command line. Commas should separate the list of parameters.

The values can be passed to stored procedures by:

1. By supplying the parameter values exactly in the same order as given in the CREATE PROCEDURE statement.
2. By explicitly naming the parameters and assigning the appropriate value.

Examples
Code:
CREATE PROCEDURE spSelectStudent (@Course INTEGER, @Grade INTEGER)
AS
SELECT * FROM Students
WHERE Std_Course=@Course AND Std_Grade <= @Grade
GO
EXEC spSelectStudent 3, 2;
Output:
Id Name Std_Course Phone Std_Grade

3 Harri Robins 3 78788 1
4 Joe Philip 3 46456 2

(2 row(s) affected)
Explanation:

In the above example, the procedure is defined with two parameters. It should be noted that while executing the procedure the parameters should be passed in the same order of that in the CREATE statement. In this case, the first argument corresponds to Std_Course and second argument to Std_Grade.
Language(s): MS SQL Server

MySQL and oracle

It is not true that SQL Server 2000 is better than Oracle 9i or vice versa. Both products can be used to build stable and efficient system and the stability and effectiveness of your applications and databases depend rather from the experience of the database developers and database administrator than from the database's provider. But SQL Server 2000 has some advantages in comparison with Oracle 9i and vice versa.

The SQL Server 2000 advantages:

* SQL Server 2000 is cheaper to buy than Oracle 9i Database.
* SQL Server 2000 holds the top TPC-C performance and price/performance results.
* SQL Server 2000 is generally accepted as easier to install, use and manage.

The Oracle 9i Database advantages:

* Oracle 9i Database supports all known platforms, not only the Windows-based platforms.
* PL/SQL is more powerful language than T-SQL.
* More fine-tuning to the configuration can be done via start-up parameters.


http://www.devguru.com/home.asp

http://www.devguru.com/technologies/t-sql/home.asp

SQL is a language used to communicate with relational databases. It defines operations for inserting, deleting, retrieving and updating information organized in tables. It is also used for managing databases. Although there are standards for SQL, such as ANSI SQL92 and SQL99, most databases use their own dialect and/or extentions. Microsoft's flavor of SQL used in SQL Server 7 and SQL Server 2000 is called T-SQL.

The following aggregate functions are available:

Aggregate Function Description
AVG Average of values in a column.
COUNT Counts how many rows.
MAX Maximum value in a column.
MIN Minimum value in a column.
STDEV Sample standard deviation of the values in a column.
STDEVP Standard deviation of the values in a column.
SUM Adds the values in a column.
VAR Sample variance of the values in a column.
VARP Variance of the values in a column.

Wednesday, June 11, 2008

Creating an axis webservice

Pre-Requisites
1) Tomcat 5.0
2) Apache axis 1.4
3) jdk 1.4.2

Copy the axis folder (inside D:\axis-1_4\webapps) into Tomcat webapps folder.

1) Create a simple java program
package com.samples.webservice;

class HelloWorld
{
public String displayName(String name) {
return "Hello " + name;
}
}

2) Copy the class file along with the package structure into D:\Tomcat 5.0\webapps\axis\WEB-INF\classes folder.

3) Create a WSDD file - deploy.wsdd







3) Deploy it as a web Service
java -classpath E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\axis.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\mail.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\jaxrpc.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\
commons-logging-1.0.4.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\
lib\commons-discovery-0.2.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\
lib\saaj.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\activation.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\activation-1.1.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\mailapi_1_3_1.jar org.apache.axis.client.AdminClient deploy.wsdd

4) Access the service by using the url http://localhost:8080/axis/services/HelloWorld

5)Create a client program to access this service
import org.apache.axis.client.Call;
import org.apache.axis.client.Service;
import org.apache.axis.encoding.XMLType;
import javax.xml.rpc.ParameterMode;

public class HelloWorldClient {
public static void main(String [] args) {
try {
String endpoint =
"http://localhost:8080/axis/services/HelloWorld";

Service service = new Service();
Call call = (Call) service.createCall();

call.setTargetEndpointAddress( new java.net.URL(endpoint) );
call.setOperationName( "displayName" );
call.addParameter( "op1", XMLType.XSD_STRING, ParameterMode.IN );
call.setReturnType( XMLType.XSD_STRING );
String ret = (String) call.invoke( new Object[] { "Srikant !!" } );
System.out.println("Sent 'Srikant !!', got '" + ret + "'");
} catch (Exception e) {
System.err.println(e.toString());
}
}
}

Compile it using -----
javac -classpath E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\axis.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\jaxrpc.jar HelloWorldClient.java

Run the program using -----
java -cp .;E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\axis.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\jaxrpc.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\commons-logging-1.0.4.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\commons-discovery-0.2.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\saaj.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\activation.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\mailapi_1_3_1.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\wsdl4j-1.5.1.jar HelloWorldClient

6) To generate Client program using WSDL File
a) java -cp .;E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\axis.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\jaxrpc.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\commons-logging-1.0.4.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\commons-discovery-0.2.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\saaj.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\activation.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\mailapi_1_3_1.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\wsdl4j-1.5.1.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\endorsed\xercesImpl-2.6.2.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\endorsed\xml-apis-2.6.2.jar org.apache.axis.wsdl.WSDL2Java testsap.wsdl

b) Compile the generated classes.

c) Write a standalone java file to invoke the client as follows

class TestHelloWorld
{
public static void main(String[] args)
{
try
{
System.out.println("Hello World Starts!");
helloworld.HelloWorldService hws = new helloworld.HelloWorldServiceLocator();
helloworld.HelloWorld hw = hws.getHelloWorld();
hw.displayName("Srikant........");
System.out.println("Hello World Ends!");
}
catch (java.rmi.RemoteException rmiEx)
{
System.out.println("RMI Exception is :: " + rmiEx.getMessage());
}
catch (javax.xml.rpc.ServiceException serEx)
{
System.out.println("Service Exception is :: " + serEx.getMessage());
}
}
}

Compile this file :: javac -classpath .;./helloworld;E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\axis.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\jaxrpc.jar TestHelloWorld.java

d) Run this file
java -cp .;E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\axis.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\jaxrpc.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\commons-logging-1.0.4.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\commons-discovery-0.2.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\saaj.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\activation.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\mailapi_1_3_1.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\wsdl4j-1.5.1.jar TestHelloWorld


7) To generate WSDL file from a deployed service
java -cp .;E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\axis.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\jaxrpc.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\commons-logging-1.0.4.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\commons-discovery-0.2.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\saaj.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\activation.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\mailapi_1_3_1.jar;
E:\Softwares\servers\apache-tomcat-5.5.25\webapps\axis\WEB-INF\lib\wsdl4j-1.5.1.jar org.apache.axis.wsdl.Java2WSDL -o helloworld.wsdl -l http://localhost:8080/axis/services/HelloWorld -n "urn:helloworld" -p"com.samples.webservice" "urn:helloworld" com.samples.webservice.HelloWorld



********************************************************************************************************
java -classpath D:\Dumps\Software\axis-1_4\lib\axis.jar;
D:\Dumps\Software\axis-1_4\lib\jaxrpc.jar;
D:\Dumps\Software\axis-1_4\lib\commons-logging-1.0.4.jar;
D:\Dumps\Software\axis-1_4\lib\commons-discovery-0.2.jar;
D:\Dumps\Software\axis-1_4\lib\saaj.jar;
D:\Dumps\Software\axis-1_4\lib\activation.jar;
D:\Dumps\Software\axis-1_4\lib\mailapi_1_3_1.jar;
D:\Dumps\Software\axis-1_4\lib\endorsed\xml-apis-2.6.2.jar;
D:\Dumps\Software\axis-1_4\lib\endorsed\xercesImpl-2.6.2.jar org.apache.axis.client.AdminClient deploy.wsdd

D:\Dumps\Software\axis-1_4\lib

java -cp .;D:\Dumps\Software\axis-1_4\lib\axis.jar;
D:\Dumps\Software\axis-1_4\lib\jaxrpc.jar;
D:\Dumps\Software\axis-1_4\lib\commons-logging-1.0.4.jar;
D:\Dumps\Software\axis-1_4\lib\commons-discovery-0.2.jar;
D:\Dumps\Software\axis-1_4\lib\saaj.jar;
D:\Dumps\Software\axis-1_4\lib\activation.jar;
D:\Dumps\Software\axis-1_4\lib\mailapi_1_3_1.jar;
D:\Dumps\Software\axis-1_4\lib\wsdl4j-1.5.1.jar;
D:\Dumps\Software\axis-1_4\lib\endorsed\xml-apis-2.6.2.jar;
D:\Dumps\Software\axis-1_4\lib\endorsed\xercesImpl-2.6.2.jar org.apache.axis.wsdl.Java2WSDL -o cureservice.wsdl -l http://localhost:8080/axis/services/CUREWebService -n "urn:CUREWebService" -p"com.cadence.cure.webservice" "urn:CUREWebService" com.cadence.cure.webservice.CUREWebService


java -cp .;D:\Dumps\Software\axis-1_4\lib\axis.jar;
D:\Dumps\Software\axis-1_4\lib\jaxrpc.jar;
D:\Dumps\Software\axis-1_4\lib\commons-logging-1.0.4.jar;
D:\Dumps\Software\axis-1_4\lib\commons-discovery-0.2.jar;
D:\Dumps\Software\axis-1_4\lib\saaj.jar;
D:\Dumps\Software\axis-1_4\lib\activation.jar;
D:\Dumps\Software\axis-1_4\lib\mailapi_1_3_1.jar;
D:\Dumps\Software\axis-1_4\lib\wsdl4j-1.5.1.jar;
D:\Dumps\Software\axis-1_4\lib\endorsed\xercesImpl-2.6.2.jar;
D:\Dumps\Software\axis-1_4\lib\endorsed\xml-apis-2.6.2.jar org.apache.axis.wsdl.WSDL2Java cureservice.wsdl