Re: Count a Subquery with group clause

[Vasily Ivanov]

Sorry again, :)

Your query will run, but it will not make any sence. It is the same as write:
SELECT count(*) FROM
FROM people

Cheers,
 Vasily

On 8/9/06, Vasily Ivanov <[EMAIL PROTECTED]> wrote:

Hi Bin,

Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM  people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { "lastname, age, city, count(*)" });
subQuery.addGroupBy(new String[] { "lastname, age, city" });
broker.getReportQueryIteratorByQuery(query);
=

Cheers,
  Vasily

On 8/9/06, Bin Gao <[EMAIL PROTECTED]> wrote:
> Hello All,
>
> How can I generate the following SQL statement using
> OJB?
>
> SELECT count(*) FROM (
> SELECT   lastname, age, city
> FROM people
> GROUP BY lastname, age, city
> );
>
> Thanks for your help,
>
> Bin
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Count a Subquery with group clause

[Vasily Ivanov]

Sorry,

Read this:
subQuery.addGroupBy(new String[] { "lastname, age, city" });
as this:
query.addGroupBy(new String[] { "lastname, age, city" });

Cheers,
 Vasily

On 8/9/06, Vasily Ivanov <[EMAIL PROTECTED]> wrote:

Hi Bin,

Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM  people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { "lastname, age, city, count(*)" });
subQuery.addGroupBy(new String[] { "lastname, age, city" });
broker.getReportQueryIteratorByQuery(query);
=

Cheers,
  Vasily

On 8/9/06, Bin Gao <[EMAIL PROTECTED]> wrote:
> Hello All,
>
> How can I generate the following SQL statement using
> OJB?
>
> SELECT count(*) FROM (
> SELECT   lastname, age, city
> FROM people
> GROUP BY lastname, age, city
> );
>
> Thanks for your help,
>
> Bin
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Count a Subquery with group clause

[Vasily Ivanov]

Hi Bin,

Your query will not run because you can not put in GROUP BY without
count(*) in SELECT clause of subquery. If you want to run this:
=
SELECT lastname, age, city, count(*)
FROM  people
GROUP BY lastname, age, city
=
that you shoud do something like this in OJB:
=
ReportQueryByCriteria query =
QueryFactory.newReportQuery(People.class, new Criteria());
query.setAttributes(new String[] { "lastname, age, city, count(*)" });
subQuery.addGroupBy(new String[] { "lastname, age, city" });
broker.getReportQueryIteratorByQuery(query);
=

Cheers,
 Vasily

On 8/9/06, Bin Gao <[EMAIL PROTECTED]> wrote:

Hello All,

How can I generate the following SQL statement using
OJB?

SELECT count(*) FROM (
SELECT   lastname, age, city
FROM people
GROUP BY lastname, age, city
);

Thanks for your help,

Bin

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Count a Subquery with group clause

[Bin Gao]

Hello All,

How can I generate the following SQL statement using
OJB?

SELECT count(*) FROM (
SELECT   lastname, age, city
FROM people
GROUP BY lastname, age, city
);

Thanks for your help,

Bin

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Methods of calling stored procedures in OJB

[Ron Gallagher]

Jim --

OJB supports the use of stored procedures to handle all persistence operations 
(insert, update and delete).  The stored procedures that are configured in the 
repository are used in place of standard insert, update and delete SQL 
commands.  You do not need to use any sort of database triggers in order to 
take advantage of OJB's use of stored procedures for persistence operations.

The example that you refer in the documentation to was culled from an 
application that I was one of the lead architects on.  In the environment that 
that application was to be deployed in, the DBAs insisted that all persistence 
operations be performed by stored procedures.  This was accomplished by 
utilizing OJB's support for stored procedures.  In that same environment, the 
business community insisted that we track who created and performed the last 
update on each record.  That requirement was accomplished via the insert/update 
triggers that are referenced in the sample.  The final feature was the 
requirement that any updates to the four audit columns be reflected in the java 
objects that were managed by the application.  This requirement was 
accomplished in two steps: (1) the stored procedures that OJB utilized returned 
these values to the caller (i.e. OJB) via the "RETURNING" clause and (2) the 
configuration of the stored procedure in the repository file included the attri
 bute 'return="true"' attribute on any arguments that were returned to OJB.

I hope this helps you out.

Let me know if I can clarify things any further.

Ron Gallagher

