You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Pritam Paul <pr...@lycos.com> on 2004/11/12 15:27:43 UTC
Unable to create Criteria object or executeQuery
Hi,
I have the following sql statement whose equivalent i need to generated using Criteria settings to fetch the results:
[THIS SQL Query works well in SQL Server and Query explorer - to fetch 8 records]
-----------------------------------------------------------------
SELECT PME_EmpInfo.EMPLOYEEID,
PME_EmpInfo.FIRSTNAME,
PME_EmpInfo.LASTNAME,
PME_EmpInfo.SUPERVISORID,
PME_EmpInfo.DEPTNO,
PME_EmpInfo.DEPTNAME,
(SELECT (empinner.FirstName + ' ' + empinner.LastName)
FROM PME_EmpInfo empinner
WHERE PME_EmpInfo.SUPERVISORID = empinner.EmployeeID) AS SupervisorName,
(SELECT phaseinner.PhaseName
FROM PME_Phase phaseinner
WHERE PME_Main.CURRENTPHASEID = phaseinner.PhaseID) AS PhaseName
FROM PME_EmpInfo INNER JOIN
PME_Main ON PME_EmpInfo.EMPLOYEEID = PME_Main.EMPLOYEEID INNER JOIN
PME_Phase ON PME_Main.CURRENTPHASEID = PME_Phase.PHASEID
WHERE (PME_Main.CURRENTSTATUSID='7') AND (PME_EmpInfo.DEPTNO='11080')
-----------------------------------------------------------------
I tried using Criteria class but since i dont know Torque well i dont know how
this can be achieved using the Criteria class. I tried creating a Query object
for this SQL statement and its (.toString()) method outputs this exact SQL query.
But how do i execute this query to fetch the results.
When i try using the BasePeer.executeQuery(sqlString) method, it complains "There was no DataSourceFactory configured for the connection".
Also how would i get the resultset object casted in such a way that i can read the mentioned columns.
In summary - how to execute a raw sql statement (which was generated by some other
app layer) using torque and get the resultset?
Any help is greatly appreciated.
regards,
Paul
--
_______________________________________________
Find what you are looking for with the Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Unable to create Criteria object or executeQuery
Posted by Guy Galil <gu...@guardium.com>.
The error message you get indicates a configuration problem - check your
Torque.properties.
After you are able to connect you can use your code as listed and get a
List of Reccord objects with your results - Torque cannot map multiple
tables to specific objects.
Another way would be to use Torque just to get the connection and then
process the query using plain jdbc:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
....
Connection con = null;
Statement st = null;
ResultSet rs = null;
String query = "Select a from b where c = 'd' ";
try
{
con = Torque.getConnection("default_database");
st = con.createStatement();
rs = st.executeQuery(query);
while (rs.next())
{
// process results
}
}
finally
{
if (rs != null)
rs.close();
if(st != null)
st.close();
Torque.closeConnection(con);
}
On Fri, 2004-11-12 at 09:27, Pritam Paul wrote:
> Hi,
>
> I have the following sql statement whose equivalent i need to generated using Criteria settings to fetch the results:
>
> [THIS SQL Query works well in SQL Server and Query explorer - to fetch 8 records]
> -----------------------------------------------------------------
> SELECT PME_EmpInfo.EMPLOYEEID,
> PME_EmpInfo.FIRSTNAME,
> PME_EmpInfo.LASTNAME,
> PME_EmpInfo.SUPERVISORID,
> PME_EmpInfo.DEPTNO,
> PME_EmpInfo.DEPTNAME,
> (SELECT (empinner.FirstName + ' ' + empinner.LastName)
> FROM PME_EmpInfo empinner
> WHERE PME_EmpInfo.SUPERVISORID = empinner.EmployeeID) AS SupervisorName,
> (SELECT phaseinner.PhaseName
> FROM PME_Phase phaseinner
> WHERE PME_Main.CURRENTPHASEID = phaseinner.PhaseID) AS PhaseName
>
> FROM PME_EmpInfo INNER JOIN
> PME_Main ON PME_EmpInfo.EMPLOYEEID = PME_Main.EMPLOYEEID INNER JOIN
> PME_Phase ON PME_Main.CURRENTPHASEID = PME_Phase.PHASEID
> WHERE (PME_Main.CURRENTSTATUSID='7') AND (PME_EmpInfo.DEPTNO='11080')
>
> -----------------------------------------------------------------
>
> I tried using Criteria class but since i dont know Torque well i dont know how
> this can be achieved using the Criteria class. I tried creating a Query object
> for this SQL statement and its (.toString()) method outputs this exact SQL query.
>
> But how do i execute this query to fetch the results.
>
> When i try using the BasePeer.executeQuery(sqlString) method, it complains "There was no DataSourceFactory configured for the connection".
>
> Also how would i get the resultset object casted in such a way that i can read the mentioned columns.
>
> In summary - how to execute a raw sql statement (which was generated by some other
> app layer) using torque and get the resultset?
>
> Any help is greatly appreciated.
>
> regards,
> Paul
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Unable to create Criteria object or executeQuery
Posted by Richard Bayet <ba...@enseirb.fr>.
Richard Bayet wrote:
> Thomas Fischer wrote:
>
>> Hi,
>>
>> I do remember there was a discussion about this in the user mailing list
>> some time ago. The problem with your statement is that it uses
>> subselects,
>> which is not (yet ???) supported in torque (contributions are
>> welcome). One
>> solution was to manually create a view in the database [...]
>>
>> Thomas
>>
>
> Well, the kind of subselect he wants to perform is not yet supported,
> but "classical" subselects are supported, aren't they ?
> (Like "select A, B from T1, T2
> where A in (select MAX(C) from T3 ...)
> and ...").
>
> But I might be wrong, I know I had to extend the Criteria class to code
> a "not in" operator. I got the idea from either the dev or user
> mailing-list.
>
Here it is.
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=torque-user@db.apache.org&msgId=1739554
(If direct link fails, head to
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=torque-user@db.apache.org&msgId=1739554
and search messages bodies for "SimpleCriteria").
You were right, subqueries aren't supported but they can be easily added.
Regards.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Unable to create Criteria object or executeQuery
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Richard Bayet wrote:
> Richard Bayet wrote:
>
>> But I might be wrong, I know I had to extend the Criteria class to
>> code a "not in" operator. I got the idea from either the dev or user
>> mailing-list.
I'm a bit puzzled, Richard: there is a Criteria.addNotIn(...) method.
> Here it is.
> http://nagoya.apache.org/eyebrowse/ReadMsg?listName=torque-user@db.apache.org&msgId=1739554
Thanks for pointing this out.
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Unable to create Criteria object or executeQuery
Posted by Richard Bayet <rb...@club-internet.fr>.
Richard Bayet wrote:
> Thomas Fischer wrote:
>
>> Hi,
>>
>> I do remember there was a discussion about this in the user mailing list
>> some time ago. The problem with your statement is that it uses
>> subselects,
>> which is not (yet ???) supported in torque (contributions are
>> welcome). One
>> solution was to manually create a view in the database [...]
>>
>> Thomas
>>
>
> Well, the kind of subselect he wants to perform is not yet supported,
> but "classical" subselects are supported, aren't they ?
> (Like "select A, B from T1, T2
> where A in (select MAX(C) from T3 ...)
> and ...").
>
> But I might be wrong, I know I had to extend the Criteria class to code
> a "not in" operator. I got the idea from either the dev or user
> mailing-list.
>
Here it is.
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=torque-user@db.apache.org&msgId=1739554
(If direct link fails, head to
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=torque-user@db.apache.org&msgId=1739554
and search messages bodies for "SimpleCriteria").
You were right, subqueries aren't supported but they can be easily added.
Regards.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Unable to create Criteria object or executeQuery
Posted by Richard Bayet <ba...@enseirb.fr>.
Thomas Fischer wrote:
> Hi,
>
> I do remember there was a discussion about this in the user mailing list
> some time ago. The problem with your statement is that it uses subselects,
> which is not (yet ???) supported in torque (contributions are welcome). One
> solution was to manually create a view in the database [...]
>
> Thomas
>
Well, the kind of subselect he wants to perform is not yet supported,
but "classical" subselects are supported, aren't they ?
(Like "select A, B from T1, T2
where A in (select MAX(C) from T3 ...)
and ...").
But I might be wrong, I know I had to extend the Criteria class to code
a "not in" operator. I got the idea from either the dev or user
mailing-list.
Regards
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Unable to create Criteria object or executeQuery
Posted by Thomas Fischer <Fi...@seitenbau.net>.
Hi,
I do remember there was a discussion about this in the user mailing list
some time ago. The problem with your statement is that it uses subselects,
which is not (yet ???) supported in torque (contributions are welcome). One
solution was to manually create a view in the database, declare it in the
project-schema.xml, run the generator to create the sql script which
creates your tables, edit it manually to remove the sql statements which
would interfere with your manually created view, and use the edited sql
sript to create the tables.
This may not be a solution if your sql is very dynamic. I have never used
BasePeer.executeQuery(sqlString) myself, but I suspect that some
preprocessing of the criteria object is necessary. However, your error
sounds if there is some mistake in your Torque configuration. Are you able
to do a simple table read using Torque ?
Thomas
"Pritam Paul" <pr...@lycos.com> schrieb am 12.11.2004 15:27:43:
> Hi,
>
> I have the following sql statement whose equivalent i need to
> generated using Criteria settings to fetch the results:
>
> [THIS SQL Query works well in SQL Server and Query explorer - to
> fetch 8 records]
> -----------------------------------------------------------------
> SELECT PME_EmpInfo.EMPLOYEEID,
> PME_EmpInfo.FIRSTNAME,
> PME_EmpInfo.LASTNAME,
> PME_EmpInfo.SUPERVISORID,
> PME_EmpInfo.DEPTNO,
> PME_EmpInfo.DEPTNAME,
> (SELECT (empinner.FirstName + ' ' + empinner.LastName)
> FROM PME_EmpInfo empinner
> WHERE PME_EmpInfo.SUPERVISORID = empinner.EmployeeID)
> AS SupervisorName,
> (SELECT phaseinner.PhaseName
> FROM PME_Phase phaseinner
> WHERE PME_Main.CURRENTPHASEID = phaseinner.PhaseID)
ASPhaseName
>
> FROM PME_EmpInfo INNER JOIN
> PME_Main ON PME_EmpInfo.EMPLOYEEID = PME_Main.EMPLOYEEID INNER JOIN
> PME_Phase ON PME_Main.CURRENTPHASEID = PME_Phase.PHASEID
> WHERE (PME_Main.CURRENTSTATUSID='7') AND
(PME_EmpInfo.DEPTNO='11080')
>
> -----------------------------------------------------------------
>
> I tried using Criteria class but since i dont know Torque well i dont
know how
> this can be achieved using the Criteria class. I tried creating a Query
object
> for this SQL statement and its (.toString()) method outputs this
> exact SQL query.
>
> But how do i execute this query to fetch the results.
>
> When i try using the BasePeer.executeQuery(sqlString) method, it
> complains "There was no DataSourceFactory configured for the connection".
>
> Also how would i get the resultset object casted in such a way that
> i can read the mentioned columns.
>
> In summary - how to execute a raw sql statement (which was generated
> by some other
> app layer) using torque and get the resultset?
>
> Any help is greatly appreciated.
>
> regards,
> Paul
>
>
>
> --
> _______________________________________________
> Find what you are looking for with the Lycos Yellow Pages
> http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.
> com/default.asp?SRC=lycos10
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org