Transcript Tutorial slides - SUNY

CSE 305
Theory of Database
Tutorial on Connecting with Sybase
from Java program and Developing GUI
Jalal Mahmud, TA , CSE 305
Main topics in this tutorial
Setting Environment in your account
 Connecting with Sybase from a Java program
(applet/application) using JDBC.
 Executing queries from Java program
(applet/application)
 Developing a GUI using html, java script
 Working from Home

Set the Environment ….Step 1

Create a directory called html under your home
directory. Your html pages and java class files should
reside in the html directory.

Copy the com folder under c:\program
files\sybase\jConnect-4_5\classes to your html
directory to make JDBC working
Set the Environment ….Step 2

Make sure that the CLASSPATH variable is set.

set CLASSPATH=%CLASSPATH%;.;c:\Program
Files\sybase\jConnect-4_5\classes;c:\Program
Files\jdk\lib;
set path=%path%;c:\Program Files\jdk\bin


You can download a batch file which sets the
CLASSPATH from here .
CLASSPATH : More…

Once the CLASSPATH variable is set, it
remains set until the DOS windows is closed.

The CLASSPATH variable is only required
for compiling the java file. It is not required to
connect to Sybase through the www server
(assuming you have copied the com directory
to your html directory).
Remember….

Make sure that you have the com directories located
in c:\program files\jdbc\classes in your html folder.

The com directory has to be in the same directory as
the connection applet.

e.g. if your applet connect.class is located in
h:\myproject\html\, then the com directory should be
in h:\myproject\html\.
How to compile a Java source code

Open a DOS window

Compile your java source code by typing
javac source.java on the command prompt.
Where to get JDBC

a new version of JDBC in the lab. The current
version is now 4.5. It is installed locally on
every machine. The path is c:\Program
Files\sybase\jConnect-4_5.
Set the Property First

String USER = “smith”; // replace with your sybase user id
String Password = “abcd”; // replace with your password
Properties prop = new Properties();
prop.put("user",USER);
prop.put("password",Password);

// The proxy is setup to the TDS Tunneling servlet

