We have a PowerBuilder application that runs a bunch of .sql script files.

It reads the file into a string variable and executes it like this:

EXECUTE IMMEDIATE :ls_sqlstatement USING sqlca;

Hi, Ruth -

What program are you planning on using to execute the file that contains the SQL? I'm having some difficulty understanding the larger picture as to what you are trying to accomplish, so some elaboration on your part would be very helpful.

Will the SQL be only a single SELECT statement?

Is the SQL dynamically created or are you running a saved/verified single SQL command or a sequence of SQL commands?

Will the SQL always work or is there a possibility it will contain syntax errors, logic errors, etc. (i.e., written by users or by developers)?

Will you be creating tables/triggers or other DB objects on the fly?

Creating logins?

Granting permissions?

Executing a stored procedure?

Depending on the particulars of what you want/need to do, the code in the "Run and Wait" example app may not be a viable solution.

-----------------------------

In regards your concerns about documentation/help, the free code samples on the TopWizProgramming web site are very concise and very well-written, but they do make extensive use of Window API functions, which are called from PowerBuilder via external function declarations. This is because they provide a means of doing things that cannot be done without using the API functions.

There is extensive documentation on the Windows API documentation available from Microsoft that you can access:

    https://learn.microsoft.com/en-us/windows/win32/apiindex/windows-api-list

Use the search field in the left sidebar on this web page to find documentation topics on API functions and structures. You need to supply the Windows names for these functions and structures, etc., and not the names by which they are called in PowerBuilder code. When an external function declaration in PB contains the "ALIAS FOR" clause, this means PB's name for the API function differs from the actual API function's name listed in the ALIAS FOR clause.

Please realize that Windows API documentation is supplied by Microsoft, not Appeon, and the documentation is directed at an audience that codes in C or C++. Microsoft uses type definitions out the wazoo (even nested, multi-level type definitions), which admittedly, can be confusing if all you've ever seen is PB code. In many cases, there are PB equivalents, but even so, it can be confusing. For example, a (typedef'd) DWORD is actually a PB UnsignedLong or ULong. Here's a link which helps explain most of the Windows API type definitions:

   https://learn.microsoft.com/en-us/windows/win32/winprog/windows-data-types

I've written a four-part tutorial on the subject of Interfacing Windows API functions to PB applications, and these documents are available in the Tech Articles section of the Community:

    https://community.appeon.com/index.php/articles-blogs/tutorials-articles/2-powerbuilder

The links to these four articles are currently listed beginning on page 4. The Appendix in Part 4 of the tutorial contains PB translations for many of the more commonly-used Windows API data types.

Best regards, John

I saw the topwiz site in the link, and I downloaded the .pbls, but I did't see any documentation or explaination of the code.  There are lots of parameters, but I can't find anything that tells what the values mean.

Check this out:

https://www.topwizprogramming.com/freecode_runandwait.html

It has RunAndWait and RunAndCapture functions. Also ShellRun and ShellRunAndWait.

It is not a batch file, it is an sql file.  Temp.sql, that contains sql code

this is the code

STARTUPINFO lstr_si
PROCESS_INFORMATION lstr_pi
long ll_null, ll_CreationFlags, ll_ExitCode, ll_msecs
String ls_null

// initialize arguments
SetNull(ll_null)
SetNull(ls_null)
lstr_si.cb = 72
lstr_si.dwFlags = STARTF_USESHOWWINDOW
lstr_si.wShowWindow = al_showwindow
ll_CreationFlags = CREATE_NEW_CONSOLE + NORMAL_PRIORITY_CLASS

// create process/thread and execute the passed program
If CreateProcess(ls_null, as_exefullpath, ll_null, &
ll_null, False, ll_CreationFlags, ll_null, &
ls_null, lstr_si, lstr_pi) Then
// wait for the process to complete
If il_millsecs > 0 Then
// wait until process ends or timeout period expires
ll_ExitCode = WaitForSingleObject(lstr_pi.hProcess, il_millsecs)
// terminate process if not finished
If ib_terminate And ll_ExitCode = WAIT_TIMEOUT Then
TerminateProcess(lstr_pi.hProcess, -1)
ll_ExitCode = WAIT_TIMEOUT
Else
// check for exit code
GetExitCodeProcess(lstr_pi.hProcess, ll_ExitCode)
End If
Else
// wait until process ends
WaitForSingleObject(lstr_pi.hProcess, INFINITE)
// check for exit code
GetExitCodeProcess(lstr_pi.hProcess, ll_ExitCode)
End If
// close process and thread handles
CloseHandle(lstr_pi.hProcess)
CloseHandle(lstr_pi.hThread)
Else
// return failure
ll_ExitCode = -1
End If

Return ll_ExitCode

If you are running a batch file, you can get your input from for example a text file "yes.txt" which contains a Y. (or any other value that needs to be passed in to continue the process).

batch_file.bat < yes.txt

for outputting your your output to a file use ">".

Not sure if you might have to use << instead of < and >> instead of >.

regards.