Fri Aug 19, 2022 2:35 pm
Login Register Lost Password? Contact Us

Please Note: The HPCC Systems forums are moving to Stack Overflow. We invite you to post your questions on Stack Overflow utilizing the tag hpcc-ecl ( This legacy forum will be active and monitored during our transition to Stack Overflow but will become read only beginning September 1, 2022.

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


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

    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
    #EXPORTXML(out, lay)
    #FOR (out)
      #FOR (Field)
            #ERROR('Unable to process child datasets.')
        #ELSEIF(REGEXFIND('data|set of',%'{@type}'%))
            #ERROR('Unable to process '+%'{@type}'%)
        #SET(sep,'+ \',\'+')

SHARED MAC_makeFieldListFromLayout(lay) := MACRO
    #EXPORTXML(out, lay)
    #FOR (out)
      #FOR (Field)


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

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


// 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});

// 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});

// 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});

// 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});
Posts: 444
Joined: Sat Oct 01, 2011 7:26 pm

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


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.


Community Advisory Board Member
Community Advisory Board Member
Posts: 1619
Joined: Wed Oct 26, 2011 7:40 pm

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


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!

Posts: 444
Joined: Sat Oct 01, 2011 7:26 pm

Return to Tips & Tricks

Who is online

Users browsing this forum: No registered users and 1 guest