Re: (2) Waiting for an SQL script to finish

The script I've made mention of is an SQL Script, here it is:

rem finished_goods.sql
set feedback off
set heading off
set echo off
set pagesize 0
set linesize 510
spool invent_sap1.unl
SELECT
    b.article_id || ' ' ||
    'CWL' || ' ' ||
    b.mill_id || ' ' ||
    b.track_num || ' ' ||
    stat || ' ' ||
    class || ' ' ||
    TRIM(' ' FROM TO_CHAR(SUM(A.wgt_est), '999999990.9')) || ' ' ||
    TRIM(' ' FROM TO_CHAR(SUM(sheet_count), '999999990.9'))
FROM 
    invent a, 
    (SELECT 
        unit_id, 
        article_id, 
        track_num, 
        mill_id
    FROM 
         invent
    WHERE 
            (class = 'U' 
        OR class = 'W' 
        OR(class = 'T' 
        AND class_orig IN('U', 'W')) 
        OR (class = 'L' 
        AND class_orig IN('U', 'W'))) 
        AND stat IN('G','J','T') 
        AND ts_scaled > 0 
        AND unit_id=roll_id_lead) b
WHERE 
         a.roll_id_lead=b.unit_id
    AND (a.type_code = 'S' 
    OR   a.type_code = 'C')
GROUP BY 
    b.article_id, 
    'CWL', 
    b.mill_id, 
    b.track_num, 
    stat, 
    class
UNION
SELECT 
    b.article_id || ' ' ||
    'CWL' || ' ' ||
    b.mill_id || ' ' ||
    b.track_num || ' ' ||
    stat || ' ' ||
    class || ' ' ||
    TRIM(' ' FROM TO_CHAR(SUM(A.wgt_scaled), '999999990.9')) || ' ' ||
    TRIM(' ' FROM TO_CHAR(SUM(sheet_count), '999999990.9'))
FROM 
    invent a, 
    (SELECT
        unit_id, 
        article_id, 
        track_num, 
        mill_id
     FROM 
         invent
     WHERE 
             (class = 'U' 
        OR  class = 'W' 
        OR  (class = 'T' 
        AND class_orig IN('U', 'W')) 
        OR  (class = 'L' 
        AND class_orig IN('U', 'W'))) 
        AND    stat IN('G','J','T') 
        AND ts_scaled > 0 
        AND unit_id=roll_id_lead) b
WHERE 
        a.roll_id_lead=b.unit_id
    AND a.type_code='R'
GROUP BY 
    b.article_id, 
    'CWL', 
    b.mill_id, 
    b.track_num, 
    stat, 
    class;
spool OFF
set feedback on
set heading on
set echo on
set pagesize 1000
set linesize 100
[download]

When I said I think I have the FTP part, I should have said I've found a code snippet that I should be able to use. My starting point is figuring out how to call that SQL script. Thanks.

Comment on Re: (2) Waiting for an SQL script to finish Download Code

Replies are listed 'Best First'.
Re: Waiting for an SQL script to finish by Abigail-II (Bishop) on Aug 26, 2003 at 20:16 UTC
Most, if not all, relational databases come with a commandline tool. Assuming the tool takes commands from standard input, the tool is named `toolsql` and your SQL file is called `whatever.sql`, you could do something like this in your program: `system "toolsql < whatever.sql"; die "toolsql failed with exit code ", $? >> 8 if $?;` [download] Abigail	[reply] [d/l]
Re: (2) Waiting for an SQL script to finish by monktim (Friar) on Aug 26, 2003 at 20:34 UTC
Adding to Abigail-II'suggestion (and other monk's intuituon), you can also call your system's command line ftp program from perl the same way. Depending on what you're doing and what command line tools you have available, you may be able to do this in 4 lines. Abigail-II's system command executes shell stuff. I put backticks just to show another way to do it :) I don't know about other dB's but MS SQL Server can do FTP and you won't even need Perl. If you happen to be using MS SQL Server, check out Data Transformation Services. Good luck. use strict; use warnings; `isql -S server_name -U login_id -P password -d database -i file` `ftp -s:file ftp://user:password@ftp.site.com/` [download] Tim	[reply] [d/l]
Re: Waiting for an SQL script to finish by Abigail-II (Bishop) on Aug 26, 2003 at 20:42 UTC
I put backticks just to show another way to do it. You shouldn't have done that; it's bad advice. Backticks make Perl collect the output of programs run (which then gets discarded because you aren't doing anything with it). Furthermore, you aren't checking for any failures, just like with `system`, it's a good thing to test the value of `$?` after using backticks. In short, while you could use backticks instead of system, in most cases either system is the right approach, or backticks. There might be a rare case where both are correct, but I can't think of any. Abigail	[reply]
Re: (2) Waiting for an SQL script to finish by monktim (Friar) on Aug 26, 2003 at 21:07 UTC
Re: (3) Waiting for an SQL script to finish by hmerrill (Friar) on Aug 26, 2003 at 20:14 UTC
I'm going to take a wild guess and say that that script is not a shell script, right? What database are you using? That script actually looks like a session from a database client, like the MySQL client "mysql>", or the Oracle client "sqlplus>", or the PostgreSQL client "psql>". Are you sure you just want to know how to run that "script" from Perl? Forget about Perl for the moment and using Perl to execute that "script" - how do you execute that "script" now?	[reply]
Re: Re: (3) Waiting for an SQL script to finish by LeeC79 (Acolyte) on Aug 27, 2003 at 12:11 UTC
Right, it is not a shell script. Simply a text file called something.sql. I am using the Oracle client to write/run the script. However, the script gets run as it is right now on a Unix platform through crons. We are moving an app over to Microsoft, the perl script I am trying to write will be scheduled through the SQL Server.	[reply]
Re: (3) Waiting for an SQL script to finish by CountZero (Bishop) on Aug 26, 2003 at 21:06 UTC
What program do you feed this script to? CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]