asked on

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

Emmanuel PASQUIER

have you tried with specifying the full exe path ?

Emmanuel PASQUIER

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

aceswildab1

ASKER

I tried that. Still nothing.

ASKER CERTIFIED SOLUTION

Emmanuel PASQUIER

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aceswildab1

ASKER

Where would I pass in my "command"/parameter for the bcp?

Emmanuel PASQUIER

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

aceswildab1

ASKER

That worked. Thanks for the help there. Question, why did your code work and my call to bcp not work?

Emmanuel PASQUIER

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

aceswildab1

ASKER

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.