SQL TRIGGERs

When we were doing a database project we faced to a little problem when we try to check some constraints on a table on insert , update, delete operations.
There is a table called 'infos' and there is a column called age. The values of the age should be greater than 0. So to check that constraint we used SQL CHECK constrain. It appears to be accepted by mySQL as a valid one but when inserts happens we noticed that it is not working properly. (negative values can also be added as the age).

create table infos (
id        varchar(10) NOT NULL,
name      varchar(30) NOT NULL,
age       int ,
PRIMARY KEY (id),
CHECK (age > 0)
)

After some searching we found that CHECK constraint is not yet implemented in mySQL version that we have and we have to use SQL TRIGGERs for check those constraints.

Here are the things what we found . This is not all about SQL TRIGGERs but this was enough to accomplish our task.

Sometimes we need to control , check , monitor and manage a group of tables whenever an insert, update, or delete operation is performed.
The SQL CREATE TRIGGER statement provides a way for the database management system to actively do those things.
The statements specified in the SQL trigger are executed each time an SQL insert, update, delete operation is performed. These SQL triggers cannot be directly called from an application. They will automatically invoked by the database management system when a insert , update or delete operation take place in a relevant table.
the definition of the SQL trigger is stored in the data base management system and is invoked by the data base management system.

BEFORE SQL triggers
These triggers may not modify the tables. Using these triggers we can check for some constraints before add data to the data base table. And we can use them to modify other values based on the data to be inserted or updated or deleted.

AFTER SQL triggers
These triggers may modify the tables. After make the changes to the table we can check for some constraints or we can update some other tables or the same table values based on the new state of the table.

So let's see how we can use SQL triggers to accomplish that above task.

First we have to create the table.

create table infos (
id         varchar(10) NOT NULL,
name       varchar(30) NOT NULL,
age        int ,
PRIMARY KEY (id)  
)

The Trigger can be define as follows.

delimiter $$
create trigger infos_insert BEFORE insert on infos
for each row
begin
if(
   new.age <0
)then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Invalid age';
end if;
end;
$$
delimiter ;

As the delimiter we define another new one. (the default one is the semicolon )
And this a BEFORE SQL trigger . So the constraints will be checked before the data is entered and data will be entered if and only if the age is greater than 0. (assuming other constraints are satisfied ).
If error occurs (age is not valid) message will be displayed. We can catch this in our application also.

