You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@groovy.apache.org by GroovyBeginner <gr...@gmail.com> on 2016/07/26 06:46:11 UTC
Oracle Insert Data from one table to another table using Groovy
I have a requirement of inserting the data from table A into table B and am
using Oracle database. My condition is am going to insert only those records
which are not present in table B and am facing an issue of retrieving the
current row column values. Here is the code am trying.
import groovy.sql.Sql;
import java.sql.ResultSet;
import java.util.Properties;
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE","username",
"password", "oracle.jdbc.driver.OracleDriver")
sql.query("select ID, NAME from A where ID not in (select id from
B)") {
row-> while(row.next()) {
sql.executeInsert "INSERT INTO A (ID, Name) VALUES ("+$row.ID"+,+"
$row.Name+")"
}
}
and am facing issue `No such property: $row`. Kindly suggest me where am
going wrong and also is there any better approach of doing this in terms of
security and performance.
--
View this message in context: http://groovy.329449.n5.nabble.com/Oracle-Insert-Data-from-one-table-to-another-table-using-Groovy-tp5734285.html
Sent from the Groovy Dev mailing list archive at Nabble.com.
Re: Oracle Insert Data from one table to another table using Groovy
Posted by Jochen Theodorou <bl...@gmx.org>.
On 26.07.2016 08:46, GroovyBeginner wrote:
> I have a requirement of inserting the data from table A into table B and am
> using Oracle database. My condition is am going to insert only those records
> which are not present in table B and am facing an issue of retrieving the
> current row column values. Here is the code am trying.
>
> import groovy.sql.Sql;
> import java.sql.ResultSet;
> import java.util.Properties;
>
> sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE","username",
> "password", "oracle.jdbc.driver.OracleDriver")
> sql.query("select ID, NAME from A where ID not in (select id from
> B)") {
> row-> while(row.next()) {
> sql.executeInsert "INSERT INTO A (ID, Name) VALUES ("+$row.ID"+,+"
> $row.Name+")"
> }
> }
>
> and am facing issue `No such property: $row`. Kindly suggest me where am
> going wrong and also is there any better approach of doing this in terms of
> security and performance.
my sql is rusty, but:
Insert into B
select ID, NAME from A
where ID not in (select id from B);
bye Jochen
Re: Oracle Insert Data from one table to another table using Groovy
Posted by GroovyBeginner <gr...@gmail.com>.
Thanks !! It worked
--
View this message in context: http://groovy.329449.n5.nabble.com/Oracle-Insert-Data-from-one-table-to-another-table-using-Groovy-tp5734285p5734338.html
Sent from the Groovy Dev mailing list archive at Nabble.com.
Re: Oracle Insert Data from one table to another table using Groovy
Posted by Dinko Srkoč <di...@gmail.com>.
Result is probably being displayed as null because `forEach` method in
fact returns `void`, which Groovy transforms into `null`, as it
expects a method to actually return something.
As for nothing being inserted, perhaps there is nothing to insert
(every ID in A is also in B)?
Cheers,
Dinko
On 27 July 2016 at 12:57, GroovyBeginner <gr...@gmail.com> wrote:
> I have tried the following code now and seems to be nothing is being inserted
> into the database table. I have executed the following code and the result
> is displayed as null.
>
> import groovy.sql.Sql;
> import java.sql.ResultSet;
> import java.util.Properties;
>
> sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE","username",
> "password", "oracle.jdbc.driver.OracleDriver")
> sql.eachRow("select ID, NAME from A where ID not in (select id from B)")
> { row->
> sql.executeInsert "INSERT INTO A (ID, Name) VALUES ($row.ID,$row.Name)"
> }
>
>
>
> --
> View this message in context: http://groovy.329449.n5.nabble.com/Oracle-Insert-Data-from-one-table-to-another-table-using-Groovy-tp5734285p5734303.html
> Sent from the Groovy Dev mailing list archive at Nabble.com.
Re: Oracle Insert Data from one table to another table using Groovy
Posted by Michael Tetzlaff <te...@skillsoftware.de>.
I guess you should do: "...values (${row.ID}, ${row.Name})
The curly brackets...
Am 27.07.2016 um 12:57 schrieb GroovyBeginner:
> I have tried the following code now and seems to be nothing is being inserted
> into the database table. I have executed the following code and the result
> is displayed as null.
>
> import groovy.sql.Sql;
> import java.sql.ResultSet;
> import java.util.Properties;
>
> sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE","username",
> "password", "oracle.jdbc.driver.OracleDriver")
> sql.eachRow("select ID, NAME from A where ID not in (select id from B)")
> { row->
> sql.executeInsert "INSERT INTO A (ID, Name) VALUES ($row.ID,$row.Name)"
> }
>
>
>
> --
> View this message in context: http://groovy.329449.n5.nabble.com/Oracle-Insert-Data-from-one-table-to-another-table-using-Groovy-tp5734285p5734303.html
> Sent from the Groovy Dev mailing list archive at Nabble.com.
>
--
*Michael Tetzlaff*
Developer
tetzlaff@skillsoftware.de
Office: +49 69 153 2282 22
Mobil: +49 177 3000 633
Skill Software GmbH
Berger Strae 179-181
60385 Frankfurt
http://www.skillsoftware.de
*Sieger Telekom-Innovationspreis 2012*
Skill Software GmbH, Sitz: 60385 Frankfurt
Geschftsfhrer: Edgar Reh, Klaus Szilvas
Amtsgericht Frankfurt am Main, HRB 33988
Umsatzsteuer-ID: DE 114211536
Re: Oracle Insert Data from one table to another table using Groovy
Posted by GroovyBeginner <gr...@gmail.com>.
I have tried the following code now and seems to be nothing is being inserted
into the database table. I have executed the following code and the result
is displayed as null.
import groovy.sql.Sql;
import java.sql.ResultSet;
import java.util.Properties;
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE","username",
"password", "oracle.jdbc.driver.OracleDriver")
sql.eachRow("select ID, NAME from A where ID not in (select id from B)")
{ row->
sql.executeInsert "INSERT INTO A (ID, Name) VALUES ($row.ID,$row.Name)"
}
--
View this message in context: http://groovy.329449.n5.nabble.com/Oracle-Insert-Data-from-one-table-to-another-table-using-Groovy-tp5734285p5734303.html
Sent from the Groovy Dev mailing list archive at Nabble.com.
Re: Oracle Insert Data from one table to another table using Groovy
Posted by Russel Winder <ru...@winder.org.uk>.
It does seem a pity that Groovy only provides drivers of SQL statements
when Groovy is a language superb for DSLs and builders.
Python is very similar to Groovy in this respect and Python has
SQLAlchemy which provides the low level SQL driving stuff, but also
provides an expression language, DSL and SQL builder, and also a full-
on ORM.
I tried to do something on a Groovy expression language for SQL from
earlier work by various people at https://github.com/russel/GSQL but it
seemed no one was at all interested and when I stopped having to worry
about SQL databases on the JVM I stopped caring.
It just seems a shame.
If anyone working with SQL wants to progress this expression language
stuff, I am happy to forward the repository to their ownership.
Re: Oracle Insert Data from one table to another table using Groovy
Posted by Dinko Srkoč <di...@gmail.com>.
On 26 July 2016 at 15:47, GroovyBeginner <gr...@gmail.com> wrote:
> I tried this and facing the following issue
>
> groovy.lang.MissingPropertyException: No such property: ID for class:
> oracle.jdbc.driver.OracleResultSetImpl
I'm only guessing here because I can't run the code at the moment and
I'd always avoided using `query` method, but I'd say that the reason
why there's no property `ID` is that you used `sql.query`, and so your
`row` value is just a Java's `ResultSet`, not Groovy's enhanced
version of it.
So, instead of using `query`:
sql.query("select ID, ...") { row ->
while (row.next()) {
sql.executeInsert ...
}
}
try using `eachRow` method instead:
sql.eachRow("select ID, ...") { row ->
sql.executeInsert ...
}
That way, instead of `ResultSet`, you get `GroovyResultSet`, which
shouldn't break on things like `row.ID`, and you don't have to use a
while loop because `eachRow` does the looping internally.
Cheers,
Dinko
PS
this topic is not really suited for the dev list, which is for the
development of Groovy itself. It should go to the users list. Please
see http://groovy-lang.org/mailing-lists.html
> Possible solutions: row
>
> at
> org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:51)
>
> at
> org.codehaus.groovy.runtime.callsite.GetEffectivePojoPropertySite.getProperty(GetEffectivePojoPropertySite.java:63)
>
> at
> org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGetProperty(AbstractCallSite.java:291)
>
> at ConsoleScript1$_run_closure1.doCall(ConsoleScript1:8)
>
>
>
>
> --
> View this message in context: http://groovy.329449.n5.nabble.com/Oracle-Insert-Data-from-one-table-to-another-table-using-Groovy-tp5734285p5734298.html
> Sent from the Groovy Dev mailing list archive at Nabble.com.
Re: Oracle Insert Data from one table to another table using Groovy
Posted by GroovyBeginner <gr...@gmail.com>.
I tried this and facing the following issue
groovy.lang.MissingPropertyException: No such property: ID for class:
oracle.jdbc.driver.OracleResultSetImpl
Possible solutions: row
at
org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:51)
at
org.codehaus.groovy.runtime.callsite.GetEffectivePojoPropertySite.getProperty(GetEffectivePojoPropertySite.java:63)
at
org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGetProperty(AbstractCallSite.java:291)
at ConsoleScript1$_run_closure1.doCall(ConsoleScript1:8)
--
View this message in context: http://groovy.329449.n5.nabble.com/Oracle-Insert-Data-from-one-table-to-another-table-using-Groovy-tp5734285p5734298.html
Sent from the Groovy Dev mailing list archive at Nabble.com.
Re: Oracle Insert Data from one table to another table using Groovy
Posted by ng...@ABINITIO.COM.
The notation $row is for use inside a string (called string
interpolation).
Try changing your sql.executeInsert line to:
sql.executeInsert "INSERT INTO A (ID, Name) VALUES
($row.ID,$row.Name)"
See:
http://docs.groovy-lang.org/latest/html/documentation/index.html#_string_interpolation
From: GroovyBeginner <gr...@gmail.com>
To: dev@groovy.incubator.apache.org,
Date: 07/26/2016 02:46 AM
Subject: Oracle Insert Data from one table to another table using
Groovy
I have a requirement of inserting the data from table A into table B and
am
using Oracle database. My condition is am going to insert only those
records
which are not present in table B and am facing an issue of retrieving the
current row column values. Here is the code am trying.
import groovy.sql.Sql;
import java.sql.ResultSet;
import java.util.Properties;
sql =
Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE","username",
"password", "oracle.jdbc.driver.OracleDriver")
sql.query("select ID, NAME from A where ID not in (select id from
B)") {
row-> while(row.next()) {
sql.executeInsert "INSERT INTO A (ID, Name) VALUES ("+$row.ID"+,+"
$row.Name+")"
}
}
and am facing issue `No such property: $row`. Kindly suggest me where am
going wrong and also is there any better approach of doing this in terms
of
security and performance.
--
View this message in context:
http://groovy.329449.n5.nabble.com/Oracle-Insert-Data-from-one-table-to-another-table-using-Groovy-tp5734285.html
Sent from the Groovy Dev mailing list archive at Nabble.com.
NOTICE from Ab Initio: This email (including any attachments) may contain
information that is subject to confidentiality obligations or is legally
privileged, and sender does not waive confidentiality or privilege. If
received in error, please notify the sender, delete this email, and make
no further use, disclosure, or distribution.