SQL Automation Help

[Kovitt]

Hello,

I am writing a script that runs a few queries and analyzes the data. The following is what I have so far.

expandcollapse popup

RunWait(@ComSpec & " /c Type rollupSelect.txt | sqlplus -s ****/*****@**** > RollupJobs.txt")
        $path = FileOpen("RollupJobs.txt",0)
    _FileReadToArray($path,$jobs)
       For $x=0 to Ubound($jobs) - 1
        $Line = FileReadLine($path)
        ;------------------------------------------------------------------------------------;
        ;-->>   Pulls CE_ID and Instance from TB_Board_Msg output                        ;
        ;------------------------------------------------------------------------------------;
        $instance = StringRegExp($Line,'([0-9]{1,})(?:\s{1,})([0-9]{1,})',1)
        If @error = 0 then
        $ce_id[$x] = $instance[0] 
        $CEID_Instance[$x] = $instance[1]
        EndIf
        
    Next
    RunWait(@ComSpec & " /c Type JobSelect.txt  | sqlplus -s *****/*****@**** > Jobs.txt")
         $pathJ = FileOpen("Jobs.txt",0)
         While True
             $LineJ = FileReadLine($PathJ)
             If @error = -1 Then
                 ExitLoop
         EndIf
 $a_sre = StringRegExp($LineJ, "([0-9]{1,})(?:\s{1,})(?:IFACT.Call_Rollup_Pkg.Call_Rollup_Proc\W)([0-9]{1,})(?:, )([0-9]{1,})", 3)
            if @error = 0 Then
                $Job[$count] = $a_sre[0]
                $RangeS[$count] = $a_sre[1]
                $RangeE[$count] = $a_sre[2]
            EndIf
            $count = $count + 1
        WEnd  
            
        For $x=0 to Ubound($ce_id) - 1
            For $i=0 to Ubound($Jobs) - 1
                if $ce_id[$x] >= $RangeS[$i] and $ce_id[$x] <= $RangeE[$i] Then 
                $Job_Instance[$i] = $Job_Instance[$i] + $CEID_Instance[$x]
                                Exitloop
                EndIf
            Next
        Next

        _ArrayDisplay($Job_Instance)

The contents of rollupselect is:

Select CE_ID, count(1) as "Instances of CE_ID" from TB_BOARD_MSG where CRT_TS > '08-JUL-08' and CE_ID between 12000 and 14000 group by CE_ID;

JobSelect:

Column job format 9999;
Column what format a60;
select job, what from all_jobs;

example of rollupjobs:

CODE

CE_ID Instances of CE_ID

---------- ------------------

12000 30

12001 81

12004 23

12005 42

12007 56

12008 28

12009 23

12033 54

12034 61

12043 66

12045 65

CE_ID Instances of CE_ID

---------- ------------------

12050 38

12051 32

12074 33

12076 44

12116 162

12133 79

12135 79

12136 79

12137 79

12140 79

12141 79

Example of Jobs:

CODE

JOB WHAT

----- ------------------------------------------------------------

301 IFACT.TMP_OTPT_EXTRACT_PROC;

381 begin

ogw_refresh_ogw_cgw_device;

ogw_refresh_ogw_ts100_device;

end;

289 dbms_refresh.refresh('"IFACT"."MV_BBS_CE_LST"');

530 IFACT.Call_Rollup_Pkg.Call_Rollup_Proc(351, 10000);

529 IFACT.Call_Rollup_Pkg.Call_Rollup_Proc(1, 350);

221 dbms_refresh.refresh('"IFACT"."MV_SMS_BILL_UNIT"');

141 IFACT.Call_Rollup_Pkg.Call_Rollup_Proc(10001, 51000);

JOB WHAT

----- ------------------------------------------------------------

533 IFACT.Call_Rollup_Pkg.Call_Rollup_Proc(55001, 60000);

532 IFACT.Call_Rollup_Pkg.Call_Rollup_Proc(51001, 55000);

241 IFACT.Call_Rollup_Pkg.Call_Rollup_Proc(60001, 65000);

281 IFACT.Call_Rollup_Pkg.Call_Rollup_Proc(65001, 999999999);

My for loop at the end of my script doesnt seem to be working properly. It should give me multiple numbers(fairly large) from which I can then take the largest one and continue with my script.

Unfortunatly it only gives me one number which is in $Job_Instance[11] and is 9407. This number may be correct but I beleive there should be more.

Anythoughts?

I really appreciate you taking the time to help me out!

Edited July 15, 2008 by Kovitt

[weaponx]

Why are you parsing the text result of a query rather than interfacing directly with SQL?

[Kovitt]

It is the only way I know how muttley

I am quite bad with sql, the queries are provided for me and I only have a very basic understanding of them..

Edited July 15, 2008 by Kovitt

[weaponx]

It is the only way I know how muttley

I am quite bad with sql, the queries are provided for me and I only have a very basic understanding of them..

SQL is very powerful and you can retrieve much cleaner ordered results with it. Is your database on your local machine, remote, etc...???

[Kovitt]

It is located on a server, and is in Oracle..

[weaponx]

There are some SQL functions here (by ChrisL):

http://www.autoitscript.com/forum/index.ph...DODB.Connection

#include <array.au3>
#include <_sql.au3>

$con = _SQLStartup()
If @error then Msgbox(0,"Error","Error starting ADODB.Connection")
    
_SQLConnect(-1,"192.168.1.10","db","username","password")
if @Error then Msgbox(0,"",$SQLErr)

$Query = "Select CE_ID, count(1) as 'Instances of CE_ID' from TB_BOARD_MSG where CRT_TS > '08-JUL-08' and CE_ID between 12000 and 14000 group by CE_ID;"

$data = _SQLExecute(-1,$Query)
If Not @error then 
    $aData = _SQLGetData2D($data)
    _arrayDisplay($aData)
Else
    Msgbox(0,"",$SQLErr)
EndIf

_SQLClose()