SQLUtilExecuteResource - cDbUpdateFunctionLibrary

Executes a SQL script - that has been compiled into the program - as embedded SQL (ESQL). Use it together with the SQLIncludeScriptFile command to compile the SQL script file to the program.

Type: Function

Parameters: String sMemFileName String sDriverID Boolean bCreateScriptFile

Parameter	Description
sMemFileName	The name of the resource name used with the SQLIncludeScriptFile command.
sDriverID	One of MSSQLDRV_ID, DB2_DRV_ID, ODBC_DRV_ID, ORAFLEX, SQLFLEX, MDSPgSQL or MDSMySQL
bCreateScriptFile	Boolean True/False. Indicates if the embedded SQL script file that was embedded as a resource to the program should be written to disk when executed. Good for debugging.
bShowProgress	Optional. If the backup progress should be shown or not. If the ghoStatusPanel is active - the info will be shown there. Else Showln is used to display the progress percentage.

Syntax

Function SQLUtilExecuteResource String sMemFileName String sDriverID Boolean bCreateScriptFile Boolean bShowProgress

Call:

Get SQLUtilExecuteResource sMemoryResourceName sSQLDriverID False False to bOK

Description

Executes the embedded SQL script file as a series of Embedded SQL statements. If the passed SQL script string is very large it will be send to the embedded SQL handler in chunks of (by default) 500 lines to speed up execution. If a 'GO' statement is encountered the statement string will be send immediately to the embedded SQL handler. First the SQL script file is "sanitized" or cleaned up by removing all comments and empty lines as the ESQL interface doesn't like them.

ESQL (embedded SQL) statements should have "SET NOCOUNT ON" or you might experience strange results. The function will automatically adjust statements to have "NOCOUNT ON" in them before sending them to the SQL query handler.

Note: The command SQLIncludeScriptFile can be used to embed a SQL script file into the program.

Example

Procedure OnClick

String sInfoText

Boolean bOK

TimeSpan tsTotalQueryTime

Integer iRetval

// The default path for the resource command is the Data folder. So anything else needs a path.

// Note: The file name and the resource name (the 'as' part) needs to be exactly the same.

// It is advised that you create a "Scripts" folder parallel to AppSrc, Programs etc to

// store all SQL Script files.

SQLIncludeScriptFile ..\Scripts\CreateOrderEntry.sql as CreateOrderEntry.sql

Get YesNo_Box "This will create the [OrderEntry] database from the DataFlex ;

samples for Microsoft SQL Server, complete with data. If it exists it will first ;

be dropped, then re-created and populated with new data. Continue?" to iRetval

If (iRetval <> MBR_Yes) Begin

Procedure_Return

End

Send StartWorkingMessage "Executing SQL Script. Please wait..."

Get SqlUtilExecuteResource of ghoDbUpdateFunctionLibrary "CreateOrderEntry.sql" MSSQLDRV_ID False to bOK

Send StopWorkingMessage

If (bOK = True) Begin

Get ptsTotalQueryTime of ghoDbUpdateFunctionLibrary to tsTotalQueryTime

Move ("Success! DataFlex Order Entry Sample Database was created as [OrderEntry]. Time elapsed:" * ;

String(tsTotalQueryTime)) to sInfoText

End

Else Begin

Send UtilShowErrorList of ghoDbUpdateFunctionLibrary

Move ("Nope, that didn't work. There was a problem running the script.\n\n" + "See Notepad for details...") to sInfoText

End

Send Info_Box sInfoText

End_Procedure