JDBC

Connecting the Database : Type : 4
**************************
For Running the Java database driver
PostGRESQL Download Driver from Website "pg80jdbc3.jar"

CLASSPATH has to be set in ~/.cshrc
"setenv CLASSPATH .:${HOME}/pg80jdbc3.jar"

Driver Loaded Properly But Error While Connecting Database :

If Problem in getConnection :

Say :
" org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:122) "

To Solve :

For Initialize the Database
***************************

1. Check the Folder and FileName "/var/lib/pgsql/" & "/usr/bin/pg_ctl"
is available.

2. The Folder "pgsql" must be local user accessible. say "755" for
that change the permission using "chmod".

3. remove the older "data" directory from "/var/lib/pgsql" and create under
local user or from root user and change owner for local access
say "chown -R kes data ".

Note : Must give local user only for the "data" folder, remaining folder from
"pgsql" must be in default say username and usergroup is "postgres"

Must initialise DB by giving the command "initdb /var/lib/pgsql/data"

4. Check the file inside "data" folder "vi pg_hba.conf" & "vi postgresql.conf"

a) Enable the line which localhost of 127.0.0.1 in "pg_hba.conf"

# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust

b) Enable the line in which tcpip_scoket make to be "true" earlier it was false
in file "postgresql.conf"

# - Connection Settings -
tcpip_socket = true

5. Restart the psql server database by giving Stop and Start
for Stop : /usr/bin/pg_ctl -D /var/lib/pgsql/data/ stop
for Start : /usr/bin/pg_ctl -D /var/lib/pgsql/data/ start
* ALL CAN BE DONE UNDER LOCAL USER BECAUSE PERMISSION
GIVEN IN STEP 2 ITSELF....

6. create database from local user say "createdb ccadsysdb"

7. Import the sql file by giving

a) psql -d ccadsysdb -f /home/kes/incad_source/scripts/meanfittolerance.sql
[OR]
b) login to ccadsysdb and import it
\i /home/kes/incad_source/scripts/meanfittolerance.sql

8. Check for running the postgres.
ps -ef | grep post

Example :
kes 16674 1 0 11:01 pts/8 00:00:00 /usr/bin/postmaster -D /var/lib/pgsql/data
kes 16677 16674 0 11:01 pts/8 00:00:00 postgres: stats buffer process
kes 16678 16677 0 11:01 pts/8 00:00:00 postgres: stats collector process
kes 17051 16674 0 11:09 pts/8 00:00:00 postgres: kes ccadsysdb 127.0.0.1 idle
kes 19244 4750 0 12:08 pts/3 00:00:00 grep post

9. Example of creating the table :

[kes@localhost ~]$ createdb kesdb
CREATE DATABASE
[kes@localhost ~]$ psql -l
List of databases
Name | Owner | Encoding
-----------+-------+----------
kesdb | kes | UTF8
postgres | kes | UTF8
template0 | kes | UTF8
template1 | kes | UTF8
(4 rows)

[kes@localhost ~]$ psql kesdb
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

kesdb=# create table kestable(Name varchar(20), Phone integer);
CREATE TABLE
kesdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+-------
public | kestable | table | kes
(1 row)

kesdb=# select * from kestable;
name | phone
------+-------
(0 rows)

kesdb=#

-----------------------------------------------------------

How to Install in Windows Vista (Postgresql)

1. Activate the Administrator in Vista by

1. Launch the cmd prompt - Make sure you select, 'Run as administrator'
2. Net user administrator p£ssw0rD
3. Net user administrator /active:yes
4. Switch User, or logoff
5. Logon as Administrator Password p£ssw0rD

2. Now Install it in Administrator Mode by giving option in postgres (Install as Service)
which will generate forcable automatic password.

3. On Next Step it will ask for internal database password set for 'postgres'. This password
can be given by user which will used in the application while running (pgAdm III).

4. Log out Administrator and login to Local Kishore User

5. Configuring command for command prompt from Environment setting (System Settings)
path = c:\......\bin

5. Run the application from start menu, Connect the server and create database user
for the Local User and password (Say Kishore) where u can add database from the current user.

6. Once Database Name created and login to
psql kesdb
password : (password has to be given which created for current user)

How to set ClassPATH in Windows

1. Common Mistake that user forget to put dot while
setting the environment will cause
"Exception in thread "main" java.lang.NoClassDefFoundError"

Solution : This Dot will search class from current directory
of java program

CLASSPATH =
.;
C:\Program Files\Java\jdk1.5.0\bin;
C:\Program Files\PostgreSQL\8.0\bin;
D:\Study\code\pg80jdbc3.jar

------------------------------------------------------------------

Class.forName("org.postgresql.Driver");

public static final String url = "jdbc:postgresql://localhost:5432/"
+kesdb+"?user="+user+"&password="+pw;

connect = DriverManager.getConnection(url);



http://www.cse.unsw.edu.au/~jas/talks/jdbc/notes.html

Types of Drivers used in JDBC

http://en.wikipedia.org/wiki/JDBC_driver