<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>
<%
ResultSet result = null;
Connection Conn = null;
String status = "";
String url = "jdbc:jiql://local";
String host = "";
String password = "";
String user = "";
String dsName = "";
String sql = "";
if (request.getParameter("query") != null){
try{
sql = request.getParameter("sql");
if (sql == null || sql.length() < 1)
throw new SQLException("Please Enter a valid SQL Statement!");
Properties props = new Properties();
props.put("user",user);
props.put("password",password);
Class clazz = Class.forName("org.jiql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
Conn = driver.connect(url,props);
Statement Stmt = Conn.createStatement();
Stmt.execute(sql);
result = Stmt.getResultSet();
status = sql + "
SQL COMPLETED SUCESSFULLY";
}catch (Exception ex){
status = "" + sql + "
SQL FAILED " + ex.toString() + "";
ex.printStackTrace(response.getWriter());
}
//Conn.close();
}
%>
USER: <%=user%>
HOST: <%=host%>
<%=status%>
<% if (result != null) {
ResultSetMetaData mres = result.getMetaData();
int cc = mres.getColumnCount();
%>
Result INFO: Fetch Size:<%=result.getFetchSize()%>
<% for (int c = 0;c < cc;c++){
%>
| <%=mres.getColumnName(c + 1)%> |
<%}%>
<% while (result.next()){%>
<% for (int c = 0;c < cc;c++){%>
| <%=result.getObject(mres.getColumnName(c + 1))%> |
<%}%>
<%}%>
<%}%>
Follow the examples below and preferable in the order shown:
1)Enter the following SQL statement to create a table:
create table testable ( name varchar(18) ,countf int,yesno varchar(90) )
2)Enter the following SQL statements to populate the table:
INSERT into testable (name,countf,yesno) values ('counter',1,'yes');
INSERT into testable (name,countf,yesno) values ('counter',2,'no');
INSERT into testable (name,countf,yesno) values ('counter',3,'yes');
3)Enter the following SQL statement to select all from the table:
select * from testable
4)Enter the following SQL statements to select with filter from the table:
select countf from testable where yesno='yes';
select countf,yesno from testable where yesno='yes' AND countf=3;
select * from testable where countf=2 OR countf=3;
5)Enter the following SQL statements to update values in the table:
UPDATE testable SET countf=4 where countf = 3
UPDATE testable SET countf=4 where countf = 2
UPDATE testable SET countf=6 where countf = 4 AND yesno='no';
6)Enter the following SQL statements to delete entries from the table:
delete from testable where countf=1
delete from testable where yesno='no'
delete from testable
7)Enter the following SQL statements to create tables with PRIMARY KEYS:
CREATE TABLE realm_user (realm_username varchar(120),realm_passphrase varchar(120),PRIMARY KEY (realm_username)) ;
CREATE TABLE realm_userrole ( realm_username varchar(120), realm_rolename varchar(120), PRIMARY KEY (realm_username,realm_rolename)) ;
8)Enter the following SQL statements to populate the tables:
INSERT into realm_user (realm_username,realm_passphrase) values ('ruser1','tigres');
INSERT into realm_user (realm_username,realm_passphrase) values ('ruser1','tigres');
INSERT into realm_userrole (realm_username,realm_rolename) values ('ruser1','role1');
INSERT into realm_userrole (realm_username,realm_rolename) values ('ruser1','role1');
INSERT into realm_userrole (realm_username,realm_rolename) values ('ruser1','role2');
9)Enter the following SQL statements to ALTER with UNIQUE CONSTRAINT tables:
drop table realm_user
CREATE TABLE realm_user (realm_username varchar(120),realm_passphrase varchar(120)) ;
alter table realm_user add constraint realm_user_uq unique ( realm_username );
INSERT into realm_user (realm_username,realm_passphrase) values ('ruser1','tigres');
INSERT into realm_user (realm_username,realm_passphrase) values ('ruser1','tigres');
10)Enter the following SQL statements to ALTER with FOREIGN KEYS tables:
drop table realm_user
drop table realm_userrole
CREATE TABLE realm_user (realm_username varchar(120) primary key,realm_passphrase varchar(120))
CREATE TABLE realm_userrole ( realm_user varchar(120), realm_rolename varchar(120), PRIMARY KEY (realm_user,realm_rolename))
INSERT into realm_user (realm_username,realm_passphrase) values ('ruser1','tigres');
alter table realm_user add constraint ws_userid_fk foreign key ( realm_username ) references realm_userrole ( realm_user )
INSERT into realm_userrole (realm_user,realm_rolename) values ('ruser1','role2')
alter table realm_user add constraint ws_userid_fk foreign key ( realm_username ) references realm_userrole ( realm_user )
INSERT into realm_user (realm_username,realm_passphrase) values ('ruser2','tigres');
INSERT into realm_userrole (realm_user,realm_rolename) values ('ruser2','role2');
INSERT into realm_user (realm_username,realm_passphrase) values ('ruser2','tigres');
11)Enter the following SQL statements with not null primary key tables:
drop table testable
create table testable ( name varchar(18) not null primary key,countf int,yesno varchar(90) )
INSERT into testable (name,countf,yesno) values ('counter',1,'yes');
INSERT into testable (name,countf,yesno) values ('counter',1,'yes');
12)Enter the following SQL statements without not null tables:
drop table testable
create table testable ( name varchar(18),countf int,yesno varchar(90) )
INSERT into testable (name,countf,yesno) values ('counter',1,'yes');
INSERT into testable (name,countf,yesno) values (null,2,'yes');
INSERT into testable (countf,yesno) values (42,'yes');
select * from testable;
13)Enter the following SQL statements with not null tables:
drop table testable
create table testable ( name varchar(18) not null,countf int,yesno varchar(90) )
INSERT into testable (name,countf,yesno) values ('counter',1,'yes');
INSERT into testable (name,countf,yesno) values (null,2,'yes');
14)Enter the following SQL statements with DISTINCT and IN tables:
drop table testable
create table testable ( name varchar(18),countf int,yesno varchar(90) )
INSERT into testable (name,countf,yesno) values ('counter',1,'yes');
INSERT into testable (name,countf,yesno) values (null,2,'yes');
INSERT into testable (countf,yesno) values (3,'yes');
INSERT into testable (name,countf,yesno) values ('counter',1,'no');
INSERT into testable (name,countf,yesno) values (null,2,'yes');
INSERT into testable (countf,yesno) values (3,'no');
select * from testable;
select distinct * from testable
SELECT DISTINCT countf FROM testable WHERE countf IN (2,3);
SELECT DISTINCT t.countf FROM testable t WHERE t.countf IN (2,3);
15)Enter the following SQL statements with default value tables:
drop table testable
create table testable ( name varchar(18),countf int default 22,yesno varchar(90) )
INSERT into testable (name,countf,yesno) values ('counter',1,'yes');
INSERT into testable (name,yesno) values ('counter2','yes');
select * from testable;
16)Enter the following SQL statements with 'space , commas () or where and not null primary key' tables:
drop table testable
create table testable ( name varchar(18),countf int default 22,yesno varchar(100) default 'Yes and :) (:where or, No not null primary key' not null )
INSERT into testable (name,countf,yesno) values ('counter7',7,'NO AND (: not null primary key:) WHERE OR, YES');
INSERT into testable (name) values ('counter8')
select * from testable;
select * from testable where yesno = 'Yes and :) (:where or, No not null primary key';
17)Enter the following SQL statements with 'max column data length' tables:
drop table testable
create table testable ( name varchar(18),countf int,yesno varchar(3) )
INSERT into testable (name,countf,yesno) values ('counter4',1,'yesnono');
18)Enter the following SQL statements to test ORDER BY:
drop table testable
create table testable ( name varchar(18),countf int,yesno varchar(30) )
INSERT into testable (name,countf,yesno) values ('counter4',1,'a');
INSERT into testable (name,yesno) values ('counter2','c');
INSERT into testable (name,countf,yesno) values (null,3,'b');
INSERT into testable (name,countf,yesno) values ('counter4',5,'e');
INSERT into testable (countf,yesno) values (4,'g');
INSERT into testable (name,countf,yesno) values ('counter6',6,'d');
INSERT into testable (name,countf,yesno) values ('counter6b',6,'g');
INSERT into testable (name,countf,yesno) values ('counter7',7,'f');
INSERT into testable (countf,yesno) values (9,'h');
INSERT into testable (name,countf,yesno) values (null,8,'i');
select * from testable order by countf,yesno DESC
19)Enter the following SQL statements to test IS NULL:
drop table testable
create table testable ( name varchar(18),countf int,yesno varchar(30) )
INSERT into testable (name,countf) values ('counter4',1);
INSERT into testable (name,countf,yesno) values ('counter6',6,'d');
INSERT into testable (name,countf) values ('counter4b',5);
INSERT into testable (name,countf,yesno) values ('counter7',7,'f');
select count(*) from testable where yesno is null
UPDATE testable SET yesno='yep' where yesno is null;
select * from testable where yesno is null;
20)Enter the following SQL statement to drop the tables:
drop table testable
drop table realm_user
drop table realm_userrole
<%
if (Conn != null)
Conn.close();
%>