Transcript Final Program Database Programming

Department of Computer and Information Science,
School of Science, IUPUI
Database Programming
Dale Roberts, Lecturer
Computer Science, IUPUI
E-mail: [email protected]
3/29/2016
Dale Roberts
1
DataBase Programming
Creating a Database Application:
Database applications usually consist of the following major
components:
– Program Modules written in a high-level programming language
allow the programmer to encode the logic for the application.
(eg: Java, C++, VB)
– User interface allows programs to interact with the person using the
application. Can be simple text based interface or GUI.
– DBMS is used to create, access, and administer the data. (eg:
Oracle, SQL Server, DB2)
– Data Lanaguage is used to manipulate the data in the DBMS (eg:
SQL)
– Operating System Access mechanism to interface with the OS. This
is provided through OS specific calls on the host computer.
Dale Roberts
7-2
DataBase Programming
Options for accessing Oracle from
an application program:
– Embedded SQL. In this approach SQL statements are placed in
the program source code, a source code pre-comiler then
converts the SQL into the proper syntax for compilation and
linkage.
Examples: Pro*C, Pro*COBOL, SQLJ (Java) Java
– API. A second option is to use an Application Program Interface
(API) to develop your program. Oracle provides the OCI (Open
Call Interface) which is a run-time library that can be called
directly from within the application program to access the
database.
– PL/SQL. The third option is to use PL/SQL. It has the advantage
of being tightly integrated with the database, but it is not a
standard language and is Oracle specific.
Dale Roberts
7-3
DataBase Programming
Embedded SQL:
– Advantages:
• SQL is easy to understand,
• standardized and commonly used.
– Disadvantage:
• Takes an extra step to use the Pro*C precompiler.
– Example, after pre-compile step:
/*
EXEC SQL
DELETE FROM emp
WHERE CURRENT OF emp_cursor;
*/
{
sqlstm.stmt = “DELETE FROM EMP WHERE ROWID=:b1”;
sqlstm.iters = (unsigned long)1;
sqlstm.offset = (unsigned short)193;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)0;
sqlcex(&sqlctx, &sqlstm, &sqlfpn);
}
Dale Roberts
7-4
DataBase Programming
Developing an Embedded SQL Application
Pro*C pre-c ompiler
Source code
with em bedded SQL
Modified s ource code
with Oracle run-tim e calls
C c ompiler
Object Module
Linker
Final Databas e
A pplication Program
Orac le run-time library
Developing an OCI Application
Source code
with OCI calls
C c ompiler
Object Module
Linker
Final Databas e
A pplication Program
Orac le run-time library
Dale Roberts
7-5
DataBase Programming
DB program using an API:
– Advantages:
• Avoids the use of a pre-compiler like Pro*C.
– Disadvantage:
• Not as intuitive as embedded SQL .
• Database vendor specific.
– Simple C and OCI example:
SetupUpdate(szUpdate)
if (oopen(DML_CURS, LDA, (char far *)0, -1, -1,
(char far *)0, -1))
{ /* place error-handler here */
return rc;
}
if (oparse(DML_CURS, (char far *)szUpdate, -1))
{ /* place error-handler here */
return rc;
}
if (obndrv(DML_CURS, (char far *)”:rid_row”, -1,
(char far *)&((SEL_CURS)->rid), 14, ROWID,
-1, (char far*)0, (char far*)0, -1, -1))
{ /* place error-handler here */
return rc;
}
if (obndrv(DML_CURS, (char far *)”:nEmployee”, -1,
(char far *)emp_empno, sizeof(emp_empno),
far*) &ind_emp_empno,
(char far*)0, -1, -1))
{ /* place error-handler here */
return rc;
}
Dale Roberts
NULLTERM, -1, (char
7-6
DataBase Programming
Microsoft APIs:
– ODBC
• Open Database Connectivity API.
• Slower than OCI
• Not always Oracle friendly
– DAO, RDO, ADO
•
•
•
•
•
Other Microsoft APIs to access databases.
DAO = Data Access Objects
RDO = Remote Data Objects
ADO = Advanced Data Objects
All these APIs are faster and generally simpler to program with than the
ODBC API.
Dale Roberts
7-7
DataBase Programming
•
The choice between ODBC and DAO is often difficult to make. In general, you will find that DAO provides more flexibility,
with support for Data Definition Language (DDL) and Data Manipulation Language (DML).
•
The first consideration when deciding whether DAO or ODBC is the best solution for your application is to determine
whether the data source that you are using is supported by the technology.
•
The following table shows many of the similarities and differences between the DAO and ODBC sets of classes.
•
Characteristic
DAO Classes
ODBC Classes
Access .MDB files
Yes
Yes
Access ODBC data sources Yes
Yes
Available for 16-bit
No
Yes
Available for 32-bit
Yes
Yes
Database compaction
Yes
No
Database engine support
MS Jet DB engine Target DBMS
DDL support
Yes
Only via direct ODBC calls
DML support
Yes
Yes
Optimal for
.MDB files
Any DBMS for which you have
(Microsoft Access) a driver, especially in client/server
Transaction support
Per "workspace” or,
for ODBC data,
per database
Thread-safe
DAO 3.0 – No.
Yes, if used with a thread-safe
DAO 3.5 supports ODBC driver.
apartment model threading
Updatable joins
Yes
Per database
No
Note In most situations, DAO will be more efficient than ODBC, with one major exception. Use the ODBC classes if you
are working strictly with ODBC data sources, particularly in client-server situations. Here, the MFC ODBC classes provide
better performance.
Dale Roberts
7-8
DataBase Programming
Oracle the Java Way
– SQLJ
• Embedded SQL for Java
• similar to Pro*C
– JDBC API and Oracle
• ODBC.
– You can use Sun’s JDBC-ODBC Bridge software.
– Also requires appropriate Oracle ODBC driver on client installation.
• Oracle “Thin” Driver.
– Typically used in Java applets in browsers.
– Oracle's JDBC Thin driver is a Type 4 driver that uses Java sockets to
connect directly to Oracle. It provides its own implementation of a TCP/IP
version of Oracle's SQL*Net.
– Because it is 100% Java, this driver is platform independent.
• Oracle OCI7 and OCI8 drivers.
– Typically used in Java applications, not in browsers.
– Oracle's JDBC OCI drivers are Type 2 JDBC drivers. They provide an
implementation of the JDBC interfaces that uses the OCI to interact with
an Oracle database.
– You must use a JDBC OCI driver appropriate to your Oracle client
installation.
Dale Roberts
7-9
DataBase Programming
Using• SQLJ
SQLJ Runtime Environments
Dale Roberts
7-10
DataBase Programming
The JDBC Classes to Call:
DriverManager
getConnec tion()
Connection
createStatement()
Statement
ex ec uteQuery ()
ResultSet
next()
getInt()
getString()
getFloat()
Colum n values
from query
Dale Roberts
7-11
DataBase Programming
Steps in making a JDBC Program.
– Bring in the JDBC API into your program.
import java.sql.*;
– load the driver into memory.
Class.forName(“driver”);
or
new Driver;
– Get a connection to the database.
• specify the data source using JDBC URL format.
• jdbc:<subprotocol>:<subname>
• subprotocol = data source type like odbc or oracle
• subname = //hostname:port/database
– Use the getConnection method
of the DriverManager.
String url =
“@134.68.140.211:1521:WG73”;
Connection con =
DriverManager.getConnection(url,
“tiger”);
– Create a Statement:
Statement stmt =
con.createStatement();
Dale Roberts
“scott”,
7-12
DataBase Programming
– Run the query, get a result set back.
String s = “select emp_name from emp”;
ResultSet rs = stmt.executeQuery(s);
– Navigate through the results,
one row at a time,
one column at a time.
while(rs.next()) {
a = rs.getInt(1);
b = rs.getString(2);
c = rs.getFloat(3);
}
– Most all JDBC calls “throw exceptions”,
this means that all JDBC code must be in try blocks.
try {
/* JDBC code goes here */
}
catch ( Exception e ) {
e.printStackTrace();
}
Dale Roberts
7-13
Acknowledgements
Loney, Oracle Database 10g The Complete Reference
3/29/2016
Dale Roberts
14