QTP and SQL stored procedures

Thu, 15 Aug 2002 08:26:33 +0200

Yes, We have experienced the same behavior. QTP only seems to check the
results of updating database record buffers, and will not detect an error
caused by call to an Oracle stored procedure. This applies independant of
what COMMIT frequency you have chosen. This is probably the only way QTP
can respond, since there will be no way of detecting that the stored proc
failed unless you choose to return some error-indicator from the procedure
yourself.

We have solved this by using av IN/OUT parameter that returning status
codes / error codes from the stored proc, and forcing qtp to terminate if
an error is detected. Se example:

COMMIT AT REQUEST

REQUEST DEMO_CALL_SP ON CALC ERROR TERMINATE RUN

temporary T_SP_STATUS_CODE   integer signed   size 2
temporary T_SP_ERROR         integer UNSIGNED size 1

; Will be set negative by stored proc if error occurs in procedure
item T_SP_STATUS_CODE = 0

sql call <stored proc> &
( &
  <other IN/OUT parameteres>, &
  T_SP_STATUS_CODE  in out &
)

; Detects error status returned from stored proc, and causes qtp to
terminate if error occured
item T_SP_ERROR = -1 if T_SP_STATUS_CODE < 0 else 0

NOTE that T_SP_ERROR is declared as UNSIGNED and will cause a conversion
error when assigened a negative value. Using ON CALC ERROR TERMINATE RUN,
this will terminate QTP. Combined with COMMIT AT REQUEST this will ROLLBACK
the whole request. Using COMMIT AT 1 TRANSACTION, only the transaction that
failed will be rolled back.

I addition to this, we also generate a readable error-message that is
written to a portable nodict subfile BEFORE terminating QTP. Since changes
to subfiles are NOT rolled back, this will give us a readable log of what
error that occured.

Jon Kvisli
----------------------------------------------
Senior systemkonsulent
Lindorff Applications as
Hellandtunet forsknings- og nęringssenter
Postboks 4, 3833 Bų i Telemark
tlf: 35 06 15 71
fax: 35 06 15 01
e-post: jon.kvisli@lindorffapplications.com
www.lindorffapplications.com
----------------------------------------------

                    "Kevin Erne"                                                                                 
                    <kevin.erne@lineon       To:     powerh-l@lists.swau.edu                                     
                    e.net>                   cc:                                                                 
                    Sent by:                 Subject:     QTP and SQL stored procedures                          
                    powerh-l-admin@cub                                                                           
                    e.swau.edu                                                                                   

                    14.08.2002 21:19                                                                             
                    Please respond to                                                                            
                    mail                                                                                         

Running QTP V8.13D on AIX. The QTP has no commit statements and uses
serializable
CONSISTENCY transaction for every table accessed. Calling Oracle (V8.1.7)
stored procedure in very last request. If this fails e.g. constraint
violation
- then previous updates are still committed.
This QTP is usually run in a batched ksh script and tests $? after  QTP
is invoked. This seems to think that QTP has worked fine even after stored
proceduree has failed. Is this a bug/feature?

= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.