Toad World® Forums

Testing an update statement


#1

Dear QCTO experts,

During the past two weeks I’ve been using CodeTester quite a lot. I have mixed (but mostly positive) feelings about the tool at the moment,but those will be subject of an other thread I will post sometime in the near future.

At this moment I am stuck creating a testcase that can check if an update statement succeeded or not. I have created a new outcome for the procedure that performs the update. I have entered the following information for the outcome:

Changed by Program: Query

The query I entered here is:
SELECT abokab.abokab_afsluitdatum, abo.abo_ed_stop, abo.abo_mut_dd,
abo.abo_mut_by
FROM f30abonnement abo, f30abokabnaa abokab
WHERE abokab_abo_pk = abo_pk AND abokab.abokab_abonnee_nr = lv_abonnement_nr

I’ve turned on the checkbox “Evaluate string as an expression”

In the WHERE-clause I use a local variable called “lv_abonnement_nr"which is declared on tab page “Declarations” during Step 1. This variabele is filled using a query which is defined on tab page"Initialization”.

Expected Results: Query

The query I entered here is:
SELECT 20080105 /* Eq: abokab_afsluitdatum /
, 200712 /
Eq: abo_ed_stop /
, TO_CHAR (SYSDATE, ‘YYYYMMDD’) /
Eq: abo_mut_dd /
, ‘CBS Test Wijzig Abonnement’ /
Eq: abo_mut_by */
FROM DUAL

For one reason or another CodeTester keeps complaining that the outcome/row of both queries do not match. I can’t get it to work, which is driving me a little crazy at the moment!

I am absolutely sure that the update succeeded. When I check the results in the updated table I see the expected values in the updated columns. And when I perform the queries I entered for the outcome manually and compare the results, then I see no differences at all.

What am I doing wrong here? I hope someone can point it out to me.

Kind regards,
Theo Tol


#2

If you instantiate your ‘lv_abonnement_nr’ variable in the declarations tab, I’m not sure if you need to enable the ‘Evaluate string as expression’ option.

Your variable should be accessible to the test when it’s run.

I will try a simple test case using scott.emp to see if I can replicate your environment and then share the source and test code.

Jeff


#3

I love these posts! It forces me to actually LEARN the product :slight_smile:

OK, so you definitely need the ‘evaluate’ as expression. This allows Code Tester to look at your variable declaration and evaluate it and send the hardcoded literal to your query to use for the Outcome.

So I just tried this with our demonstration procedure - DELETE_MULTI_ROWS

CREATE OR REPLACE PROCEDURE JEFF.qctod#delete_mult_rows (
low_in IN PLS_INTEGER DEFAULT NULL
, high_in IN PLS_INTEGER DEFAULT NULL
)
IS
l_low PLS_INTEGER := low_in;
l_high PLS_INTEGER := high_in;
BEGIN
IF l_low IS NULL
THEN
SELECT MIN (n)
INTO l_low
FROM qctod#dmr_table;
END IF;

IF l_high IS NULL
THEN
SELECT MAX (n)
INTO l_high
FROM qctod#dmr_table;
END IF;

DELETE FROM qctod#dmr_table
WHERE n BETWEEN l_low AND l_high;
END qctod#delete_mult_rows;
/

I created a test case with the following evaluated as expression:
SELECT *
FROM qctod#dmr_table
WHERE n = my_local_variable – don’t put a semi-colon here!

I placed this in the declaration section for my test case:
/* DECLARATIONS OF LOCAL VARIABLES */
my_local_variable NUMBER;

I placed this in the init code for the outcome definition
– Initialization code
my_local_variable := 5;

I then run my test and it evaluates OK with test inputs 1 and 4.
screen1.jpeg


#4

I love these posts! It forces me to actually LEARN the product :slight_smile:

OK, so you definitely need the ‘evaluate’ as expression. This allows Code Tester to look at your variable declaration and evaluate it and send the hardcoded literal to your query to use for the Outcome.

So I just tried this with our demonstration procedure - DELETE_MULTI_ROWS

