Netrequire Server

Catalogue > db_merge.lua

A Lua implementation of the db.merge function. This is helpful if you need to customize the behaviour beyond the built-in function.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:
461:
462:
463:
464:
465:
466:
467:
468:
469:
470:
471:
472:
473:
474:
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
488:
489:
490:
491:
492:
493:
494:
495:
496:
497:
498:
499:
500:
501:
502:
503:
504:
505:
506:
507:
508:
509:
510:
511:
512:
513:
514:
515:
516:
517:
518:
519:
520:
521:
522:
523:
524:
525:
526:
527:
528:
529:
530:
531:
532:
533:
534:
535:
536:
537:
538:
539:
540:
541:
542:
543:
544:
545:
546:
547:
548:
549:
550:
551:
552:
553:
554:
555:
556:
557:
558:
559:
560:
561:
562:
563:
564:
565:
566:
567:
568:
569:
570:
571:
572:
-- $Revision: 1.11 $
-- $Date: 2012-12-06 17:11:49 $

--
-- The db_merge module
-- Copyright (c) 2011-2012 iNTERFACEWARE Inc. ALL RIGHTS RESERVED
-- iNTERFACEWARE permits you to use, modify, and distribute this file in accordance
-- with the terms of the iNTERFACEWARE license agreement accompanying the software
-- in which it is used.
--

require 'dbc'
require 'node'

local function trace(a,b,c,d) return end

