Re: Setting Variable Procedure Names

["Kloprogge,F.J.M. (Frank)" <F.J.M.Kloprogge@FONTYS.NL>]

Hi Paul,

there is a second method.
but I have to warn you.
It's very tricky and ...

If you get the idea, it's fine.
Otherwise it's something to try and understand because this could be a lot
of fun.
I use this method to extract data for exporting them to different other
programs and
some programs to test data-integrity on the database.
In the last case I created a panel on wich you can enter multiple simple
queries
that are being fired at the database by means of just one sqr.
(we're running about 600 different tests..)

So:

In your program you could do this

                ...
                ...
                let $tablename='MYDATA'

                do insert-data
                if $myerror='YESYES'    ! the only 2 lines you need extra in
                        do error-routine        ! your program
                end-if
                ...
                ...

begin-procedure error-routine
        do create tmp-table
        let $tablename='TMP_' || $tablename
        do insert-data
        do show-data
        stop
end-procedure

begin-procedure create-tmp-tbl
let $tmp='TMP_' $table_name
! the next sql-block should be changed to be dynamic too.
! you have to analyze the table as shown further below
! to create a where clause that generates no rows so
! you just get the structure
! or use a dynamic create-statement

begin-sql
create table [$tmp] as select * from [$tablename]
end-sql
end-procedure


begin-procedure insert-data
begin-sql on-error=set-flag
insert into [$tablename]
values
(vsafgag,
wrtw,
...
...
)
end-sql
end-procedure

begin-procedure set-flag
        let $myerror='YESYES'
end-procedure

And now this is the tricky part.

The next part is fictious, it depends on the database
It's just to show you the way it works.


begin-procedure show-data
let $column=''
begin-select
column_name
column_type
column_length
        evaluate column_type
                when ='char'
                   let $column=$column || ' rpad(' || &column_name || ',' ||
column_length || ','' ''),' ! i.e. rpad(address,30,' '),
                   break
                when='num'
                   let $column=$column || ' to_char(' || &column_name ||
'),' ! i.e. to_char(number),
                   break
                when='date'
                   ......
from [$tablename]
end-select

begin-select
[$column] &column
        print $sql-error (+1,1)
        print ' in table ' ()
        print $tablename ()
        print &column (+1,1)
from [$tablename]
end-select
end-procedure






Frank Kloprogge
Fontys ICT-Services
DBA PeopleSoft
08778-78710


-----Oorspronkelijk bericht-----
Van: Paul O'Riordan [mailto:Paul_O'Riordan@AVERYDENNISON.COM]
Verzonden: dinsdag 9 oktober 2001 13:39
Aan: SQR-USERS@LIST.IEX.NET
Onderwerp: Re: Setting Variable Procedure Names



I like that, 1 procedure for them all - potentially terrible for
maintenance though!
     Thanks for the reply
          Paul





"Kloprogge,F.J.M. (Frank)" <F.J.M.Kloprogge@FONTYS.NL>@list.iex.net> on
09/10/2001 12:32:54

Please respond to sqr-users@list.iex.net

Sent by:  "Discussion of SQR,              Brio Technology's database
      reporting language"              <SQR-USERS@list.iex.net>


To:   SQR-USERS@list.iex.net
cc:

Subject:  Re: Setting Variable Procedure Names


One way could be to build the insert-command into a variable i.e.

let $values='1,''' | sysdate ........
insert into [$table] values ($values)

Then you only have to show $values in your error-procedure

Frank Kloprogge
Fontys ICT-Services
DBA PeopleSoft
08778-78710


-----Oorspronkelijk bericht-----
Van: Paul O'Riordan [mailto:Paul_O'Riordan@AVERYDENNISON.COM]
Verzonden: dinsdag 9 oktober 2001 12:59
Aan: SQR-USERS@LIST.IEX.NET
Onderwerp: Setting Variable Procedure Names


Hi there all,
     I'm new to this list and decided to post a question. Does anyone know
if it is possible to assign a Value to a variable and then call that
veriable as a procedure:
for example, in my error-handling I want to display all of the variables
that I was trying to insert or update when I caused and error. I have
created a "Show" procedure for each of my Insert procedures but I don't
want to hard-code the search for which procedure erred out. (Laziness).
Also each procedure is seperate and unique so it is not as if I can pass
parameters
"Do show_procedure($whatever)". Below is what I want to do, sort of, but
it doesn't work. Has anyone tried anything similar and succeeded?

     If #sql-status      = -9
        !Duplicates on insert
     Else
        Show 'Error in Procedure : ' $proc
        Show 'SQL-Error = ' $Sql-Error
        Show 'SQL-Status = ' #SQL-Status
             Let $procedure = 'Show_' || $proc
        Do $procedure
     End-if

     Thanks and regards,
          Paul


-------------------------------
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited.  If you received
this in error, please contact the sender and delete the material from any
computer.