Database & DDL Triggers in Oracle

In this post, we shall create a series of triggers to illustrate the use of Database triggers in Oracle.

Database Triggers:-

Database triggers are fired on Login & Logout. Let us create a trigger to maintain a list of logins & logouts. First a table to store the login information:-


create table LoginStats(username varchar(50),datetimeoflogin date)

Here is the trigger:-

create or replace trigger logintrigger
after logon on database
begin
insert into LoginStats values(ora_login_user,sysdate);
end;

A logon trigger can only be a after trigger.



A trigger for Logout

The table :

create table LogoutStats(username varchar(50),datetimeoflogout date)


create or replace trigger logofftrigger
before logoff on database
begin
insert into LogoutStats values(ora_login_user,sysdate);
end;

DDL Triggers are created for Create,Alter & Drop statements.

Here is a trigger that tracks created objects:-

create table CreatedObjects(CreatedByUser varchar(100),ObjectName varchar(100),ObjectType varchar(100),DateofCreation date)



CREATE or replace TRIGGER Databasecreateobjecttrigger AFTER CREATE
   ON database
begin
insert into CreatedObjects values(ora_login_user,ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;

For DML Triggers , please refer http://champakonline.blogspot.com/2010/10/some-triggers-in-oracle.html

Calling a Web Service through Android and passing Parameters

In the last post http://champakonline.blogspot.com/2012/02/calling-web-service-through-android.html , we learned how to call a .Net Web Service from Android. In this post, we shall call a Web Service that requires parameters to be sent.
For this application, we shall use the Web Service whose WSDL is at http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx?WSDL

The WSDL is



<wsdl:definitions targetNamespace="http://tempuri.org/"><wsdl:types><s:schema elementFormDefault="qualified" targetNamespace="http://tempuri.org/"><s:element name="getBirthday"><s:complexType><s:sequence><s:element minOccurs="0" maxOccurs="1" name="name" type="s:string"/></s:sequence></s:complexType></s:element><s:element name="getBirthdayResponse"><s:complexType><s:sequence><s:element minOccurs="0" maxOccurs="1" name="getBirthdayResult" type="s:string"/></s:sequence></s:complexType></s:element><s:element name="GetAQuote"><s:complexType/></s:element><s:element name="GetAQuoteResponse"><s:complexType><s:sequence><s:element minOccurs="0" maxOccurs="1" name="GetAQuoteResult" type="s:string"/></s:sequence></s:complexType></s:element></s:schema></wsdl:types><wsdl:message name="getBirthdaySoapIn"><wsdl:part name="parameters" element="tns:getBirthday"/></wsdl:message><wsdl:message name="getBirthdaySoapOut"><wsdl:part name="parameters" element="tns:getBirthdayResponse"/></wsdl:message><wsdl:message name="GetAQuoteSoapIn"><wsdl:part name="parameters" element="tns:GetAQuote"/></wsdl:message><wsdl:message name="GetAQuoteSoapOut"><wsdl:part name="parameters" element="tns:GetAQuoteResponse"/></wsdl:message><wsdl:portType name="HypatiaQuotesServiceSoap"><wsdl:operation name="getBirthday"><wsdl:input message="tns:getBirthdaySoapIn"/><wsdl:output message="tns:getBirthdaySoapOut"/></wsdl:operation><wsdl:operation name="GetAQuote"><wsdl:input message="tns:GetAQuoteSoapIn"/><wsdl:output message="tns:GetAQuoteSoapOut"/></wsdl:operation></wsdl:portType><wsdl:binding name="HypatiaQuotesServiceSoap" type="tns:HypatiaQuotesServiceSoap"><soap:binding transport="http://schemas.xmlsoap.org/soap/http"/><wsdl:operation name="getBirthday"><soap:operation soapAction="http://tempuri.org/getBirthday" style="document"/><wsdl:input><soap:body use="literal"/></wsdl:input><wsdl:output><soap:body use="literal"/></wsdl:output></wsdl:operation><wsdl:operation name="GetAQuote"><soap:operation soapAction="http://tempuri.org/GetAQuote" style="document"/><wsdl:input><soap:body use="literal"/></wsdl:input><wsdl:output><soap:body use="literal"/></wsdl:output></wsdl:operation></wsdl:binding><wsdl:binding name="HypatiaQuotesServiceSoap12" type="tns:HypatiaQuotesServiceSoap"><soap12:binding transport="http://schemas.xmlsoap.org/soap/http"/><wsdl:operation name="getBirthday"><soap12:operation soapAction="http://tempuri.org/getBirthday" style="document"/><wsdl:input><soap12:body use="literal"/></wsdl:input><wsdl:output><soap12:body use="literal"/></wsdl:output></wsdl:operation><wsdl:operation name="GetAQuote"><soap12:operation soapAction="http://tempuri.org/GetAQuote" style="document"/><wsdl:input><soap12:body use="literal"/></wsdl:input><wsdl:output><soap12:body use="literal"/></wsdl:output></wsdl:operation></wsdl:binding><wsdl:service name="HypatiaQuotesService"><wsdl:port name="HypatiaQuotesServiceSoap" binding="tns:HypatiaQuotesServiceSoap"><soap:address location="http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx"/></wsdl:port><wsdl:port name="HypatiaQuotesServiceSoap12" binding="tns:HypatiaQuotesServiceSoap12"><soap12:address location="http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx"/></wsdl:port></wsdl:service></wsdl:definitions>



Since we shall be calling the getBirthday method. The relevant portions to remember are :-


1) <wsdl:definitions targetNamespace="http://tempuri.org/">

