You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@servicemix.apache.org by DEBANJAN <De...@level3.com> on 2007/10/04 21:17:06 UTC
SQL and XPath Query(Urgent)
I am using a schema ADM containing a table MESSAGE. In the sql.properties
file i have a statement like-
ROLLUPSELECT=SELECT DOCUMENTELEMENT\, TRUNC(RECEIVEDDATE)\,
RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\, COUNT(*) FROM
ADM.MESSAGE WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
TRUNC(SYSDATE) GROUP BY TRUNC(RECEIVEDDATE)\, DOCUMENTELEMENT\,
RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER
Then I have a statement like-
ROLLUPINSERT=INSERT INTO ADM.ESB_ACTIVITY_ROLLUP (DOCUMENTELEMENT\,
EVENTDATE\, RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\,
MESSAGECOUNT) VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
'YYYY-MM-DD')\, '{XPATH://COLUMN3}'\, '{XPATH://COLUMN4}'\,
'{XPATH://COLUMN5}'\, {XPATH://COLUMN6})
My queries are-
1. What are ROLLUPSELECT and ROLLUPINSERT? Are they created functions which
are called by the java code?
2. Does date does this part of the SQL statement considers valid?
".....WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
TRUNC(SYSDATE)......"
3. Is this a XPath query? can someone let me know how this XPath query
works?
"...VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
'YYYY-MM-DD')\, ....."
Thanks in advance.
-Dev
--
View this message in context: http://www.nabble.com/SQL-and-XPath-Query%28Urgent%29-tf4570958s12049.html#a13046959
Sent from the ServiceMix - User mailing list archive at Nabble.com.
Re: SQL and XPath Query(Urgent)
Posted by DEBANJAN <De...@level3.com>.
Bruce,
Thanks a ton.
-Dev
bsnyder wrote:
>
> On 10/4/07, DEBANJAN <De...@level3.com> wrote:
>>
>> I am using a schema ADM containing a table MESSAGE. In the sql.properties
>> file i have a statement like-
>>
>> ROLLUPSELECT=SELECT DOCUMENTELEMENT\, TRUNC(RECEIVEDDATE)\,
>> RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\, COUNT(*) FROM
>> ADM.MESSAGE WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
>> TRUNC(SYSDATE) GROUP BY TRUNC(RECEIVEDDATE)\, DOCUMENTELEMENT\,
>> RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER
>>
>> Then I have a statement like-
>>
>> ROLLUPINSERT=INSERT INTO ADM.ESB_ACTIVITY_ROLLUP (DOCUMENTELEMENT\,
>> EVENTDATE\, RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\,
>> MESSAGECOUNT) VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
>> 'YYYY-MM-DD')\, '{XPATH://COLUMN3}'\, '{XPATH://COLUMN4}'\,
>> '{XPATH://COLUMN5}'\, {XPATH://COLUMN6})
>>
>> My queries are-
>>
>> 1. What are ROLLUPSELECT and ROLLUPINSERT? Are they created functions
>> which
>> are called by the java code?
>
> It looks to me like it's just a SQL statement that is assigned to the
> property name ROLLUPSELECT and ROLLUPINSERT. I'm guessing that the
> properties file is read in by a Java class and the queries are each
> accessed via a call to the properties.getProperty(String) method.
>
>> 2. Does date does this part of the SQL statement considers valid?
>> ".....WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
>> TRUNC(SYSDATE)......"
>
> Sure, this WHERE clause is comparing some dates against the Oracle
> SYSDATE but it is using the Oracle TRUNC function to truncate the
> SYSDATE to some precision before the comparison.
>
>> 3. Is this a XPath query? can someone let me know how this XPath query
>> works?
>> "...VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
>> 'YYYY-MM-DD')\, ....."
>
> I've not used the XPath functions in Oracle so I'll have to guess
> here, but it seems that the COLUMN1 and COLUMN2 variables are columns
> in the database that are stored as XML and I think the XPath function
> probably supports XPath query syntax.
>
> Hope that helps ;-).
>
> Bruce
> --
> perl -e 'print
> unpack("u30","D0G)U8V4\@4VYY9&5R\"F)R=6-E+G-N>61E<D\!G;6%I;\"YC;VT*"
> );'
>
> Apache ActiveMQ - http://activemq.org/
> Apache ServiceMix - http://servicemix.org/
> Apache Geronimo - http://geronimo.apache.org/
> Castor - http://castor.org/
>
>
--
View this message in context: http://www.nabble.com/SQL-and-XPath-Query%28Urgent%29-tf4570958s12049.html#a13058078
Sent from the ServiceMix - User mailing list archive at Nabble.com.
Re: SQL and XPath Query(Urgent)
Posted by Bruce Snyder <br...@gmail.com>.
On 10/4/07, DEBANJAN <De...@level3.com> wrote:
>
> I am using a schema ADM containing a table MESSAGE. In the sql.properties
> file i have a statement like-
>
> ROLLUPSELECT=SELECT DOCUMENTELEMENT\, TRUNC(RECEIVEDDATE)\,
> RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\, COUNT(*) FROM
> ADM.MESSAGE WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
> TRUNC(SYSDATE) GROUP BY TRUNC(RECEIVEDDATE)\, DOCUMENTELEMENT\,
> RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER
>
> Then I have a statement like-
>
> ROLLUPINSERT=INSERT INTO ADM.ESB_ACTIVITY_ROLLUP (DOCUMENTELEMENT\,
> EVENTDATE\, RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\,
> MESSAGECOUNT) VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
> 'YYYY-MM-DD')\, '{XPATH://COLUMN3}'\, '{XPATH://COLUMN4}'\,
> '{XPATH://COLUMN5}'\, {XPATH://COLUMN6})
>
> My queries are-
>
> 1. What are ROLLUPSELECT and ROLLUPINSERT? Are they created functions which
> are called by the java code?
It looks to me like it's just a SQL statement that is assigned to the
property name ROLLUPSELECT and ROLLUPINSERT. I'm guessing that the
properties file is read in by a Java class and the queries are each
accessed via a call to the properties.getProperty(String) method.
> 2. Does date does this part of the SQL statement considers valid?
> ".....WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
> TRUNC(SYSDATE)......"
Sure, this WHERE clause is comparing some dates against the Oracle
SYSDATE but it is using the Oracle TRUNC function to truncate the
SYSDATE to some precision before the comparison.
> 3. Is this a XPath query? can someone let me know how this XPath query
> works?
> "...VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
> 'YYYY-MM-DD')\, ....."
I've not used the XPath functions in Oracle so I'll have to guess
here, but it seems that the COLUMN1 and COLUMN2 variables are columns
in the database that are stored as XML and I think the XPath function
probably supports XPath query syntax.
Hope that helps ;-).
Bruce
--
perl -e 'print unpack("u30","D0G)U8V4\@4VYY9&5R\"F)R=6-E+G-N>61E<D\!G;6%I;\"YC;VT*"
);'
Apache ActiveMQ - http://activemq.org/
Apache ServiceMix - http://servicemix.org/
Apache Geronimo - http://geronimo.apache.org/
Castor - http://castor.org/
Re: SQL and XPath Query(Urgent)
Posted by DEBANJAN <De...@level3.com>.
Well, I know this is not a typical ServiceMix query but the SQL queries and
standards I am pondering over are used extensively in ServiceMix on which I
am currently working. So I thought some one can help me out. Sorry if I have
put it in the wrong Forum. Any helpful pointer from any member will be very
helpful for me.
-Dev
gnodet wrote:
>
> How is that related to servicemix ? ;-)
>
> On 10/4/07, DEBANJAN <De...@level3.com> wrote:
>>
>> I am using a schema ADM containing a table MESSAGE. In the sql.properties
>> file i have a statement like-
>>
>> ROLLUPSELECT=SELECT DOCUMENTELEMENT\, TRUNC(RECEIVEDDATE)\,
>> RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\, COUNT(*) FROM
>> ADM.MESSAGE WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
>> TRUNC(SYSDATE) GROUP BY TRUNC(RECEIVEDDATE)\, DOCUMENTELEMENT\,
>> RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER
>>
>> Then I have a statement like-
>>
>> ROLLUPINSERT=INSERT INTO ADM.ESB_ACTIVITY_ROLLUP (DOCUMENTELEMENT\,
>> EVENTDATE\, RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\,
>> MESSAGECOUNT) VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
>> 'YYYY-MM-DD')\, '{XPATH://COLUMN3}'\, '{XPATH://COLUMN4}'\,
>> '{XPATH://COLUMN5}'\, {XPATH://COLUMN6})
>>
>> My queries are-
>>
>> 1. What are ROLLUPSELECT and ROLLUPINSERT? Are they created functions
>> which
>> are called by the java code?
>>
>> 2. Does date does this part of the SQL statement considers valid?
>> ".....WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
>> TRUNC(SYSDATE)......"
>>
>> 3. Is this a XPath query? can someone let me know how this XPath query
>> works?
>> "...VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
>> 'YYYY-MM-DD')\, ....."
>>
>> Thanks in advance.
>>
>> -Dev
>> --
>> View this message in context:
>> http://www.nabble.com/SQL-and-XPath-Query%28Urgent%29-tf4570958s12049.html#a13046959
>> Sent from the ServiceMix - User mailing list archive at Nabble.com.
>>
>>
>
>
> --
> Cheers,
> Guillaume Nodet
> ------------------------
> Blog: http://gnodet.blogspot.com/
>
>
--
View this message in context: http://www.nabble.com/SQL-and-XPath-Query%28Urgent%29-tf4570958s12049.html#a13047296
Sent from the ServiceMix - User mailing list archive at Nabble.com.
Re: SQL and XPath Query(Urgent)
Posted by Guillaume Nodet <gn...@gmail.com>.
How is that related to servicemix ? ;-)
On 10/4/07, DEBANJAN <De...@level3.com> wrote:
>
> I am using a schema ADM containing a table MESSAGE. In the sql.properties
> file i have a statement like-
>
> ROLLUPSELECT=SELECT DOCUMENTELEMENT\, TRUNC(RECEIVEDDATE)\,
> RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\, COUNT(*) FROM
> ADM.MESSAGE WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
> TRUNC(SYSDATE) GROUP BY TRUNC(RECEIVEDDATE)\, DOCUMENTELEMENT\,
> RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER
>
> Then I have a statement like-
>
> ROLLUPINSERT=INSERT INTO ADM.ESB_ACTIVITY_ROLLUP (DOCUMENTELEMENT\,
> EVENTDATE\, RECEIVINGSERVER\, ORIGINATIONIP\, ORIGINATIONUSER\,
> MESSAGECOUNT) VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
> 'YYYY-MM-DD')\, '{XPATH://COLUMN3}'\, '{XPATH://COLUMN4}'\,
> '{XPATH://COLUMN5}'\, {XPATH://COLUMN6})
>
> My queries are-
>
> 1. What are ROLLUPSELECT and ROLLUPINSERT? Are they created functions which
> are called by the java code?
>
> 2. Does date does this part of the SQL statement considers valid?
> ".....WHERE RECEIVEDDATE > TRUNC(SYSDATE)-1 AND RECEIVEDDATE <
> TRUNC(SYSDATE)......"
>
> 3. Is this a XPath query? can someone let me know how this XPath query
> works?
> "...VALUES('{XPATH://COLUMN1}'\, TO_DATE ('{XPATH://COLUMN2}'\,
> 'YYYY-MM-DD')\, ....."
>
> Thanks in advance.
>
> -Dev
> --
> View this message in context: http://www.nabble.com/SQL-and-XPath-Query%28Urgent%29-tf4570958s12049.html#a13046959
> Sent from the ServiceMix - User mailing list archive at Nabble.com.
>
>
--
Cheers,
Guillaume Nodet
------------------------
Blog: http://gnodet.blogspot.com/