Mon Jun 24, 2019 7:27 pm
Login Register Lost Password? Contact Us


Generate SQL INSERT statements from a DATASET

Share ideas, code, best practices and techniques with other community members

Mon Jun 25, 2018 7:28 am Change Time Zone

Hi,

I've just had to create a load of SQL INSERT statements from an ECL result (DATASET), thought it might come in handy for someone else.
It does not cope with child datasets, SET OF or DATA field types, but does escape quotes and '\'. Warns of DATASET is really too large to be used in this way, and suggests bulk inserts be done instead. I've also appended some example uses.
Code: Select all
IMPORT STD;

/*
    Constructs MySql INSERT statments from a DATASET input.
    Does not cope with:
        Child datasets
        DATA fields
        SET OF fields
*/
SHARED MAC_content(reference,lay) := MACRO
    #UNIQUENAME(attrib)
    #SET(attrib,'')
    #UNIQUENAME(sep)
    #SET(sep,'')
    #UNIQUENAME(out)
    #EXPORTXML(out, lay)
    #FOR (out)
      #FOR (Field)
        #IF(REGEXFIND('table',%'{@type}'%))
            #ERROR('Unable to process child datasets.')
        #ELSEIF(REGEXFIND('data|set of',%'{@type}'%))
            #ERROR('Unable to process '+%'{@type}'%)
        #ELSEIF(REGEXFIND('boolean',%'{@type}'%))
            #APPEND(attrib,%'sep'%+'IF('+#TEXT(reference)+'.'+%'{@label}'%+',\'TRUE\',\'FALSE\')')       
        #ELSEIF(REGEXFIND('decimal|integer|real|unsigned',%'{@type}'%))
            #APPEND(attrib,%'sep'%+#TEXT(reference)+'.'+%'{@label}'%)
        #ELSE
            #APPEND(attrib,%'sep'%+'\'\\\'\'+f((STRING)'+#TEXT(reference)+'.'+%'{@label}'%+')+\'\\\'\'')
        #END
        #SET(sep,'+ \',\'+')
      #END
    #END
    %attrib%
ENDMACRO;

SHARED MAC_makeFieldListFromLayout(lay) := MACRO
    #UNIQUENAME(attrib)
    #SET(attrib,'')
    #UNIQUENAME(sep)
    #SET(sep,'(')
    #UNIQUENAME(out)
    #EXPORTXML(out, lay)
    #FOR (out)
      #FOR (Field)
        #APPEND(attrib,%'sep'%+%'{@label}'%)
        #SET(sep,',')
      #END
    #END
    %'attrib'%+')'
ENDMACRO;


EXPORT MAC_GenerateSQLStatementsFromDATASET(ds,TgtSQLTable) := FUNCTIONMACRO


    LOCAL f(STRING txt) := REGEXREPLACE('(\'|"|\\\\)',txt,'\\\\\\1');     // For MySql interpretor, escapes any quote delimiters and the escape character itself that is inside any STRING fields.

    {STRING SQLStatement} MakeSQLStatement(RECORDOF(ds) L) := TRANSFORM

        SELF.SQLStatement :=  'INSERT INTO '+ TgtSQLTable +' '
                             + MAC_makeFieldListFromLayout(RECORDOF(ds))
                             + ' VALUES ('
                             + MAC_content(L,RECORDOF(ds))
                             +');';
    END;

    ASSERT(COUNT(ds) < 1000,'DATASET of a size that you really should consider bulk insert.');
    RETURN PROJECT(ds,MakeSQLStatement(LEFT));

ENDMACRO;


////////////////////////
// Some Unit test cases:
////////////////////////

// Test 1:  Must fail indicating child datasets cannot be processed. But can process a child dataset from a de-referenced parent.
d1 := DATASET([{'ab\\c'}],{STRING txt});
d2 := DATASET([{'de\'f'},{'gh\"i'}],{STRING txt});
dt1  := DATASET([{FALSE,'',1.1,88.4,1,1.4,'',d1},{TRUE,'',1.2,99.52,2,2.5,'',d2},{FALSE,'',1.3,75.754,3,3.6,'',d1+d2}],{BOOLEAN b,QSTRING5 qs5,DECIMAL2 d2, UDECIMAL3 ud3,INTEGER5 id,REAL4 r4,UTF8 ut8,DATASET(RECORDOF(d1)) child});
//MAC_GenerateSQLStatementsFromDATASET(dt1,'Schema.TestA1');
//MAC_GenerateSQLStatementsFromDATASET(dt1.Child,'Schema.TestA2');


// Test 2: Must fail indicating DATA types cannot be processed
dt2  := DATASET([{'ab\'c',1.1,88.4,1,1.4,'CRUMP    BILL',x'ffed344a'},{' hhtryf',1.2,99.52,2,2.5,'',x'ffed344a'},{'treSDE',1.3,75.754,3,3.6,'ZASE',x'0011223344aa'}],{QSTRING5 qs5,DECIMAL2 d2, UDECIMAL3 ud3,INTEGER5 id,REAL4 r4,UTF8 ut8,DATA da1});
//MAC_GenerateSQLStatementsFromDATASET(dt2,'Schema.TestB1');

// Test 3: Must fail indicating 'SET OF' types cannot be processed
dt3  := DATASET([{'ab\'c',1.1,88.4,1,1.4,'CRUMP    BILL',[1,2,3,4]},{' hhtryf',1.2,99.52,2,2.5,'',[1,2,3,4]},{'treSDE',1.3,75.754,3,3.6,'ZASE',[1,2,3,4]}],{QSTRING5 qs5,DECIMAL2 d2, UDECIMAL3 ud3,INTEGER5 id,REAL4 r4,UTF8 ut8,SET OF UNSIGNED sou});
//MAC_GenerateSQLStatementsFromDATASET(dt3,'Schema.TestC1');

// Test 4: Success, must not fail.
dt4  := DATASET([{TRUE,'ab\'c',1.1,88.4,1,1.4,'CRUMP    BILL'},{FALSE,' hhtryf',1.2,99.52,2,2.5,''},{FALSE,'treSDE',1.3,75.754,3,3.6,'ZASE'}],{BOOLEAN b,QSTRING5 qs5,DECIMAL2 d2, UDECIMAL3 ud3,INTEGER5 id,REAL4 r4,UTF8 ut8});
//MAC_GenerateSQLStatementsFromDATASET(dt4,'Schema.TestD1');
Allan
 
Posts: 371
Joined: Sat Oct 01, 2011 7:26 pm

Mon Jun 25, 2018 3:13 pm Change Time Zone

Allan,

Cool code! :)

I had to change lines 67 & 68 to eliminate this error:
"Error: WHEN must be used to associate an action with a definition (68, 12)"
Code: Select all
    TooBig := ASSERT(COUNT(ds) < 1000,
                           'DATASET too big -- you really should consider bulk insert.');
    RETURN WHEN(PROJECT(ds,MakeSQLStatement(LEFT)),TooBig);
So, I have to assume you're using "legacy" in your environment, otherwise you would also be getting this error on syntax check. So for anybody outside LNRS, they would also need to make this change.

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1459
Joined: Wed Oct 26, 2011 7:40 pm

Tue Jun 26, 2018 1:40 pm Change Time Zone

Richard,

Thanks very much for this correction, will take on-board.

I must admit it's reassuring we have You and Bob + others as backstop reviewers!

Yours
Allan
Allan
 
Posts: 371
Joined: Sat Oct 01, 2011 7:26 pm


Return to Tips & Tricks

Who is online

Users browsing this forum: No registered users and 0 guests

cron