Mailing List Archive: 49091 messages
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search

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 = '')"] 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 = '' ) } 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