Re: [castor-dev] Complex query problem

[Alexey A. Efimov]

Hello,
Did you try use CALL SQL?


        import java.sql.*;
        import org.exolab.castor.jdo.engine.DatabaseImpl;
        
        Database        db;

        db.begin();


           // Field must be ordered such as in mapping file
           OQLQuery query = db.getOQLQuery("CALL SQL SELECT id, field1,
field2 ... FROM foo_table WHERE ... As foo");
            

        db.close();

Quted from Castor site:
http://www.castor.org/jdo-faq.html#OQL
---
Can I write a pass-thru OQL?

Yes. Just put "CALL SQL" in front of your SQL statement.

For example, 

    OQLQuery oql = castorDb.getOQLQuery(
        "CALL SQL SELECT id, name, date "+
        "FROM user WHERE upper(name) like $1 AS myapp.Product");
        
Be remember that the order of the fields listed must match what is
defined in the mapping file.
---

Thanks

-----Original Message-----
From: Peter Jeszenszky [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 09, 2003 6:47 PM
To: [EMAIL PROTECTED]
Subject: [castor-dev] Complex query problem


Hello,

I must execute a complex query that currently can not be formulated in
the OQL subset that Castor supports. To achieve my goal I do the
following:

        import java.sql.*;
        import org.exolab.castor.jdo.engine.DatabaseImpl;
        
        Database        db;

        db.begin();

        // Query the underlying JDBC connection
        Connection conn =  (Connection) ((DatabaseImpl)
db).getConnection();

        Statement       st = conn.createStatement();

        // Execute the SQL query that returns the IDs of the objects
that satisfy the search condition
        ResultSet       rs = st.executeQuery("SELECT id FROM foo WHERE
...");

        Vector  result = new Vector();  // This vector holds the result
objects

        while (rs.next()) {
                // Request the ID of the next object
                int     id = rs.getInt(1);

                // Load the object with that ID
                Foo     foo = (Foo) db.load(Foo.class, new Integer(id));
                result.addElement(foo);
        }

        db.close();

I would like to know whether the solution above is safe and gives the
correct results. Yours sincerely,

Peter

----------------------------------------------------------- 
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
        unsubscribe castor-dev

----------------------------------------------------------- 
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
        unsubscribe castor-dev