UDF and JDBC

[* Tong *]

Hi,

Is it true that UDF cannot be use in JDBC SQL statements?

The very statement runs fine in DB2 but gives
javax.servlet.ServletException when called in JDBC. This seems weird
though, doesn't JDBC just pass whatever sql statement back to DB2
for execution rather than execute it on its own?

FYI, my UDF is isBeginWith, as is used as

select ... from ...
where isBeginWith(PrdTag,CatTag) = 1

Please give a simple example for the workaround. Thanks

--
Tong (remove underscore(s) to reply)
*niX Power Tools Project: http://xpt.sourceforge.net/
- All free contribution & collection

[* Tong *]

* Tong * <sun_...@users.sourceforge.net> writes:

> Hi,
>
> Is it true that UDF cannot be use in JDBC SQL statements?

in case I'm wrong, here is the full error:
(the JSP pages are correct, showing all other results correctly
except this one)

Included servlet error: 500

Location: /eshop/jsp/TableGeneral.jsp

Error Location: /eshop/jsp/TableShow.jsp

Internal Servlet Error:

javax.servlet.ServletException:
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:459)
at jsp._0002fjsp_0002fTableShow_0002ejspTableShow_jsp_14._jspService(_0002fjsp_0002fTableShow_0002ejspTableShow_jsp_14.java:198)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:177)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:318)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:391)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:404)
at org.apache.tomcat.core.Handler.service(Handler.java:286)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.facade.RequestDispatcherImpl.include(RequestDispatcherImpl.java:345)
at org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java:414)
at jsp._0002fjsp_0002fTableGeneral_0002ejspTableGeneral_jsp_0._jspService(_0002fjsp_0002fTableGeneral_0002ejspTableGeneral_jsp_0.java:63)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:177)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:318)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:391)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:404)
at org.apache.tomcat.core.Handler.service(Handler.java:286)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.facade.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:194)
at Reporting.SalesDistribution.showCategorySalesDistributionPage(SalesDistribution.java:118)
at Reporting.SalesDistribution.showSalesDistributionPage(SalesDistribution.java:70)
at Reporting.SalesDistribution.doGet(SalesDistribution.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:404)
at org.apache.tomcat.core.Handler.service(Handler.java:286)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:797)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
at org.apache.tomcat.service.connector.Ajp13ConnectionHandler.processConnection(Ajp13ConnectionHandler.java:160)
at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:498)
at java.lang.Thread.run(Thread.java:475)

Root cause:

java.lang.NullPointerException:
at jsp._0002fjsp_0002fTableShow_0002ejspTableShow_jsp_14._jspService(_0002fjsp_0002fTableShow_0002ejspTableShow_jsp_14.java:144)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:177)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:318)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:391)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:404)
at org.apache.tomcat.core.Handler.service(Handler.java:286)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.facade.RequestDispatcherImpl.include(RequestDispatcherImpl.java:345)
at org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java:414)
at jsp._0002fjsp_0002fTableGeneral_0002ejspTableGeneral_jsp_0._jspService(_0002fjsp_0002fTableGeneral_0002ejspTableGeneral_jsp_0.java:63)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:177)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:318)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:391)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:404)
at org.apache.tomcat.core.Handler.service(Handler.java:286)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.facade.RequestDispatcherImpl.forward(RequestDispatcherImpl.java:194)
at Reporting.SalesDistribution.showCategorySalesDistributionPage(SalesDistribution.java:118)
at Reporting.SalesDistribution.showSalesDistributionPage(SalesDistribution.java:70)
at Reporting.SalesDistribution.doGet(SalesDistribution.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:404)
at org.apache.tomcat.core.Handler.service(Handler.java:286)
at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:797)
at org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
at org.apache.tomcat.service.connector.Ajp13ConnectionHandler.processConnection(Ajp13ConnectionHandler.java:160)
at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:498)
at
java.lang.Thread.run(Thread.java:475)

