How do we run a external Power Builder Program from a Stored Procedure

[glenn_barber]

We have a small standalone non-visual application written in powerbuilder that we would like to startup from a SA11 stored procedure. Once running, the program will carry out its activities and end when complete.

How do we script this in a stored procedure?

Are there any special considerations?

[Former Member]

The only way to do this would be to use the xp_cmdshell() system procedure. The first thing that comes to mind is that the procedure will wait for the external application to complete before finishing.

[glenn_barber]

Thanks Calvin - just what I was looking for - otherwise Id have to do some wrapper around my app so it could be called.

[Breck_Carter]

FWIW you can make it asynchronous (fire-and-forget instead of call-and-return) in Windows by using "start"...

BEGIN
DECLARE @rc INTEGER;
@rc = CALL xp_cmdshell ( 'start notepad', 'no_output' );
SELECT @rc;
END;

For one of the more insane examples, see http://sqlanywhere.blogspot.ca/2012/01/you-cant-do-that-in-sql.html

[Former Member]

Ah, nice solution!

[glenn_barber]

Thanks Calvin and Breck - this was very helpful...I was going blind trying to find this in the documentation.

[glenn_barber]

Hi Breck

While I can get

@rc = call xp_cmdshell('"c:\\Program Files\\Appdir\\myapp.exe"','no_output') to run

@rc = call xp_cmdshell('Start "c:\\Program Files\\Appdir\\myapp.exe"','no_output') just brings up a blank cmd window

Any suggestions on syntax here?

Thanks

[glenn_barber]

@rc = call xp_cmdshell('Start /D"c:\\Program Files\\Appdir" /B myapp.exe','no_output') works

[Breck_Carter]

@Glenn: I see you've discovered the "help start" docs available in a command window 🙂

If memory serves, different versions of DOS/Windows support different flavors of START so don't go crazy with the syntax if you have to deploy to multiple platforms.

Plus, as you can see by examples that work without /D, including http://sqlanywhere.blogspot.ca/2012/01/you-cant-do-that-in-sql.html, START is inconsistent in its requirements. I believe the parser is feeble and sometimes confuses a "command" parameter with the optional "title", hence the empty window.

As a final take-away, questions like "how do I call xp_cmdshell" work well in Google, especially here since xp_cmdshell exists in SQL Server as well. Or, to keep it in the family, you can google this:

how do I call xp_cmdshell site:sybase.com