CREATE OR REPLACE PROCEDURE JEFF.qctod#delete_mult_rows (
low_in IN PLS_INTEGER DEFAULT NULL
, high_in IN PLS_INTEGER DEFAULT NULL
)
IS
l_low PLS_INTEGER := low_in;
l_high PLS_INTEGER := high_in;
BEGIN
IF l_low IS NULL
THEN
SELECT MIN (n)
INTO l_low
FROM qctod#dmr_table;
END IF;

IF l_high IS NULL
THEN
SELECT MAX (n)
INTO l_high
FROM qctod#dmr_table;
END IF;

DELETE FROM qctod#dmr_table
WHERE n BETWEEN l_low AND l_high;
END qctod#delete_mult_rows;
/

I created a test case with the following evaluated as expression:
SELECT *
FROM qctod#dmr_table
WHERE n = my_local_variable – don’t put a semi-colon here!

I placed this in the declaration section for my test case:
/* DECLARATIONS OF LOCAL VARIABLES */
my_local_variable NUMBER;

I placed this in the init code for the outcome definition
– Initialization code
my_local_variable := 5;

I then run my test and it evaluates OK with test inputs 1 and 4.
screen2.jpeg


#5

Theo, I am very glad to hear you are using the tool, and look forward to your feedback.

I have a question: which item are you marking as Evaluate as an expression?

If it is that first query you entered, then I think you will have a problem. The query is definitely not an expression.

I will take a closer look soon (just arrived in Buenos Aires for some Quest seminars, so I am getting settled in) at the queries themselves to see what else might be an issue here.

SF


#6

@Steven:
The query I’ve entered for “Changed by Program” is marked as Evaluateas an expression. I need to do this, otherwise the test case won’tvalidate. The cause of this is using a local variabele in the WHERE clause of the query as HillbillyToad stated a couple of posts earlier.

@HillbillyToad:
Have you tried to compare the values of columns from qctod#dmr_table from one row to values selected from DUAL like I do in my test case?

Could it be that my strategy to use DUAL in the compare does not work here?


#7

I had problems before - as I had tried the dual method first - but that was before I realized my code wouldn’t compile because i had a statement delimiter in my query definition and I had not enabled the ‘Evaluate as expression’ option.

I’ve just changed my query to

SELECT my_local_variable
FROM DUAL

I have my_local_variable set to 5, so it does a select 5 from dual which mirrors the results of my dmr_table which only has row 5.

This worked for me.

I wanted to test for FAILURE though so I changed my variable init to be 6 and re-ran the test, and unfortunately the test still PASSED. So I think I’ve either found a bug or am using a method that wasn’t intended to be supported.

This caused me to wonder further still, so I went back to my original query
SELECT *
FROM qctod#dmr_table
WHERE n = my_local_variable

And changed the init value to be 3, and it still passes!

I will contact DEV and/or hope Steven can shed some light on what I’m doing.

Jeff


#8

My apologies, Theo, for the slowness in getting back to you on this. I am traveling through Latin America and that is rather distracting!

Buenos Aires, by the way is a beautiful city; I presented by best practices talk and Code Tester to 150 developers in two sessions in a single day…

OK, so: bottom line is that our ability to support what you want to do is limited, but it IS possible. Here’s the rule you have to follow in constructing your query (and we will get this into the Help doc for 1.6):

**The query must be able to execute out of the scope of the package.
**We evaluate the query to determine the column structure of the query, so that we can gen the right code. If you reference a local variable, it dies. As you know.

So…

  1. Do NOT mark it as an evaluation.

  2. Create a package that returns the value you want to use in your test as in:

CREATE OR REPLACE PACKAGE abc
IS
FUNCTION lname
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE body abc
IS
FUNCTION lname
RETURN VARCHAR2
IS
BEGIN
RETURN ‘b’;
END;
END;
/

  1. Then you should be OK. You can even paste the code for this into the external initialization section of the test package to keep it all together.

I know that this is not the optimal solution and we will see what we can do about it in the future.

The important thing, though, is that I think you can get what you need done with the current release.

Regards, Steven


#9

One other thing, regarding Jeff’s issue: I am waiting to get the test definition export to see exactly what you did, but definitely setting these queries to be evaluations will lead to unexpected results. I am just responded the test code is compiling!

SF


#10

Hi Steven,

As soon as I have the opportunity I will check out your solution.
Thanks for the elaborate answer!

Best wishes,
Theo