[* Tong *]

* Tong * <sun_...@users.sourceforge.net> writes:

> Is it true that UDF cannot be use in JDBC SQL statements?

After some extensive reading, I know now that UDF can be use in JDBC
SQL, and has proven that my isBeginWith is working fine.

Further testing located the casual of the error, but I can't
understand it. The following SQL causes an error:

SQL0119N An expression starting with "NAME" specified in a SELECT
clause, HAVING clause, or ORDER BY clause i s not specified in the
GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER
BY clause with a column function and no GROUP BY clause is
specified. SQLSTATE=42803

But it works fine when specified in CLI!?

select cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||
cast(categories.name as varchar(30)) as category,
sum(quantity) as Quantity,
decimal(sum(quantity * one_order.price * (1 - discount)),10,2) as Revenue
from one_order, orders, products, categories
where orders.id = one_order.orders_id and
product_id = products.id and
isBeginWith(PrdTag,CatTag) = 1 and
date_ordered between '2001-7-1' and '2001-7-11'
group by cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||
cast(categories.name as varchar(20)), CatTag
order by CatTag
;

any idea where could be wrong? thanks

[Sailesh Krishnamurthy]

* Tong * <sun_...@users.sourceforge.net> writes:

> Hi,
>
> Is it true that UDF cannot be use in JDBC SQL statements?

There is no such restriction to my knowledge.

> The very statement runs fine in DB2 but gives
> javax.servlet.ServletException when called in JDBC. This seems weird
> though, doesn't JDBC just pass whatever sql statement back to DB2
> for execution rather than execute it on its own?

Well you need to give us the error that DB2 returns, and not the
Servlet error.

Look at your db2diag.log (with DIAGLEVEL set to 4) and give us that as
well.

--
Toodle-pip
Sailesh Krishnamurthy
IBM Almaden Research Center
Ph: (408) 927-1913

[Serge Rielau]

Hi Tong,

This error is returned if you e.g. write the following:

SELECT a, max(b) FROM T

This query should only return one row, but teh DBMS doesn't knwo which a to
return.

You can change it to
SELECT a, max(b) FROM T GROUP BY a
Which will give you one max(b) per distinct a.

Or you can use
SELECT a, max(b) over() FROM T
which will return all the a's and tag max(b) to all of them.

If you post the failing SQL (it has nothing to do with JDBC) it should be easy
to find the problem.

Cheers
Serge

[* Tong *]

Serge Rielau <sri...@ca.ibm.com> writes:

> If you post the failing SQL (it has nothing to do with JDBC) it
> should be easy to find the problem.

thanks Serge

The SQL I posted up the thread is *the* actual SQL that I tested,
running from both CLP and java. The former succeeded and the latter
failed, for the same SQL statement.

select cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||
cast(categories.name as varchar(30)) as category,
sum(quantity) as Quantity,
decimal(sum(quantity * one_order.price * (1 - discount)),10,2) as Revenue
from one_order, orders, products, categories
where orders.id = one_order.orders_id and
product_id = products.id and
isBeginWith(PrdTag,CatTag) = 1 and
date_ordered between '2001-7-1' and '2001-7-11'
group by cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||
cast(categories.name as varchar(20)), CatTag
order by CatTag
;

Any idea why is the difference?

[Serge Rielau]

The forest for the trees.. I got lost in all the apache stuff in the post
above...
Anyways:

cast(categories.name as varchar(30)) as category,

vs.
cast(categories.name as varchar(20))

looks odd to me. 20 or 30 as length? These two expressions are NOT the same.

Cheers
Serge

[* Tong *]

Serge Rielau <sri...@ca.ibm.com> writes:

> The forest for the trees.. I got lost in all the apache stuff in the post
> above...

Sorry about this. Previously I have no clue what's wrong, so I threw
out a lot of noisy info. Now please focus on why the same statement
have different results.

