Raw Data transfer

[jmac13]

Hi I'm using open edge 10.2b

We have a program that tranfers records on central database to other 'child' databases

the following code is an example

        create tt-cp2_08. /*Temp table thats defined*/        
        raw-transfer syncloc.ToLocal.Data to tt-cp2_08.    
       /*syncloc.ToLocal.Data is a raw that stores the record */   
        
        /*find to seee it the record exists on this database*/
        find cp2_08 where        
             cp2_08.compno   = tt-cp2_08.compno   and        
             cp2_08.Level1   = tt-cp2_08.Level1   and        
             cp2_08.Level2   = tt-cp2_08.Level2        
             exclusive-lock no-error.        
        
        if not available cp2_08 then /*if not create record*/       
        do:        .        
            create cp2_08.        
        end.        
        
        buffer-copy tt-cp2_08 to cp2_08.  /*copy contense into record*/

the trouble with this is there is a procedure for each table we are doing is there a better way of doing it. I do store the table name within syncloc.ToLocal. So I know I could create a temp table dynamically but how do I search for the existence of the record dynamically? Can you pickup the indexes so you can check to see if we are going to try a create one that exists already?

 

[Stefan]

Yes this can all be done dynamically.

Two options:

1. simply attempt to :BUFFER-CREATE() the record and :BUFFER-COPY() the temp-table to the database with NO-ERROR, UNDOing if this fails due to a record already exists error
2. check the _file / _field / _index / _field-index (off the top of my head) to find the fields of the unique index and perform a :FIND-UNIQUE( "WHERE ..." ) to check if it exists first - you may want to store the meta info in your own temp-tables somewhere to not have to repeat this for every record

 

[jmac13]

Hi cheers

Just having ago at it now and I've got this far:

create temp-table hTempTable.
hTempTable:CREATE-LIKE(chrTableName).
hTempTable:TEMP-TABLE-PREPARE("ttRecs").

hPointerToTemp = hTempTable:DEFAULT-BUFFER-HANDLE.
 
  
hTranfser = buffer syncloc.ToLocal.Data:handle.
 
hPointerToTemp:raw-transfer(false,hTranfser:BUFFER-FIELD ("ToLocal")).
 
    CREATE QUERY hQuery. 
    hQuery:SET-BUFFERS(hPointerToTemp).
    hQuery:query-prepare("for each ttRecs").  /*Get me everything from temp table*/
    hQuery:query-open(). 
 
    hQuery:get-first(). 
    do while not hQuery:QUERY-OFF-END:
        /*in here create me proper record?*/
         

        hQuery:GET-NEXT(). 
    end.

just trying to work out how to create the record now? with hard coding it?

 

[jmac13]

Think I've got that bit done now it just need to do a check on it... to see if the record is there already

create temp-table hTempTable.
hTempTable:CREATE-LIKE(chrTableName).
hTempTable:TEMP-TABLE-PREPARE("ttRecs").
 
hPointerToTemp = hTempTable:DEFAULT-BUFFER-HANDLE.
 
 
hTranfser = buffer syncloc.ToLocal.Data:handle.
 
hPointerToTemp:raw-transfer(false,hTranfser:BUFFER-FIELD ("ToLocal")).
 
    CREATE QUERY hQuery. 
    hQuery:SET-BUFFERS(hPointerToTemp).
    hQuery:query-prepare("for each ttRecs").  /*Get me everything from temp table*/
    hQuery:query-open(). 
 
    CREATE BUFFER hPointerToRealTable FOR TABLE chrTableName.
 
    hQuery:get-first(). 
    do while not hQuery:QUERY-OFF-END:
        DO TRANSACTION:
            hPointerToRealTable:buffer-create().
            hPointerToRealTable:buffer-copy(hPointerToTemp).
         END.
 
        hQuery:GET-NEXT(). 
    end.

 

[Stefan]

That's about it.

I prefer:

DO WHILE hQuery:GET-NEXT():
   /* do something */
END.

instead of:

hQuery:GET-FIRST().
DO WHILE NOT hquery:QUERY-OFF-END:
   /* do something */
   hQuery:GET-NEXT().
END.

Also, very important - not sure if you're showing all your code, remember to DELETE the OBJECTs you CREATE!

 

[jmac13]

yeah thats much better I'll change it to do the same. Yeah I need to delete them this is currently a work in progress but you’re quite right. Do the Objects hang around even when the procedure is finished? (I know its good practise to do it anyway just wondering) I’ll let you know how I get on with my find. Might needs some more helping depending on how that goes. Just trying to make it as generic as possible

 

[jmac13]

Right I’m trying to make it generic to do a find on the record. If its on there then create otherwise update the record rather than creating it. Is there any generic way of doing this? I know you said about FIND-UNIQUE but not sure how I could make this generic I’ve got my record in my dynamic temp table. So wonder if I can use that some how? Because if I have to do a string it would have to be unique each time thus missing the point

 

[Stefan]

Do your tables have a unique index? If so, then you can query the database metaschema (_file / _field / _index / _index-field) to get the field names of the unique index. This can then be used to construct the query phrase for the :FIND-UNIQUE().

 

[jmac13]

Hi Stefan thanks for the reply,

well yeah my tables have indexs but for example my cusmast table (customer master table) has 8 indexies how do i know which is the right one for my record?

 

[Stefan]

How do you know which WHERE phrase to use in your static FINDs? The fields in the WHERE phrase are probably going to match a UNIQUE index.

Of your eight indexes are they all unique? Highly unlikely. Even then it does not have to be an issue, but you will need to check if a record exists with that uniqueness.

All our primary indexes are unique and functionally identify the record.

 

[jmac13]

well its dependant on what particular your doing to the table. Another example I have for a table is grades this only has 6 indexes 3 are unique and one of them is primary. How programmatically do I pick to search for? Also how can I check if the record is locked before doing the update?

 

[Stefan]

How do you pick the fields for the static where clause?

You can check if the record is locked by attempting find it with an EXCLUSIVE-LOCK and NO-WAIT - see the help file:
FIND-UNIQUE ( predicate-expression [ , lockmode [ , wait-mode ] ] )

 

[jmac13]

ermm I'll have to have a think about that... my level of knowledge on the system means i go to write a find and look at the DB and look at the table and see what index best to use.