Return-Path: <pgomez@pragma.com.co>
Mailing-List: contact cocoon-dev-help@xml.apache.org; run by ezmlm
Delivered-To: mailing list cocoon-dev@xml.apache.org
Received: (qmail 55947 invoked from network); 7 Jul 2000 14:55:57 -0000
Received: from pragma.com.co (207.159.135.113)
  by locus.apache.org with SMTP; 7 Jul 2000 14:55:57 -0000
Received: from helios.pragmalab.com.co ([209.88.10.18])
	by pragma.com.co (8.9.3/8.9.3) with ESMTP id IAA03385
	for <cocoon-dev@xml.apache.org>; Fri, 7 Jul 2000 08:55:32 -0600 (MDT)
Received: from helios ([192.168.0.10]) by
          helios.pragmalab.com.co (Netscape Messaging Server 4.1) with
          ESMTP id FXBNHN00.A01 for <cocoon-dev@xml.apache.org>; Fri, 7
          Jul 2000 09:53:47 +0000
Message-ID: <33165014.962963627336.JavaMail.root@helios>
Date: Fri, 7 Jul 2000 09:53:47 +0000 (GMT+00:00)
From: "=?ISO-8859-1?Q?Pedro_E._G=F3mez?=" <pgomez@pragma.com.co>
Reply-To: "=?ISO-8859-1?Q?Pedro_E._G=F3mez?=" <pgomez@pragma.com.co>
To: Cocoon <cocoon-dev@xml.apache.org>
Subject: Sugestions and doubs
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Organization: PRAGMA LIMITADA
X-Mailer: misiva <http://www.pragma.com.co/misiva/>

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