> Anyways:
> cast(categories.name as varchar(30)) as category,
> vs.
> cast(categories.name as varchar(20))
>
> looks odd to me. 20 or 30 as length? These two expressions are NOT the same.

Oh, that's because my categories.name is long, so I truncated
it. But the next time I careful examined the result from DB2 CLP, I
noticed a waring on the meaning data being truncated, so I increased
the truncate-length. I thought it might be the problem, but found
the error is the same after applied the above change to Java.

I can send my DB data over, together with my JSP code, if anybody
think it is necessary... thanks

[* Tong *]

* Tong * <sun_...@users.sourceforge.net> writes:

> > cast(categories.name as varchar(30)) as category,
> > vs.
> > cast(categories.name as varchar(20))
> >
> > looks odd to me. 20 or 30 as length? These two expressions are NOT the same.

> noticed a waring on the meaning data being truncated, so I increased
> the truncate-length. I thought it might be the problem, but found

Oh, I know what you mean now, yes, there is an inconsistent in the
statement, and *YES*, fixing it does fix the problem. hooray!

Now is high time that I ask the question that I want to ask a
long time ago: is there an easy way to specify the group by clause?

My previous statement is a good example:

select cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||

cast(categories.name as varchar(30)) as category,

sum(quantity) as Quantity,
decimal(sum(quantity * one_order.price * (1 - discount)),10,2) as Revenue
from one_order, orders, products, categories
where orders.id = one_order.orders_id and
product_id = products.id and
isBeginWith(PrdTag,CatTag) = 1 and
date_ordered between '2001-7-1' and '2001-7-11'
group by cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||
cast(categories.name as varchar(20)), CatTag
order by CatTag
;

I have to repeat myself for the first long expression ('category'),
and it is so easy to forget to update the other one. Besides, I know
that

group by cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||
cast(categories.name as varchar(20))

is actually the same as group by CatTag in my data, Is there a
simple way to do it? I saw the over() you used in another thread
might solve the problem, Can you explain the whole situation?
Thanks!

Another related issue. In MS Access, if I don't want to include a
'select' field in the group by clause, I can either use
first(strFld) or last(strFld) to get the first/last value for the
group (of other fields). Normally the field would be the same
through out the group, so it doesn't matter to use first or last,
and it can also be beneficial if they do change in the middle...
Any easy way I can do this in DB2?

[pm3...@attglobal.net]

CatTag seems to be the problem.

----

select cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||
cast(categories.name as varchar(30)) as category,
sum(quantity) as Quantity,
decimal(sum(quantity * one_order.price * (1 - discount)),10,2) as
Revenue

from one_order, orders, products, categories
where orders.id = one_order.orders_id and
product_id = products.id and
isBeginWith(PrdTag,CatTag) = 1 and
date_ordered between '2001-7-1' and '2001-7-11'

group by cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||
cast(categories.name as varchar(20)), CatTag

<<<<-------- i think it's here, no reference in select
order by CatTag
;

PM

[Serge Rielau]

Hi,

You can't use the correlation name in the group by clause as you can in order by.
Howvere you can push all the expressions down into a subquery (or 'inline view' for
our Oracle friends). Semantically the order by happens after the query is computed
(and all columsn visible). The group by is happens semantically before the select
list is built.

For DB2 to deduce that one and only CatTag exists for every category won't be
possible before IBM buys Maple I suppose ;-)

select category,
sum(auntity) as quantity,
decimal(sum(quantity * price * (1 - discount)),10,2) as Revenue
from
(select cast(repeat('&nbsp;', length(CatTag)) as varchar(90)) ||

cast(categories.name as varchar(30)) as category,

Quantity, one_order.price, discount, CatTag

from one_order, orders, products, categories
where orders.id = one_order.orders_id and
product_id = products.id and
isBeginWith(PrdTag,CatTag) = 1 and

date_ordered between '2001-7-1' and '2001-7-11') as t
group by category, CatTag
order by CatTag
;