2)  <soap:operation soapAction="http://tempuri.org/getBirthday" style="document"/>

3)  <s:sequence><s:element minOccurs="0" maxOccurs="1" name="name" type="s:string"/></s:sequence>

The first one gives the namespace to be used, the second is the soap action, and the third is the parameter to be passed. The name of the parameter is "name" while the type is "s:string".

It is important to note that, should any of these three items be used wrongly, the parameter passing and calling would not work.


Here is the code for the activity:-

HypatiaFriendsActivity.java

package hypatia.friends;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import org.ksoap2.SoapEnvelope;
import org.ksoap2.serialization.PropertyInfo;
import org.ksoap2.serialization.SoapObject;
import org.ksoap2.serialization.SoapSerializationEnvelope;
import org.ksoap2.transport.HttpTransportSE;
public class HypatiaFriendsActivity extends Activity  implements OnClickListener{
    /** Called when the activity is first created. */
    private Button b;
    private TextView tv;
    private EditText txtname;
    private TextView txtBirthday;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        b=(Button)findViewById(R.id.button1);
        tv=(TextView)findViewById(R.id.txtName);
        txtBirthday=(TextView)findViewById(R.id.txtBirthday);
        txtname=(EditText)findViewById(R.id.txtName);
        b.setOnClickListener(this);
       
    }
    @Override
    public void onClick(View arg0) {
        // TODO Auto-generated method stub
        try
        {
        String namespace = "http://tempuri.org/";
        String url ="http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx?WSDL";   
        String soapaction = "http://tempuri.org/getBirthday";
        String methodname = "getBirthday";
        SoapObject request = new SoapObject(namespace, methodname);    
        request.addProperty("name","" + txtname.getText());
        SoapSerializationEnvelope envelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
        envelope.dotNet=true;
        envelope.setOutputSoapObject(request);
        HttpTransportSE androidHttpTransport = new HttpTransportSE (url);

       
            androidHttpTransport.call(soapaction, envelope);
            org.ksoap2.serialization.SoapObject resultsRequestSOAP =(org.ksoap2.serialization.SoapObject) envelope.bodyIn;
            txtBirthday.setText("" + resultsRequestSOAP.getProperty(0));
        }
        catch(Exception ex)
        {
            tv.setText("" + ex);
        }
    }
}

main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="@string/hello" />



    <TextView
        android:id="@+id/txtBirthday"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/blank"
        android:textAppearance="?android:attr/textAppearanceLarge" />




    <EditText
        android:id="@+id/txtName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:inputType="textPersonName" >

        <requestFocus />
    </EditText>

    <Button
        android:id="@+id/button1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/buttontext" />

</LinearLayout>

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="hello">Hypatia Friend Finder</string>
    <string name="app_name">HypatiaFriends</string>
    <string name="buttontext">Get Birthday</string>
    <string name="blank"></string>

</resources>

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="hypatia.friends"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk android:minSdkVersion="8" />
<uses-permission
        android:name="android.permission.INTERNET" />
    <application
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <activity
            android:label="@string/app_name"
            android:name=".HypatiaFriendsActivity" >
            <intent-filter >
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

