This should help ! :)
JRun Tag Library Reference
<file:///C:/Program%20Files/Allaire/JRun%20Studio%203.0/Help/JRun_Tag_Librar
y_Reference/customactions4.htm> Previous
<file:///C:/Program%20Files/Allaire/JRun%20Studio%203.0/Help/JRun_Tag_Librar
y_Reference/customactions.htm> Up One Level
<file:///C:/Program%20Files/Allaire/JRun%20Studio%203.0/Help/JRun_Tag_Librar
y_Reference/customactions6.htm> Next
_____
Custom Actions
BM_460868
sql
BM_425070
Syntax
<sql ...>
sql statement with optional <sqlparam .../>
...
</sql>
BM_452764
Usage
Performs database operations by sending the enclosed sql statement to the
specified data source. It is possible to have flow-control tags within the
sql tag so that the sql statement can be dynamically constructed. The sql
tag can also be used with sqlparam tags to construct parameterized sql
statement, for example, inserting binary objects into the sql statement. If
the sql tag is within a transaction tag, the sql tag will be a transaction
participant and the success or failure of the sql operation will be based on
all the operations within the same transaction. If all the operations
(including sql, sendmsg and getmsg) within the transaction are successful,
all the changes will be committed. Otherwise, an exception will be raised
and changes will be rolled back. The sql tag uses the
allaire.taglib.Transaction interface to detect if it is enclosed in a
transaction tag.
The sql tag supports three types of syntax. Connection pooling is supported
when a DataSource is provided. The J2EE server administrator should
predefine data source objects with connection pooling support to maximize
Web application performance. For details, refer to the J2EE specification
and the following examples.
BM_452774
Syntax Types
Syntax Type 1: JDBC connection object (java.sql.Connection) is provided.
Syntax Type 2: J2EE data source (javax.sql.DataSource) is provided.
Syntax Type 3: JDBC driver class and url to the database are provided.
The following table shows which attributes are available using the three
different syntax types:
sql tag Syntax Types
Attribute Type 1 Type 2 Type 3
connection x
id x x x
scope x x x
datasrc x
username x x
password x x
driver x
url x
BM_505787
Attributes
BM_506417
connection (type 1)
Required. Takes either java.sql.Connection or java.lang.String as an
attribute. If a string is specified, it is assumed that the connection
object can be obtained by invoking pageContext.getAttribute(). Since the sql
tag is provided with a JDBC connection, the tag will return the connection
object without closing it.
BM_458199
id (types 1,2,3)
Optional. Takes java.lang.String as an attribute. The string name is used as
the scripting variable name for the query result. This attribute can be
omitted if the sql statement doesn't return a result set.
BM_458202
scope (types 1,2,3)
Optional. Takes java.lang.String as an attribute. Valid string values are
page, request, session, and application. The default is page.
BM_516651
datasrc (types 2)
Required. Takes javax.sql.DataSource or java.lang.String as an attribute. If
a string is specified, it is assumed that the datasource object can be
obtained by performing a JNDI lookup with "java:comp/env/jdbc/[datasrc]".
To use the sql tag in a distributed transaction, you must use this attribute
(type 2 syntax) and it must be of type java.lang.String.
BM_458258
username (types 2,3)
Optional. Takes java.lang.String as an attribute. The string value is used
as the username for the data source.
BM_458265
password (types 2,3)
Optional. Takes java.lang.String as an attribute. The string value is used
as the password for the data source.
BM_458483
driver (types 3)
Required. Takes java.lang.String as an attribute. The string value is the
JDBC driver class name.
BM_458486
url (types 3)
Required. Takes java.lang.String as an attribute. The string value is the
JDBC URL to the database.
BM_429127
TagLib Descriptor
<tag>
<name>sql</name>
<tagclass>allaire.taglib.sqlTag</tagclass>
<teiclass>allaire.taglib.sqlTei</teiclass>
<bodycontent>JSP</bodycontent>
<attribute>
<name>id</name>
<required>false</required>
<rtexprvalue>false</rtexprvalue>
</attribute>
<attribute>
<name>scope</name>
<required>false</required>
<rtexprvalue>false</rtexprvalue>
</attribute>
<attribute>
<name>driver</name>
<required>false</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
<attribute>
<name>url</name>
<required>false</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
<attribute>
<name>datasrc</name>
<required>false</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
<attribute>
<name>connection</name>
<required>false</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
<attribute>
<name>username</name>
<required>false</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
<attribute>
<name>password</name>
<required>false</required>
<rtexprvalue>true</rtexprvalue>
</attribute>
</tag>
BM_425184
Scripting Variables
The sql tag optionally stores database query result in the pageContext
object. If id is specified, an instance of allaire.taglib.QueryTable will be
stored. The scope of this object depends on the scope attribute, which is
PageContext.PAGE_SCOPE by default. The following is the
allaire.taglib.QueryTable API:
package allaire.taglib;
public class QueryTable extends Table {
public QueryTable()
public String[] Names;
public Object[] Values;
public void populate(ResultSet rs) throws sqlException;
public boolean next();
}
public class Table {
public Table()
public boolean next();
public void resetCursor();
public String get(int index) throws Exception;
public String get(String column) throws Exception;
public Object getObject(int index) throws Exception;
public Object getObject(String column) throws Exception;
public String getColumnLabel(int index);
public int getRowCount();
public int getColumnCount();
public String[] getColumnNames();
public Table nextTable();
public Table previousTable();
public Table firstTable();
public Table lastTable();
public void setNextTable(Table t);
public void setPreviousTable(Table t);
public String toString();
}
The API supports database queries that return multiple result sets by
chaining tables together. Also, for each next method call, the field values
are available as an Object array instead of calling getObject for individual
values.
BM_429159
Interface
package allaire.taglib;
public interface sql {
void setsqlParam(Object value);
void setsqlParam(Object value, int sqltype);
void setsqlParam(Object value, int sqltype, int scale);
}
BM_425221
Example
This example shows three different uses of the sql tag.
<%@ page import="java.sql.*,javax.sql.*,allaire.taglib.*" %>
<%@ taglib uri="jruntags" prefix="jrun" %>
<%
Class.forName("....").newInstance();
Connection con = DriverManager.getConnection("....");
%>
<jrun:sql connection="<%= con %>" id="q1">
select * from Table1
</jrun:sql>
<jrun:sql driver="..." url="..." id="q2">
select * from Table1
</jrun:sql>
<%-- sql uses java:comp/env/jdbc/dsn1 to lookup a datasource --%>
<jrun:sql datasrc="dsn1" id="q3">
select * from Table1
</jrun:sql>
<%-- you can enumerate the QueryTable by: --%>
<jrun:param id="q3" type="QueryTable"/>
<jrun:foreach item="x" group="<%= q3.Names %>">
<%= x %><br>
</jrun:foreach>
<jrun:foreach group="<%= q3 %>">
<jrun:foreach item="y" group="<%= q3.Values %>">
<%= y %><br>
</jrun:foreach>
</jrun:foreach>
<%-- OR --%>
<jrun:param id="q3" type="QueryTable"/>
<jrun:foreach group="<%= q3 %>">
<%
int count = q3.getColumnCount();
for (int i = 0;i < count;i += 1) {
%>
<%= q3.get(i) %><br>
<%
}
%>
</jrun:foreach>
<%-- OR --%>
<jrun:param id="q3" type="QueryTable"/>
<%
while (q3.next()) {
%>
<jrun:foreach item="y" group="<%= q3.Values %>">
<%= y %><br>
</jrun:foreach>
<%
}
%>
<%-- OR, if you want to use column names... --%>
<jrun:param id="q3" type="QueryTable"/>
<jrun:foreach group="<%= q3 %>">
<%= q3.get("id") %><br>
<%= q3.get("lastname") %><br>
<5= q3.get("firstname") %><br>
</jrun:foreach>
<%-- OR, if you want to use column index... --%>
<jrun:param id="q3" type="QueryTable"/>
<jrun:foreach group="<%= q3 %>">
<%= q3.get(1) %><br>
<%= q3.get(2) %><br>
<5= q3.get(3) %><br>
</jrun:foreach>
Copyright (c) 2000, Allaire Corporation. All rights reserved.
<file:///C:/Program%20Files/Allaire/JRun%20Studio%203.0/Help/JRun_Tag_Librar
y_Reference/customactions4.htm> Previous
<file:///C:/Program%20Files/Allaire/JRun%20Studio%203.0/Help/JRun_Tag_Librar
y_Reference/customactions.htm> Up
<file:///C:/Program%20Files/Allaire/JRun%20Studio%203.0/Help/JRun_Tag_Librar
y_Reference/customactions6.htm> Next
-----Original Message-----
From: Jay [mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ]
Sent: Friday, August 31, 2001 4:09 PM
To: JRun-Talk
Subject: Newbie question - Query
I am trying to include a jsp template that contains a query and output the
result set in another page. Somehow I cannot find good documentation on
doing this. I keep getting the following error:
********** Undefined variable or class name: get_Clips
out.print(get_Clips.get("clipNumber")); ***********
Can someone help!!!
Here is of the code
test.jsp
---------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>test</title>
</head>
<body>
<%@ page import = "allaire.taglib.*" %>
<%@ taglib uri="jruntags" prefix="jrun" %>
<jsp:include page="qry_getdata.jsp" flush="true"/>
</jsp:include>
<jrun:foreach group="get_Clips" >
<%= get_Clips.get("clipNumber") %>
</jrun:foreach>
</body>
</html>
qry_getdata.jsp
---------------------
<jrun:sql datasrc="test" id="get_Clips">
select * from ClipData
</jrun:sql>
<jrun:param id="get_Clips" type="QueryTable" />
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
