You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by daniel robinson <dr...@dnai.com> on 2002/06/03 19:11:53 UTC
SQL, Actions, ESQL Help Please!!!
Hi,
I've been poking around but I'm clueless (yes, generally as well as
specifically).
What am I trying to do:
1) I have a PostgreSQL table with a field called "StoryText" defined as
a "Text" (virtually unlmited size text data)
2) I wish to update this table with the contents of an XML element ( say
<StoryText>a lot of text with special characters like ' as well as
html code <a href="www.clueless.com/IAM">who is this
noob?</a></StoryText>
3) I've tried ESQL like this:
<esql:connection>
<esql:pool>VSolano</esql:pool>
<esql:execute-query>
<esql:query>
update "Story" set "Title"= '<xsp-request:get-parameter
name="Title"/>',
"Description"= '<xsp-request:get-parameter
name="Description"/>',
StoryText"= '<xsp-request:get-parameter
name="StoryText"/>'
where "ContentID" = '<xsp-request:get-parameter
name="ContentID"/>'
</esql:query>
</esql:execute-query>
</esql:connection>
There have been many attempts :). I HAVE been able to update the table
when I use literals and short values for the updates themselves (i.e.
...set "Title"='This is for noobs'....).
My questions:
1) should I be using ESQL or Actions? There has been some discussion on
the listserve that seems to indicate that Actions are the way to go.
2) do the DatabaseActions work? there seems to have been a lot of
confusion about this.
3) Does someone have examples - hopefully of exactly the sort of thing
I'm trying to do - Store XML in the db - I'm sure I'm not the first.
I am putting together documentation so that when this is all over I can
SHARE IT, because no-one should have to live through this again. :)
Thanks,
Dan
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: SQL, Actions, ESQL Help Please!!!
Posted by Conny Pemfors <co...@uarda.lu.se>.
where do I set my map:sitemap and pipelines to be able to use my xml
documents
/Conny
----- Original Message -----
From: "Christian Haul" <ha...@dvs1.informatik.tu-darmstadt.de>
To: <co...@xml.apache.org>; <jw...@bigfoot.com>
Sent: Tuesday, June 04, 2002 9:47 AM
Subject: Re: SQL, Actions, ESQL Help Please!!!
> On 03.Jun.2002 -- 11:53 PM, John Austin wrote:
> > On Monday 03 June 2002 01:50 pm, you wrote:
> > > On 03.Jun.2002 -- 10:11 AM, daniel robinson wrote:
> > > > Hi,
> > > >
> > > > I've been poking around but I'm clueless (yes, generally as well as
> > > > specifically).
> >
> > I have some code that simply uses embedded Java in an XSP and a Java
> > temp file. This code processes a File that is submitted by the user's
> > Applet simulating a FORM with method=PUT. The user's FILE parameter is
> > an XML document produced by the applet. I have to insert the DTD stuff
> > because the Serializer I am generating this file from doesn't generate
> > the DTD statements.
> >
> > After the file is pulled into a string and written to a File, I add it
> > to my PostgreSQL database as a BLOB.
> >
> >
> > <?xml version="1.0" encoding="ISO-8859-1"?>
> >
> > <xsp:page
> > language="java"
> > create-session="yes"
> > xmlns:xsp="http://apache.org/xsp"
> > xmlns:session="http://apache.org/xsp/session/2.0"
> > xmlns:xsp-request="http://apache.org/xsp/request/2.0"
> > xmlns:xsp-response="http://apache.org/xsp/response/2.0"
> xmlns:esql="http://apache.org/cocoon/SQL/v2"
> > xmlns:log="http://apache.org/xsp/log/2.0">
> >
> > <xsp:structure>
> > <xsp:include>java.io.*</xsp:include>
> > <xsp:include>java.sql.*</xsp:include>
> > </xsp:structure>
> > <page>
> > <content>
> > <xsp:logic>
> > File output = null;
> > try {
> >
> > String username = (String)
> > <session:get-attribute name="username" default="nobody" />;
> > //System.err.println( "username=" + username );
> >
> > String filename =
> > <xsp-request:get-parameter name="FILENAME" />;
> > //System.err.println( "filename=" + filename );
> >
> > String config_data =
> > <xsp-request:get-parameter name="FILE" />;
> > //System.err.println( "config_data=" + config_data );
> >
> > int position = config_data.indexOf( <![CDATA["<product>"]]> );
> >
> > StringBuffer saveString = new StringBuffer( config_data.substring( 0,
> > position ) );
> > <![CDATA[
> > saveString.append( "<!DOCTYPE product [\n" +
> > "<!ELEMENT product (page+)>\n" +
> > "<!ELEMENT children (page+)>\n" +
> > "<!ELEMENT Company EMPTY>\n" +
> > "<!ELEMENT page (#PCDATA | Company | children)*>\n" +
> > "<!ATTLIST page level (1 | 2 | 3 | 4 | 5 | 8 ) #REQUIRED\n" +
> > " target CDATA #REQUIRED\n" +
> > " checkbox (true | false) #REQUIRED\n" +
> > ">\n" +
> > "]>\n" );
> > ]]>
> > saveString.append( config_data.substring( position) );
> >
> > output = File.createTempFile( "phoenix_", ".xml" );
> >
> > FileWriter fw = new FileWriter( output );
> >
> > fw.write( saveString.toString() );
> >
> > fw.close();
> >
> <esql:connection>
> <esql:driver>org.postgresql.Driver</esql:driver>
> <esql:dburl>jdbc:postgresql://192.168.1.100:5432/test</esql:dburl>
> <esql:username>postgres</esql:username>
> <esql:password></esql:password>
> <esql:execute-query>
> <esql:query>UPDATE user_products SET
product_file=lo_import('"<xsp:expr>output.getAbsolutePath()</xsp:expr>"')
> where name = <esql:parameter><xsp:expr>username</xsp:expr>
> AND productname =
<esql:parameter><xsp:expr>filename</xsp:expr></esql:parameter>;</esql:query>
> <esql:update-results>
> <xsp:logic>
> int rows = <esql:get-update-count/>
> </xsp:logic>
> </esql:update-results>
> <esql:error-results>
> <xsp:logic>
> System.err.println( "Caught: " + <esql:get-message/>
> );
> <xsp:logic>
> <esql:get-stacktrace/>
> </esql:error-results>
> </esql:execute-query>
> </esql:connection>
>
> > </xsp:logic>
> > <session:set-attribute name="TEMPFILENAME">
> > <xsp:logic><xsp:expr>output.getAbsolutePath()</xsp:expr></xsp:logic>
> > </session:set-attribute>
> > <para>
> > TEMPFILENAME=<session:get-attribute name="TEMPFILENAME" />
> > </para>
> > </content>
> > </page>
> > </xsp:page>
>
> The above *might* work. If it doesn't, try to remove the
> esql:parameter tags (and leave only the xsp:expr tags).
>
> Chris.
>
> --
> C h r i s t i a n H a u l
> haul@informatik.tu-darmstadt.de
> fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
>
> To unsubscribe, e-mail: <co...@xml.apache.org>
> For additional commands, e-mail: <co...@xml.apache.org>
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: SQL, Actions, ESQL Help Please!!!
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 03.Jun.2002 -- 11:53 PM, John Austin wrote:
> On Monday 03 June 2002 01:50 pm, you wrote:
> > On 03.Jun.2002 -- 10:11 AM, daniel robinson wrote:
> > > Hi,
> > >
> > > I've been poking around but I'm clueless (yes, generally as well as
> > > specifically).
>
> I have some code that simply uses embedded Java in an XSP and a Java
> temp file. This code processes a File that is submitted by the user's
> Applet simulating a FORM with method=PUT. The user's FILE parameter is
> an XML document produced by the applet. I have to insert the DTD stuff
> because the Serializer I am generating this file from doesn't generate
> the DTD statements.
>
> After the file is pulled into a string and written to a File, I add it
> to my PostgreSQL database as a BLOB.
>
>
> <?xml version="1.0" encoding="ISO-8859-1"?>
>
> <xsp:page
> language="java"
> create-session="yes"
> xmlns:xsp="http://apache.org/xsp"
> xmlns:session="http://apache.org/xsp/session/2.0"
> xmlns:xsp-request="http://apache.org/xsp/request/2.0"
> xmlns:xsp-response="http://apache.org/xsp/response/2.0"
xmlns:esql="http://apache.org/cocoon/SQL/v2"
> xmlns:log="http://apache.org/xsp/log/2.0">
>
> <xsp:structure>
> <xsp:include>java.io.*</xsp:include>
> <xsp:include>java.sql.*</xsp:include>
> </xsp:structure>
> <page>
> <content>
> <xsp:logic>
> File output = null;
> try {
>
> String username = (String)
> <session:get-attribute name="username" default="nobody" />;
> //System.err.println( "username=" + username );
>
> String filename =
> <xsp-request:get-parameter name="FILENAME" />;
> //System.err.println( "filename=" + filename );
>
> String config_data =
> <xsp-request:get-parameter name="FILE" />;
> //System.err.println( "config_data=" + config_data );
>
> int position = config_data.indexOf( <![CDATA["<product>"]]> );
>
> StringBuffer saveString = new StringBuffer( config_data.substring( 0,
> position ) );
> <![CDATA[
> saveString.append( "<!DOCTYPE product [\n" +
> "<!ELEMENT product (page+)>\n" +
> "<!ELEMENT children (page+)>\n" +
> "<!ELEMENT Company EMPTY>\n" +
> "<!ELEMENT page (#PCDATA | Company | children)*>\n" +
> "<!ATTLIST page level (1 | 2 | 3 | 4 | 5 | 8 ) #REQUIRED\n" +
> " target CDATA #REQUIRED\n" +
> " checkbox (true | false) #REQUIRED\n" +
> ">\n" +
> "]>\n" );
> ]]>
> saveString.append( config_data.substring( position) );
>
> output = File.createTempFile( "phoenix_", ".xml" );
>
> FileWriter fw = new FileWriter( output );
>
> fw.write( saveString.toString() );
>
> fw.close();
>
<esql:connection>
<esql:driver>org.postgresql.Driver</esql:driver>
<esql:dburl>jdbc:postgresql://192.168.1.100:5432/test</esql:dburl>
<esql:username>postgres</esql:username>
<esql:password></esql:password>
<esql:execute-query>
<esql:query>UPDATE user_products SET product_file=lo_import('"<xsp:expr>output.getAbsolutePath()</xsp:expr>"')
where name = <esql:parameter><xsp:expr>username</xsp:expr>
AND productname = <esql:parameter><xsp:expr>filename</xsp:expr></esql:parameter>;</esql:query>
<esql:update-results>
<xsp:logic>
int rows = <esql:get-update-count/>
</xsp:logic>
</esql:update-results>
<esql:error-results>
<xsp:logic>
System.err.println( "Caught: " + <esql:get-message/>
);
<xsp:logic>
<esql:get-stacktrace/>
</esql:error-results>
</esql:execute-query>
</esql:connection>
> </xsp:logic>
> <session:set-attribute name="TEMPFILENAME">
> <xsp:logic><xsp:expr>output.getAbsolutePath()</xsp:expr></xsp:logic>
> </session:set-attribute>
> <para>
> TEMPFILENAME=<session:get-attribute name="TEMPFILENAME" />
> </para>
> </content>
> </page>
> </xsp:page>
The above *might* work. If it doesn't, try to remove the
esql:parameter tags (and leave only the xsp:expr tags).
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: SQL, Actions, ESQL Help Please!!!
Posted by John Austin <jo...@integerservices.no-ip.com>.
On Monday 03 June 2002 01:50 pm, you wrote:
> On 03.Jun.2002 -- 10:11 AM, daniel robinson wrote:
> > Hi,
> >
> > I've been poking around but I'm clueless (yes, generally as well as
> > specifically).
I have some code that simply uses embedded Java in an XSP and a Java
temp file. This code processes a File that is submitted by the user's
Applet simulating a FORM with method=PUT. The user's FILE parameter is
an XML document produced by the applet. I have to insert the DTD stuff
because the Serializer I am generating this file from doesn't generate
the DTD statements.
After the file is pulled into a string and written to a File, I add it
to my PostgreSQL database as a BLOB.
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsp:page
language="java"
create-session="yes"
xmlns:xsp="http://apache.org/xsp"
xmlns:session="http://apache.org/xsp/session/2.0"
xmlns:xsp-request="http://apache.org/xsp/request/2.0"
xmlns:xsp-response="http://apache.org/xsp/response/2.0"
xmlns:log="http://apache.org/xsp/log/2.0">
<xsp:structure>
<xsp:include>java.io.*</xsp:include>
<xsp:include>java.sql.*</xsp:include>
</xsp:structure>
<page>
<content>
<xsp:logic>
File output = null;
try {
String username = (String)
<session:get-attribute name="username" default="nobody" />;
//System.err.println( "username=" + username );
String filename =
<xsp-request:get-parameter name="FILENAME" />;
//System.err.println( "filename=" + filename );
String config_data =
<xsp-request:get-parameter name="FILE" />;
//System.err.println( "config_data=" + config_data );
int position = config_data.indexOf( <![CDATA["<product>"]]> );
StringBuffer saveString = new StringBuffer( config_data.substring( 0,
position ) );
<![CDATA[
saveString.append( "<!DOCTYPE product [\n" +
"<!ELEMENT product (page+)>\n" +
"<!ELEMENT children (page+)>\n" +
"<!ELEMENT Company EMPTY>\n" +
"<!ELEMENT page (#PCDATA | Company | children)*>\n" +
"<!ATTLIST page level (1 | 2 | 3 | 4 | 5 | 8 ) #REQUIRED\n" +
" target CDATA #REQUIRED\n" +
" checkbox (true | false) #REQUIRED\n" +
">\n" +
"]>\n" );
]]>
saveString.append( config_data.substring( position) );
output = File.createTempFile( "phoenix_", ".xml" );
FileWriter fw = new FileWriter( output );
fw.write( saveString.toString() );
fw.close();
try {
Class.forName( "org.postgresql.Driver" );
Connection conn =
DriverManager.getConnection(
"jdbc:postgresql://192.168.1.100:5432/test", "postgres", "" );
Statement stmt = conn.createStatement();
String update_statement = "UPDATE user_products SET
product_file=lo_import( \'" + output.getAbsolutePath() + "\') " +
" where name = \'" + username + "\'" +
" AND productname = \'" + filename + "\'";
//System.err.println( "Update statement: " + update_statement );
int rows = stmt.executeUpdate( update_statement );
//System.err.println( "Rows updated: " + rows );
stmt.close();
conn.close();
}
catch(ClassNotFoundException e ) {
System.err.println( "Caught: " + e );
e.printStackTrace(); // @@@ ToDo exit properly
}
catch(SQLException sqle ) {
System.err.println( "Caught: " + sqle );
sqle.printStackTrace(); // @@@ ToDo exit properly
}
}
//output.delete();
catch( Exception e ) {
System.err.println( "Caught: " + e );
e.printStackTrace();
}
</xsp:logic>
<session:set-attribute name="TEMPFILENAME">
<xsp:logic><xsp:expr>output.getAbsolutePath()</xsp:expr></xsp:logic>
</session:set-attribute>
<para>
TEMPFILENAME=<session:get-attribute name="TEMPFILENAME" />
</para>
</content>
</page>
</xsp:page>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: SQL, Actions, ESQL Help Please!!!
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 03.Jun.2002 -- 10:11 AM, daniel robinson wrote:
> Hi,
>
> I've been poking around but I'm clueless (yes, generally as well as
> specifically).
>
> What am I trying to do:
>
> 1) I have a PostgreSQL table with a field called "StoryText" defined as
> a "Text" (virtually unlmited size text data)
Texts are a special case that is IMHO not handled. Texts (CLOBs and
BLOBs) need to be read and written through a stream. Patches are welcome :-)
> 2) I wish to update this table with the contents of an XML element ( say
>
> <StoryText>a lot of text with special characters like ' as well as
> html code <a href="www.clueless.com/IAM">who is this
> noob?</a></StoryText>
esql:get-xml is your friend. But it requires well formed XML / XHTML.
> 3) I've tried ESQL like this:
>
> <esql:connection>
> <esql:pool>VSolano</esql:pool>
> <esql:execute-query>
> <esql:query>
>
>
> update "Story" set "Title"= '<xsp-request:get-parameter
> name="Title"/>',
> "Description"= '<xsp-request:get-parameter
> name="Description"/>',
> StoryText"= '<xsp-request:get-parameter
> name="StoryText"/>'
> where "ContentID" = '<xsp-request:get-parameter
> name="ContentID"/>'
>
> </esql:query>
> </esql:execute-query>
> </esql:connection>
>
> There have been many attempts :). I HAVE been able to update the table
> when I use literals and short values for the updates themselves (i.e.
> ...set "Title"='This is for noobs'....).
See above.
> My questions:
>
> 1) should I be using ESQL or Actions? There has been some discussion on
> the listserve that seems to indicate that Actions are the way to go.
In my opinion actions are great for inset / delete / update and esql
is great for select. BTW the database actions do support CLOBs.
> 2) do the DatabaseActions work? there seems to have been a lot of
> confusion about this.
They do work, although I advocate using the "new" ones form the
actions.modular package (CVS-HEAD or CVS-2.0.3 scratchpad).
Is there? If you can sumarize it I'd be happy to comment.
> 3) Does someone have examples - hopefully of exactly the sort of thing
> I'm trying to do - Store XML in the db - I'm sure I'm not the first.
Sorry, not at hand.
> I am putting together documentation so that when this is all over I can
> SHARE IT, because no-one should have to live through this again. :)
Speaking of documentation, there is some more on datbases and cocoon
in CVS-2.0.3 or now live on site. See
http://xml.apache.org/cocoon/userdocs/concepts/databases.html for a
start.
Plus, I'm currently (right now) writing a bit more on the esql
logicsheet. Stay tuned. That shouldn't keep you from writing docs,
though. We can't get enough of them!
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faqs.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>