The Internet permission is important here, because this application accesses the internet.
<uses-permission
        android:name="android.permission.INTERNET" />

 

Calling a Web Service through Android

Calling a Web Service using Android

In this post we shall learn how to call a web service using Android.

For this purpose we shall use the Hypatia Quotes Web Service at
http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx

which returns random quotes of Hypatia.

The WSDL for this Web Service is :-

http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx?WSDL

<wsdl:definitions targetNamespace="http://tempuri.org/"><wsdl:types><s:schema elementFormDefault="qualified" targetNamespace="http://tempuri.org/"><s:element name="GetAQuote"><s:complexType/></s:element><s:element name="GetAQuoteResponse"><s:complexType><s:sequence><s:element minOccurs="0" maxOccurs="1" name="GetAQuoteResult" type="s:string"/></s:sequence></s:complexType></s:element></s:schema></wsdl:types><wsdl:message name="GetAQuoteSoapIn"><wsdl:part name="parameters" element="tns:GetAQuote"/></wsdl:message><wsdl:message name="GetAQuoteSoapOut"><wsdl:part name="parameters" element="tns:GetAQuoteResponse"/></wsdl:message><wsdl:portType name="HypatiaQuotesServiceSoap"><wsdl:operation name="GetAQuote"><wsdl:input message="tns:GetAQuoteSoapIn"/><wsdl:output message="tns:GetAQuoteSoapOut"/></wsdl:operation></wsdl:portType><wsdl:binding name="HypatiaQuotesServiceSoap" type="tns:HypatiaQuotesServiceSoap"><soap:binding transport="http://schemas.xmlsoap.org/soap/http"/><wsdl:operation name="GetAQuote"><soap:operation soapAction="http://tempuri.org/GetAQuote" style="document"/><wsdl:input><soap:body use="literal"/></wsdl:input><wsdl:output><soap:body use="literal"/></wsdl:output></wsdl:operation></wsdl:binding><wsdl:binding name="HypatiaQuotesServiceSoap12" type="tns:HypatiaQuotesServiceSoap"><soap12:binding transport="http://schemas.xmlsoap.org/soap/http"/><wsdl:operation name="GetAQuote"><soap12:operation soapAction="http://tempuri.org/GetAQuote" style="document"/><wsdl:input><soap12:body use="literal"/></wsdl:input><wsdl:output><soap12:body use="literal"/></wsdl:output></wsdl:operation></wsdl:binding><wsdl:service name="HypatiaQuotesService"><wsdl:port name="HypatiaQuotesServiceSoap" binding="tns:HypatiaQuotesServiceSoap"><soap:address location="http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx"/></wsdl:port><wsdl:port name="HypatiaQuotesServiceSoap12" binding="tns:HypatiaQuotesServiceSoap12"><soap12:address location="http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx"/></wsdl:port></wsdl:service></wsdl:definitions>

Out of this document two parts are very important, and I have underlined them:-
1) <soap:operation soapAction="http://tempuri.org/GetAQuote" style="document"/>and

2) <wsdl:operation name="GetAQuote">

The first one becomes the soap action, and the second becomes the method name.
Now, getting to the android part.First of all download the ksoap2-android-assembly-2.6.0-jar-with-dependencies.jar. This file is currently available at :-

http://ksoap2-android.googlecode.com/svn/m2-repo/com/google/code/ksoap2-android/ksoap2-
android-assembly/2.6.0/ksoap2-android-assembly-2.6.0-jar-with-dependencies.jar
Next, add this file to the current project.
1)

2)

Here is the complete project in Eclipse:-

The Activity class in eclipse

HypatiaQuotesClientActivity

package quotes.hypatia;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.TextView;

