Problems in Data Generator (Bug?)

[TenGor]

Hy,

got the following problem:
I want to fill up 3 tables:

TAB_A, TAB_B which contains master data
and TAB_AB which is an n-m table between both above.

So i fill in Data Gerenator TAB_A, TAB_B with a set of 10 records and the TAB_AB should be filled with 1..3 Records of TAB_A.

If i look in the table TAB_AB after a test run, the first foreign key (from TAB_A) is filled correct, but for the foreign key for TAB_B i see only '?'.
In the definition for TAB_AB i have 3 columns:
ID -> primary key
FK_A -> foreign key from TAB_A
FK_B -> foreign key from TAB_B

As data i have
ID -> Sequence(1, 1) [Data column]
FK_A -> TAB_A.NR [Master column]
FK_B -> TAB_B.NR [Master column]

What is going wrong?
This is indeed the same in Ver. 6 or Ver. 7

 

[Marco Kalter]

How does the definition in the datagenerator for FK_A look? It works for me if I use: LIST(select id from tab_a).

 

[TenGor]

In the column Data i have nothing, and in column Master i have TAB_A.NR

Ok, with the List Option in Data and nothing in Master it works.

So if i see it right i can't fill a n-m table with two masters?

Btw: Who did the Function "SQL(sql function)" work?

 

[Marco Kalter]

Two masters should not be a problem, as long as tables are filled in the correct order.

The SQL function sends the parameter directly to Oracle, so this can be something simple like sysdate, or a call to your own function to generate something specific.

 

[TenGor]

Originally posted by Marco Kalter:
Two masters should not be a problem, as long as tables are filled in the correct order.

ok, so it seems to be an sequence problem of the tables as seen in the other thread.

The SQL function sends the parameter directly to Oracle, so this can be something simple like sysdate, or a call to your own function to generate something specific. [/QB]

so i can use my own sequence on the table f.e.:
SQL(seq_tab_a.nextval)
or had i to do
SQL(select seq_tab_a.nextval from dual)

 

[Marco Kalter]

The "SQL(seq_tab_a.nextval)" will do the trick.

 

[TenGor]

Thx

 

[TenGor]

Hy again,

can i use the value of the statement in an other column, too?

f.e. i fill the PK with "SQL(seq_tab_a.nextval)".
In my testdata i have a other column which is a text. So i want to nummerize it with the PK like this:
PK | Text
-----------
1 | Test_1
2 | Test_2
...

How can i do this in data generator?

 

[Marco Kalter]

Not really, but this should do the trick:

SQL('Test_' || to_char(seq_tab_a.curval))

 

[TenGor]

Hy Marco,

what i get is the following and this is excactly the value in my table (!):

'Test_'||to_char(SEQ_TAB_A.CURRVAL)

Used the following statement in Data Generator:
SQL('Test_'||to_char(SEQ_TAB_A.CURRVAL))

If i try this in SQL+ then i get the error that the squence SEQ_TAB_A "is not yet defined in this session".

 

[Marco Kalter]

Hmm, maybe the currval is evaluated before the nextval? Maybe you need to swap the nextval and currval expressions.

 

[TenGor]

Well, the SQL error has gone? Maybe a friday afternoon problem.
But i still get
'Test_'||to_char(SEQ_TAB_A.CURRVAL)
if i use
SQL('Test_'||to_char(SEQ_TAB_A.CURRVAL))
the same result with
'Test_' + SQL(to_char(SEQ_TAB_A.CURRVAL))

Perhaps i should say, that the column for the name is varchar2(100). If i use SQL(to_char(SEQ_TAB_A.CURRVAL) for a number column everything look ok.