Kbase P20478: RAW TRANSFER- solution to save data in RAW FIELDS and be able to restore it even if the original DB
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
28/09/2009 |
|
Status: Unverified
GOAL:
RAW TRANSFER- solution to save data in RAW FIELDS and be able to restore it even if the original DB structure is changed
FACT(s) (Environment):
OpenEdge Category: Language (4GL/ABL)
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
The idea is to store also the original structure of the database.
The below example show how to store into a temp table a full database record
and also its structure and also how to create a new temp-table with the saved structure and restore there the saved record.
/*
Desc.: RAW TRANSFER- solution to save data in RAW FIELDS and be able to restore it even if the original DB structure is changed
Created: Mihai Cotar
*/
&SCOPED-DEFINE SeparatorRec '#'
&SCOPED-DEFINE SeparatorField '@'
DEF VAR hField AS HANDLE NO-UNDO.
DEF VAR hbtt1 AS HANDLE NO-UNDO.
DEF VAR hbttRaw AS HANDLE NO-UNDO.
DEF VAR httRaw AS HANDLE NO-UNDO.
DEF VAR hb1 AS HANDLE NO-UNDO.
DEF VAR htt1 AS HANDLE NO-UNDO.
DEF VAR hQuery AS HANDLE NO-UNDO.
DEF VAR gctable AS CHAR NO-UNDO INIT "customer".
DEF VAR gcStruct AS CHAR NO-UNDO.
DEF VAR gcTempStruct AS CHAR NO-UNDO.
/*I will use the following TEMP-TABLE to store the records in raw fields*/
DEF TEMP-TABLE ttRaw
FIELD RawField AS RAW
FIELD cStruct AS CHAR.
/* Check if there is any value(gcTable) table in current database */
FIND FIRST _file WHERE _file._FILE-NAME = gcTable NO-ERROR.
IF error-status:ERROR THEN DO:
MESSAGE "Table " gcTable " does't exist in the current database" VIEW-AS ALERT-BOX.
END.
/* Build a dynamic temp-table from gcTable and save the structure in a char variable*/
/* Add fields */
FOR EACH _field OF _file:
/*htt1:ADD-LIKE-FIELD(_field._field-name,string(_file._file-name + '.' + _field._field-name)).*/
gcStruct = gcStruct + (IF gcStruct <> '' THEN {&SeparatorRec} ELSE '') +
_field._field-name
+ {&SeparatorField} + _field._Data-Type
+ {&SeparatorField} + string(_field._Extent)
+ {&SeparatorField} + _field._Format
+ {&SeparatorField} + _field._Initial
+ {&SeparatorField} + (IF _field._Label <> ? THEN _field._Label ELSE '')
+ {&SeparatorField} + (IF _field._Col-Label <> ? THEN _field._Col-Label ELSE '')
.
END.
RUN BuildTT(INPUT-OUTPUT htt1, "MyTT", INPUT gcStruct).
/* get the buffer handle for the temp-table */
hbtt1 = htt1:DEFAULT-BUFFER-HANDLE.
hbtt1:BUFFER-CREATE.
/* Load some data into the temp-table */
IF valid-handle(hQuery) THEN DELETE OBJECT hQuery.
IF valid-handle(hb1) THEN DELETE OBJECT hb1.
CREATE BUFFER hb1 FOR TABLE gcTable.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hb1).
hQuery:QUERY-PREPARE(substitute('FOR each &1 exclusive-lock', gcTable)).
hQuery:QUERY-OPEN.
DO TRANSACTION:
hQuery:GET-FIRST.
/* load the current database record to temp-table */
hbtt1:BUFFER-COPY(hb1).
/* delete the original field from the database. Later I intend to restore it from the TTRaw table*/
hb1:DISABLE-LOAD-TRIGGERS(NO).
hb1:BUFFER-DELETE.
END.
/* save this record as raw data in the ttRaw temp table*/
IF valid-handle(hQuery) THEN DELETE OBJECT hQuery.
IF valid-handle(hb1) THEN DELETE OBJECT hb1.
CREATE QUERY hQuery.
CREATE BUFFER hb1 FOR TABLE hbtt1.
hQuery:SET-BUFFERS(hb1).
hQuery:QUERY-PREPARE('FOR each MyTT').
hQuery:QUERY-OPEN.
hQuery:GET-FIRST.
httRaw = TEMP-TABLE ttRaw:HANDLE.
CREATE B.UFFER hbttRaw FOR TABLE "ttRaw".
hbttRaw:BUFFER-CREATE.
hField = hbttRaw:BUFFER-FIELD("cStruct").
hField:BUFFER-VALUE = gcStruct.
hField = hbttRaw:BUFFER-FIELD("RawField").
hb1:RAW-TRANSFER(YES, hField).
/* #################################################################################################################
Now our record TTRaw.RawField, and a description of it it is in TTRaw.cStruct
We want now to build a temp table based on information stored in TTRaw.cStruct, extract
from ttRaw.RawField to the new created temp-table and display the data
*/
DEF VAR hNewTT AS HANDLE NO-UNDO.
DEF VAR hbNewTT AS HANDLE NO-UNDO.
DEF VAR i AS INT NO-UNDO.
hField = hbttRaw:BUFFER-FIELD("cStruct").
RUN BuildTT(INPUT-OUTPUT hNewTT, "MyNewTT", INPUT hField:BUFFER-VALUE).
hbNewTT = hNewTT:DEFAULT-BUFFER-HANDLE.
hField = hbttRaw:BUFFER-FIELD("RawField").
hbNewTT:RAW-TRANSFER(NO, hField).
/*DISPLAY all the fields of the record created MyNewTT*/
DO i = 1 TO hbNewTT:NUM-FIELDS:
hField = hbNewTT:BUFFER-FIELD(i).
MESSAGE hField:BUFFER-VALUE.
END.
/* Now I intend to restore this record in the database table gcTable*/
IF valid-handle(hb1) THEN DELETE OBJECT hb1.
CREATE BUFFER hb1 FOR TABLE gcTable.
DO transaction:
hb1:BUFFER-CREATE. /* Create a new record in database table */
hb1:BUFFER-COPY(hbNewTT).
END.
/* ################################################################################################################# */
PROCEDURE BuildTT:
/*
*/
DEF INPUT-OUTPUT PARAM iphtt AS HANDLE NO-UNDO.
DEF INPUT PARAM ipcTTName AS CHAR NO-UNDO.
DEF INPUT PARAM ipcFieldStruct AS CHAR NO-UNDO.
DEF VAR i AS INT NO-UNDO.
DEF VAR j AS INT NO-UNDO.
DEF VAR cTab AS CHAR NO-UNDO EXTENT 7.
CREATE TEMP-TABLE iphtt.
DO i = 1 TO NUM-ENTRIES(ipcFieldStruct, {&SeparatorRec}):
DO j = 1 TO 7:
cTab[j] = ENTRY(j, ENTRY(i, ipcFieldStruct, {&SeparatorRec}), {&SeparatorField}).
END.
iphtt:ADD-NEW-FIELD(
cTab[1], /* field name */
cTab[2], /* data type */
int(cTab[3]), /* extent */
cTab[4], /* format */
cTab[5], /* init value */
cTab[6], /* label */
cTab[7] /* column label */
).
END.
/* Prepare the temp-table */
iphtt:TEMP-TABLE-PREPARE("MyTT").
END PROCEDURE..