prop.put("proxy","http://www.translab.cs.sunysb.edu/proxy/
servlet/TDSTunnelServlet");




Connect to the JDBC database.
try
{
Class.forName("com.sybase.jdbc.SybDriver");
conn = DriverManager.getConnection("jdbc:sybase:Tds:rdb.
translab.cs.sunysb.edu:5000",prop);
System.out.println(conn);
}
catch (Exception e)
{
e.printStackTrace();
return false;
}
Now you are connected , how to
execute a query ?



1. Create a statement object from the
connection object
2. Execute the statement object.
Create a statement object from the
connection Object

try
{

stmt =conn.createStatement();





}
catch (SQLException sqle)
{
}
Execute the statement object.

try
{
sSelectStml=“Select * from test“
result_status=stmt.executeUpdate(sSelectStmt);
}
catch (SQLException sqle)
{
}
Develop your HTML Page

You need to develop some ( at least one ) html page
for your GUI.

The html page should contain appropriate elements (
like textbox, button , selection, check box ) so that
you can take user inputs and also display the result.

You may use Microsoft Front Page or any html
editor for developing html pages.
You also need Java Script




Java script is very easy !
The functions are almost similar to Java
functions.
For Java script syntax : click the following
http://www.javascript.com
You need to link java script functions to some
input elements in your GUI, specially submit
button.
Add Java Script function to your GUI
elements
Q. How to link a button with a JavaScript function?
<SCRIPT LANGUAGE="Javascript1.1">
function verify() {
…
}
</SCRIPT>
…
<INPUT TYPE="button" VALUE="Send"
onClick="verify();">
…
How to get the values of fields on the current
window in a JavaScript function?


var user =
document.forms[0].elements[1].val
ue;
var password =
document.forms[0].elements[2].val
ue;
Parameter Passing from Java script

Now you have collected parameter, ( say username
and password) from your html page .

When user presses submit it directly goes to java
script function..

You can call Java applet function from here which
will take care the remaining
Q. How to pass parameters from JavaScript function to
Java Applet method?

<javascript>

function verify(){

var user = document.forms[0].elements[1].value;
var password = document.forms[0].elements[2].value;
document.applets[0].connectSybase(user, password);
…
}
</javascript>



<APPLET code=“connect.class" name="connect" codebase=.
height=850 width = 600 MAYSCRIPT></APPLET>
Displaying Result from Javascript


Now you can pass parameters to applet using
Java script function ..
You also need to display your results…

You can use Java script to set value of a
particular field of your GUI.

But , you also need java applet which will
calculate the result for you !
How to get the result and display it ?



function showResult() {
...
var result =
String(document.applets[0].getOneRow());
while(result != "" && result != null){
msg += result;
result =
String(document.applets[0].getOneRow());
}
document.oper.result.value = msg;
...
}
Getting and Processing result from a
Java Applet





public String getOneRow()
{
...
rs=stmt.executeUpdate(sSelectStmt);
String name = rs.getString(1);
String desc = rs.getString(2);
int length = rs.getInt(3);
int access = rs.getInt(4);
result= new String(name + " " + desc + " " + length
+ " " + access);
return result;
}
How html, JavaScript, JAVA Applet,
JDBC work together?

You need to develop your html page for the GUI.

You will write the java script code in your html file that will
be responsible for verifying, parameter passing and
displaying result.

Java Applet should be embedded in the html file within
<applet>…</applet> tag

Within Java applet, you will write functions to connect to
Sybase, executing Queries and calculating results….
Good Practice to Disconnect !!
<SCRIPT type="text/javascript" language="JavaScript1.2">
function doDisconnect()
{
var output;
output = parent.samplehtml.document.connect.disconnect();
if(output == 0){
alert("Not disconnected");
return true;
}
if(output == 1)
{
alert("disconnected sucessfully");
return true;
}
}
</SCRIPT>
Disconnect Code











public int disconnect()
{
if (conn != null){
try {
conn.close();
}
catch (SQLException sqle) {
}
}
}
How to work at home?



Download and install Java from JavaSoft
(JDK 1.1) on your own machine
Download and install JDBC from Sybase
(jConnect) on your own machine
For more information, see dbcourse
You need Sybase

To interact with Sybase, you can do so by
either using the gateway applet, located at:
http://www.translab.cs.sunysb.edu/gateway/ga
teway or

by installing JConnect from Sybase, and
installing Sybase's jisql. Instructions for
installing jisql can be found here.
Also JDBC

JDBC or JConnect from Sybase: You can
download JConnect from the Sybase website at
http://www.sybase.com . Make sure that you use
the same version as in the lab or else you will
have compatibility issues.

Also, you need to have the com directory tree in
your html folder within your account. The Jconnect
that you install on your home machine should be
used only to compile your code. Avoid setting the
CLASSPATH variable.
You also need JDK

Java Development Kit (or JDK). You can download
it from Sun's Website at http://www.sun.com or go to
http://www.sun.com/download . Make sure that
you use the same version of JDK as in the lab.

An HTML editor. You can download one from
http://www.evrsoft.com (1st Page 2000) which is
installed in the lab or you could simply use wordpad
or notepad or use the editors which come with IE or
Netscape.
And a SSH2 client

A SSH2 client. You will need to upload the
files to the transaction lab file server. You can
download a free windows SSH2 client from
http://www.ssh.com . The ssh2 server for the
TRANSLAB is www.translab.cs.sunysb.edu .
Useful resources






HTML
http://www.w3.org/TR/REC-html32.html
HTTP
http://www.w3.org/Protocols/HTTP/1.0/spec.html
JavaScript
http://developer.netscape.com/docs/manuals/communicator/jsguide4/cont
ents.htm
Java
http://java.sun.com/docs/books/tutorial/
JDBC
http://java.sun.com/docs/books/tutorial/
Sybase & SQL
http://sybooks.sybase.com/onlinebooks/group-as/srg1100e/sqlref

Hope this tutorial helps you !

For more info mail to:
[email protected]