Oracle Sql Taglib :


 Following  sql  tags have been implemented for 8.1.7.

  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>