SQLIncludeScriptFile - The Database Update Framework
See Also: Overview of the DataFlex Database Update Framework
Command
SQLIncludeScriptFile
Purpose
Use the SQLIncludeScriptFile command to include a SQL Script file as a resource into the program. It is suggested that a special workspace folder named "Scripts" is created where all .sql script files are kept.
Syntax
SQLIncludeScriptFile {SQL script file} as {SQL script resource name}
What it does
It tells the compiler to include a SQL script file as a resource in the program. The resource can later be retrieved from memory while running the program. It is highly recommended to use the same name for the resource as the input file name that is to be included by the compiler (without the relative path). The parameters are compiler directives and they should not be surrounded by quotes (""). The default path for embedded resources is the Data folder. If the file to include is not in your Data folder, use a relative path.
Example 1
Procedure OnClick
String sInfoText
Boolean bOK
TimeSpan tsTotalQueryTime
Integer iRetval
// The default path for the include_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.
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
Example 2
Procedure MyDropColumnMessage String sTableName String sColumnName
Boolean bOK
tSQLScriptArraySQLScriptArray
Integer iSize iCount
// The default path for the include_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.
SQLIncludeScriptFile ..\Scripts\DropConstraintAndColumnNameMSSQL.sql as DropConstraintAndColumnNameMSSQL.sql
// This script file is provided in the "Scripts" workspace folder.
// It first drops the default constraint for the passed table column and then drops the
// the column.
Get SqlUtilReadResource of ghoDbUpdateFunctionLibrary "DropConstraintAndColumnNameMSSQL.sql" ;
MSSQLDRV_ID False to SQLScriptArray
Move (SizeOfArray(SQLScriptArray)) to iSize
Decrement iSize
For iCount from 0 to iSize
If (SQLScriptArray.sSQLScriptArray[iCount] contains "TABLE_NAME_XXX") Begin
Move (Replaces("TABLE_NAME_XXX", SQLScriptArray[iCount], String(sTableName)))) to ;
SQLScriptArray.sSQLScriptArray[iCount]
End
If (SQLScriptArray.sSQLScriptArray[iCount] contains "COLUMN_NAME_XXX") Begin
Move (Replaces("COLUMN_NAME_XXX", SQLScriptArray[iCount], String(sColumnName)))) to ;
SQLScriptArray.sSQLScriptArray[iCount]
End
Loop
Get SqlUtilExecuteEmbeddedScript of ghoDbUpdateFunctionLibrary SQLScriptArray MSSQLDRV_ID False "" to bOK
End_Procedure
Command source
#COMMAND IncludeSQLScriptFile _TR "AS" _R
#Set Q$ DF_RESOURCE_TYPE_LINE // default to binary.
#Replace !3.RSC |CI!ZR // create unique resource id.
#Resource !1 !Zr !q !3 // register new resource.
#ENDCOMMAND
Notes
oFor obvious reasons the SQL Script filename must be stripped from any space characters and also characters outside the A-Z range.
Next Topic