Sugestions and doubs

["Pedro E. Gómez" <pg...@pragma.com.co>]

I have some questions and doubts about the SQL processor and XSL:

1- Do you have to declare always the params which are going to be use inside the XSL template? e.g.
<xsl:param name="myparam"/>

Without this it does not work for me.

2- How can I format the results in the XSL, for example a date?

3- Can I define my own functions to be use in the XSL?
The general idea is that i want to add my own functions to the XSL.

4- There are some kind of connection pool?
I have some sugestions about connection pooling and the queries.

Connections:
~~~~~~~~~~~~
- As some one say in the cocoon documentation, there are still many information in legacy systems. The connections to the database are expensive and it is goot to have some pool schema.


- Put the connection information in some place alone. It could be in XML or a properties file for each one. Diferent properties file is usefull if you have a big installation and diferent people working but not together (I mean, in diferent projects), and you don't want to share your users and passwords with the other people.

In properties File:
~~~~~~~~~~~~~~~~~~
autoCommit=false
driver=oracle.jdbc.driver.OracleDriver
freeTimeout=300000
lockedTimeout=7200000
getTimeout=2000
maxConnections=3
url=jdbc:oracle:thin:@homero:1521:dbhomero
user=sid
password=sid

Or as I said in an XML file:
~~~~~~~~~~~~~~~~~~~~~~~~~~~
<pools>
	<pool name="sidPool">
		<autoCommit>false</autoCommit>
		<driver>oracle.jdbc.driver.OracleDriver</driver>
		<freeTimeout>300000</freeTimeout>
		<lockedTimeout>7200000</lockedTimeout>
		<getTimeout>2000</getTimeout>
		<maxConnections>3</maxConnections>
		<url>jdbc:oracle:thin:@homer:1521:dbhomer</url>
		<user>sid</user>
		<password>sid</password>
	</pool>
</pools>


The queries
~~~~~~~~~~~
- Is so useful to have all the related queries in the same place because of the big systems. You can change so easy all the statements.

<queries>
	<query name="someDocuments">
		<sql:doc-element>documents</sql:doc-element>
		<sql:row-element>document</sql:row-element>
		<sql:tag-case>lower</sql:tag-case>
		<sql:null-indicator>yes</sql:null-indicator>
		<sql:id-attribute>coddoc</sql:id-attribute>
		<sql:id-attribute-column>coddoc</sql:id-attribute-column>
		<sql:count-attribute>count</sql:count-attribute>
		<sql:statement>select * from sidt_docume where coddoc > ? and type = ?
	<query>

	<query name="documentTypes">
		<sql:doc-element>types</sql:doc-element>
		<sql:row-element>type</sql:row-element>
		<sql:tag-case>lower</sql:tag-case>
		<sql:null-indicator>yes</sql:null-indicator>
		<sql:id-attribute>type</sql:id-attribute>
		<sql:id-attribute-column>type</sql:id-attribute-column>
		<sql:count-attribute>count</sql:count-attribute>
		<sql:statement>select * from sidt_types</statement>
	</query>

	<query name="documentComments">
		<sql:doc-element>comments</sql:doc-element>
		<sql:row-element>comment</sql:row-element>
		<sql:tag-case>lower</sql:tag-case>
		<sql:null-indicator>yes</sql:null-indicator>
		<sql:id-attribute>commentId</sql:id-attribute>
		<sql:id-attribute-column>commentId</sql:id-attribute-column>
		<sql:count-attribute>count</sql:count-attribute>
		<sql:statement>select * from sidt_comments where coddoc = ?</statement>
	</query>

</queries>



XSP queries
~~~~~~~~~~~
Something like this:

<?xml version="1.0" encoding="iso-8859-1"?>
<?xml-stylesheet href="documentsAndTypes.xsl" type="text/xsl"?>

<?cocoon-process type="xsp"?>
<?cocoon-process type="xslt"?>

<xsp:page
  language="java"
  xmlns:sql="http://www.apache.org/1999/SQL"
  xmlns:xsp="http://www.apache.org/1999/XSP/Core"
>

