JDBC -
ODBC - ODBC stands for Open Database Connectivity. it is set of APIs
Written in C Language. used for making the Connectivity with
Front end to backend. it is product of Microsoft.
ODBC Application has 5 Layers -
*1 - Application Layer - front end
2 - ODBC interface Layer
3 - Driver Manager Layer
*4 - Driver Layer - DSN
*5 - Data Source Layer. - Backend
1 - Application Layer - it's provide the GUI & business Logics written
in language like C , C++ , VB , VC++ , C# ,
JAVA etc.
Layout for users.
2 - ODBC Interface Layer - it's provide the Interface between
Application Layer & ODBC.
3 - Driver Manager Layer - it's manage the All Drivers avaliable in
System.
4 - Driver Layer - it is the actual components.
Oracle Driver - Oracle
SQL server Driver - SQL Server
VFP Driver - VFP(Visual Fox Pro)
Access Driver - Access
MySQL Driver - MySQL
Fox pro Driver - Fox Pro
5 - Data Source Layer - it's depends on the Driver Layer.
VB <---> ODBC <----> Database(SQL / Oracle / Access)
MS MS - valid
Java <----> ODBC - invalid
JDBC - JDBC stands for java Database Connectivity. it is set of APIs
, used for making the Connectivity with Java to Backend.
it is the product of Sun MicroSystem.
JDBC Application has 2 Layers.
1 - Application Layer - coding
2 - Driver Layer - DSN
JDBC is the Upper Layer of ODBC. its convert Java code to ODBC
equivalent Code.
Sun code to Microsoft Code.
JDBC -
1 - with ODBC Driver.
Java <---> JDBC <---> ODBC <--> Backend(data base)
2 - with thin Layer (Oracle) -- direct with database without IIIrd party tool
Java <---> JDBC thin Layer <-----> Oracle
Oracle8i = sun + Oracle
JDBC complete in 6 steps --
1 - to create a Driver in control panel.
start -> controlpanel -> admin tool(not in 98/NT) ->
ODBC DataSource -> add new -> name & database select -> OK
coding
2 - to import JDBC ODBC Bridge (to activate all drivers)-
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); - for ODBC driver
Class.forName("oracle.jdbc.driver.OracleDriver"); for thin Layer Driver
3 - to create the Connection Object.
Connection c = DriverManager.getConnection("jdbc:odbc:vishal");
-- with Access & VFP
Connection c = DriverManager.getConnection("jdbc:odbc:vishal","scott","tiger");
-- with Oracle & SQL Server
4 - to create the Statement Object for executing SQL statement.
Statement s = c.createStatement();
5 - to execute the SQL statement , if statement is the select
statement then data stored inside the ResultSet object.
ResultSet rs = s.executeQuery("select * from emp"); - only for select statement
s.execute(any sql statement); - insert / update / delete / alter / drop / create
6 - display
while(rs.next()) // while rs not empty. next record exists
{
System.out.println(rs.getString(1)); - Ist Column
System.out.println(rs.getString(2)); - IInd Column
System.out.println(rs.getString(3)); - IIIrd Column
}
- for running oracle in XP , if says - Oracle not avaliable
connect - internal/oracle
sql>startup
sql>exit & reconnect with any user
oracle provides default 4 users.
sys / change_on_install - ABCDE
internal/oracle - test123
system/manager - abc123
simple user
scott/tiger - abc1
abc1/abc2 - abc12
-- for creating new user , need to connect with
system/manager
-- to create new user
create user abc1 identified by abc2
-- to assign privileges -
grant connect,resource,dba to abc2
-- to connect with abc2 user
login abc1/abc2
or
sql>connect abc1/abc2
CONNECT WITH ORACLE THIN LAYER
-- to display the current database name
connect oracle - system/manager
uname pass
SQL> select name from v$database;
display the database name. (ORCL)
find out the classes111.zip paste on c:\ drive
(oraclehome/ora81/jdbc/lib/classes111.zip)
set classpath with classess111.zip
set classpath=c:\classes111.zip;.
Class.forName("oracle.jdbc.driver.OracleDriver"); // for thin layer connectiviry
Connection c = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","gaurav","gupta");
with thinlayer:@serveradd:oracleport:dbname,user,pass
-- to create a procedure in oracle
create or replace procedure mysal(x in number,s out number)
is
begin
select sal into s from emp where empno=x;
end;
- to execute procedure in oracle
SQL> var a number
SQL> exec mysal(1002,:a)
SQL> print a
- to call procedure in java
