|
|
|
|
What are they |
|
libraries of function calls that support SQL statements |
|
Why do we need them |
|
Provide a way for an application to communicate
with a database |
|
Why are they good |
|
interoperability |
|
|
|
|
|
|
|
Purpose: register a data source |
|
How-to (Windows 2000) |
|
Start->Settings->Control Panel->
Administrative Tools->Data Sources(ODBC) |
|
NB: You need to register a new SQL server before
you create the DSN |
|
|
|
|
Select the System DSN tab |
|
Click add and choose SQL server (usually the
bottom driver listed). Click Finish. |
|
|
|
|
Chose identification entered by the user. Note:
this is not the default option, so pay attention |
|
Enter the ID and password I gave you |
|
Agree with everything else in the wizard |
|
Test the data source and if your test succeeds,
hit OK |
|
|
|
|
|
What is it: |
|
Java classes that allow an app to communicate
with a database |
|
Three types of JDBC drivers: |
|
Proprietary |
|
Bridge Drivers (e.g. JDBC-ODBC) |
|
JDBC-Net |
|
|
|
|
|
|
import java.sql.*; |
|
Class FirstTry{ |
|
public static void main(String[] args){ |
|
try{ |
|
String url=“jdbc:odbc:myDSN”; |
|
/*load the driver from Sun*/ |
|
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); |
|
//get a connection |
|
Connection con=DriverManager.getConnection(url,”userID”,”password”); |
|
}catch(Exception
E){System.err.println(E.getMessage());} |
|
|
|
|
|
|
java.sql.Statement s= con.createStatement(); |
|
java.sql.ResultSet rs; |
|
rs = s.executeQuery("Select Title from
MOVIES"); |
|
java.sql.ResultSetMetaData md =
rs.getMetaData(); |
|
while (rs.next()) |
|
for (int i = 1; i <=
md.getColumnCount();i++) |
|
System.out.println(rs.getString(i)); |
|
rs.close(); |
|
} catch (Exception e){ |
|
e.printStackTrace(); |
|
System.out.println("something
went wrong in database land"); |
|
} |
|
|
|
|
|
|
|
|
Note the DSN in the database url—it is the ODBC
DSN |
|
JDBC is just a way for Java applications to do what C++ does with ODBC. |
|
|
|
|
|
|
|
|
|
Well, just have fun. Check out Sun’s API for the
sql package http://java.sun.com/j2se/1.3/docs/api/index.html |
|
See Sun’s tutorial on JDBC |
|
http://java.sun.com/tutorial |
|
|
|
|
|
|
|
Web pages: static/dynamic |
|
Techniques for creating dynamic content : |
|
CGI (Perl), ASP, JSP |
|
Requests, Responses and Headers |
|
HTTP: simple, stateless. Client(I.e.browser)
requests, web server responds |
|
Requests can be of several types(methods):
usually GET and POST |
|
|
|
|
Allow us to process application logic on a Web
Server, which produces HTML |
|
A combination of HTML tags and Active Server
scripts (VBScript,Jscript,etc) |
|
Internet Information Server can access
information from any other COM-based server and present it as HTML |
|
|
|
|
Set of objects to modify and access data through
OLE DB interface: |
|
Connection, Command,Recordset,Error |
|
Application uses ADO which talks to OLE DB
provider for ODBC, which loads the right ODBC driver. Note: providers
represent diverse sources of data, pick the right one |
|
|
|
|
|
Server Object |
|
CreateObject-instantiates ActiveX object |
|
Application Object |
|
Manages variables needed by the application
users |
|
Methods Lock,Unlock |
|
Session, Response, Request, ObjectContext |
|
|
|
|
|
|
|
|
<% ' Declare our variables... always good
practice! |
|
Dim cnnSimple ' ADO connection |
|
Dim rstSimple ' ADO recordset |
|
Dim strDBPath ' path to our Access database
(*.mdb) file |
|
' MapPath of virtual database file path to a
physical path. |
|
' If you
want you could hard code a physical path here. |
|
strDBPath =
Server.MapPath("db_scratch.mdb") |
|
' Create
an ADO Connection to connect to the scratch database. |
|
' We're using OLE DB but you could just as
easily use ODBC or a DSN. |
|
Set
cnnSimple = Server.CreateObject("ADODB.Connection") |
|
' This
line is for the Access sample database: |
|
'cnnSimple.Open
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath
& ";" |
|
' We're actually using SQL Server so we use this
line instead: |
|
cnnSimple.Open Application("SQLConnString") |
|
'
Execute a query using the connection object. It automatically |
|
' creates and returns a recordset which we store
in our variable. |
|
Set rstSimple = cnnSimple.Execute("SELECT *
FROM scratch") |
|
'
Display a table of the data in the recordset. We loop through the |
|
' recordset displaying the fields from the table
and using MoveNext |
|
' to increment to the next record. We stop when
we reach EOF. %> |
|
|
|
|
|
|
<TABLE BORDER="1"> |
|
<% Do While Not rstSimple.EOF %> |
|
<TR> <TD><%=
rstSimple.Fields("id").Value %></TD> |
|
<TD><%=
rstSimple.Fields("text_field").Value %></TD> |
|
<TD><%=
rstSimple.Fields("integer_field").Value %></TD> |
|
<TD><%=
rstSimple.Fields("date_time_field").Value %></TD>
</TR> |
|
<%
rstSimple.MoveNext Loop %> |
|
</TABLE> |
|
<% ' Close our recordset and connection and
dispose of the objects |
|
rstSimple.Close |
|
Set
rstSimple = Nothing |
|
cnnSimple.Close |
|
Set cnnSimple = Nothing |
|
' That's
all folks! %> |
|
|
|
|
|
|
<%@ Language=VBScript %> |
|
<HTML> |
|
<HEAD> |
|
<META NAME="GENERATOR"
Content="Microsoft Visual Studio 6.0"> |
|
</HEAD> |
|
<BODY> |
|
<% |
|
‘I have
not shown variable declarations to save space—see the previous example |
|
‘Application("conStr")="Provider=SQLOLEDB;User
ID=yana;Password=myPassword;Data Source=ISQL01" |
|
set
dbCon=Server.CreateObject("ADODB.Connection") |
|
dbCon.Open Application("conStr") |
|
q="select Title from MOVIES" |
|
Set
rs=Server.CreateObject("ADODB.Recordset") |
|
rs.Open q,dbCon |
|
do while not rs.EOF |
|
Response.write(rs("Title")&
"<P>") |
|
rs.MoveNext |
|
loop |
|
rs.close |
|
Set rs=nothing |
|
dbCon.Close |
|
set dbCon=nothing |
|
%> |
|
</BODY> |
|
</HTML> |
|
|
|
Notes