> 
> From: "Wright, Jim \(NIH/CIT\) [C]" <[EMAIL PROTECTED]>
> Date: 2006/08/08 Tue PM 04:57:32 EDT
> To: 
> Subject: Methods of calling stored procedures in OJB
> 
> Does one have to use database triggers in order to run stored procedures
> in OJB?  The Apache documentation seems to imply that:
> 
>  
> 
> http://db.apache.org/ojb/docu/howtos/howto-work-with-stored-procedures.h
> tml#A+simple+example
> 
>  
> 
> I need a way to run them that does not involve triggers.
> 
> Jim Wright 
> 
>  
> 
> 
> 


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Methods of calling stored procedures in OJB

[Wright, Jim \(NIH/CIT\) [C]]

Does one have to use database triggers in order to run stored procedures
in OJB?  The Apache documentation seems to imply that:

 

http://db.apache.org/ojb/docu/howtos/howto-work-with-stored-procedures.h
tml#A+simple+example

 

I need a way to run them that does not involve triggers.

Jim Wright 

 

Calling a stored procedure through OJB

[Wright, Jim \(NIH/CIT\) [C]]

Though the OJB documentation is pretty good it does not show a direct
example of how you call a stored procedure in OJB Java code.  Could
someone show me?

 

Thanks very much,

Jim Wright [Contractor - SRA International]

Web Developer

Web Development Team

Custom Applications Branch (CAB)

Division of Enterprise and Custom Applications, CIT/NIH/DHHS

6707 Democracy Blvd, Suite 304

Bethesda, MD 20892

Office: 301.

Email: [EMAIL PROTECTED]

URL: http://webdev.cit.nih.gov/  

 

Re: mysql pool problem (again)

[Dennis Bekkering]

Martin,

Thanks for the help. As usual i was to fast with conclusions, my problems at
the time originated from a connection leak.

Thanks,
Dennis

2006/8/8, Martin Kalén <[EMAIL PROTECTED]>:


Dennis Bekkering skrev:
> A validation query doesn't help in the MySQL scenario: The driver will
> automatically reconnect and successfully execute the query, but the
> Connection still has a chance of remaining in an inconsistent state.
This
> won't happen on every reconnect, just on some.

You are perfectly right that the MySQL JDBC-driver's auto re-connect
feature conflicts with the Java connection pool settings,
creating this inconsistency. However, I don't agree that a validation
query does not help - you should be able to turn off the MySQL-specific
feature in the JDBC driver and let the OJB or DBPC connection pool
implementations handle re-connect via validation query
(and validation query only).

To turn off the MySQL re-connect feature:
1. make sure the JDBC URL does *not* include ?autoReconnect=true
(the default value is false = off)

2. make sure that the OJB connection pool settings does *not*
include the JDBC connection property:


To make sure that MySQL auto re-connect is off, you can add an
explicit "false" setting in the OJB connection-pool configuration
(but do make sure to clean the JDBC URL according to #1 above):

...
  
...

(Although the default setting has been false since MySQL JDBC v1.1)

Wouldn't this help in your scenario?

See also:
*) MySQL documentation:
Driver/Datasource Class Names, URL Syntax and Configuration Properties
for Connector/J
http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html


*) OJB documentation:
Repository File, connection-pool, Custom attributes, jdbc.*
http://db.apache.org/ojb/docu/guides/repository.html#jdbc.*

Regards,
  Martin


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]





--
mvg,
Dennis

Re: mysql pool problem (again)

[Martin Kalén]

Dennis Bekkering skrev:

A validation query doesn't help in the MySQL scenario: The driver will
automatically reconnect and successfully execute the query, but the
Connection still has a chance of remaining in an inconsistent state. This
won't happen on every reconnect, just on some.


You are perfectly right that the MySQL JDBC-driver's auto re-connect
feature conflicts with the Java connection pool settings,
creating this inconsistency. However, I don't agree that a validation
query does not help - you should be able to turn off the MySQL-specific
feature in the JDBC driver and let the OJB or DBPC connection pool
implementations handle re-connect via validation query
(and validation query only).

To turn off the MySQL re-connect feature:
1. make sure the JDBC URL does *not* include ?autoReconnect=true
(the default value is false = off)

2. make sure that the OJB connection pool settings does *not*
include the JDBC connection property:


To make sure that MySQL auto re-connect is off, you can add an
explicit "false" setting in the OJB connection-pool configuration
(but do make sure to clean the JDBC URL according to #1 above):

...
 
...

(Although the default setting has been false since MySQL JDBC v1.1)

Wouldn't this help in your scenario?

See also:
*) MySQL documentation:
Driver/Datasource Class Names, URL Syntax and Configuration Properties 
for Connector/J

http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

*) OJB documentation:
Repository File, connection-pool, Custom attributes, jdbc.*
http://db.apache.org/ojb/docu/guides/repository.html#jdbc.*

Regards,
 Martin


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]