Apache Database › Torque › Apache DB - Torque Users

get the SQL to be executed

Classic

List

Threaded

8 messages Options

Ivano Luberti

get the SQL to be executed

Hello all, is there a way in Torque to get the SQL that is sent to the DB.
I have to generate a txt file where I save queries basically INSERT
statements of long records.
I was thinking of create the Torque classes, populate the objects with
data that I get from a local instance of the DB and then, instead of
using a save command, get the SQL that would be executed and store it in
a file.

Alternatively I could use this:

http://openhms.sourceforge.net/sqlbuilder/

but has dependencies that requires GPL license and I would avoid in this
case that kind of license.

--
==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==================================================

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Thomas Fox-2

RE: get the SQL to be executed

Perhaps you can use the log output (with some tweaks to the log4j
configuration). However I'm not sure how and if Torque 3.3 logs the update
statements. But then again I've heard of drivers which at as a bridge
between the application and the real driver (e.g
http://www.squirrelsql.org/) and which can do the logging for you.

Torque 4 will not work because it uses prepared statements for updates,
this will not give you any sensible sql.

Thomas

> Hello all, is there a way in Torque to get the SQL that is sent to the
DB.

> I have to generate a txt file where I save queries basically INSERT
> statements of long records.
> I was thinking of create the Torque classes, populate the objects with
> data that I get from a local instance of the DB and then, instead of
> using a save command, get the SQL that would be executed and store it in
> a file.
>
> Alternatively I could use this:
>
> http://openhms.sourceforge.net/sqlbuilder/
>
> but has dependencies that requires GPL license and I would avoid in this
> case that kind of license.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Joe Carter

Re: get the SQL to be executed

You can use a logging driver such as p6spy to see your SQL statements.
http://sourceforge.net/projects/p6spy/

On 19 January 2012 08:25, Thomas Fox <[hidden email]> wrote:

> Perhaps you can use the log output (with some tweaks to the log4j
> configuration). However I'm not sure how and if Torque 3.3 logs the update
> statements. But then again I've heard of drivers which at as a bridge
> between the application and the real driver (e.g
> http://www.squirrelsql.org/) and which can do the logging for you.
>
> Torque 4 will not work because it uses prepared statements for updates,
> this will not give you any sensible sql.
>
> Thomas
>
>> Hello all, is there a way in Torque to get the SQL that is sent to the
> DB.
>> I have to generate a txt file where I save queries basically INSERT
>> statements of long records.
>> I was thinking of create the Torque classes, populate the objects with
>> data that I get from a local instance of the DB and then, instead of
>> using a save command, get the SQL that would be executed and store it in
>> a file.
>>
>> Alternatively I could use this:
>>
>> http://openhms.sourceforge.net/sqlbuilder/
>>
>> but has dependencies that requires GPL license and I would avoid in this
>> case that kind of license.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Greg Monroe

RE: get the SQL to be executed

If you are trying to generate an initial data scripts rather
than a "audit log", you might look at the dumpdata and datasql
tasks that Torque supplies. I have used these to create
initial data sets that applications need (e.g. initial user
entry, rights entry, etc.)

If you need to do this inside the code, you might look at
the apache ddlutils project db.apache.com/ddlutils. This has
some code callable functions to generate various SQL scripts.

If you don't need SQL, but are really looking for a way to
export/import data between Torque applications, look at
sf.net/projects/torque-addons. This has a framework for
easily creating XML exports of records. You may need to
write some record handler code to deal with foreign keys.

Greg

-----Original Message-----
From: Joe Carter [mailto:[hidden email]]
Sent: Thursday, January 19, 2012 3:32 AM
To: Apache Torque Users List
Subject: Re: get the SQL to be executed

You can use a logging driver such as p6spy to see your SQL statements.
http://sourceforge.net/projects/p6spy/

On 19 January 2012 08:25, Thomas Fox <[hidden email]> wrote:

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Ivano Luberti

Re: get the SQL to be executed

Thanks all for your responses.
Just to clarify, I need to generate sql commands inside my code to
generate a file that can be used by another software (not written by me)
to import data.

I would like not to execute commands but only write them (because this
is what I need).
If I cannot find alternatives, I can execute them and use one of the
logging facilities that have been suggested.

Il 19/01/2012 16:05, Greg Monroe ha scritto:

> If you are trying to generate an initial data scripts rather
> than a "audit log", you might look at the dumpdata and datasql
> tasks that Torque supplies. I have used these to create
> initial data sets that applications need (e.g. initial user
> entry, rights entry, etc.)
>
> If you need to do this inside the code, you might look at
> the apache ddlutils project db.apache.com/ddlutils. This has
> some code callable functions to generate various SQL scripts.
>
> If you don't need SQL, but are really looking for a way to
> export/import data between Torque applications, look at
> sf.net/projects/torque-addons. This has a framework for
> easily creating XML exports of records. You may need to
> write some record handler code to deal with foreign keys.
>
> Greg
>
> -----Original Message-----
> From: Joe Carter [mailto:[hidden email]]
> Sent: Thursday, January 19, 2012 3:32 AM
> To: Apache Torque Users List
> Subject: Re: get the SQL to be executed
>
> You can use a logging driver such as p6spy to see your SQL statements.
> http://sourceforge.net/projects/p6spy/
>
> On 19 January 2012 08:25, Thomas Fox <[hidden email]> wrote:
>> Perhaps you can use the log output (with some tweaks to the log4j
>> configuration). However I'm not sure how and if Torque 3.3 logs the update
>> statements. But then again I've heard of drivers which at as a bridge
>> between the application and the real driver (e.g
>> http://www.squirrelsql.org/) and which can do the logging for you.
>>
>> Torque 4 will not work because it uses prepared statements for updates,
>> this will not give you any sensible sql.
>>
>> Thomas
>>
>>> Hello all, is there a way in Torque to get the SQL that is sent to the
>> DB.
>>> I have to generate a txt file where I save queries basically INSERT
>>> statements of long records.
>>> I was thinking of create the Torque classes, populate the objects with
>>> data that I get from a local instance of the DB and then, instead of
>>> using a save command, get the SQL that would be executed and store it in
>>> a file.
>>>
>>> Alternatively I could use this:
>>>
>>> http://openhms.sourceforge.net/sqlbuilder/
>>>
>>> but has dependencies that requires GPL license and I would avoid in this
>>> case that kind of license.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted with
> it are confidential communication or may otherwise be privileged or
> confidential and are intended solely for the individual or entity
> to whom they are addressed. If you are not the intended recipient
> you may not rely on the contents of this email or any attachments,
> and we ask that you please not read, copy or retransmit this
> communication, but reply to the sender and destroy the email, its
> contents, and all copies thereof immediately. Any unauthorized
> dissemination, distribution or copying of this communication is
> strictly prohibited.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==================================================

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Re: get the SQL to be executed

On 19.01.12 18:38, Ivano Luberti wrote:
> Thanks all for your responses.
> Just to clarify, I need to generate sql commands inside my code to
> generate a file that can be used by another software (not written by me)
> to import data.
>
> I would like not to execute commands but only write them (because this
> is what I need).
> If I cannot find alternatives, I can execute them and use one of the
> logging facilities that have been suggested.

Well, inserts are sort of tricky because the actual SQL generation
happens deep inside Village. If you have control over your class loader,
you may modify a copy of the com.workingdogs.village.Record class and
put that first in the chain. Look for the saveWithInsert() method. This
is probably the closest you can get, IMHO.

Bye, Thomas.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Greg Monroe

RE: get the SQL to be executed

Or, if the info you're exporting maps to your Torque app's
tables, you could probably just add "toSQL()" methods to
the record stubs that creates the INSERT statements.

E.g. if you have a User record object, modify the User.java
Torque created with something like:

Public String toSQL() {
StringBuffer sql = new StringBuffer;

// Build SQL statement from current object contents

return sql.toString();
}

Then just do a normal Torque query to get the data you're
interested in and loop thru the results calling toSQL to
create your output file.

Note that once the "stub" classes are built, Torque will not
replace them if your regenerate the code. So the toSQL() code
is safe.

It would also be possible to create some generic Torque record to
SQL code by using the "map" information. This lets you determine
the column info in a table. So you could look up the table info
for a record object, then create the sql by iterating over the
column names and using the column type to determine the string
format to use.

But for a few table objects, it might be easier to just do the
manually created toSQL() methods.

-----Original Message-----
From: Thomas Vandahl [mailto:[hidden email]]
Sent: Thursday, January 19, 2012 12:51 PM
To: Apache Torque Users List
Subject: Re: get the SQL to be executed

On 19.01.12 18:38, Ivano Luberti wrote:
> Thanks all for your responses.
> Just to clarify, I need to generate sql commands inside my code to
> generate a file that can be used by another software (not written by me)
> to import data.
>
> I would like not to execute commands but only write them (because this
> is what I need).
> If I cannot find alternatives, I can execute them and use one of the
> logging facilities that have been suggested.

Well, inserts are sort of tricky because the actual SQL generation
happens deep inside Village. If you have control over your class loader,
you may modify a copy of the com.workingdogs.village.Record class and
put that first in the chain. Look for the saveWithInsert() method. This
is probably the closest you can get, IMHO.

Bye, Thomas.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Ivano Luberti

Re: get the SQL to be executed

Greg, the problem for me is exactly how to build.
I wanted to avoid buildig them by hand (ie: get the values of all the
field and combining them into a java String or StringBuffer).
But thank you anyway for the suggestion: if I end up for manually
building statements , now I have a good location where to place my mehtods.
Even though also peer classes can be used for that, couldn't be?

Il 19/01/2012 19:19, Greg Monroe ha scritto:

> Or, if the info you're exporting maps to your Torque app's
> tables, you could probably just add "toSQL()" methods to
> the record stubs that creates the INSERT statements.
>
> E.g. if you have a User record object, modify the User.java
> Torque created with something like:
>
> Public String toSQL() {
> StringBuffer sql = new StringBuffer;
>
> // Build SQL statement from current object contents
>
> return sql.toString();
> }
>
> Then just do a normal Torque query to get the data you're
> interested in and loop thru the results calling toSQL to
> create your output file.
>
> Note that once the "stub" classes are built, Torque will not
> replace them if your regenerate the code. So the toSQL() code
> is safe.
>
> It would also be possible to create some generic Torque record to
> SQL code by using the "map" information. This lets you determine
> the column info in a table. So you could look up the table info
> for a record object, then create the sql by iterating over the
> column names and using the column type to determine the string
> format to use.
>
> But for a few table objects, it might be easier to just do the
> manually created toSQL() methods.
>
> -----Original Message-----
> From: Thomas Vandahl [mailto:[hidden email]]
> Sent: Thursday, January 19, 2012 12:51 PM
> To: Apache Torque Users List
> Subject: Re: get the SQL to be executed
>
> On 19.01.12 18:38, Ivano Luberti wrote:
>> Thanks all for your responses.
>> Just to clarify, I need to generate sql commands inside my code to
>> generate a file that can be used by another software (not written by me)
>> to import data.
>>
>> I would like not to execute commands but only write them (because this
>> is what I need).
>> If I cannot find alternatives, I can execute them and use one of the
>> logging facilities that have been suggested.
> Well, inserts are sort of tricky because the actual SQL generation
> happens deep inside Village. If you have control over your class loader,
> you may modify a copy of the com.workingdogs.village.Record class and
> put that first in the chain. Look for the saveWithInsert() method. This
> is probably the closest you can get, IMHO.
>
> Bye, Thomas.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted with
> it are confidential communication or may otherwise be privileged or
> confidential and are intended solely for the individual or entity
> to whom they are addressed. If you are not the intended recipient
> you may not rely on the contents of this email or any attachments,
> and we ask that you please not read, copy or retransmit this
> communication, but reply to the sender and destroy the email, its
> contents, and all copies thereof immediately. Any unauthorized
> dissemination, distribution or copying of this communication is
> strictly prohibited.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>