Sign in to follow this  
Followers 0
Kovitt

SQL Automation Help

6 posts in this topic

#1 ·  Posted (edited)

Hello,

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

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 by Kovitt

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Kovitt

Share this post


Link to post
Share on other sites

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...???

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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()

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0