The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

alcdotcom (10) [Avatar] Offline
#1
I'm fairly new to Groovy, although I have used it in a Grails project I built and often use it for simple scripts in place of windows BAT or Linux BASH. I often recommend it to clients when I feel it would be of some help. I recently used Groovy to write a simple script to move information from one DB table to another for a client of mine. The initial table has 100+ rows, but after running the script I noticed that only around 70 of the tables were processed. I tried to fix it, but I just can't seem to get it to reproduce the same behavior when debugging. It was a bit embarrassing because I ended up having to switch to using Java. I believe there is some asynchronous behavior going on, but I'm not familiar enough with Groovy to figure it out. Here is the script I'm using (truncated and with queries and hostnames changed to protect my client):

def ss = Sql.newInstance("jdbc:oracle:thin:removed", "removed",
"removed", "oracle.jdbc.driver.OracleDriver")

def ecv = Sql.newInstance("jdbc:oracle:thin:removed", "removed",
"removed", "oracle.jdbc.driver.OracleDriver")

def service;

def count = 0
rows = ss.rows('select * from ADDRESS')
for (address in rows) {
def user = ss.rows('select * from USER where user_id = ?', address.user_id)[0]

GetAccountInfoRequest request = new GetAccountInfoRequest()
request.setAccountId(address.acct_no)
def phone = proxy.getAccountInfo(request).getBillingPhone1()?.trim()
print count++ + " phone: ${phone} "

def query2 = '''select
c.cust_no, s.site_no,
case c.custype_id
when 'U' then 'AA'
when 'Z' then 'AA'
when 'C' then 'AA'
when 'D' then 'AA'
when 'S' then 'AA'
when 'W' then 'AA'
when 'A' then 'AA'
when 'B' then 'AA'
when 'M' then 'AA'
when 'T' then 'AA'
when 'I' then 'BB'
when 'TPM' then 'BB'
when 'SMB' then 'CC'
when 'RES' then 'CC'
when 'MFH' then 'CC'
when 'HOA' then 'CC'
when 'CMB' then 'CC'
when 'BLD' then 'CC'
when 'VZ' then 'DD'
when 'E' then 'NOTFOUND'
else 'NOTFOUND' end as billing_system
from customer c
join customer_site cs on cs.cust_no = c.cust_no
join site s on s.site_no = cs.site_no
where
s.phone = ?'''

for (it in ecv.rows(query2, phone)) {
def billingSysNumber = ss.rows('select id from billing_system where name = ?',it.billing_system)[0].id
def insertQ = '''Insert into ADDRESS2 (ADDRESS_ID,USER_ID,CUST_NO,SITE_NO,ACCT_NO,EMAIL,BILLING_SYSTEM,IN_MTM)
values (ADDRESS_SEQ.NextVal,?,?,?,?,?,?,'N')'''
ss.execute(insertQ, [address.user_id,it.cust_no,it.site_no,address.acct_no,address.email,billingSysNumber])
println insertQ
}
}

I initially used Sql.eachRow in place of Rows because I thought that was causing the issues, but I still get the same problems when I iterate using for-in loops. I placed println statements to count the total number of rows processed. As you'll see from the following output, it seems that query2 is not being executed for rows 5 - 8 of the outer query loop (bolded text).

0 phone: 4103566318 Insert into WEB_ADDRESS2 (ADDRESS_ID,USER_ID,CUST_NO,SITE_NO,ACCT_NO,EMAIL,BILLING_SYSTEM,IN_MTM)
values (WEB_ADDRESS_SEQ.NextVal,?,?,?,?,?,?,?)
1 phone: 4102980045 2 phone: 5013275860 Insert into WEB_ADDRESS2 (ADDRESS_ID,USER_ID,CUST_NO,SITE_NO,ACCT_NO,EMAIL,BILLING_SYSTEM,IN_MTM)
values (WEB_ADDRESS_SEQ.NextVal,?,?,?,?,?,?,?)
3 phone: null null phone
4 phone: 9512435748 Insert into WEB_ADDRESS2 (ADDRESS_ID,USER_ID,CUST_NO,SITE_NO,ACCT_NO,EMAIL,BILLING_SYSTEM,IN_MTM)
values (WEB_ADDRESS_SEQ.NextVal,?,?,?,?,?,?,?)
5 phone: 5187015693 6 phone: 4804305547 7 phone: 2815013781 8 phone: 3172154097
...

Any help would be appreciated.

Thanks,
Tony
Mittie (397) [Avatar] Offline
#2
Re: Question about Sql behavior
Hi Tony,

thanks for your post. However, this is not quite the best place to put it as it concerned with Groovy itself rather than book content.
You will get much better answers (that others can learn from as well) when posting it to groovy-user@codhaus.org.
It may also help to mention the Groovy version you are using.

all the best
Dierk
alcdotcom (10) [Avatar] Offline
#3
Re: Question about Sql behavior
Thanks Dierk. I registered for the lsit, but was unable to send a message. I tried sending to user@groovy.codehaus.org and was rejected. I also tried sending to groovy-user@codhaus.org, but received a DNS error response. I forwarded the first message to user-owner@groovy.codehaus.org, but have yet to receive a response. Is there something I'm missing?
Mittie (397) [Avatar] Offline
#4
Re: Question about Sql behavior
looks like you post now made it through to the groovy-user list smilie

keep groovin'
Dierk