Dropping table that has many objects dependent on it

Dropping table automatically,
the program throws error:

Exception in thread "Thread-3" java.lang.NullPointerException
Changed the drop table query statement in Java from
stmt.execute(droptable);
          to
stmt.executeUpdate(droptable);

Still it displayed the same error.

While executing the query on pgadmin, it displayed the error:

ERROR: cannot drop table h*** because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.

Too many triggers and views are dependent on the table

Changed the Drop table statement from

DROP TABLE TABLENAME
to
DROP TABLE TABLENAME CASCADE

Program works fine.

Change postgresql password on Ubuntu

How to change postgresql password on Ubuntu

 

I’m using Postgresql-8.3  on Ubuntu 9.04.

Initially , take pg_hba.conf from /etc/postgresql/8.3/main/

In that you will find an entry like this,

host    all         all         172.16..0/24        md5

Change it to

host    all         all         172.16..0/24        trust

Then restart the postgresql database..

if it is ubuntu use the command /etc/init.d/postgresql restart 

Now issue the command

sudo psql -h -d -U postgres

(it should not ask for postgresql password..). 

It will ask for your unix password

Then give the command

alter user postgres with password 'new password'

to change the password..

Thats it.. 

Java program to insert image as byte array to postgres database

//Program to insert an image into postgres database as byte array

import java.sql.*;
import java.io.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class insertImage
{
  public static void main(String[] args)
  {
    PreparedStatement statement = null;
    {
      FileInputStream fin = null;
      try
      {
        System.out.println("Insert Image Example!");
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://x.x.x.x:5432/MYDB";
        Connection oConnection = DriverManager.getConnection(url, "username", "password");
        System.out.println("Sucessfully connected to Postgres Database");
        File imgfile = new File("myimage.jpg");
        fin = new FileInputStream(imgfile);
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        byte[] buf = new byte[1024];
        for (int readNum; (readNum = fin.read(buf)) != -1;)
        {
          bos.write(buf, 0, readNum);
          byte[] bytes = bos.toByteArray();
          String sql = "INSERT INTO my_table (byte_array) VALUES (?)";
          statement = oConnection.prepareStatement(sql);
          statement.setBytes(1, bytes);
          statement.executeUpdate();
          System.out.println("Image inserted into database!");

        }
        statement.close();
        oConnection.close();
      }
      catch (Exception ex)
      {
        System.out.println("Exception:- " + ex);
      }
      try
      {
        fin.close();
      }
      catch (IOException ex)
      {
        System.out.println(" IOException:- " + ex);
      }
    }
  }
}

Create case sensitive Database

My aim was to create a postgresql database through Java Program. The database name has consecutive Capital and small letters.

//Program to Create a Postgresql database
import java.io.*;
import java.sql.*;

public class CreateDatabase
{
public static void main(String[] args)
{
System.out.println("Database creation example!");
Connection oConnection = null;
try
{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://172.x.x.x:5432/postgres";
oConnection = DriverManager.getConnection(url, "postgres", "12345");
System.out.println("Sucessfully connected to Postgres Database");
try
{
Statement st = oConnection.createStatement();
st.executeUpdate("CREATE DATABASE \"TeStDb\" WITH
OWNER=postgres ENCODING='UTF8';);
}
catch (Exception s)
{
System.out.println("SQL statement is not executed:-" + s);
}
}
catch (Exception e)
{
System.out.println("Exception:- "+e);
}
}
}