Re: A weird SQR problem...

[Dale Erjo <Dale.Erjo@AMS.COM>]

The array is storing the information okay.  In fact, if I comment out the
SQL-insert command and issue a print command instead the information stored
in the array shows up in the SPF file.

To add more weirdness to the already weirdness of this...

Check out how I got around the problem...

I let $AMND_JUSF_4 store the data from the array as normal
and then used 6 temporary variable to hold a subset of the entire data
string.

     let $AMND_JUSF_4    = DataArray.AMND_JUSF(#recordDataLineNumber)
     let $A1 = substr($AMND_JUSF_4,1,50)
     let $A2 = substr($AMND_JUSF_4,51,50)
     let $A3 = substr($AMND_JUSF_4,101,50)
     let $A4 = substr($AMND_JUSF_4,151,50)
     let $A5 = substr($AMND_JUSF_4,201,50)
     let $A6 = substr($AMND_JUSF_4,251,5)

Then in my insert command I substituted the temporary string variables
concatenated together for the original ($AMND_JUSF_4).

     insert into INTF_DARTS2K (AMND_JUSF) VALUES
($A1||$A2||$A3||$A4||$A5||$A6);

STRANGE!

- Dale




                    Stu-Wood
                    <SWood2@OFFICE       To:     SQR-USERS@list.iex.net
                    DEPOT.COM>           cc:
                    Sent by:             Subject:     Re: A weird SQR problem...
                    "Discussion of
                    SQR, Brio
                    Software's
                    database
                    reporting
                    language"
                    <SQR-USERS@lis
                    t.iex.net>


                    08/28/2002
                    03:55 PM
                    Please respond
                    to sqr-users






I was having an issue with array references such as array.field(#element).
The problem was caused by the data type of the variable did not equal the
data type of the array field.  Double check this.

-----Original Message-----
From: Dale Erjo [mailto:Dale.Erjo@AMS.COM]
Sent: Wednesday, August 28, 2002 2:45 PM
To: SQR-USERS@list.iex.net
Subject: A weird SQR problem...


My SQR program was working until I tried to write the data I have stored in
an array to a table in the db.

Essentially, my program reads data from an Oracle table and stores it in an
array.
The data is manipulated and then stored in a second array on a line to line
ratio.
As I'm processing each line of the second array I write out the
corresponding line of the first array to a transaction table to show what I
processed.
This routine had been working when I called it once and looped through the
array WITHIN the procedure.
Now that I'm calling the procedure multiple times and it processes only a
single line from the array based on a loop variable that increments
elsewhere, it has started acting weird.

Calls 1 through 13 to the procedure work fine.  (they don't have much data
in the AMND_JUSF field)

The 14th call to the recordData procedure causes the SQR program to close
without any messages at all.  The print statements that I had been
outputting to the SPF file never show up.  SQR just closes completely down
without so much as a burp.

Some info about line 14.

The field causing the problem is AMND_JUSF.
The column in the table being read from is varchar2 (255) of name
AMND_JUSF.
The column in the table being written to   is varchar2 (255) of name
AMND_JUSF.
The actual data stored is a character string of 149 characters.  Nothing
weird in there like CRLF or anything else.
When I subStr the field to 53 characters of data, or less, it works just
fine.
54 characters or more and the program just evaporates into the ether.
This is not the only column causing this problem.  It seems any large
amount of data causes the same issue.

It only goes nuts when the SQL-Insert command is issued.

Any thoughts?

THANKS!

Here's a snippet of my code.

BEGIN-procedure gatherData_Non_Item

BEGIN-select
Fund.AGCY_ID        &AGCY_ID_2
PR.AMND_JUSF   &AMND_JUSF_2
PR.USID_ID          &USID_ID_2

     let DataArray.AGCY_ID(#array_cntr)       = &AGCY_ID_2
     let DataArray.AMND_JUSF(#array_cntr)          = &AMND_JUSF_2
     let DataArray.USID_ID(#array_cntr)       = &USID_ID_2

     let #array_cntr = #array_cntr + 1

FROM mf_pr_amd PR, mf_rqt_actg_ln_amd LN, mf_fund Fund
WHERE PR.sys_dt_tm >= $last_run_dat
AND (PR.LAST_USID_ID NOT like 'ACMS%')
AND (PR.UIDY LIKE '%CC%' OR PR.UIDY LIKE '%CS%' OR PR.UIDY LIKE '%C1%' OR
PR.UIDY LIKE '%C5%')
AND (PR.UIDY = LN.PARN_OF_LINE_ID)
AND (LN.FUND_ID = Fund.UIDY)
ORDER by PR.DOC_NUM, PR.AMND_NUM
END-select

END-procedure


!!!!  This procedure fails
BEGIN-procedure recordData

!!   #recordDataLineNumber increments elsewhere

     let $AGCY_ID_4      = DataArray.AGCY_ID(#recordDataLineNumber)

     let $AMND_JUSF_4    = DataArray.AMND_JUSF(#recordDataLineNumber)

     let $USID_ID_4      = DataArray.USID_ID(#recordDataLineNumber)

BEGIN-SQL
     insert into    INTF_DARTS2K (AGCY_ID, AMND_JUSF, USID_ID)
               VALUES ($AGCY_ID_4, $AMND_JUSF_4, $USID_ID_4);
END-SQL
     commit
END-procedure !recordData

- Dale