Dataset with custom fill query for children

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

And I changed the before-fill-stexob procedure to

and all the data was loaded:

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... :-)