<page title="results">
	<sql:execute-query>
		<!-- If you don't want the statements in other place they could be inlile -->
		<sql:statement>someDocuments</sql:statement>
		<sql:pool>sid</sql:pool>
		<sql:parameters>
			<sql:parameter><xsp:expr>request.getParameter("coddoc")</xsp:expr></sql:parameter>
			<sql:parameter><xsp:expr>request.getParameter("type")</xsp:expr></sql:parameter>
		</sql:parameters>
	</sql:execute-query>

	<sql:execute-query>
		<!-- If you don't want the statements in other place they could be inlile -->
		<sql:statement>documentTypes</sql:statement>
		<sql:pool>sid</sql:pool>
	</sql:execute-query>

</page>

Now lets supose that we have the comments of each one document, I mean, a new query for each document something like this:


	<sql:execute-query>
		<sql:statement>someDocuments</sql:statement>
		<sql:pool>sid</sql:pool>
		<sql:parameters>
			<sql:parameter><xsp:expr>request.getParameter("coddoc")</xsp:expr></sql:parameter>
			<sql:parameter><xsp:expr>request.getParameter("type")</xsp:expr></sql:parameter>
		</sql:parameters>
		<sql:subquery>
			<sql:statement>documentComments</sql:statement>
			<sql:parameters>
				<!-- I don't know how to say that the param is the current coddoc document-->
				<sql:parameter>document.coddoc</sql:parameter>
			</sql:parameters>
		</sql:subquery>
	</sql:execute-query>


I said all this because we have our own html template framework and it suport all this and we are so happy with it, and I want to share it with you, and you will be happy to.

In our schema we have diferents statements files, becouse we have many systems living together. The statement name could be something like <sql:statement>sidStatements.documentComments</sql:statement>


For example I can have a html template like this:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

<html>
<%=global.PJR_BASEHREF/%>
<body>
<h1>Documentos</h1>
<!-- The params are: 1- Pool, 2- Statements set, 3- The statement, 4,5....- The params -->
<%PJRBLOCK NAME="documents" SRC="DBManager.executeQuery('pgomez.sample01.isaPool','pgomez.sample01.isaSt','documents',form.coddoc,form.type)"%>
<TABLE BORDER=1 CELLSPACING=0>
	<TR>
	<TD><B>Code</B></TD>
	<TD><B>Name</b></TD>
	<TD><B>Comments</b></TD>
	</TR>
	<%PJRBODY%>
		<TD><%=documents.coddoc/%></TD>
		<TD><%=documents.nombre/%>, <%=documents.getObject("date").formatDate("dd/MM/yyyy")/%></TD>
		<TD>
			<%PJRBLOCK NAME="comments" SRC="DBManager.executeQuery('pgomez.sample01.isaPool','pgomez.sample01.isaSt','comments',documents.coddoc)"%>
				<%=comments.comment/%><br>
			<%/PJRBLOCK%>
		</TD>
		</TR>
	<%/PJRBODY%>
	</TABLE>
<%/PJRBLOCK%>
</body>
</html>


CONCLUSION
~~~~~~~~~~
I can work on something like this, what do you thing about all?


______________________________________
Message sent with Misiva.
Visit us at http://www.pragma.com.co/misiva

Re: Sugestions and doubs

[Donald Ball <ba...@webslingerZ.com>]

On Fri, 7 Jul 2000, [ISO-8859-1] Pedro E. G�mez wrote:

> I have some questions and doubts about the SQL processor and XSL:

use the sql logicsheet - http://xml.apache.org/cocoon/sqltaglib.html

> 1- Do you have to declare always the params which are going to be use
> inside the XSL template? e.g. <xsl:param name="myparam"/>
> 
> Without this it does not work for me.

yes, you do.

> 2- How can I format the results in the XSL, for example a date?

if you're formatting results from a sql query, you can use the
SimpleDateFormat class from the JDK - see the sql logicsheet docs for an
example. or you can use call a xalan extension function directly in your
stylesheet.

> 3- Can I define my own functions to be use in the XSL?
> The general idea is that i want to add my own functions to the XSL.

yes - see the extension function documentation on the xalan site.

> 4- There are some kind of connection pool?
> I have some sugestions about connection pooling and the queries.

a connection pool has been added to the latest cvs for the sql
logicsheet. The pools are declared in the cocoon.properties file.

- donald