Getting beaten up with WinBatch vs SQLite3 Program

[KeithW]

Greetings,

I do not get to play with WB too often these days and I seem to have to go thru the learning curve over & over.
The script below (STEP1) builds a bunch SQLite INSERT INTO lines.  The (STEP2) attempts to runs that .sql file
built in STEP1. 

At the TOP of STEP2 is what I manually run from within the desired folder AND it functions as desired.  However,
trying to call it from WB have proven to be a disaster, for me.  I am not getting any errors and I am not getting
any results, a hard place to be.

The MESSAGE prior to execution shows the command line to be correct with quotes around both the DB name &
the parameters as both paths contain spaces.  Not sure if I should be using Run, RunShell or ShellExecute BUT
whatever the case I cannot get the script to fire, but like I said what I show at the beginning of STEP loads the
table.

Can anyone, please point out what I am overlooking as I have spent hours and not gotten any closer other than
eliminate Error Message.

Regards,
Keith

Code Select Expand


DirChange("D:\Dropbox (CLC, Inc.)\CLC Deals\Duke Energy Progress\Monthly Reporting\2020 Mar\Client Files\SQL3")
Display(3,"qu_Z999_SQLite Script.sql", "Beginning File Creation")
filelist = "qu_z*.sql"
infoarray = FileInfoToArray(filelist, 1|2)
Goto STEP2

:STEP1
;infostr = StrCat("Number of files: ",infoarray[0,0],@CRLF)
;infostr = StrCat(infostr,"Total size of files: ",infoarray[0,1],@CRLF,@CRLF)
infostr = "DELETE FROM specific_obligors WHERE 1 = 1;" : @CRLF

For xx = 1 To infoarray[0,0]
   ;infostr = StrCat(infostr,"File Name: ",infoarray[xx,0],@CRLF)
   ;infostr = StrCat(infostr,"Root: ", FileRoot(infoarray[xx,0]),@CRLF)
   ;infostr = StrCat(infostr,"File Size: ",infoarray[xx,1],@CRLF)
   ;infostr = StrCat(infostr,"Last Modified: ",infoarray[xx,2],@CRLF)
   ;infostr = StrCat(infostr,"Last Accessed: ",infoarray[xx,3],@CRLF)
   ;infostr = StrCat(infostr,"Created: ",infoarray[xx,4],@CRLF)
   ;infostr = StrCat(infostr,"Attribute: ",infoarray[xx,5],@CRLF)
   ;infostr = StrCat(infostr,"Attribute Bitmask: ",infoarray[xx,6],@CRLF)
   ;infostr = StrCat(infostr,@CRLF)

item = FileRoot(infoarray[xx,0]) ;get base filename w/o extension
while (StrSub(item,1,6) <> "qu_z00" && StrSub(item,1,7) <> "qu_z999")
item = Strsub(item,4,strlen(item)-2)
infostr = StrCat(infostr,"INSERT INTO specific_obligors (AA_Script_Name, Amount) VALUES ('", "%item%" , "', 'n/a');",@CRLF)
break
end while

Next
;message("Output Stream", infostr)
tmpfile = FileCreateTemp("TMP")
FilePut(tmpfile, infostr)
FileCopy(tmpfile, "qu_z999_SQLite Script.sql",@False)
;AskFiletext("File Data","qu_z999_SQLite Script.sql",@UNSORTED,@SINGLE,@FALSE)
FileDelete(tmpfile)
Display(3,"qu_Z999_SQLite_Script.sql", "File Created !!")

:STEP2
Display(3,"qu_z999_SQLite_Script.sql", "Beginning Execution !!")


; The follow lines run the SQL file just fine, trying to duplicate in WinBatch...
;D:\Dropbox (CLC, Inc.)\CLC Deals\Duke Energy Progress\Monthly Reporting\2020 Mar\Client Files\SQL3>
;  c:\sqlite\sqlite3 DTC.db <"qu_Z999_SQLite_Script.sql"
;
;D:\Dropbox (CLC, Inc.)\CLC Deals\Duke Energy Progress\Monthly Reporting\2020 Mar\Client Files\SQL3>


CmdLine = "c:\sqlite\sqlite3.exe"
;DBname  =  "  " : Num2Char(34) : DirGet() : "DTC.db" : Num2Char(34) : "  <"
;Params  =  Num2Char(34) : DirGet() : "qu_z999_SQLite_Script.sql" : Num2Char(34)

DBname  =  StrCat("  ",Num2Char(34),DirGet(),"DTC.db",Num2Char(34),"  <")
Params  =  StrCat(Num2Char(34),DirGet(),"qu_z999_SQLite_Script.sql",Num2Char(34))

message("Command to Execute", CmdLine : DBname : Params )
;Run(CmdLine, DBNAME : DBname : Params)
;RunShell(CmdLine,Params,DirGet(),@ZOOMED,@WAIT)
ShellExecute(CmdLine,DBname : Params,DirGet(),@NORMAL,"")
Exit


[jmburton2001]

Hi Keith!

