# Oracle Sql Taglib :

Following sql tags have been implemented for 8.1.7.

- dbOpen
- dbClose
- dbQuery
- dbCloseQuery
- dbNextRow
- dbExecute

### 1. dbOpen

Syntax : <jml:dbOpen [ connId="<connection-id>" ] user="<user-name>" password="<password>"
URL="<jdbc-format-URL>" >
. ......

</jml:dbOpen>

1. Implements Tag and doesn't do any BodyTag specific.
2. Extends ConnBean
3. User, Password, and URL are mandatory properties
4. ConnBean properties like stmtCacheSize, Prefetch, and BatchSize can also be set and get.
5. connId is optional
6. If connId is present, then the connection is not closed during "release" time and instead application should explicitly close with dbClose tag

### 2. dbClose

Syntax : <jml:dbClose connId="connection-id"/>

1. Just implements Tag and doesn't do BodyTag specific.
2. connId is mandatory

### 3. dbQuery

Syntax : <jml:dbQuery [queryId="<query-id>" connId="<connection-id>" output="{HTML|XML|JDBC}"]>

1. Implements BodyTag
2. Extends CursorBean
3. queryId, connId, and output are all optional
4. if connId is null, then we get from parent using (findAccessorClass). If there is no top level nested dbOpen clause, then this will fail.
5. if queryId is not null, then the Cursor is not closed during release, and should be explicitly closed by dbCloseQuery call.
6. Cursor Bean properties like ResultSetType, ResultSet concurrency, BatchSize, Prefetch Size and Query Timeout can also be set and retrieved.
7. output types :

1. HTML/html : Generates a HTML table. This is the default. (Something similar to what Coldfusion does).
2. XML/xml : Generates a XML string
3. JDBC/jdbc : Generates a JDBC result set, that can be iterated over with dbNextRow call.

### 4. dbCloseQuery

Syntax: <jml:dbClose queryId="<query-id>" />

1. Extends TagSupport
2. queryId is mandatory
3. closes the cursor bean (Query) with the given queryId.

### 5. dbNextRow

Syntax : <jml:dbNextRow queryId="<query-id>">
.......
</jml:dbNextRow>

1. dbNextRow is only valid if the output is specified as JDBC when dbQuery is executed. The Resultset object is created in the TEI class (of dbQuery) and passed over to the subsequent tag.
2. queryId is must
3. Extends BodyTagSupport.
4. The body is executed which each row in the resultset

### 6. dbExecute

Syntax: <jml:dbExecute [connId="<connection-id>" output="{yes/no}"]>
... DML or DDL statements
</jml:dbExecute>

1. To execute both DMLs and DDLs
2. Extends CursorBean
3. Implements BodyTag
4. If connId is null, then we get from parent using (findAccessorClass). If there is no top level nested dbOpen clause, then this will fail.
5. if the Output yes, then HTML string "<no> row[s] affected" will be printed. for DML and the statement execution status will be printef for DDLs. Default is no.

## Examples :

###### 1. Simple Query

<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="jml" %>
<HTML>
<HEAD>
<TITLE>Simple Query - Output is a HTML table </TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<HR>
<jml:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger" connId="con1">
</jml:dbOpen>
<jml:dbQuery connId="con1">
select * from EMP
</jml:dbQuery>
<jml:dbClose connId="con1" />
<HR>
</BODY>
</HTML>

2. Nested Tags

<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="jml" %>
<HTML>
<HEAD>
<TITLE>Nested tags - XML string</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<HR>
<jml:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger" >
<jml:dbQuery output="xml">
select * from EMP
</jml:dbQuery>
<jml:dbOpen>
<HR>
</BODY>
</HTML>

3. Iterating over result sets

<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="jml" %>
<HTML>
<HEAD>
<TITLE>A sample that iterates over resultset </TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<HR>
<jml:dbOpen connId="con1" URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger">
</jml:dbOpen>
<jml:dbQuery connId="con1" output="jdbc" queryId="myquery">
select * from EMP
</jml:dbQuery>
<jml:dbNextRow queryId ="myquery">
<%= myquery.getString(1) %>
</jml:dbNextRow>
</jml:dbCloseQuery queryId="myquery"/>
</jml:dbClode connId="con1" />
<HR>
</BODY>
</HTML>

4. Using DML

<%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="jml" %>
<HTML>
<HEAD>
</TITLE>DML Sample <TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<HR>
<jml:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger" connId="con1">
</jml:dbOpen>
<jml:dbExecute connId="con1">
insert into EMP (EMPNO, ENAME) values (1500, "LESLIE")
</jml:dbExecute>
<jml:dbClose connId="con1" />
<HR>
</BODY>
</HTML>

5. Nested dbNext within dbQuery

%@ taglib uri="/WEB-INF/sqltaglib.tld" prefix="jml" %>
<HTML>
<HEAD>
<TITLE>Connection Taglib </TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<HR>
<jml:dbOpen URL="jdbc:oracle:thin:@dlsun991:1521:816" user="scott" password="tiger">
<jml:dbQuery output="jdbc">
select * from EMP
<jml:dbNextRow >
<%
if (result == null)
out.print("result was null");
else
out.println("String is " + result.getString(1));
%>
</jml:dbNextRow>
</jml:dbQuery>
</jml:dbOpen>
<HR>
</BODY>
</HTML>