DB2 will end up with the same access plan

I'm still not 100% clear on what you mean with first and last.
Using OLAP you can tag on information about the set without grouping the rows.
E.g. you could write
select max(discount) over(partition by category), discount, product, category from
...
to compare each row with the maximum discount for its category.
You can use rownumber() over (...), or rank/dense_rank to number rows and then put a
predicate in a query above to just take the first (or last using a reverse order).

I suppose it would be great if you can post the goal of the feature and we can see
what SQL feature achieves that goal.

Cheers
Serge

[* Tong *]

Serge Rielau <sri...@ca.ibm.com> writes:

> I'm still not 100% clear on what you mean with first and last.

first and last is to the string field as min and max to the numeric
fields.

> I suppose it would be great if you can post the goal of the
> feature and we can see what SQL feature achieves that goal.

Yeah, me too want DB2 to be better, stronger...
Let me explain with an simple example -- a table of peoples' posting:

postdb(Name, address, lines )

Normally people won't tweak their names, so first and last come in
handy when I want to know each person's contribution:

select first(name), address, sum(lines) from postdb group by address

This is normally the same as

select name, address, sum(lines) from postdb group by address, name

But, if some one tweak his name field (eg, to FAQ posting, Weekly
static, etc), Then the first version shows its real power.

,-----

| if I don't want to include a 'select' field in the group by clause,
| I can either use first(strFld) or last(strFld) to get the first/last
| value for the group (of other fields). Normally the field would be
| the same through out the group, so it doesn't matter to use first or
| last, and it can also be beneficial if they do change in the
| middle...

`-----

Hope I've made myself clear this time. thanks.

[* Tong *]

Serge Rielau <sri...@ca.ibm.com> writes:

> For DB2 to deduce that one and only CatTag exists for every
> category won't be possible before IBM buys Maple I suppose ;-)

:-) I was hoping that there is a way for me to inform DB2 about it,
like using first/last...

[David Sharpe]

FYI, MIN and MAX work on string too.

[Serge Rielau]

.. which then makes:

select min(name), address, sum(lines) from postdb group by address;

Cheers
Serge

[* Tong *]

* Tong * <sun_...@users.sourceforge.net> writes:

> Serge Rielau <sri...@ca.ibm.com> writes:
>
> > For DB2 to deduce that one and only CatTag exists for every
> > category won't be possible before IBM buys Maple I suppose ;-)
>
> :-) I was hoping that there is a way for me to inform DB2 about it,

Thanks, David and Serge for the tip on min/max.

Now I think, the "easy" answer for my previous problem might be:

Select very_complicated_expression_on(f1,f2,f3)
group by f1,f2,f3

is this acceptable in DB2? If so, at least I don't need to repeat
the very_complicated_expression again in the group by clause.

[Knut Stolze]

On 10 Jul 2001 19:52:10 -0300, * Tong * wrote:
> * Tong * <sun_...@users.sourceforge.net> writes:
>
> > Serge Rielau <sri...@ca.ibm.com> writes:
> >
> > > For DB2 to deduce that one and only CatTag exists for every
> > > category won't be possible before IBM buys Maple I suppose ;-)
> >
> > :-) I was hoping that there is a way for me to inform DB2 about it,
>
> Thanks, David and Serge for the tip on min/max.
>
> Now I think, the "easy" answer for my previous problem might be:
>
> Select very_complicated_expression_on(f1,f2,f3)
> group by f1,f2,f3
>
> is this acceptable in DB2? If so, at least I don't need to repeat
> the very_complicated_expression again in the group by clause.

You might get a different result than you expect. Grouping by f1, f2, f3 is
not the same as grouping by an expression using f1, f2, f3. For instance:

SELECT f1/2
FROM tab
GROUP BY f1

returns something different than

SELECT f1/2
FROM tab
GROUP BY f1/2

--
Knut Stolze
DB2 UDB Spatial Extender
IBM Silicon Valley Lab