OpenEdge 11.5.1 x64
I'm attempting to massage some data from the database into a dataset but am running into a wall. The child table has different datatypes than the parent, so I thought that providing an alternative fill-where-string should get the data:
/* "database" tables */ define temp-table debtor field debtor as int64 field name as char index ttix is unique primary debtor. define temp-table stexob field key_value as char field seqnr_stexob as int field descr as char index ttix is unique primary key_value seqnr_stexob. create debtor. assign debtor.debtor = 1 debtor.name = "one". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 1. create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 2. create debtor. assign debtor.debtor = 2 debtor.name = "two". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 1. /* temp-tables */ define temp-table tmp-debtor serialize-name "debtor" field debtor as int64 xml-node-type "attribute" field name as char index ttix is unique primary debtor. define temp-table tmp-stexob serialize-name "stexob" field debtor_recid as recid field key_value as char serialize-hidden field seqnr_stexob as int xml-node-type "attribute" field descr as char index ttix is unique primary key_value seqnr_stexob. /* dataset */ define dataset debtors for tmp-debtor, tmp-stexob parent-id-relation for tmp-debtor, tmp-stexob parent-id-field debtor_recid . define data-source dsdebtor for debtor keys ( debtor ). define data-source dsstexob for stexob keys ( key_value, seqnr_stexob ). buffer tmp-debtor:handle:attach-data-source( data-source dsdebtor:handle ). buffer tmp-stexob:handle:attach-data-source( data-source dsstexob:handle ). buffer tmp-stexob:handle:set-callback-procedure( "before-fill", "before-fill-stexob" ). /* fill */ dataset debtors:fill(). procedure before-fill-stexob: define input parameter dataset for debtors. data-source dsstexob:fill-where-string = "where stexob.key_value = string( tmp-debtor.debtor )". end procedure. /* display result */ def var lcc as longchar no-undo. dataset debtors:handle:write-xml( "longchar", lcc, true ). message string( lcc ) view-as alert-box.
The problem is that none of the child records are retrieved. In a different attempt I have added the character key value to the parent and filled that in an after-row-fill procedure - this works, but gives me issues when importing data (from xml) into the dataset - so I was looking into adding a parent-relation - but with the fill-where-string not working it is not helping...:
/* "database" tables */ define temp-table debtor field debtor as int64 field name as char index ttix is unique primary debtor. define temp-table stexob field key_value as char field seqnr_stexob as int field descr as char index ttix is unique primary key_value seqnr_stexob. create debtor. assign debtor.debtor = 1 debtor.name = "one". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 1. create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 2. create debtor. assign debtor.debtor = 2 debtor.name = "two". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 1. /* temp-tables */ define temp-table tmp-debtor serialize-name "debtor" field debtor as int64 xml-node-type "attribute" field name as char field stexob_key_value as char index ttix is unique primary debtor. define temp-table tmp-stexob serialize-name "stexob" field debtor_recid as recid field key_value as char serialize-hidden field seqnr_stexob as int xml-node-type "attribute" field descr as char index ttix is unique primary key_value seqnr_stexob. /* dataset */ define dataset debtors for tmp-debtor, tmp-stexob data-relation for tmp-debtor, tmp-stexob relation-fields ( stexob_key_value, key_value ) . define data-source dsdebtor for debtor keys ( debtor ). define data-source dsstexob for stexob keys ( key_value, seqnr_stexob ). buffer tmp-debtor:handle:attach-data-source( data-source dsdebtor:handle ). buffer tmp-debtor:handle:set-callback-procedure( "after-row-fill", "after-row-fill-debtor" ). buffer tmp-stexob:handle:attach-data-source( data-source dsstexob:handle ). /* fill */ dataset debtors:fill(). procedure after-row-fill-debtor: define input parameter dataset for debtors. tmp-debtor.stexob_key_value = string( tmp-debtor.debtor ). end procedure. /* display result */ def var lcc as longchar no-undo. dataset debtors:handle:write-xml( "longchar", lcc, true ). message string( lcc ) view-as alert-box.
Am I missing something obvious?
Hi Stefan,
I do not have ABL expertise in this area. If you do not hear from someone on this forum, I suggest logging a call with Tech Support.
Hello, Stefan,
The quickest solution, to me, would be adding a type-compliant field in the temp-table and use that to guide the fill, and in the post-fill procedure replicate that field to the string field, by converting it. Does it make sense to you?
Nothing obvious. The datatype conversion is causing the AVM problems.
I got some data by doing having the same data-relation you have in the last example
define dataset debtors for tmp-debtor, tmp-stexob data-relation for tmp-debtor, tmp-stexob relation-fields ( stexob_key_value, key_value ) .
And I changed the before-fill-stexob procedure to
procedure before-fill-stexob: define input parameter dataset for debtors. data-source dsstexob:fill-where-string = "where stexob.key_value = " + quoter( tmp-debtor.debtor ). message 'before-fill-stexob' skip tmp-debtor.debtor data-source dsstexob:query:prepare-string view-as alert-box. end procedure.
and all the data was loaded:
<?xml version="1.0"?> <debtors xmlns:xsi="www.w3.org/.../XMLSchema-instance"> <debtor debtor="1"> <name>one</name> </debtor> <debtor debtor="2"> <name>two</name> </debtor> <stexob seqnr_stexob="1"> <debtor_recid xsi:nil="true"/> <descr/> </stexob> <stexob seqnr_stexob="2"> <debtor_recid xsi:nil="true"/> <descr/> </stexob> <stexob seqnr_stexob="1"> <debtor_recid xsi:nil="true"/> <descr/> </stexob> </debtors>
Note that it is NOT nested. If you add the NESTED qualifier to the DATA-RELATION statement you will see errors along the lines of
Failed to auto-prepare an automatic fill query. (11981)
This is because the field types are mismatched (I suspect).
[quote user="Murilo Pereira"]
The quickest solution, to me, would be adding a type-compliant field in the temp-table and use that to guide the fill, and in the post-fill procedure replicate that field to the string field, by converting it. Does it make sense to you?
[/quote]
That is what my first example attempts to do - the "database" temp-tables represent the structure of the database tables (for easy, no database required, code example). I was under the impression that with the prodataset events I should be able to iron out the database issues in the prodataset temp-table structure.
But with the fill-where-string on the stexob data-source not being applied, kills it.
I can add a query and fill it all manually, but that destroys the elegance of the dataset and the attached data-sources.
[quote user="Peter Judge"]
Nothing obvious. The datatype conversion is causing the AVM problems.
I got some data by doing having the same data-relation you have in the last example
1
2
3
|
define dataset debtors for tmp-debtor, tmp-stexob data-relation for tmp-debtor, tmp-stexob relation-fields ( stexob_key_value, key_value ) . |
And I changed the before-fill-stexob procedure to
1
2
3
4
5
6
7
8
9
10
11
12
13
|
procedure before- fill -stexob: define input parameter dataset for debtors. data-source dsstexob: fill-where-string = "where stexob.key_value = " + quoter ( tmp-debtor.debtor ). message 'before-fill-stexob' skip tmp-debtor.debtor data-source dsstexob: query : prepare-string view-as alert-box . end procedure . |
and all the data was loaded:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<? xml version = "1.0" ?> < debtors xmlns:xsi = "<a href=" http://www.w3.org/2001/XMLSchema-instance" target = "_blank" >www.w3.org/.../XMLSchema-instance"></ a > < debtor debtor = "1" > < name >one</ name > </ debtor > < debtor debtor = "2" > < name >two</ name > </ debtor > < stexob seqnr_stexob = "1" > < debtor_recid xsi:nil = "true" /> < descr /> </ stexob > < stexob seqnr_stexob = "2" > < debtor_recid xsi:nil = "true" /> < descr /> </ stexob > < stexob seqnr_stexob = "1" > < debtor_recid xsi:nil = "true" /> < descr /> </ stexob > </ debtors > |
Note that it is NOT nested. If you add the NESTED qualifier to the DATA-RELATION statement you will see errors along the lines of
Failed to auto-prepare an automatic fill query. (11981)
This is because the field types are mismatched (I suspect).
[/quote]
As soon as the query is nested (with matching data types) the child records are not considered part of their parent... but... relooking at the code, of the /first/ example - the part missing is the parent key not being filled. So for creating a dataset based on the database, the following seems to do the trick:
/* "database" tables */ define temp-table debtor field debtor as int64 field name as char index ttix is unique primary debtor. define temp-table stexob field key_value as char field seqnr_stexob as int field descr as char index ttix is unique primary key_value seqnr_stexob. create debtor. assign debtor.debtor = 1 debtor.name = "one". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 1 stexob.descr = "1.1". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 2 stexob.descr = "1.2". create debtor. assign debtor.debtor = 2 debtor.name = "two". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 1 stexob.descr = "2.1" . /* temp-tables */ define temp-table tmp-debtor serialize-name "debtor" field debtor as int64 xml-node-type "attribute" field name as char field stexob_key_value as char serialize-hidden index ttix is unique primary debtor. define temp-table tmp-stexob serialize-name "stexob" field key_value as char serialize-hidden field seqnr_stexob as int xml-node-type "attribute" field descr as char index ttix is unique primary key_value seqnr_stexob. /* dataset */ define dataset debtors for tmp-debtor, tmp-stexob data-relation for tmp-debtor, tmp-stexob relation-fields ( stexob_key_value, key_value ) foreign-key-hidden nested. . define data-source dsdebtor for debtor keys ( debtor ). define data-source dsstexob for stexob keys ( key_value, seqnr_stexob ). buffer tmp-debtor:handle:attach-data-source( data-source dsdebtor:handle ). buffer tmp-debtor:handle:set-callback-procedure( "after-row-fill", "after-row-fill-debtor" ). buffer tmp-stexob:handle:attach-data-source( data-source dsstexob:handle ). buffer tmp-stexob:handle:set-callback-procedure( "before-fill", "before-fill-stexob" ). /* fill */ dataset debtors:fill(). procedure after-row-fill-debtor: define input parameter dataset for debtors. tmp-debtor.stexob_key_value = string( debtor.debtor ). end procedure. procedure before-fill-stexob: define input parameter dataset for debtors. data-source dsstexob:fill-where-string = substitute( "where stexob.key_value = &1", quoter( tmp-debtor.debtor ) ). end procedure. /* display result */ def var lcc as longchar no-undo. dataset debtors:handle:write-xml( "longchar", lcc, true ). message string( lcc ) view-as alert-box.
I will have to kick this code around a bit more to see if it is really doing what I want. Getting to this stage I have had various moments that I thought I had it figured but then something went wrong again.
Thank you both for the comments.
Grrrr.... second message was on the initial longchar... but nearly there... The combination of data-relation and parent-id-relation on the same two buffers is messing up the read-xml.
With only the parent-id-relation, the recids of the parent are filled, but when there is also a data-relation, the recids of the parent are /not/ filled.
If the header data type fixed field is not serialized, the data-relation import fails to find the children.
So back to keeping two variants on the dataset for read / write - the trick to getting the data out needs to be reversed for getting the data in (ie add a dummy key field to the child instead of to the parent). Some additional processing will be required to get the dataset back into the database:
/* "database" tables */ define temp-table debtor field debtor as int64 field name as char index ttix is unique primary debtor. define temp-table stexob field key_value as char field seqnr_stexob as int field descr as char index ttix is unique primary key_value seqnr_stexob. create debtor. assign debtor.debtor = 1 debtor.name = "one". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 1 stexob.descr = "1.1". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 2 stexob.descr = "1.2". create debtor. assign debtor.debtor = 2 debtor.name = "two". create stexob. assign stexob.key_value = string( debtor.debtor ) stexob.seqnr_stexob = 1 stexob.descr = "2.1" . /* temp-tables */ define temp-table otmp-debtor serialize-name "debtor" field debtor as int64 xml-node-type "attribute" field name as char field stexob_key_value as char serialize-hidden index ttix is unique primary debtor. define temp-table otmp-stexob serialize-name "stexob" field key_value as char xml-node-type "attribute" field seqnr_stexob as int xml-node-type "attribute" field descr as char index ttix01 is primary unique key_value seqnr_stexob. /* dataset */ define dataset odebtors serialize-name "debtors" for otmp-debtor, otmp-stexob data-relation for otmp-debtor, otmp-stexob relation-fields ( stexob_key_value, key_value ) foreign-key-hidden nested . define data-source dsdebtor for debtor keys ( debtor ). define data-source dsstexob for stexob keys ( key_value, seqnr_stexob ). buffer otmp-debtor:handle:attach-data-source( data-source dsdebtor:handle ). buffer otmp-debtor:handle:set-callback-procedure( "after-row-fill", "after-row-fill-debtor" ). buffer otmp-stexob:handle:attach-data-source( data-source dsstexob:handle ). buffer otmp-stexob:handle:set-callback-procedure( "before-fill", "before-fill-stexob" ). /* fill */ dataset odebtors:fill(). procedure after-row-fill-debtor: define input parameter dataset for odebtors. otmp-debtor.stexob_key_value = string( debtor.debtor ). end procedure. procedure before-fill-stexob: define input parameter dataset for odebtors. data-source dsstexob:fill-where-string = "where stexob.key_value = string( otmp-debtor.debtor )" . end procedure. /* display result */ def var lccout as longchar no-undo. dataset odebtors:handle:write-xml( "longchar", lccout, true ). message string( lccout ) view-as alert-box. /* and now import it again */ define temp-table itmp-debtor serialize-name "debtor" field debtor as int64 xml-node-type "attribute" field name as char index ttix is unique primary debtor. define temp-table itmp-stexob serialize-name "stexob" field debtor as int64 xml-node-type "attribute" field seqnr_stexob as int xml-node-type "attribute" field descr as char field key_value as char serialize-hidden index ttix01 is primary unique debtor seqnr_stexob. define dataset idebtors serialize-name "debtors" for itmp-debtor, itmp-stexob data-relation for itmp-debtor, itmp-stexob relation-fields ( debtor, debtor ) foreign-key-hidden nested . dataset idebtors:handle:read-xml( "longchar", lccout, "empty", ?, ? ). for each itmp-stexob: itmp-stexob.key_value = string( itmp-stexob.debtor ). end. def var lccin as longchar no-undo. dataset idebtors:handle:write-xml( "longchar", lccin, true ). message string( lccin ) skip lccin = lccout view-as alert-box.
Now let's see how long I can kick at this... :-)