import org.ksoap2.SoapEnvelope;
import org.ksoap2.serialization.SoapObject;
import org.ksoap2.serialization.SoapSerializationEnvelope;
import org.ksoap2.transport.HttpTransportSE;
public class HypatiaQuotesClientActivity extends Activity  implements OnClickListener{
    /** Called when the activity is first created. */
    private Button b;
    private TextView tv;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        b=(Button)findViewById(R.id.button1);
        tv=(TextView)findViewById(R.id.txtQuote);
        b.setOnClickListener(this);
       
    }
    @Override
    public void onClick(View arg0) {
        // TODO Auto-generated method stub
        try
        {
        String namespace = "http://hypatiasoftwaresolutions.com";
        String url ="http://hypatiasoftwaresolutions.com/HypatiaQuotesService.asmx?WSDL";   
        String soapaction = "http://tempuri.org/GetAQuote";
        String methodname = "GetAQuote";
        SoapObject request = new SoapObject(namespace, methodname);        
        SoapSerializationEnvelope envelope =
            new SoapSerializationEnvelope(SoapEnvelope.VER11);

        envelope.setOutputSoapObject(request);
        HttpTransportSE androidHttpTransport = new HttpTransportSE(url);

       
            androidHttpTransport.call(soapaction, envelope);
            org.ksoap2.serialization.SoapObject resultsRequestSOAP =(org.ksoap2.serialization.SoapObject) envelope.bodyIn;
            int n=resultsRequestSOAP.getPropertyCount();
            tv.setText("" + resultsRequestSOAP.getPropertyAsString(n-1));
        }
        catch(Exception ex)
        {
            tv.setText("" + ex);
        }
    }
}

main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="@string/hello" />




    <TextView
        android:id="@+id/txtQuote"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/nothing"
        android:textAppearance="?android:attr/textAppearanceLarge" />



    <Button
        android:id="@+id/button1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/ButtonText" />

</LinearLayout>

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="quotes.hypatia"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk android:minSdkVersion="8" />
<uses-permission
        android:name="android.permission.INTERNET" />
    <application
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <activity
            android:label="@string/app_name"
            android:name=".HypatiaQuotesClientActivity" >
            <intent-filter >
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Since, we are calling web services, the internet permission is required:-

<uses-permission
        android:name="android.permission.INTERNET" />

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="hello">Random Hypatia Quotes</string>
    <string name="app_name">HypatiaQuotesClient</string>
    <string name="nothing"></string>
    <string name="ButtonText">Hypatia Quotes</string>

</resources>

Here is the output of the program in the emulator:-

Sample output 1

Sample output 2

In the next post of this series, we shall call a web service that requires parameters.

The Web Service running on my Mobile.

Creating & Calling SQL Stored Procedures, SQL Stored Functions through C#

In this Post we shall learn how to create stored procedures, and functions  in MS SQL Server, and also call them from a C# application.

First of all let us create a table

create table scores(Sno int primary key,batsman varchar(50),runs int)   

Next we shall create a stored procedure for inserting data into the table:-

create procedure InsertIntoScores(@batsmanname varchar(50) ,@runsscored int)
as
declare @sno int;
select @sno=max(sno) from scores;
if(@sno is NULL)
set @sno=1;
else
set @sno=@sno+1;
insert into scores values(@sno,@batsmanname,@runsscored);

To execute the procedure we shall write 




exec InsertIntoScores 'Champak',99

Next we create a function




create function GetScores(@batsmanname varchar(50))
returns int
begin
declare @runs int;
declare @total int;
set @total=0;
DECLARE runs_cursor CURSOR FOR SELECT runs FROM scores WHERE batsman=@batsmanname;
OPEN runs_cursor
FETCH NEXT FROM runs_cursor INTO @runs

WHILE @@FETCH_STATUS = 0
BEGIN
SET @total=@runs+ @total;

FETCH NEXT FROM runs_cursor INTO @runs
END

CLOSE runs_cursor
DEALLOCATE runs_cursor
return @total;
end
The function uses a cursor to loop through the results and add up the scores.Using the sum aggregate query would be better, but I wanted to also provide an example of a cursor.

To test the procedure follow this procedure :-

Next give the value for the parameter

Here is the output:-

To access the procedure from a C# application we need to establish a connection, then create a command object to which we pass the required parameters. For the function we also require a return parameter. Here is how you access the stored procedure:-

System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=smartsystem;Integrated Security=True");
        con.Open();
        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("InsertIntoScores", con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("batsmanname", "Champak");
        cmd.Parameters.AddWithValue("runsscored", 100);
        cmd.ExecuteNonQuery();


Here is how you access the stored function :-


 System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=smartsystem;Integrated Security=True");
        con.Open();
        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("GetScores", con);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("batsmanname", "Champak");
        System.Data.SqlClient.SqlParameter retvalue =new System.Data.SqlClient.SqlParameter("MyRuns",System.Data.SqlDbType.Int );
        retvalue.Direction = System.Data.ParameterDirection.ReturnValue;
        cmd.Parameters.Add(retvalue);
        cmd.ExecuteNonQuery();

        TextBox1.Text = "" + retvalue.Value;