Communicate with SQL - D3 Code Sample

Look at perl and DBI.  Perl has a simple DBI interface and all the picks have the simple ability to execute local os programs.  You are not going to find any product that does all the mapping work for you, but perl can quickly do this for you.

001 SUBROUTINE WHIRLER.DO.SQL(OPT,CMND,RESULT)
002 ** THIS ROUTINE WILL GENERATE A PERL SCRIPT ON THE FLY
003 RESULT=''
004 OPEN 'unix:/tmp/scripts/' TO SCRIPT.FILE ELSE RETURN
005 CODE='use Pg;'; * replace this with specific database driver
006 DATABASE=OPT<1>
007 CODE<-1>="$conn = Pg::connectdb('dbname=":DATABASE:"');"
008 Q="'"
009 CODE<-1>='$result = $conn->exec("':CMND:'");'
010 IF INDEX(CMND,'SELECT',1) THEN
011    * DOING A SELECT STATEMNET
012    DOING.SELECT=1
013    CODE<-1>='while (@d = $result->fetchrow) {'
014    CODE<-1>="     print join('|', @d);"
015    CODE<-1>='     print "\n";'
016    CODE<-1>='}'
017    CODE<-1>='exit;'
018 END ELSE
019    CODE<-1>='$r = $result->resultStatus;'
020    CODE<-1>='print $r;'
021    CODE<-1>='print "\n";'
022    CODE<-1>='exit;'
023 END
024 ID=SYSTEM(19)
025 WRITE CODE ON SCRIPT.FILE, ID
026 EXECUTE '!perl /tmp/scripts/':ID CAPTURING RESULT
027 DELETE SCRIPT.FILE, ID
028 CONVERT '|' TO CHAR(253) IN RESULT
029 RETURN
 

Now this script connects to Postgres sql database.  It outputs all SQL Select statements as | delimited files.

Now I have used this in pick in the manner you have asked as such.

Login to Read Complete Article.

Downloads:

menu
menu