Inside the 'if section' we can have complex SQL parts also.
Assume that we want to insert persons to infos table only such that no 4 persons can have the same age.
To check that constraint we can use the below trigger.
(Some versions of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table')

delimiter $$
create trigger infos_insert BEFORE insert on infos
for each row
begin
if(
(select count(*) as counts   from infos
where age =new.age
group by age )+1 >3 
)then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Maximum ageGroup limit reached';
end if;
end;
$$
delimiter ;

Read More

C# Socket Programming

As with Java we can use C# to handle Sockets in our computers. The Socket Basics are the same as described in here.

But there are some different key words which C# use to  play with Sockets. Some of them are described as below. (Found them on the internet). They will give some brief explanation about those words. (Actually those words represent C# classes and Methods)

GetStream returns a NetworkStream that you can use to send and receive data. The NetworkStream class inherits from the Stream class, which provides a rich collection of methods and properties used to facilitate network communications.

The NetworkStream class provides methods for sending and receiving data over Stream sockets in blocking mode.

You must call the Connect method first, or the GetStream method will throw an InvalidOperationException. After you have obtained the NetworkStream, call the Write method to send data to the remote host. Call the Read method to receive data arriving from the remote host. Both of these methods block until the specified operation is performed. You can avoid blocking on a read operation by checking the DataAvailable property. A true value means that data has arrived from the remote host and is available for reading. In this case, Read is guaranteed to complete immediately. If the remote host has shutdown its connection, Read will immediately return with zero bytes.

You must close the NetworkStream when you are through sending and receiving data. Closing TcpClient does not release the NetworkStream.

The TcpClient class provides simple methods for connecting, sending, and receiving stream data over a network in synchronous blocking mode.

The TcpListener class provides simple methods that listen for and accept incoming connection requests in blocking synchronous mode. You can use either a TcpClient or a Socket to connect with a TcpListener. Create a TcpListener using an IPEndPoint, a Local IP address and port number, or just a port number. Specify Any for the local IP address and 0 for the local port number if you want the underlying service provider to assign those values for you. If you choose to do this, you can use the LocalEndpoint to identify the assigned information.

Use the Start method to begin listening for incoming connection requests. Start will queue incoming connections until you either call the Stop method or it has queued MaxConnections. Use either AcceptSocket or AcceptTcpClient to pull a connection from the incoming connection request queue. These two methods will block. If you want to avoid blocking, you can use the Pending method first to determine if connection requests are available in the queue.

Call the Stop method to close the TcpListener.

The Stop method does not close any accepted connections. You are responsible for closing these separately.

 Before jump in to the code, we have to make sure that all the required  C# name spaces are inserted in to the project source files.

normally C# project  source file contains ( by default ) some name spaces. 

they are  : 

• using System;
• using System.Collections.Generic;
• using System.Linq;
• using System.Text; 

 We have to  add additional 3 name spaces to the file. They are :

• using System.IO;
• using System.Net;
• using System.Net.Sockets;

 ========================================================================

Server Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.IO;
using System.Net;
using System.Net.Sockets;

namespace server
{
    class Program
    {
        static void Main(string[] args)
        {
            TcpClient AcceptedClient;
            NetworkStream instream;
            TcpListener clientListner = new TcpListener(IPAddress.Parse("127.0.0.1"), 7000);
            clientListner.Stop();
            clientListner.Start();

            while (true)
            {
                if (clientListner.Pending())
                {
                    // when the client connected to the port, this will 
                    //return that client as the Accepted client
                    AcceptedClient = clientListner.AcceptTcpClient();
                    // get the Accepted client's NetworkStream, 
                    //we want that to read data, which client is sending                    
                    instream = AcceptedClient.GetStream();

                    // read the input data into a byte list.
                    //(lets read byte at a time)
                    BinaryReader reader = new BinaryReader(instream);
                    List inputStr = new List();
                    int a = 0;

                    while (a != -1)
                    {
                        try
                        {
                            a = reader.ReadByte();
                            inputStr.Add((Byte)a);
                        }
                        catch (Exception e)
                        {
                            break;
                        }
                    }

                   // make a full string and write  to  Console
                   string s = Encoding.ASCII.GetString(inputStr.ToArray());
                   Console.Write(s);
                }
            }          
          
        }
    }
}

Client Code

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.IO;
using System.Net;
using System.Net.Sockets;

namespace client
{
    class Program
    {
        static void Main(string[] args)
        {
            NetworkStream outStream;
            StreamWriter writer;

            TcpClient client = new TcpClient();             

            // client is trying to connect to the given ip address and port
            // so this client will be catched by the 
            //TcpLstner of server program
            client.Connect(IPAddress.Parse("127.0.0.1"),7000);

            // get the Network stream, we have to send data
            outStream = client.GetStream();

            // make the writer
            writer = new StreamWriter(outStream);

            Console.Write("Enter your name : ");
            string name = Console.ReadLine();
            writer.Write(name);

            // finally Flush and Close
            writer.Flush();
            writer.Close();
            outStream.Close();
            client.Close();
        }
    }
}

Read More

Java Socket Programming

Sockets in some kind of device (basically a computer) provide the communication mechanism between two devices(computers) using TCP. A client program which represent the client end creates a socket (virtual) on its end of the communication and attempts to connect that socket to a server. 

When the connection is made, the server creates a socket object(virtual) on its end of the communication. Here server is another program . We can read and write to the socket. And the client program and the Server program can communicate through the socket. 

In java there is a package of the J2SE APIs called java.net which contains a collection of classes and interfaces that provides the capability to communicate using sockets. And we can write some handy programs using this package. 

 Java.net package supports TCP, which allows for reliable communication between two applications, (obviously) and UDP(User Datagram Protocol)

there are two important classes in this package which allow us to do those basic things.

·         java.net.Socket          -        represents a socket

·         java.net.ServerSocket         -       provides a mechanism for the server program to listen for clients and establish connections with them

let's start by understanding what is going on server side and client side when this communication through sockets happens.

•          First the server initiates a ServerSocket object. It contains a port number to select which port is to use.

                ServerSocket   serverSocket = new ServerSocket(3000, 10);

                In here it use the constructor public ServerSocket(int port, int backlog) throws   IOException

                port = specifies port number

                backlog = specifies how many incoming clients to store in a wait queue 

•          Now the server has to  listen. Until  someone connect to the port. To do that the Server  Socket class has a method called accept().

                Socket connection = serverSocket.accept();

•          Now a client is trying to connect. To do that client initiates a Socket object which specify a server name and a port.

                Socket clientSocket = new Socket("localhost", 3000);

                here the server and the client both are running on a same machine using local host(otherwise this "localhost" should be any other valid IP address ). Client should try to connect to the same port (3000 , this can be any valid port number)

•         Now the constructor of the Socket class try to connect the client to the specified server and the port.

•        If the connection successful then , accept()  method of the server will returns a reference of the new Socket.

•         Now the server and the client can communicate using that Socket.

========================================================================

Server code

 

import java.io.*;
import java.net.*;

public class Server  {

    ServerSocket serverSocket;
    Socket connection = null;
    ObjectOutputStream out;
    ObjectInputStream in;
    String message;
    //this is for maitainig a state(not necessary)
    static boolean state = true;

    Server () {
    }

    void run() {
        try {
            // creating a Server socket
            serverSocket = new ServerSocket(3000, 10);

            // Waiting for a client to  connect to the port
            System.out.println("Waiting for connection....");
            connection = serverSocket.accept();
            System.out.println("Connection successfully received from " + connection.getInetAddress().getHostName());
            // get Input and Output streams
            out = new ObjectOutputStream(connection.getOutputStream());
            out.flush();
            in = new ObjectInputStream(connection.getInputStream());

            // Communication part with the server
            // ah. now do what ever you want :D

            try {

                String msg = "Enter your name : ";
                sendMessage(msg);
                message = (String) in.readObject();
                System.out.println("client>" + message);
                if (message.equals("vidudaya")) {
                    sendMessage("Rocognized user.. welcome " + message + ".....");
                } else {
                    sendMessage("New user.. welcome " + message + ".....");
                }


                sendMessage("continue...(y/n)");
                message = (String) in.readObject();
                if (!message.equals("y")) {
                    state = false;
                    sendMessage("bye");
                } else {
                    sendMessage("c u some other time...");
                    state = false;
                }

            } catch (ClassNotFoundException classnot) {
                System.err.println("Data received in unknown format");
            }



        } catch (IOException ioException) {
            ioException.printStackTrace();
        } finally {
            //Closing connection
            try {
                in.close();
                out.close();
                serverSocket.close();
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        }
    }

    void sendMessage(String msg) {
        try {
            out.writeObject(msg);
            out.flush();
            System.out.println("server>" + msg);
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
    }

    public static void main(String args[]) {
        Server  server = new Server();
        while (state) {
            server.run();
        }
    }
}

Client code

 

import java.io.*;
import java.net.*;
import java.util.Scanner;

public class Client {

    Socket clientSocket;
    ObjectOutputStream out;
    ObjectInputStream in;
    String message;
    // to  read inputs
    Scanner sc = new Scanner(System.in); 

    Client () {
    }

    void run() {
        try {
            //  creating a Socket to connect to the server
            clientSocket = new Socket("localhost", 3000);
            System.out.println("Connected to localhost in port 3000");
            
            // get Input and Output streams
            out = new ObjectOutputStream(clientSocket.getOutputStream());
            out.flush();
            in = new ObjectInputStream(clientSocket.getInputStream());
           
            // Communication part with the server
             // here you can play as you want :D
            
            try {
                message = (String) in.readObject();
                System.out.println("server>" + message);
                String reply = sc.next();
                sendMessage(reply);
                message = (String) in.readObject();
                System.out.println("server>" + message);
                message = (String) in.readObject();
                System.out.println("server>" + message);

                sendMessage(sc.next());
                message = (String) in.readObject();
                System.out.println("server>" + message);
                
                if (message.equals("bye")) {
                    System.exit(0);
                }

            } catch (ClassNotFoundException classNot) {
                System.err.println("data received in unknown format");
            }

        } catch (UnknownHostException unknownHost) {
            System.err.println("You are trying to connect to an unknown host!");
        } catch (IOException ioException) {
            ioException.printStackTrace();
        } finally {
            //Closing the connection
            try {
                in.close();
                out.close();
                clientSocket.close();
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        }
    }    

    // this method is used to send the message through the Socket
    void sendMessage(String msg) {
        try {
            out.writeObject(msg);
            out.flush();
            System.out.println("client>" + msg);
        } catch (IOException ioException) {
            ioException.printStackTrace();
        }
    }

    public static void main(String args[]) {
        Client client = new Client();
        client.run();
    }
}

================================================================================= The two programs can be in the same package. But it is not really necessary.(We are accessing the Socket, so does not matter where the server or client is deployed :D )
 But here. First run the Server code. And then run the Client code. (Client must have a working server first :D)

Read More

Watch Star Wars Using Command Prompt

To do this we have to use TelNet protocol.

Telnet is a network protocol used on the Internet or Local area networks to provide bidirectional interactive text-oriented communications facility using a virtual terminal connection.

 

To install Telnet Client on Windows 7 or Windows Vista 

     

Click Start, and then click Control Panel.

On the Control Panel Home page, click Programs.

 In the Programs and Features section, click Turn Windows features on or off.If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue

 In the Windows Features list, 
select Telnet Client, and then click OK.



Then open the command prompt and type,

telnet towel.blinkenlights.nl

then press Enter and Watch Star Wars...

Read More