Problem with BCP through ShellExecute

I am writing a procedure that takes a file and imports it into a table. It is a simple file that was exported using bcp, and I'm trying to use a bcp call through ShellExecute to import the file back in. The problem I'm running into is that I make the call, the command prompt flashes (showing text that I can't read because it flashes too fast), then no data is imported. I have run the script manually, and it inserts fine, but my call from my program is not working. Any thoughts?

Here's my code:

strCommand := db.dbo.table in "file name" -S server\instance,port -U username -P password -N

ShellExecute(Application.Handle, 'open', pchar('bcp.exe'), pchar(strCommand), nil, SW_SHOWNORMAL);
aceswildab1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Emmanuel PASQUIERFreelance Project ManagerCommented:
have you tried with specifying the full exe path ?
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
you can try with this, if it works for you it will return the output as a string
You can try with Capture = True (will use pipe for both stderror and stdoutput to get the resulting text) or Capture = False (will use command line redirection of the output to a temp file)
Depending on the applications, one method works better than the other.
Env parameter is a TstringList containing environment variable you might want to set, IncludeParentEnv will run the exe with the parent application environment. both have default values, so you don't have to bother.

for example :
ShowMessage(RunDosApp('C:\BCPPath\Bcp.exe', 'C:\SomeWorkingDir', True));
unit BatchUtils;

interface

Uses SysUtils,Classes;

Function RunDosApp(DosApp,CurDir:String;Capture:Boolean=False;Env:TStringList=nil;IncludeParentEnv:Boolean=True):String;

implementation

uses
  Forms, ShellApi,Windows, PJEnvVars;

Function RunDosApp(DosApp,CurDir:String;Capture:Boolean=False;Env:TStringList=nil;IncludeParentEnv:Boolean=True):String;
const ReadBuffer = 2400;
var
 Security : TSecurityAttributes;
 ReadPipe,WritePipe : THandle;
 start : TStartUpInfo;
 ProcessInfo : TProcessInformation;
 Buffer : Pchar;
 BytesRead : DWord;
 Apprunning : DWord;
 StrL:TStringList;
 BufSize: Integer;         // env block size
 EnvBuffer: PChar;            // env block
begin
 Result:='';
 With Security do
  begin
   nlength := SizeOf(TSecurityAttributes) ;
   binherithandle := true;
   lpsecuritydescriptor := nil;
  end;
 if Not Capture then
  begin
   DosApp:=DosApp+' >RES.TMP';
   DeleteFile(PChar(CurDir+'RES.TMP'));
  end;
 if Createpipe (ReadPipe, WritePipe, @Security, 0) then
  begin
   if Env=nil then EnvBuffer:=nil Else
    begin
     EnvBuffer := StrAlloc(2000);
     BufSize := CreateEnvBlock(Env, IncludeParentEnv, True, nil, 2000);
     if BufSize>2000 then
      begin
       StrDispose(EnvBuffer);
       EnvBuffer := StrAlloc(BufSize);
       CreateEnvBlock(Env, IncludeParentEnv, True, EnvBuffer, BufSize);
      end;
    end;
   Buffer := AllocMem(ReadBuffer + 1) ;
   FillChar(Start,Sizeof(Start),#0) ;
   start.cb := SizeOf(start) ;
   if Capture then
    begin
     start.hStdOutput := WritePipe;
     start.hStdError := WritePipe;
     start.dwFlags := STARTF_USESHOWWINDOW+STARTF_USESTDHANDLES ;
    end Else start.dwFlags := STARTF_USESHOWWINDOW;
   start.wShowWindow := SW_HIDE;
   if CreateProcess(nil,
           PChar(DosApp),
           @Security,
           @Security,
           true,
           NORMAL_PRIORITY_CLASS,
           EnvBuffer,
           PChar(CurDir),
           start,
           ProcessInfo) then
    begin
     CloseHandle(WritePipe) ;
     repeat
      Apprunning := WaitForSingleObject(ProcessInfo.hProcess,100) ;
      Application.ProcessMessages;
     until (Apprunning <> WAIT_TIMEOUT) ;
     if Capture then
      begin
       Repeat
        BytesRead := 0;
        ReadFile(ReadPipe,Buffer[0], ReadBuffer,BytesRead,nil) ;
        Buffer[BytesRead]:= #0;
        OemToAnsi(Buffer,Buffer) ;
        Result:=Result+ String(Buffer) ;
       until (BytesRead=0) ;
      end;
    end Else
    begin
      Capture:=True;
      CloseHandle(WritePipe) ;
    end;
   CloseHandle(ReadPipe) ;
   FreeMem(Buffer);
   StrDispose(EnvBuffer);
   CloseHandle(ProcessInfo.hProcess) ;
   CloseHandle(ProcessInfo.hThread) ;
   if Not Capture then
    begin
     StrL:=TStringList.Create;
     try
      StrL.LoadFromFile(CurDir+'RES.TMP');
      Result:=StrL.Text;
     finally
      StrL.Free;
     end;
    end;
  end;
end;

end.

Open in new window

0
aceswildab1Author Commented:
I tried that. Still nothing.
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Emmanuel PASQUIERFreelance Project ManagerCommented:
sorry, it will not compile without another unit.
Here is a complete standalone BatchUtils unit

tell us what it does, even if it not works
unit BatchUtils;

interface

Uses SysUtils,Classes;

Function RunDosApp(DosApp,CurDir:String;Capture:Boolean=False;Env:TStringList=nil;IncludeParentEnv:Boolean=True):String;

implementation

uses
  Forms, ShellApi,Windows;

function GetAllEnvVars(const Vars: TStrings): Integer;
var
  PEnvVars: PChar;    // pointer to start of environment block
  PEnvEntry: PChar;   // pointer to an env string in block
begin
  // Clear the list
  if Assigned(Vars) then
    Vars.Clear;
  // Get reference to environment block for this process
  PEnvVars := GetEnvironmentStrings;
  if PEnvVars <> nil then
  begin
    // We have a block: extract strings from it
    // Env strings are #0 separated and list ends with #0#0
    PEnvEntry := PEnvVars;
    try
      while PEnvEntry^ <> #0 do
      begin
        if Assigned(Vars) then
          Vars.Add(PEnvEntry);
        Inc(PEnvEntry, StrLen(PEnvEntry) + 1);
      end;
      // Calculate length of block
      Result := (PEnvEntry - PEnvVars) + 1;
    finally
      // Dispose of the memory block
      FreeEnvironmentStrings(PEnvVars);
    end;
  end
  else
    // No block => zero length
    Result := 0;
end;

function CreateEnvBlock(const NewEnv: TStrings;
  const IncludeCurrent: Boolean;
  const Buffer: Pointer;
  const BufSize: Integer): Integer;
var
  EnvVars: TStringList; // env vars in new block
  Idx: Integer;         // loops thru env vars
  PBuf: PChar;          // start env var entry in block
  S:String;
  P:Integer;
begin
  // String list for new environment vars
  EnvVars := TStringList.Create;
  try
    // include current block if required
    if IncludeCurrent then
      GetAllEnvVars(EnvVars);
    // store given environment vars in list
    if Assigned(NewEnv) then for Idx:=0 to NewEnv.Count-1 do
     begin
      S:=NewEnv[Idx];
      P:=Pos('=',S);
      if P>0 Then
       begin
        if EnvVars.Values[Copy(S,1,P-1)]<>'' Then
         begin
          EnvVars.Values[Copy(S,1,P-1)]:=Copy(S,P+1,1000);
         End Else P:=0;
       end;
      if P<=0 Then EnvVars.Add(S);
     end;
    // Calculate size of new environment block
    Result := 0;
    for Idx := 0 to Pred(EnvVars.Count) do
      Inc(Result, Length(EnvVars[Idx]) + 1);
    Inc(Result);
    // Create block if buffer large enough
    if (Buffer <> nil) and (BufSize >= Result) then
    begin
      // new environment blocks are always sorted
      EnvVars.Sorted := True;
      // do the copying
      PBuf := Buffer;
      for Idx := 0 to Pred(EnvVars.Count) do
      begin
        StrPCopy(PBuf, EnvVars[Idx]);
        Inc(PBuf, Length(EnvVars[Idx]) + 1);
      end;
      // terminate block with additional #0
      PBuf^ := #0;
    end;
  finally
    EnvVars.Free;
  end;
end;

Function RunDosApp(DosApp,CurDir:String;Capture:Boolean=False;Env:TStringList=nil;IncludeParentEnv:Boolean=True):String;
const ReadBuffer = 2400;
var
 Security : TSecurityAttributes;
 ReadPipe,WritePipe : THandle;
 start : TStartUpInfo;
 ProcessInfo : TProcessInformation;
 Buffer : Pchar;
 BytesRead : DWord;
 Apprunning : DWord;
 StrL:TStringList;
 BufSize: Integer;         // env block size
 EnvBuffer: PChar;            // env block
begin
 Result:='';
 With Security do
  begin
   nlength := SizeOf(TSecurityAttributes) ;
   binherithandle := true;
   lpsecuritydescriptor := nil;
  end;
 if Not Capture then
  begin
   DosApp:=DosApp+' >RES.TMP';
   DeleteFile(PChar(CurDir+'RES.TMP'));
  end;
 if Createpipe (ReadPipe, WritePipe, @Security, 0) then
  begin
   if Env=nil then EnvBuffer:=nil Else
    begin
     EnvBuffer := StrAlloc(2000);
     BufSize := CreateEnvBlock(Env, IncludeParentEnv, nil, 2000);
     if BufSize>2000 then
      begin
       StrDispose(EnvBuffer);
       EnvBuffer := StrAlloc(BufSize);
       CreateEnvBlock(Env, IncludeParentEnv, EnvBuffer, BufSize);
      end;
    end;
   Buffer := AllocMem(ReadBuffer + 1) ;
   FillChar(Start,Sizeof(Start),#0) ;
   start.cb := SizeOf(start) ;
   if Capture then
    begin
     start.hStdOutput := WritePipe;
     start.hStdError := WritePipe;
     start.dwFlags := STARTF_USESHOWWINDOW+STARTF_USESTDHANDLES ;
    end Else start.dwFlags := STARTF_USESHOWWINDOW;
   start.wShowWindow := SW_HIDE;
   if CreateProcess(nil,
           PChar(DosApp),
           @Security,
           @Security,
           true,
           NORMAL_PRIORITY_CLASS,
           EnvBuffer,
           PChar(CurDir),
           start,
           ProcessInfo) then
    begin
     CloseHandle(WritePipe) ;
     repeat
      Apprunning := WaitForSingleObject(ProcessInfo.hProcess,100) ;
      Application.ProcessMessages;
     until (Apprunning <> WAIT_TIMEOUT) ;
     if Capture then
      begin
       Repeat
        BytesRead := 0;
        ReadFile(ReadPipe,Buffer[0], ReadBuffer,BytesRead,nil) ;
        Buffer[BytesRead]:= #0;
        OemToAnsi(Buffer,Buffer) ;
        Result:=Result+ String(Buffer) ;
       until (BytesRead=0) ;
      end;
    end Else
    begin
      Capture:=True;
      CloseHandle(WritePipe) ;
    end;
   CloseHandle(ReadPipe) ;
   FreeMem(Buffer);
   StrDispose(EnvBuffer);
   CloseHandle(ProcessInfo.hProcess) ;
   CloseHandle(ProcessInfo.hThread) ;
   if Not Capture then
    begin
     StrL:=TStringList.Create;
     try
      StrL.LoadFromFile(CurDir+'RES.TMP');
      Result:=StrL.Text;
     finally
      StrL.Free;
     end;
    end;
  end;
end;

end.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aceswildab1Author Commented:
Where would I pass in my "command"/parameter for the bcp?
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
ah, sorry I didn't tell
In the same string as the exe, just like you would from a cmd prompt


ShowMessage(RunDosApp('C:\BCPPath\Bcp.exe db.dbo.table in "file name" -S server\instance,port -U username -P password -N', 'C:\SomeWorkingDir', True));

Open in new window

0
aceswildab1Author Commented:
That worked. Thanks for the help there. Question, why did your code work and my call to bcp not work?
0
Emmanuel PASQUIERFreelance Project ManagerCommented:
that's difficult to tell without having the stderror output. Which is why this is handy, because at least when it does not work you have a chance to know why, and how to fix it.
Maybe the working directory ? or the environment ? I don't know how BCP works...
0
aceswildab1Author Commented:
The solution gave me the code that took care of the issue (createprocess...) as well as a tool/unit that wrapped around it to display results to I could see them. Very complete.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.