# # ITSV GmbH # CCDB - Command and Control Database # # FILE: dquerymfile_SAVESELECTBIGFILE.txt # DESCRIPTION: DQUERY definition for DQUERY SAVESELECTBIGFILE # saves a big result from a SELECT into an external file using the databases "save-to-file" mechanism # # ATTENTION: MYSQL must be configured for allowing SELECT ... INTO OUTFILE # see: https://computingforgeeks.com/how-to-solve-mysql-server-is-running-with-the-secure-file-priv-error/ # # @querytitle SQL-Select direkt in Datei exportieren @querydescription exportiert das Ergebnis eines SQL-SELECT direkt mit Mitteln der Datenbank in ein managed file in CSV-Format @group DATAFILE @attributenames mftype:mfiletype:{{mftypeoptions}},mfilename:string:{{mfilenameoptions}},mfilelpath:mfilelpath:{{mfilelpathoptions}},mfileattribs:string:{{mfileattribsoptions}},cfgname:dbconfigname:{{cfgnameoptions}},select:string:{{selectoptions}},countselect:string:{{countselectoptions}},tabname:string:{{tabnameoptions}},impl:string:{{imploptions}},linesep:linesep:{{linesepoptions}},fieldsep:string:{{fieldsepoptions}},headfirstline:yesno:{{headfirstlineoptions}},mfiledirectory:string:{{mfiledirectoryoptions}},chunksize:string:{{chunksizeoptions}},startchunkpos:string:{{startchunkposoptions}},segmentsize:string:{{segmentsizeoptions}},maxsegmentcount:string:{{maxsegmentcountoptions}},filepersegment:noyes:{{filepersegmentoptions}} @mftypeoptions { labeltext: "Managed File Type", typedesc: "Type des Managed File, in das das Ergebnis des SELECT exportiert werden soll" } @mfilenameoptions { labeltext: "Dateiname", typedesc: "Original-Name der Datei, wenn sie downloaded wird" } @mfilelpathoptions { labeltext: "Logischer Pfad", typedesc: "Logischer managed File Pfad, an dem die Datei angelegt werden soll" } @mfileattribsoptions { labeltext: "Attribute", typedesc: "JSON-String mit Attributen, die zum Managed File gespeichert werden sollen", is_optional: true } @cfgnameoptions { labeltext: "Datenbank", typedesc: "Name der Datenbank-Konfiguration, in der das SELECT ausgeführt bzw. die Tabelle gelesen werden soll", is_optional: true } @selectoptions { labeltext: "Select-Statement", typedesc: "SQL-SELECT-Statement, das in der Datenbank ausgeführt werden soll. Wird hier nichts angegeben, so muss eine Tabelle angegeben werden", is_optional: true } @countselectoptions { labeltext: "Datensatz-Anzahl Select-Statement", typedesc: "SQL-SELECT-Statement, das ausgeführt werden soll, um die Anzahl der Datensätze zu ermitteln. Wird benötigt by CHUNKTYPE, wenn SELECT angegeben wird", is_optional: true } @tabnameoptions { labeltext: "Tabelle", typedesc: "Name der Tabelle, die in die Datei exportiert werden soll. Diese Angabe wird nur berücksichtigt, wenn kein Select-Statement angegeben wird.", is_optional: true } @imploptions { type: { structure: "scalar.enum", vlist: "DEFAULT:Vorgabe:benutze die Vorgabe (saveoutfile),"+ "SAVEOUT:SaveToOutFile:Verwende SQL-Kommando SELECT INTO OUTFILE,"+ "CSVTAB:CSVTableEngine:Verwende CSV-Engine-Tabelle,"+ "CHUNKWRITE:chunkwrite:Lese und Schreibe Ausgabedatei in begrenzten Paketen (chunks)" }, labeltext: "Implementierung", typedesc: "Wählt die Art aus, mit der die CCDB die Ausgabedatei erzeugt", is_optional: true } @linesepoptions { type: { structure: "scalar.enum", vlist: "DEFAULT:Vorgabe:benutze die Vorgabe (CRLF),"+ "CR:CarriageReturn:Carriage Return,"+ "LF:LineFeed:Line Feed,"+ "CRLF:CR+LF:CarriageReturn dann Line Feed,"+ "LFCR:LF+CR:Line Feed dann Carriage Return" }, labeltext: "Zeilentrenner", typedesc: "Zeichen(folge), durch die Zeilen (Datensätze) in der Datei getrennt sein sollen", is_optional: true } @fieldsepoptions { type: { structure: "scalar.enum", vlist: "DEFAULT:Vorgabe:benutze die Vorgabe - Semikolon/Strichpunkt (;),"+ "SEMICOLON:Semikolon:Strichpunkt (;),"+ "COMMA:Komma:Beistrich,"+ "TAB:Tabulator:Tabulator-Zeichen (Zeichencode 09),"+ "COLON:Doppelpunkt:Doppelpunkt(:)," }, labeltext: "Feldtrennzeichen", typetext: "Zeichen(folge), mit dem(der) Felder untereinander getrennt sein sollen", is_optional: true } @headfirstlineoptions { labeltext: "Kopf in erster Zeile", typetext: "gibt an, ob eine Kopfzeile mit Spaltenkopfnamen erstellt werden soll", is_optional: true, default: true } @mfiledirectoryoptions { labeltext: "Zielverzeichnispfad", typetext: "(absoluter) Verzeichnispfad, an dem das Ergebnis-Managed-File abgelegt werden soll", is_optional: true } @chunksizeoptions { labeltext: "Paketgröße", typetext: "Anzahl der Datensätze pro Paket (Vorgabe: 10000)", is_optional: true, default: 10000 } @startchunkposoptions { labeltext: "StartPaketbeginn", typetext: "Nummer des Datensatzes (0=erster), bei dem die Sicherung beginnen soll", is_optional: true, default: 0 } @segmentsizeoptions { labeltext: "Segmentgröße", typetext: "Größe eines Sicherungs-Segements in Datensätzen", is_optional: true, default: 1000000 } @maxsegmentcountoptions { labeltext: "Max Anzahl Segmente", typetext: "Maximale Anzahl von Segmenten, die in einer Ausführung gesichert werden", is_optional: true, default: 999999999999 } @filepersegmentoptions { labeltext: "Datei pro Segment", typetext: "Wenn \"Ja\", dann wird pro Segment eine Ausgabedatei erzeugt", is_optional: "true", default: "N" } @querytype function @function seqtrans.seqtrans @init.qexpression this.options.maxrecsteps = 1000000000; ~query.tsteps # # <>: prepare parameters # qexpression proc: { this.implementation = "saveoutfile"; if (this.query.impl) { switch (this.query.impl) { case "DEFAULT": this.implementation = "saveoutfile"; break; case "SAVEOUT": this.implementation = "saveoutfile"; break; case "CSVTAB": this.implementation = "csvtable"; break; case "CHUNKWRITE": this.implementation = "chunkwrite"; break; default: this.implementation = "saveoutfile"; break; } } else { this.implementation = "saveoutfile"; } if (this.query.mftype) { this.mfiletype = this.query.mftype; } else { this.mfiletype = "GENCSV"; } if (this.query.mfilename) { this.mfilename = this.query.mfilename; } else { this.mfilename = mfiletype + "_" + aux.syncUniqueNowId() + ".csv"; } if (this.query.mfilelpath) { this.mfilelpath = this.query.mfilelpath; } else { this.mfilelpath = "/"; } if (this.query.fieldsep) { this.fieldsep = ";"; switch (this.query.fieldsep) { case "DEFAULT": this.fieldsep = ";"; break; /* default is semicolon */ case "SEMICOLON": this.fieldsep = ";"; break; case "COLON": this.fieldsep = ":"; break; case "TAB": this.fieldsep = "\t"; break; case "COMMA": this.fieldsep = ","; break; default: this.errcoll.collect(null,"Illegal token \""+this.query.fieldsep+"\" for field separator",this.query); } } else { this.fieldsep = ";"; } if (this.query.linesep) { this.linesep = "\\r\\n"; /* line separator command line input format */ this.binlinesep = "\r\n"; /* binary line separator, used directly in data output */ switch (this.query.linesep) { case "DEFAULT": this.linesep = "\\r\\n"; this.binlinesep = "\r\n"; break; /* default is CRLF */ case "CR": this.linesep = "\\r"; this.binlinesep = "\r"; break; case "LF": this.linesep = "\\n"; this.binlinesep = "\n"; break; case "CRLF": this.linesep = "\\r\\n"; this.binlinesep = "\r\n"; break; case "LFCR": this.linesep = "\\n\\r"; this.binlinesep = "\n\r"; break; default: this.errcoll.collect(null,"Illegal token \""+this.query.linesep+"\" for line separator",this.query); } } else { this.linesep = "\\r\\n"; this.binlinesep = "\r\n"; } if (this.query.cfgname) { this.cfgname = this.query.cfgname; } else { this.cfgname = db.getCurrentConfig().getName(); } this.dbconfig = db.getConfigByName(this.cfgname); if (this.query.select) { this.select = this.query.select; if (this.query.countselect) { this.countselect = this.query.countselect; } else { if (this.implementation=='chunkwrite') { this.errcoll.collect(null,"SAVESELECTBIGFILE: implementation is "+this.implementation+", but no given",{ query: this.query }); } } } else if (this.query.tabname) { this.tabname = this.query.tabname; this.select = "SELECT * FROM " + this.tabname; this.countselect = "SELECT count(*) from " + this.tabname; } else { this.errcoll.collect(null,"Either select or tabname must be given",this.query); break proc; } if (this.query.mfiledirectory) { this.mfiledirectory = this.query.mfiledirectory; logger.debug(this.phead("MFILEDIRECTORY_GIVEN=")+this.mfiledirectory); } if (this.query.chunksize) { this.chunksize = this.query.chunksize + 0; } else { this.chunksize = 10000; } if (this.query.hasOwnProperty("mfileattribs")) { this.mfileattribs = JSON.parse(this.query.mfileattribs); } else { this.mfileattribs = {}; if (this.tabname) { this.mfileattribs.TABLE = this.tabname; } else if (this.select) { this.mfileattribs.SELECT = this.select; } } this.headfirstline = aux.getBoolOption(this.query,"headfirstline",true); this.startchunkpos = 0; if (this.query.startchunkpos) { this.startchunkpos = Number(this.query.startchunkpos); } logger.debug(this.phead("STARTCHUNKPOS="+this.startchunkpos)); this.segmentsize = this.chunksize; if (this.query.segmentsize) { this.segmentsize = Number(this.query.segmentsize); } logger.debug(this.phead("SEGMENTSIZE="+this.segmentsize)); this.filepersegment = false; if (this.query.filepersegment) { this.filepersegment = aux.boolFromString(this.query.filepersegment); } logger.debug(this.phead("FILEPERSEGMENT="+this.filepersegment)); } # # <>: get mysql base path and path to command line client executable # sql select @@basedir result_varname sqlres post_qexpression this.mysql_basedir = this.sqlres.getCellValue(0,0); this.mysql_bindir = this.mysql_basedir + "\\bin"; this.mysql_clientexe = this.mysql_bindir + "\\mysql.exe"; # # <>: create the (only/first) MFILE entry and create proper file path # pre_qexpression this.currentreadrow = 0; if (this.filepersegment) { this.effectivemfilename = this.mfilename + "_" + this.currentreadrow; } else { this.effectivemfilename = this.mfilename; } aexpression this.mfileCreateOptions = { donotwrite: true, mfileattribs: JSON.stringify(this.mfileattribs), filename: this.effectivemfilename, mfilelpath: this.mfilelpath }; if (this.mfiledirectory) { this.mfileCreateOptions.uploadDir = this.query.mfiledirectory; } logger.debug(this.phead("MFILECREATEOPTIONS: ")+aux.objTxt(this.mfileCreateOptions)) mfile.saveToMfile( null, /* no session needed */ this.mfiletype, null, /* no file data as we don't write here */ this.mfileCreateOptions, function(err,res) { logger.debug(this.phead("MFILECREATED")+".ERR: "+aux.objTxt(err)); if (err) { this.errcoll.collect(err,"Error in saveToMfile/creating managed file entry and path",res); this(); return; } if (res.resulttype!='object') { this.errcoll.collect(null,"Illegal result type \""+res.resulttype+"\" from saveToMfile",res); this(); return; } logger.debug(this.phead("MFILECREATED")+".RES: "+aux.objTxt(res.resultobject)); this.mfilepath = res.resultobject.mfilepath; this.mfilesqlpath = this.mfilepath.replace(/\\/g,"\\\\"); // physical file path name usable in SQL statements this.mfilewinpath = this.mfilepath.replace(/\//g,"\\"); // physical file path name usable in Windows environment this.mfileid = res.resultobject.mfileid; this.ppush([this.phead("MFILECREATED"),"mfileid="+this.mfileid,"",""]); this(); }.bind(this) ); # # <>: make a single row select to obtain column names and build a string list from them # pre_qexpression if (this.select.startsWith("SELECT ") || this.select.startsWith("select ")) { this.select = this.select.substr(7); } if (this.select.indexOf(" limit ")>=0) { /* in this case the select already contains a "limit" clause, * replace it temporarily with "limit 1,1" */ this.hselect = this.select.replace(/\s*limit\s*[0-9,]+/," limit 1,1") logger.debug(this.phead("UDBG.HSELECTLIMITREPLACE")+"HEADERSELECT=\""+this.hselect+"\""); } else { /* the select does not contain a "limit" clause, simply temporarily append a "limit 1,1" */ this.hselect = this.select + " limit 1,1" } sql select @@hselect@@ result_varname hres post_qexpression let cnames = new Array(); for (let ci=0; ci>: branch to specific implementation # pre_qexpression proc: { this.implab = "imp_" + this.implementation; if (!this.labels[this.implab]) { this.errcoll.collect(null,"Cannot execute Implementation \""+this.implementation+"\" of CCDB-Query \""+this.query.dataname+"\", seqtrans-label \""+this.implab+"\" does not exist"); break proc; } } goto @@implab@@ # # ------------------------------------------------------------------------------ # # <>: implementation "saveoutfile" # create SQL command file and invocation windows console command # imp_saveoutfile: qexpression /* * variable read_buffer_size limits maximum data read by SELECT INTO OUTFILE: * see also: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_select_into_disk_sync * so we have to crank it up * */ this.setbuffercommand = "SET GLOBAL read_buffer_size = 2000000000;"; let fsepclause = " FIELDS TERMINATED BY '"+this.fieldsep+"' "; let quotclause = " OPTIONALLY ENCLOSED BY '\"' "; if (this.headfirstline) { /* when headers in first line do 2 selects in a UNION ALL, the first selecting only literal strings * and the second the real data. The second is enclosed in parantheses to correctly apply an optional "limit" clause only to the second select */ this.dataselect = "SELECT "+this.headerlineclause+" UNION ALL (SELECT "+this.select+")"; } else { this.dataselect = "SELECT "+this.select; } let lsepclause = " LINES TERMINATED BY '"+this.linesep+"' "; this.selectsqlcmd = this.dataselect + " INTO OUTFILE '"+this.mfilesqlpath+"' "+ " "+fsepclause+ " "+quotclause+ " "+lsepclause; this.sqlfilename = aux.makeUniqueTempFileName() + "_SAVESELECTBIGFILE.sql"; fs.writeFileSync(this.sqlfilename,this.setbuffercommand+"\r\n"+this.selectsqlcmd); this.batcmd = this.mysql_clientexe + " -u"+this.dbconfig.getUser()+" -p"+this.dbconfig.getPassword()+" -Dccdb --local-infile=1 <"+this.sqlfilename; this.ppush([this.phead("CMDGENERATED"),"SQLFILE="+this.sqlfilename,"",""]); # # <>: execute select command for windows by invoking Shell command in a child process # aexpression childprocess.exec(this.batcmd, { timeout: 30000000 }, function(err,stdout,stderr) { logger.debug(this.phead("EXEC_CALLBACK")); if (err) { this.errcoll.collect(err,"Error in select/export to MFILEID=\""+this.query.mfid+"\", executing \""+this.batcmd+"\"", { stdout: stdout, stderr: stderr }); this(); return; } this.ppush([ this.phead("cmdlineexecuted"), { rawhtml: "Managed File "+this.mfileid+" generated"}, { rawhtml: "STDOUT:
"+stdout+"
" }, { rawhtml: "STDERR:
"+stderr+"
" } ]); this(); }.bind(this) ); # # <>: end of implementation "saveoutfile" # end # # ------------------------------------------------------------------------------ # # <>: implementation "csvtable" # this implementation creates a table with the output data handled by MySQL engine "CSV" # and then moves the thereby created csv file to the location of the managed file # imp_csvtable: sql show global variables like 'datadir' result_varname ddres post_qexpression proc: { if (this.ddres.getRowCount()!=1) { this.errcoll.collect(null,"Illegal row count when getting datadir location",this.ddres); break proc; } this.datadir = this.ddres.getCellValue(0,1).replace(/\//g,"\\"); } # # <>: generate SQL and DOS-CMD commands # qexpression this.ttabname = "SSTT_" + aux.syncUniqueNowID() + "_CSV"; this.cretabclause = "CREATE TABLE " + this.ttabname + " ENGINE=csv "; if (this.headfirstline) { this.dataselect = "SELECT "+this.headerlineclause+" UNION ALL (SELECT "+this.select+")"; } else { this.dataselect = "SELECT "+this.select; } this.selectsqlcmd = this.cretabclause + " " + this.dataselect; this.sqlfilename = aux.makeUniqueTempFileName() + "_SAVESELECTBIGFILE.sql"; fs.writeFileSync(this.sqlfilename,this.selectsqlcmd); this.batfilename = aux.makeUniqueTempFileName() + "_SAVESELECTBIGFILE.bat"; this.outfilename = aux.makeUniqueTempFileName() + "_SAVESELECTBIGFILE_OUT.txt"; this.batscript = this.mysql_clientexe + " -u"+this.dbconfig.getUser()+" -p"+this.dbconfig.getPassword()+" -Dccdb --local-infile=1 <"+this.sqlfilename + "\r\n" + "dir " + this.datadir + "ccdb\\" + this.ttabname + ".csv" + "\r\n" + "copy " + this.datadir + "ccdb\\" + this.ttabname + ".csv " + this.mfilewinpath + "\r\n"; fs.writeFileSync(this.batfilename,this.batscript); this.batcmd = this.batfilename + " >" + this.outfilename; this.ppush([this.phead("CMDGENERATED"),"SQLFILE="+this.sqlfilename,"BATFILE="+this.batfilename,"BATCMD="+this.batcmd]); # # <>: execute select command for windows # aexpression childprocess.exec(this.batcmd, { timeout: 30000000 }, function(err,stdout,stderr) { logger.debug(this.phead("EXEC_CALLBACK")); if (err) { this.errcoll.collect(err,"Error in select/export to MFILEID=\""+this.query.mfid+"\", executing \""+this.batcmd+"\"", { stdout: stdout, stderr: stderr }); this(); return; } this.ppush([ this.phead("cmdlineexecuted"), { rawhtml: "Managed File "+this.mfileid+" generated"}, { rawhtml: "STDOUT:
"+stdout+"
" }, { rawhtml: "STDERR:
"+stderr+"
" } ]); this(); }.bind(this) ); # # <>: now remove to CSV-Table again # sql drop table @@ttabname@@ result_varname dtres post_qexpression this.ppush([this.phead("TEMPTABLEDROPPED"),"dropped_temptable="+this.ttabname,"",this.dtres]); # # <>: end of implementation "csvtable" # end # # ------------------------------------------------------------------------------ # # <>: implementation 'chunkwrite' # this implementation reads the data chunk by chunk in order to avoid excessive memory usage and immediately writes the chunk data out to a file # only the content of one chunk is held in memory each time # # before loop: determine records to be processed by reading count # imp_chunkwrite: pre_qexpression this.writeheader = this.headfirstline; this.rowsinsegment = 0; this.tstep.sql = this.countselect; sql SELECT 'ERROR: this dummy select should be overwritten by the count-select SQL' as ERROR, 'ERROR: 2nd column to force error' as PARAMETER cfgname_varname cfgname result_varname cntres post_qexpression if (this.cntres.getRowCount()!=1) { this.errcoll.collect(null,"count select has not exactly one row as result",{ result: this.cntres }); } if (this.cntres.getColumnCount()!=1) { this.errcoll.collect(null,"count select has not exactly one column as result",{ result: this.cntres }); } this.rowstoprocess = this.cntres.getCellValue(0,0); this.rowsprocessed = 0; if (this.startchunkpos) this.rowsprocessed = this.startchunkpos; this.totalbyteswritten = 0; this.chunksinsegment = 0; this.bytesinsegment = 0; this.segmentswritten = 0; this.ppush([this.phead("ROWSTOPROCESS=")+this.rowstoprocess,"","",""]); # # # <>: prepare first (only) physical file to write # aexpression fs.open(this.mfilewinpath,"w", function(err,fd) { if (err) { this.errcoll.collect(err,"error opening output file for write",{ err: err, fd: fd }); return; } this.chunkfd = fd; this(); }.bind(this)); # # <>: if applicable, write header line to first (or only) physical file # if (this.headfirstline) aexpression fs.write( this.chunkfd, this.headerlinecsv + this.binlinesep, function(err,bytesWritten,rString) { if (err) { this.errcoll.collect(err,"error writing chunk at row " + this.rowsprocessed + " to output file",{ err: err, bytesWritten: bytesWritten }); return; } this.totalbyteswritten += bytesWritten; this.bytesinsegment += bytesWritten; this(); }.bind(this)); # # <>: the chunkwrite loop - check if we have more rows to read # imp_chunkwrite_loop: pre_qexpression this.currentreadrow = this.rowsprocessed; if (this.rowsprocessed>=this.rowstoprocess) goto imp_chunkwrite_complete # # <>: # if (!this.filepersegment) goto imp_chunkwrite_nextchunk # # <>: check if new segment to be opened # if (this.rowsinsegment>: close previous segment file # aexpression fs.close( this.chunkfd, function(err) { if (err) { this.errcoll.collect(err,"error closing off segment output file in chunkwrite", { rowstoprocess: this.rowstoprocess, rowsprocessed: this.rowsprocessed, rowsinsegment: this.rowsinsegment }); return; } this.segmentswritten++; this.ppush([this.phead("SEGMENT_WRITTEN"), "MFILEID="+this.mfileid, "ROWSTOPROCESS="+this.rowstoprocess+".ROWSPROCESSED="+this.rowsprocessed, "ROWSINSEGMENT="+this.rowsinsegment+".BYTESWRITTEN="+this.totalbyteswritten]); this(); }.bind(this)); # # <>: check if maximum segments written # if (this.segmentswritten>=this.maxsegmentcount) goto imp_chunkwrite_done # # <>: prepare next segment MFILE # pre_qexpression if (this.filepersegment) { this.effectivemfilename = this.mfilename + "_" + this.currentreadrow; } else { this.effectivemfilename = this.mfilename; } aexpression this.mfileCreateOptions = { donotwrite: true, mfileattribs: JSON.stringify(this.mfileattribs), filename: this.effectivemfilename, mfilelpath: this.mfilelpath }; if (this.mfiledirectory) { this.mfileCreateOptions.uploadDir = this.query.mfiledirectory; } logger.debug(this.phead("MFILECREATEOPTIONS: ")+aux.objTxt(this.mfileCreateOptions)) mfile.saveToMfile( null, /* no session needed */ this.mfiletype, null, /* no file data as we don't write here */ this.mfileCreateOptions, function(err,res) { logger.debug(this.phead("MFILECREATED")+".ERR: "+aux.objTxt(err)); if (err) { this.errcoll.collect(err,"Error in saveToMfile/creating managed file entry and path",res); this(); return; } if (res.resulttype!='object') { this.errcoll.collect(null,"Illegal result type \""+res.resulttype+"\" from saveToMfile",res); this(); return; } logger.debug(this.phead("MFILECREATED")+".RES: "+aux.objTxt(res.resultobject)); this.mfilepath = res.resultobject.mfilepath; this.mfilesqlpath = this.mfilepath.replace(/\\/g,"\\\\"); // physical file path name usable in SQL statements this.mfilewinpath = this.mfilepath.replace(/\//g,"\\"); // physical file path name usable in Windows environment this.mfileid = res.resultobject.mfileid; this.ppush([this.phead("MFILECREATED"),"mfileid="+this.mfileid,"",""]); this(); }.bind(this) ); # # <>: open next segment file # aexpression this.rowsinsegment = 0; this.bytesinsegment = 0; fs.open(this.mfilewinpath,"w", function(err,fd) { if (err) { this.errcoll.collect(err,"error opening output file for write",{ err: err, fd: fd }); return; } this.chunkfd = fd; this(); }.bind(this)); # # <>: write next segment file header (if applicable) # if (this.headfirstline) aexpression fs.write( this.chunkfd, this.headerlinecsv + this.binlinesep, function(err,bytesWritten,rString) { if (err) { this.errcoll.collect(err,"error writing chunk at row " + this.rowsprocessed + " to output file",{ err: err, bytesWritten: bytesWritten }); return; } this.totalbyteswritten += bytesWritten; this(); }.bind(this)); # # <>: read one chunk from database # imp_chunkwrite_nextchunk: pre_qexpression this.tstep.sql = "SELECT " + this.select + " limit " + this.currentreadrow + "," + this.chunksize; logger.debug(this.phead("CHUNKWRITE.READ_BEGIN.AT_ROW="+this.currentreadrow+".SELECT=")+this.tstep.sql.substring(0,100)+"..."); sql SELECT 'ERROR: this dummy select should be overwritten by the select SQL' as ERROR, 'ERROR: 2nd column to force error' as PARAMETER cfgname_varname cfgname result_varname creadres post_qexpression proc: { if (!this.creadres) { this.errcoll.collect(null,"no result reading chunk at row " + this.currentreadrow + " for chunkwrite"); break proc; } if (this.creadres.resulttype!="dbresult") { this.errcoll.collect(null,"result reading chunk at row " + this.currentreadrow + " is not dbresult", { creadres: this.creadres }); break proc; } this.chunkrowsread = this.creadres.getRowCount(); logger.debug(this.phead("CHUNKWRITE.READ_END.AT_ROW=")+this.currentreadrow+ ".ROWS_READ="+this.chunkrowsread+".TOTALROWSTOPROCESS="+this.rowstoprocess+".ROWSPROCESSED="+this.rowsprocessed); if ((this.chunkrowsread==0) && ((this.rowstoprocess-this.rowsprocessed)>0)) { this.errcoll.collect(null,"error in SAVESELECTBIGFILE/chunkwrite, reading from row "+this.currentreadrow+": no rows read although rows missing", { rowstoprocess: this.rowstoprocess, rowsprocessed: this.rowsprocessed }); break proc; } } # # <>: write one chunk to the file # aexpression this.csvdata = aux.csv_from_array_of_arrays(this.creadres.rows,{ csep: this.fieldsep }).join(this.binlinesep); fs.write( this.chunkfd, this.csvdata, function(err,bytesWritten,rString) { if (err) { this.errcoll.collect(err,"error writing chunk at row " + this.currentreadrow + " to file",{ err: err, bytesWritten: bytesWritten }); this(); return; } this.totalbyteswritten += bytesWritten; this.rowsprocessed += this.chunkrowsread; this.rowsinsegment += this.chunkrowsread; logger.debug( this.phead("CHUNKWRITTEN") +".ROWSTOPROCESS="+this.rowstoprocess +".ROWSPROCESSED="+this.rowsprocessed +".TOTALBYTESWRITTEN="+this.totalbyteswritten +".PERCENTCOMPLETE="+(Math.floor((this.rowsprocessed*1000000)/(this.rowstoprocess*100))/100) ); this(); }.bind(this)); # # <>: now repeat # goto imp_chunkwrite_loop # # <>: chunkwrite data complete, now close file # imp_chunkwrite_complete: aexpression fs.close( this.chunkfd, function(err) { if (err) { this.errcoll.collect(err,"error closing off output file in chunkwrite", { rowstoprocess: this.rowstoprocess, rowsprocessed: this.rowsprocessed }); return; } this.ppush([this.phead("COMPLETED"), "MFILEID="+this.mfileid, "ROWSTOPROCESS="+this.rowstoprocess+".ROWSPROCESSED="+this.rowsprocessed, "BYTESWRITTEN="+this.totalbyteswritten]); this(); }.bind(this)); # # <>: chunkwrite done # imp_chunkwrite_done: pre_qexpression this.ppush([this.phead("CHUNKWRITE_DONE"),"WRITTEN_MFILEID="+this.mfileid,"PHYSICAL_FILE="+this.mfilewinpath,""]); end