local function OracleDateTimeCvt(N, R)
   R[#R+1] = 'TO_DATE('
   R[#R+1] = '\''
   R[#R+1] = tostring(N)
   R[#R+1] = '\''
   R[#R+1] = ', \'YYYY-MM-DD HH24:MI:SS\')'
end

local function StringValue(N, R, DbType)
   if N:isNull() then
      R[#R+1] = 'NULL'
   elseif N:S():match('0x') then
      R[#R+1] = tostring(N)
   else
      R[#R+1] = '\''
      R[#R+1] = DbType.Escape(tostring(N))
      R[#R+1] = '\'' 
   end
end

local function DateValue(N, R, DbType)
   if N:isNull() then
      R[#R+1] = 'NULL'
   elseif DbType.DateTimeCvt ~= nil then
      DbType.DateTimeCvt(N, R)
   else
      R[#R+1] = '\''
      R[#R+1] = tostring(N)
      R[#R+1] = '\''
   end
end

local function DoubleValue(N, R)
   if N:isNull() then
      R[#R+1] = 'NULL'
   else
      R[#R+1] = tostring(N)
   end      
end

local function IntegerValue(N, R)
   if N:isNull() then
      R[#R+1] = 'NULL'
   else
      R[#R+1] = tostring(N)
      trace(R)
   end    
end

local ValueTable={
   string=StringValue,   
   datetime=DateValue,
   double=DoubleValue,
   integer=IntegerValue,
}

local function MergeTableValue(N, R, DbType)
   ValueTable[N:nodeType()](N, R, DbType)     
end

local function MergeTableRow(T, R, DbType, merge_null) 
   -- it may be a little counter-intuitive to do a 
   -- sparse insert when merge_null = false but it
   -- means you get column defaults with the insert
   -- which is the desired behaviour
   R[#R+1] = '('
   for i=1, #T do
      if T[i]:nodeValue() ~= '' or
         (T[i]:nodeValue() == '' and merge_null) then
         if i ~= 1 and R[#R] ~= '(' then
            R[#R+1] = ', '
         end
         R[#R+1] = T[i]:nodeName()
      end
   end
   trace(R)
   R[#R+1] = ') VALUES('
   for i=1, #T do
      if T[i]:nodeValue() ~= '' or
         (T[i]:nodeValue() == '' and merge_null) then
         if i ~= 1 and R[#R] ~= ') VALUES(' then
            R[#R+1] = ', '
         end
         MergeTableValue(T[i], R, DbType)
      end
   end
   R[#R+1] = '); '
   trace(R)
end

local function UpdateTableRow(T, R, DbType, TableKeySet, merge_null)
   R[#R+1] = ' SET '  
   for i=1, #T do
      trace(T[i]:nodeValue())
      if T[i]:nodeValue() ~= '' or
         (T[i]:nodeValue() == '' and merge_null) then
         R[#R+1] = T[i]:nodeName()
         R[#R+1] = ' = '
         MergeTableValue(T[i], R, DbType)
         R[#R+1] = ', '
      end
   end
   trace(R)
   R[#R] = ' '
   R[#R+1] = ' WHERE '
   
   -- use local TableKeySet to match case for tables (T)
   -- to match the hard coded case in the VMD
   local tablekeyset = {}
   for i = 1, #TableKeySet do    
      for j = 1, #T do  
         local tname = T[j]:nodeName()
         if TableKeySet[i]:lower() == tname:lower() then 
            tablekeyset[i] = tname
         end
      end
   end
   trace(tablekeyset)
   
   for i = 1, #TableKeySet do    
      R[#R+1] = tablekeyset[i]
      R[#R+1] = ' = '
      MergeTableValue(T[tablekeyset[i]], R, DbType)
      R[#R+1] = ' AND '
   end   
   R[#R] = ' ' 
end

local function mergeKeys(T, R, TableKeySet, DbType)
   -- using NULL when we get a '' node value may seem
   -- counter-intuitive but it allows code which will
   -- (attempt) to do an insert as no row will be 
   -- found with a NULL key value this has two advantages
   -- (1) nicer error messages when insert fails
   -- (2) when an autoincrementing PK is used the 
   -- insert will SUCCEED which is the correct
   -- behaviour   
   for j=1, #TableKeySet do
      if j > 1 then
         R[#R+1] = ' AND '
      end
      R[#R+1] = TableKeySet[j]
      R[#R+1] = ' = '
      for k=1, #T do
         trace(j,TableKeySet[j],k)
         if T[k]:nodeName():lower() == TableKeySet[j]:lower() then
            if T[k]:nodeValue() == '' then 
               R[#R+1] = 'NULL'
            else
               MergeTableValue(T[k], R, DbType)
            end
         end
      end
   end
   trace(R)
end

local function SqlServerReplace(T, output, DbType, TableKeySet, P)
   local R = {}
   for i=1, #T do
      R[#R+1] = '\n'
      if TableKeySet[1] then
         R[#R+1] = 'IF (SELECT COUNT('
         R[#R+1] = TableKeySet[1]
         trace('hello',TableKeySet[1])
         R[#R+1] = ') FROM '
         R[#R+1] = T:nodeName()
         R[#R+1] = ' WHERE '
         mergeKeys(T[i], R, TableKeySet, DbType)
         R[#R+1] = ') = 1'
         R[#R+1] = '\n\t'
         R[#R+1] = 'UPDATE '
         R[#R+1] = T:nodeName()
         UpdateTableRow(T[i], R, DbType, TableKeySet, P.merge_null)
         R[#R+1] = '\n'
         R[#R+1] = 'ELSE'
         R[#R+1] = '\n\t'
      end
      R[#R+1] = 'INSERT INTO '
      R[#R+1] = T:nodeName()
      MergeTableRow(T[i], R, DbType, P.merge_null)
      output[#output+1] = table.concat(R) 
      trace(output[2])
      R={}         
   end
end

local function MySQLReplace(T, output, DbType, TableKeySet, P)
   local R = {}
   for i=1, #T do
      local r = {}
      R[#R+1] = '\n'
      local cnt
      if TableKeySet[1] then
         r[#r+1] = 'SELECT COUNT('
         r[#r+1] = TableKeySet[1]
         r[#r+1] = ') FROM '
         r[#r+1] = T:nodeName()
         r[#r+1] = ' WHERE '
         mergeKeys(T[i], r, TableKeySet, DbType)
         r[#r+1] = ';'
         local sql = table.concat(r)
         cnt = DB:query(sql)[1][1]:nodeValue()
      end
      if cnt == '1' then
         R[#R+1] = 'UPDATE '
         R[#R+1] = T:nodeName()
         UpdateTableRow(T[i], R, DbType, TableKeySet, P.merge_null)
         R[#R+1] = ';'
      else
         R[#R+1] = 'INSERT INTO '
         R[#R+1] = T:nodeName()
         MergeTableRow(T[i], R, DbType, P.merge_null)
      end
      R[#R+1] = '\n'
      output[#output+1] = table.concat(R) 
      trace(output[2])
      R={}         
   end
end

local function OracleReplace(T, output, DbType, TableKeySet, P)
   local R = {}
   for i=1, #T do
      if not P.transaction then
         R[#R+1] = 'DECLARE R INT; '
         R[#R+1] = '\n'
         R[#R+1] = 'BEGIN '
      end
      if TableKeySet[1] then
         R[#R+1] = '\n'
         R[#R+1] = 'SELECT COUNT('
         R[#R+1] = TableKeySet[1]
         R[#R+1] = ')INTO R '
         R[#R+1] = 'FROM '
         R[#R+1] = T:nodeName()
         R[#R+1] = ' WHERE '
         mergeKeys(T[i], R, TableKeySet, DbType)
         R[#R+1] = ';'
         R[#R+1] = '\n'
         R[#R+1] = 'IF R = 1 THEN'
         R[#R+1] = '\n\t'
         R[#R+1] = 'UPDATE '
         R[#R+1] = T:nodeName()
         UpdateTableRow(T[i], R, DbType, TableKeySet, P.merge_null)
         R[#R+1] = ';'
         R[#R+1] = '\n'
         R[#R+1] = 'ELSE'
      end
      R[#R+1] = '\n\t'
      R[#R+1] = 'INSERT INTO '
      R[#R+1] = T:nodeName()
      MergeTableRow(T[i], R, DbType, P.merge_null)
      R[#R+1] = '\n'
      if TableKeySet[1] then
         R[#R+1] = 'END IF;'
         R[#R+1] = '\n'
      end
      if not P.transaction then
         R[#R+1] = 'COMMIT;'
         R[#R+1] = 'END;'
      end
      output[#output+1] = table.concat(R) 
      trace(output[1])
      R={}         
   end
end

local function MySQLEscape(Value)
   return Value:gsub('["\'\\%z]', {
         ['"']  = '\\"', ['\0'] = '\\0',
         ["'"]  = "\\'", ['\\'] = '\\\\',
      })
end

local function SingleQuoteEscape(Value)
   return Value:gsub("'", "''")
end

local function PostgresEscape(Value)
   return Value:gsub("['\\]", {["'"] = "''", ["\\"] = "\\\\"})
end

local db_TYPE ={ 
   [db.MY_SQL]      = {TableKeyMap = false, Escape = MySQLEscape, Replace =  MySQLReplace,  Transaction = {BeginTransaction = 'START TRANSACTION;', Commit = 'COMMIT;'}},
   [db.ORACLE_OCI]  = {TableKeyMap = true, Escape = SingleQuoteEscape, Replace =  OracleReplace, DateTimeCvt = OracleDateTimeCvt, Transaction = {BeginTransaction = 'DECLARE R INT; BEGIN', Commit = 'COMMIT; END;'}},
   [db.ORACLE_ODBC] = {TableKeyMap = true, Escape = SingleQuoteEscape, Replace =  OracleReplace, DateTimeCvt = OracleDateTimeCvt, Transaction = {BeginTransaction = 'DECLARE R INT; BEGIN', Commit = 'COMMIT; END;'}},
   [db.SQLITE]      = {Escape = SingleQuoteEscape}, 
   [db.SQL_SERVER]  = {TableKeyMap = true, Escape = SingleQuoteEscape, Replace =  SqlServerReplace, Transaction = {BeginTransaction = 'BEGIN TRANSACTION;', Commit = 'COMMIT;'}},
   [db.POSTGRES]    = {Escape = PostgresEscape},
   [db.DB2]         = {Escape = SingleQuoteEscape},
   [db.INFORMIX]    = {Escape = SingleQuoteEscape},
   [db.INTERBASE]   = {Escape = SingleQuoteEscape},
   [db.FILEMAKER]   = {Escape = SingleQuoteEscape},
   [db.SYBASE_ASA]  = {Escape = SingleQuoteEscape},
   [db.SYBASE_ASE]  = {Escape = SingleQuoteEscape},
   [db.ACCESS]      = {Escape = SingleQuoteEscape}
}

local function MergeTable(T, output, DbType, TableKeySet, P)
   DbType.Replace(T, output, DbType, TableKeySet, P)
end

local function GetTableList(T)
   local R ={}
   for i=1, #T do
      R[i] = T[i]:nodeName()  
   end
   return R
end

local function CheckTable(T,Usage)
   if type(T) ~= 'table' then
      error(Usage,3)
   end
end

local function CheckParam(T, List, Usage)
   for i=1, #List do
      if not T[List[i]] then error('Missing parameter "'..List[i]..'"\n'..Usage, 3) end
   end
end

local Usage = [[
Merges table records into the database. This is a Special Implementation 
written in Lua it is NOT the default (builtin) implementation of db.merge.
It currently supports MySQL, SQL_SERVER and ORACLE_ODBC and will be extended to other databases in the future

Returns: nothing.
Accepts a table with the required entries:
   'name'        - database name/address. 
   'user'        - user name 
   'password'    - password 
   'data'        - set to a node table tree created using db.tables
   'api'         - database api, should be in the form: db.SQL_SERVER, db.MY_SQL, db.POSTGRES and etc.
   'transaction' - when this is true the merge will be performed as a single transaction (default = true)
These additional optional entries exist:
   'live'          - if true, the merge will be executed in the editor
   'pre_queries'   - table of queries to be executed BEFORE the merge in the same transaction
   'post_queries'  - table of queries to be executed AFTER the merge in the same transaction
   'table_order'   - optionally give an array table names in the order you would like to insert them
   'merge_null'    - when false do not update/insert any NULL values, to preserve current column value
                     defaults to true to match behaviour of the builtin db.merge()
   'table_key_map' - optional for SQL Server and Oracle - only for backward compatibility (IGNORED if supplied)
                     NOTE: the mapping is now generated automatically from database system tables

e.g. db.merge{
   name='test@localhost', 
   user='root', 
   password='secret',
   data=Out, 
   merge_null=false, -- do not use quotes
   api = db.SQL_SERVER,
   live = true
}
]]
 
local function MakeSql(P, TableKeyMap, DbType)
   local T = P.data
   if TableKeyMap == nil then
      TableKeyMap = {}
   end
   if not P.table_order then
      P.table_order=GetTableList(T)   
   end
   local output = {}
   if P.transaction then
      output = {DbType.Transaction.BeginTransaction}
   end
   if not P.preQueries then P.preQueries = {} end
   for k,v in ipairs(P.preQueries) do
      output[#output + 1] = '\n'..v..'\n'
   end
   for t=1, #P.table_order do
      local TableName = P.table_order[t]
      trace(TableName)
      trace(TableKeyMap)
      local keyname = {}
      -- loop to get keyname case independent
      for k,v in pairs(TableKeyMap) do
         trace (k,v)
         if k:lower() == TableName:lower() then
            keyname = v
         end
      end
      MergeTable(T[TableName], output, DbType, keyname, P)
   end
   if not P.postQueries then P.postQueries = {} end
   for k,v in ipairs(P.postQueries) do
      output[#output + 1] = '\n'..v..'\n'
   end
   if P.transaction then
      output[#output + 1] = DbType.Transaction.Commit
   end
--   trace(tostring(output))
   return output
end

local function mkMap(DB, sql)
   local r = DB:query(sql)
   local map = {}
   for i=1,#r do
      if r[i].HasKey:S():lower() == 'true' then
         if i > 1 and (r[i].Table:S():lower() ~= r[i-1].Table:S():lower()) then
            map[r[i].Table:S()] = {r[i].KeyCol:S()}
         else
            if map[r[i].Table:S()] == nil then
               map[r[i].Table:S()] = {}
               map[r[i].Table:S()][1] = r[i].KeyCol:S()
            else
               local len = #map[r[i].Table:S()]
               map[r[i].Table:S()][len+1] = r[i].KeyCol:S()
            end
         end
      else
         -- table without a PK
         map[r[i].Table:S()] = {}
      end
   end
   return map
end

-- NOTE: the ODBC source must point to the correct DB
-- MSSQL specific query (based on system views) 
-- NOTE: using CASE for backward compatibility
--       IIF only works with SQL Server 2012 (Denali)
local sqlMSSQL =[[
SELECT s.TABLE_NAME [Table] 
   ,CASE WHEN c.CONSTRAINT_TYPE = 'PRIMARY KEY'
      THEN 'TRUE' ELSE 'FALSE' END HasKey
   ,k.COLUMN_NAME KeyCol
   ,c.CONSTRAINT_NAME KeyName
   ,k.ORDINAL_POSITION IndexOrder
FROM INFORMATION_SCHEMA.TABLES s
   LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
      ON s.TABLE_NAME = c.TABLE_NAME 
      AND s.TABLE_TYPE = 'BASE TABLE'
      AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
   LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
      ON c.TABLE_NAME = k.TABLE_NAME 
      AND c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
ORDER BY k.TABLE_NAME, k.COLUMN_NAME, k.ORDINAL_POSITION]]

local sqlOracle =[[
SELECT t.table_name "Table"
   ,CASE WHEN k.constraint_type = 'P' THEN 'TRUE' ELSE 'FALSE' END "HasKey"
   ,c.column_name "KeyCol"
   ,k.constraint_name "KeyName"
   ,c.position "IndexOrder"
FROM sys.all_tables t
   LEFT JOIN all_constraints k
      ON k.table_name = t.table_name AND k.constraint_type = 'P'
   LEFT JOIN all_cons_columns c
      ON c.table_name = k.table_name AND c.constraint_name = k.constraint_name
WHERE t.owner NOT IN ( 'SYS','SYSTEM','XDB','CTXSYS','OUTLN','MDSYS','HR','FLOWS_FILES','APEX_040000')
ORDER BY "Table", c.position]]

local function sqlMySQL(dbname)
   local sql = [[SELECT s.TABLE_NAME "Table"
      ,CASE WHEN c.CONSTRAINT_NAME = 'PRIMARY' THEN 'TRUE' ELSE 'FALSE' END HasKey
      ,c.COLUMN_NAME KeyCol
      ,c.CONSTRAINT_NAME KeyName
      ,c.ORDINAL_POSITION IndexOrder
   FROM INFORMATION_SCHEMA.TABLES s
      LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
         ON s.TABLE_NAME = c.TABLE_NAME 
         AND s.TABLE_TYPE = 'BASE TABLE'
         AND c.CONSTRAINT_NAME = 'PRIMARY'
   WHERE s.TABLE_SCHEMA = ']]..dbname..[['
   ORDER BY c.TABLE_NAME, c.COLUMN_NAME, c.ORDINAL_POSITION]]
   return sql
end

local function dbName(api)
   if type(api) == type(db.MY_SQL) then
      for k,v in pairs(db) do
         if v == api then return 'db.'..k end
      end
   end
   return 'unknown API ('..tostring(api)..')'
end

--replace merge function
function db.merge(P)
   CheckTable(P, Usage)
   CheckParam(P, {'name','user','password', 'data','api'}, Usage) 
   local DbType = db_TYPE[P.api]

   if not DbType or DbType.Transaction == nil then 
      error('Merge using '..dbName(P.api)..' is not currently supported.', 2) end
   
   if type(P.data) ~= 'userdata' or P.data:nodeType() ~= 'table_collection' then
      error('The data parameter must be a table collection as produced from db.tables{}\n'..Usage,2)
   end 
   
   -- merge_null defaults to true to match legacy behaviour
   if P.merge_null == nil then P.merge_null = true end
   -- transaction defaults to true to match legacy behaviour
   if P.transaction == nil then P.transaction = true end

   local Sql = ''
   -- generate 'table_key_map' (ignore P.table_key_map)
   DB = dbc.Connection{ 
      api=P.api, 
      name=P.name,
      user =P.user,
      password = P.password,
      use_unicode = P.use_unicode,
      live = true}
   if P.api == db.SQL_SERVER then
      Sql = MakeSql(P, mkMap(DB, sqlMSSQL), DbType)
   elseif P.api == db.ORACLE_ODBC or P.api == db.ORACLE_OCI then
      Sql = MakeSql(P, mkMap(DB, sqlOracle), DbType)
   elseif P.api == db.MY_SQL then
      local dbname = P.name:gsub('@.*','')
      Sql = MakeSql(P, mkMap(DB, sqlMySQL(dbname)), DbType)
   --elseif P.api == db.SQLITE then
   -- need different logic for SQLite as it only has one system table
   -- sqlite_master will need to parse the table create statements 
   -- to find the primary key details... (not so hard)
   else
      -- use table_key_map in other cases
      -- NOTE: AT PRESENT THIS CODE WILL NEVER EXECUTE
      --       there will be an unsupported DB first (this could be changed)
      Sql = MakeSql(P.data, P.table_order, P.table_key_map, DbType, P)
   end

   trace (Sql)
   local SQL = ''
   if P.api == db.MY_SQL then
      -- execute transaction as a single "big query" does not work
      -- so always execute each line separately
      for i=1,#Sql do
         DB:execute(Sql[i], P.live)
      end
   elseif P.transaction then 
      -- execute queries as a single transaction
      for i=1,#Sql do
         SQL = SQL..Sql[i]
      end
      trace(SQL)
      DB:execute(SQL, P.live)
   else
      -- execute each query separately
      for i=1,#Sql do
         DB:execute(Sql[i], P.live)
      end
   end

   if P.live == nil or P.live == false then
      return "Not executed in editor.  To execute set live=true in input parameters", Sql
   else
      return "Executed in editor since live=true.", Sql
   end  
end