CENG 513 – Lab 8

idiotdiscΛογισμικό & κατασκευή λογ/κού

15 Αυγ 2012 (πριν από 5 χρόνια και 2 μήνες)

256 εμφανίσεις

Out: Nov 21, 2011

Due: Nov 28
, 2009


in class

CENG 513


Lab
8


Embedded
Java Functions


Java has a number of functions that might be useful in an SQL environment.
We can make
these functions available by creating a wrapper function which calls it and t
hen defining an
Oracle function that maps to our wrapper function. An example of this covered in class was
the function
rand(n).


A second use for java functions is to add some system level functionality to SQL statements as
well. Here the class exampl
es are
uptime
which reports on how long the underlying OS has
been running since the last restart,
send

which adds email capability to SQL and
myexec

which
runs any specified system command with a single argument.

A full list can be found @
http://download.oracle.com/docs/cd/B19306_01/java.102/b14188/datamap.htm#CHDFJDIC

but what you may need consists of the following:


SQL Data Type

JDBC Mapping

Char, Varcha
r2, Long

Java.lang.String

Numeric

Java.math.BigDecimal

Double, Float

Java.lang.Double

Date, Timestamp

Java.sql.Timestamp

Blob

Java.sql.BLOB

Integer

Java.lang.Integer


It’s possible to use regular ints or doubles, but java.lang.Integer and java.lang.D
ouble allow you
to return NULL.






Methodology




Develop a static top level function in Java and test it in a command line or NetBeans environment.
Avoid advanced features introduced after java version 1.5



Right click on the
Java
entry under your connec
tion in the Connections tab of SQLDeveloper. Select
Load Java…
Cut and paste your tested code into the displayed textarea and then click
Apply
.



Write
and run
a ‘create or replace function …’ statement modelled on the sample statements given
on the co
urse web site. The idea is to map an Oracle function name, arguments and return data
types in the first part of the statement onto a Java function name, data types and return type.

Always end the statement with a double terminator: ;/ (semicolon/slas
h)



It’s always better to debug your program outside Oracle rather than inside as you have better
debugging tools. However if you wish to send debug messages to the terminal
using System.out or
System.err
while running in Oracle your need to run the follow
ing SQLPlus statements:


set serveroutput on


exec dbms_java.set_output(10000);







The Assignment


1.

Write java functions to meet the following specifications. Test them first in a main program in the
command line environment

or Net
Beans
, load them into oracle and test them again in Oracle.
Grant execute privilege to
king
.


a)

Get the sample functions
fact
and
quoteIt

to work in the lab. All the code has been given to
you


all you need to do is load it and test them. Demo the wo
rkin
g programs. (3

marks)


b)

JDK 1.5 added C style formatting by introducing the
String.format

method. Wr
ite and test a
simple static
wrapper function

in java

format(formatString, Double)

to format a double

.



Test
the function in NetBeans, then load

the function into Oracle and map it into the Oracle
environment using a
create or replace function

statement. Show that it works both in a Java
and in an Oracle environment and hand in your code with a summary of results.

(6 marks)


Sample formats:

i)

“As currency: %5.2f”

ii)

“ The number %
-
10.3f is left justified”

iii)

“Leading zeroes %06.3f in the format”



c)

The following 2 line bash shell script takes 2 arguments


a user name and a number, and
returns the nth last time this user was logged on as a string

. (7 marks)


/bin/bash

last $1 | head
-
$2 | tail
-
1 | cut
-
c44
-
56


Set up this script, give it permissions so that anyone can run it and verify that it works.


Review the code for creating the function
uptime
.

i)

Write and test java function
that takes 2 arguments: String userID, int n, executes the
bash script and returns a String.



ii)

Make the function available in the Oracle Environment.



iii)

Spend at most ½ hour to create a version of the function (with a different name) that
returns an o
bject of type javax.sql.Date. Discuss what you tried and the extent to which
you were successful. Approaches to consider:


(1)

Java.sql.Date is a direct subclass of java.util.Date. There is a parse method that could
help you.

(2)

SimpleDateFormat is a class
that can read in Dates in different formats, as is
DateFormat.

(3)

You could use the String method split to extract each of the different fields

(4)

You may have some C code that you’ve written to read the utmp file directly

(5)

You may be able to use the date command

in Unix to reformat.


2)

In this exercise you are to create shared data for use in the next lab. (3 marks each)


a)

Load a picture of yourself (or your favourite picture) into the table king.images along with your
username, a title and when the picture was load
ed.

b)

Using an insert/select and a subquery, load the data from your movie table into king.movie that
is not already into king.role.

c)

Using an insert/select and a subquery, load the data from your role table into king.role that is
not already into king.role