I had some of the same type of problems you're experiencing with Run, RunShell, and ShellExecute. The commands would run perfectly from a command prompt, but not from Winbatch. Please see this post (https://forum.winbatch.com/index.php?topic=2047.msg10515#msg10515) for some really good insights from Tony.

In my case it was modifying the format of the prompt command to be compatible with Winbatch.

[KeithW]

JM,

Thanx for the referenced post... it turns out to be fairly easy if you understand the process.  Following your referenced post and the reference within to another it became clear where I had gone wrong.  The following code does the whole process I desired to achieve.  If it help someone else, great !!  Thanx to all that participated.

Regards,
Keith

Code Select Expand


ScriptStart = TimeYmdHms()
DirChange("D:\Dropbox (CLC, Inc.)\CLC Deals\Duke Energy Progress\Monthly Reporting\2020 Mar\Client Files\SQL3")
Display(2,"qu_Z999_SQLite Script.sql", "Beginning File Creation")

ComSpec = Environment('comspec')
CmdLine = "c:\sqlite\sqlite3.exe"
DBname  =  StrCat("  ",Num2Char(34),DirGet(),"DTC.db",Num2Char(34),"  <")

filelist = "qu_z*.sql"
infoarray = FileInfoToArray(filelist, 1|2)
;Goto STEP2

:STEP1
;infostr = StrCat("Number of files: ",infoarray[0,0],@CRLF)
;infostr = StrCat(infostr,"Total size of files: ",infoarray[0,1],@CRLF,@CRLF)
infostr = "DELETE FROM specific_obligors WHERE 1 = 1;" : @CRLF

For xx = 1 To infoarray[0,0]
   ;infostr = StrCat(infostr,"File Name: ",infoarray[xx,0],@CRLF)
   ;infostr = StrCat(infostr,"Root: ", FileRoot(infoarray[xx,0]),@CRLF)
   ;infostr = StrCat(infostr,"File Size: ",infoarray[xx,1],@CRLF)
   ;infostr = StrCat(infostr,"Last Modified: ",infoarray[xx,2],@CRLF)
   ;infostr = StrCat(infostr,"Last Accessed: ",infoarray[xx,3],@CRLF)
   ;infostr = StrCat(infostr,"Created: ",infoarray[xx,4],@CRLF)
   ;infostr = StrCat(infostr,"Attribute: ",infoarray[xx,5],@CRLF)
   ;infostr = StrCat(infostr,"Attribute Bitmask: ",infoarray[xx,6],@CRLF)
   ;infostr = StrCat(infostr,@CRLF)

item = FileRoot(infoarray[xx,0]) ;get base filename w/o extension
while (StrSub(item,1,6) <> "qu_z00" && StrSub(item,1,7) <> "qu_z999")
item = Strsub(item,4,strlen(item)-2)
infostr = StrCat(infostr,"INSERT INTO specific_obligors (AA_Script_Name, Amount) VALUES ('", "%item%" , "', 'n/a');",@CRLF)
break
end while
Next

;message("Output Stream", infostr)
tmpfile = FileCreateTemp("TMP")
FilePut(tmpfile, infostr)
FileCopy(tmpfile, "qu_z999_SQLite Script.sql",@False)
;AskFiletext("File Data","qu_z999_SQLite Script.sql",@UNSORTED,@SINGLE,@FALSE)
FileDelete(tmpfile)
Display(2,"qu_Z999_SQLite Script.sql", "File Created !!")

:STEP2
Display(2,"qu_z999_SQLite Script.sql", "Beginning Execution !!")
Params  =  StrCat(Num2Char(34),DirGet(),"qu_z999_SQLite Script.sql",Num2Char(34))
ShellExecute(ComSpec, "/c " : CmdLine : DBname : Params, DirGet(), @HIDDEN, "")
Display(2,"qu_z999_SQLite Script.sql", "Execution Completed !!")

:STEP3
For xx = 1 To infoarray[0,0]
item = FileRoot(infoarray[xx,0]) ;get base filename w/o extension
while (StrSub(item,1,6) <> "qu_z00" && StrSub(item,1,7) <> "qu_z999")
item = Strsub(item,4,strlen(item)-2) : ".sql"
Display(2,"Running Script", "%item%" )
Params  =  StrCat(Num2Char(34), DirGet() ,"qu_" : "%item%", Num2Char(34) )
; Message("Script Run",CmdLine : DBname : Params)
TStart = TimeYmdHms()
;ShellExecute(ComSpec,"/c " : CmdLine : DBname : Params,DirGet(),@HIDDEN,"")
RunHideWait(ComSpec, "/c " : CmdLine : DBname : Params)
TEnd = TimeYmdHms()
Elapsed = TimeDiffSecs(TEnd,TStart)
Display(2,"Script Completed !!", "%item%, Executed in: %Elapsed% Seconds !!")
break
end while

Next
ScriptEnd = TimeYmdHms()
Elapsed = TimeDiffSecs(ScriptEnd,ScriptStart)
Message("specific_obligor","Processing Complete !!" : @CRLF : "Executed in: %Elapsed% Seconds" )
Exit