You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Bob Harner <bo...@gmail.com> on 2012/09/04 02:55:34 UTC
Executing raw SQL
Hey everybody,
In a web app that uses Cayenne 3.0.1 with an embedded Derby database, I
need to perform a Derby-specific SQL query to do a database backup:
CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('D:/dbbackups/');
How would I do that using Cayenne APIs? I looked at SQLTemplate (
http://cayenne.apache.org/doc/sqltemplate-basics.html), but it wants an
entity class to use as a root, which doesn't seem applicable here.
Alternatively, how can I get access to the database connection so I can run
straight JDBC?
private void backUpDatabase(Connection conn) throws SQLException
{
String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
CallableStatement cs = conn.prepareCall(sqlstmt);
cs.setString(1,"D:/dbbackups/");
cs.execute();
cs.close();
}
Re: Executing raw SQL
Posted by Bob Harner <bo...@gmail.com>.
Thanks, Mike, I'll give it a try.
On Sep 3, 2012 10:18 PM, "Michael Gentry" <mg...@masslight.net> wrote:
> Hi Bob,
>
> The reason Cayenne wants the entity class is to determine the proper
> DataNode to use to route the query to the correct database channel.
> If you only have one DataNode (which is probably the case), then any
> entity class should be fine. I'd stick with the SQLTemplate for now
> and just give it any entity that resides in the database you are
> trying to back up. Be sure you use a non-selecting query, too:
>
> dataContext.performNonSelectingQuery(template);
>
> mrg
>
>
> On Mon, Sep 3, 2012 at 8:55 PM, Bob Harner <bo...@gmail.com> wrote:
> > Hey everybody,
> >
> > In a web app that uses Cayenne 3.0.1 with an embedded Derby database, I
> > need to perform a Derby-specific SQL query to do a database backup:
> >
> > CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('D:/dbbackups/');
> >
> > How would I do that using Cayenne APIs? I looked at SQLTemplate (
> > http://cayenne.apache.org/doc/sqltemplate-basics.html), but it wants an
> > entity class to use as a root, which doesn't seem applicable here.
> >
> > Alternatively, how can I get access to the database connection so I can
> run
> > straight JDBC?
> >
> > private void backUpDatabase(Connection conn) throws SQLException
> > {
> > String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
> > CallableStatement cs = conn.prepareCall(sqlstmt);
> > cs.setString(1,"D:/dbbackups/");
> > cs.execute();
> > cs.close();
> > }
>
Re: Executing raw SQL
Posted by Michael Gentry <mg...@masslight.net>.
Hi Bob,
The reason Cayenne wants the entity class is to determine the proper
DataNode to use to route the query to the correct database channel.
If you only have one DataNode (which is probably the case), then any
entity class should be fine. I'd stick with the SQLTemplate for now
and just give it any entity that resides in the database you are
trying to back up. Be sure you use a non-selecting query, too:
dataContext.performNonSelectingQuery(template);
mrg
On Mon, Sep 3, 2012 at 8:55 PM, Bob Harner <bo...@gmail.com> wrote:
> Hey everybody,
>
> In a web app that uses Cayenne 3.0.1 with an embedded Derby database, I
> need to perform a Derby-specific SQL query to do a database backup:
>
> CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('D:/dbbackups/');
>
> How would I do that using Cayenne APIs? I looked at SQLTemplate (
> http://cayenne.apache.org/doc/sqltemplate-basics.html), but it wants an
> entity class to use as a root, which doesn't seem applicable here.
>
> Alternatively, how can I get access to the database connection so I can run
> straight JDBC?
>
> private void backUpDatabase(Connection conn) throws SQLException
> {
> String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
> CallableStatement cs = conn.prepareCall(sqlstmt);
> cs.setString(1,"D:/dbbackups/");
> cs.execute();
> cs.close();
> }
Re: Executing raw SQL
Posted by Bob Harner <bo...@gmail.com>.
Thanks, guys, it worked perfectly:
DataContext dataContext = DataContext.createDataContext();
File backupDir = new File(System.getProperty("java.io.tmpdir"), "dbbackups");
String query = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE('D:/dbbackups/')";
SQLTemplate template = new SQLTemplate(FooBar.class, query);
dataContext.performNonSelectingQuery(template);
On Tue, Sep 4, 2012 at 7:05 AM, Bryan Lewis <jb...@gmail.com> wrote:
> In you really need to get a connection, this has worked for me:
>
> Connection connection =
> dataContext.getParentDataDomain().getNode("DataNode").getDataSource().getConnection()
>
>
> On Mon, Sep 3, 2012 at 8:55 PM, Bob Harner <bo...@gmail.com> wrote:
>
>
>> Alternatively, how can I get access to the database connection so I can run
>> straight JDBC?
>>
>> private void backUpDatabase(Connection conn) throws SQLException
>> {
>> String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
>> CallableStatement cs = conn.prepareCall(sqlstmt);
>> cs.setString(1,"D:/dbbackups/");
>> cs.execute();
>> cs.close();
>> }
>>
Re: Executing raw SQL
Posted by Andrus Adamchik <an...@objectstyle.org>.
+1 (for 3.2)... we already have an implicit "default node" concept when deciding what to use for DataMaps with no nodes. We might just expand on that in this case as well as add SQLTemplate default routing for SQLTemplates.
Andrus
On Sep 5, 2012, at 2:33 PM, Michael Gentry wrote:
> I was wondering if we might should go one step further. If there is
> only one DataNode, perhaps have the option of leaving the entity or
> DataNode name out of those calls? (Can throw an exception if there
> are multiple DataNodes registered.) That might be more intuitive for
> most (and the typical use-case).
>
> mrg
>
>
> On Wed, Sep 5, 2012 at 1:43 AM, Andrus Adamchik <an...@objectstyle.org> wrote:
>> A bit OT, since Bob has found a solution already... Cayenne 3.1 makes it a bit easier to obtain a DataSource, compared to the earlier versions:
>>
>> Connection connection = serverRuntime.getDataSource("DataNode").getConnection();
>>
>> Andrus
>>
>> On Sep 4, 2012, at 2:05 PM, Bryan Lewis wrote:
>>
>>> In you really need to get a connection, this has worked for me:
>>>
>>> Connection connection =
>>> dataContext.getParentDataDomain().getNode("DataNode").getDataSource().getConnection()
>>>
>>>
>>> On Mon, Sep 3, 2012 at 8:55 PM, Bob Harner <bo...@gmail.com> wrote:
>>>
>>>
>>>> Alternatively, how can I get access to the database connection so I can run
>>>> straight JDBC?
>>>>
>>>> private void backUpDatabase(Connection conn) throws SQLException
>>>> {
>>>> String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
>>>> CallableStatement cs = conn.prepareCall(sqlstmt);
>>>> cs.setString(1,"D:/dbbackups/");
>>>> cs.execute();
>>>> cs.close();
>>>> }
>>>>
>>
>
Re: Executing raw SQL
Posted by Bryan Lewis <jb...@gmail.com>.
Agreed. In the usual case when there's only one node, I've sometimes used
an iterator, like:
dataDomain.getDataNodes().iterator().next().getDataSource().
On Wed, Sep 5, 2012 at 7:33 AM, Michael Gentry <mg...@masslight.net>wrote:
> I was wondering if we might should go one step further. If there is
> only one DataNode, perhaps have the option of leaving the entity or
> DataNode name out of those calls? (Can throw an exception if there
> are multiple DataNodes registered.) That might be more intuitive for
> most (and the typical use-case).
>
> mrg
>
>
> On Wed, Sep 5, 2012 at 1:43 AM, Andrus Adamchik <an...@objectstyle.org>
> wrote:
> > A bit OT, since Bob has found a solution already... Cayenne 3.1 makes it
> a bit easier to obtain a DataSource, compared to the earlier versions:
> >
> > Connection connection =
> serverRuntime.getDataSource("DataNode").getConnection();
> >
> > Andrus
> >
> > On Sep 4, 2012, at 2:05 PM, Bryan Lewis wrote:
> >
> >> In you really need to get a connection, this has worked for me:
> >>
> >> Connection connection =
> >>
> dataContext.getParentDataDomain().getNode("DataNode").getDataSource().getConnection()
> >>
> >>
> >> On Mon, Sep 3, 2012 at 8:55 PM, Bob Harner <bo...@gmail.com> wrote:
> >>
> >>
> >>> Alternatively, how can I get access to the database connection so I
> can run
> >>> straight JDBC?
> >>>
> >>> private void backUpDatabase(Connection conn) throws SQLException
> >>> {
> >>> String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
> >>> CallableStatement cs = conn.prepareCall(sqlstmt);
> >>> cs.setString(1,"D:/dbbackups/");
> >>> cs.execute();
> >>> cs.close();
> >>> }
> >>>
> >
>
Re: Executing raw SQL
Posted by Michael Gentry <mg...@masslight.net>.
I was wondering if we might should go one step further. If there is
only one DataNode, perhaps have the option of leaving the entity or
DataNode name out of those calls? (Can throw an exception if there
are multiple DataNodes registered.) That might be more intuitive for
most (and the typical use-case).
mrg
On Wed, Sep 5, 2012 at 1:43 AM, Andrus Adamchik <an...@objectstyle.org> wrote:
> A bit OT, since Bob has found a solution already... Cayenne 3.1 makes it a bit easier to obtain a DataSource, compared to the earlier versions:
>
> Connection connection = serverRuntime.getDataSource("DataNode").getConnection();
>
> Andrus
>
> On Sep 4, 2012, at 2:05 PM, Bryan Lewis wrote:
>
>> In you really need to get a connection, this has worked for me:
>>
>> Connection connection =
>> dataContext.getParentDataDomain().getNode("DataNode").getDataSource().getConnection()
>>
>>
>> On Mon, Sep 3, 2012 at 8:55 PM, Bob Harner <bo...@gmail.com> wrote:
>>
>>
>>> Alternatively, how can I get access to the database connection so I can run
>>> straight JDBC?
>>>
>>> private void backUpDatabase(Connection conn) throws SQLException
>>> {
>>> String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
>>> CallableStatement cs = conn.prepareCall(sqlstmt);
>>> cs.setString(1,"D:/dbbackups/");
>>> cs.execute();
>>> cs.close();
>>> }
>>>
>
Re: Executing raw SQL
Posted by Andrus Adamchik <an...@objectstyle.org>.
A bit OT, since Bob has found a solution already... Cayenne 3.1 makes it a bit easier to obtain a DataSource, compared to the earlier versions:
Connection connection = serverRuntime.getDataSource("DataNode").getConnection();
Andrus
On Sep 4, 2012, at 2:05 PM, Bryan Lewis wrote:
> In you really need to get a connection, this has worked for me:
>
> Connection connection =
> dataContext.getParentDataDomain().getNode("DataNode").getDataSource().getConnection()
>
>
> On Mon, Sep 3, 2012 at 8:55 PM, Bob Harner <bo...@gmail.com> wrote:
>
>
>> Alternatively, how can I get access to the database connection so I can run
>> straight JDBC?
>>
>> private void backUpDatabase(Connection conn) throws SQLException
>> {
>> String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
>> CallableStatement cs = conn.prepareCall(sqlstmt);
>> cs.setString(1,"D:/dbbackups/");
>> cs.execute();
>> cs.close();
>> }
>>
Re: Executing raw SQL
Posted by Bryan Lewis <jb...@gmail.com>.
In you really need to get a connection, this has worked for me:
Connection connection =
dataContext.getParentDataDomain().getNode("DataNode").getDataSource().getConnection()
On Mon, Sep 3, 2012 at 8:55 PM, Bob Harner <bo...@gmail.com> wrote:
> Alternatively, how can I get access to the database connection so I can run
> straight JDBC?
>
> private void backUpDatabase(Connection conn) throws SQLException
> {
> String sqlstmt = "CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)";
> CallableStatement cs = conn.prepareCall(sqlstmt);
> cs.setString(1,"D:/dbbackups/");
> cs.execute();
> cs.close();
> }
>