subquery in a loop
[1/5] from: jblake:arsenaldigital at: 28-Jun-2006 14:29
Ok,
I've been hitting my head over this the past 2 days and I've finally
decided to go to the group.
I'm reading in a list of ip's from a file and need to sql to each of
those ip's.
I have it outputting the ips correctly but I can't get it to connect to
each one.
Its not using the "variable" of the ip.
I'm trying to use a function now.
utilip: read/lines %/opt/nms/scripts/ip.txt
foreach line utilip [
print line
query: func [line] [read join
pgsql://xxxx:xxxx-line:5555/mcdb? "select name, epid, active from
v_ev_cus_prof where Active = 'true' and epid in (select epid from
v_ev_cus_snmp_contact where snmp_host = '10.1.0.20')"]
query line
print query
]
The output is;
connecting to: line
** Access Error: Cannot connect to line
** Where: open-proto
** Near: read join pgsql://xxxx:xxx-line:5555/mcdb? {select name, epid,
active from v_ev_cus_prof where Active = 'true'...
So it's using "line" instead of the ip.
The "print line" printouts the actual IP which is ok.
Thoughts?
John
[2/5] from: anton:wilddsl:au at: 29-Jun-2006 4:45
Hello John,
My first thought is not to redefine QUERY, which is an inbuilt
function which may very likely be used by port handler code.
But I see the real reason:
pgsql://xxxx:xxxx-line:5555/mcdb?
How does rebol know to reduce "line" in this url ?
Answer: it doesn't. :)
Solution:
rejoin [pgsql://xxxx:xxxx- line ":5555/mcdb?" ...]
or
join pgsql://xxxx:xxxx- [line ":5555/mcdb?" ...]
(I left out the "select ...")
A further suggestion, if each line reliably contains an ip, then
I suggest calling it 'ip:
ips: read/lines %ip.txt
foreach ip utilip [
print ip
...
]
Yes, you don't really need the function in there at all.
Regards,
Anton.
[3/5] from: jblake::arsenaldigital::com at: 28-Jun-2006 15:07
Kewl.
I tried the join stuff but I was trying to join the commands.
Cmd: xxxx:xxxx-
Cmd2: copy line
Cmd3: join cmd cmd2
And then pgsql://cmd4:5555......
But it was still outputting the same thing about not knowing what
line
was.
I didnt think of using the join inside the sql itself.
Thanks.
John
[4/5] from: dockimbel:free at: 28-Jun-2006 22:59
Try this :
sql: {
select name, epid, active
from v_ev_cus_prof
where
Active = 'true'
and epid in (
select epid
from v_ev_cus_snmp_contact
where snmp_host = '10.1.0.20'
)
}
get-info: func [line][
read rejoin [
pgsql://xxxx:xxxx-
trim/all line
":5555/mcdb?"
sql
]
]
utilip: read/lines %/opt/nms/scripts/ip.txt
foreach line utilip [print get-info line]
;----------- end -----------
The only important modification here, is the addition of 'trim/all
function to clean up your 'line content read from file (may contain
blanks or other unwanted characters).
If you still getting an error in open-proto function, add this line at
the beginning of your script :
trace/net on
Then publish here the output so we can analyze this deeper.
Btw, you really have so much Postgresql servers, each running on port
5555 (not the default pgsql port) ?
Regards,
--
DocKimbel
John Blake wrote:
[5/5] from: jblake::arsenaldigital::com at: 28-Jun-2006 17:04
Yes.
It's actually an app that uses postgres to store info and I'm using
Rebol to login and verify its config.
Thanks
John