Functions are structured in the following subsets :
- Excel
- SQL
- XML
Function syntax uses the following notations :
Create_Data_Zone(Code, Label, Category, Type, Path, ConnectString) : A function syntax always begins with the function name (character case doesn't matter), here "Create_Data_Zone", followed by parenthesis. Parenthesis can contain none to many parameters seperated by commas, in this case 6 parameters. Whatever the function or SQL query, called an instruction, it must end with an instruction seperator, the semi-colon ";".
Code (String) : Function parameters always come with their data type.
The data type "String" is any text inside double quotes ("). For instance : "This is a strong". Quotes can be omitted when the string does not include any character that could conflict with the language syntax such as : " (double quotes), ' (simple quotes), ; (semi-colon), \ (slash), etc... because string is the default type.
The data type "Integer" is a sequence of numeric characters.
The data type "Boolean" is eather "Yes" or "No" but also "True" or "False" or also 0 for No or 1 for Yes.
[optional string] : A string inside brackets is optional and may be omitted. If a function syntax is written f1([param1]) for function f1, it is legal to call function f1 without any parameters : f1(); or else with one parameter : f1("toto");. Brackets must not be written down when calling the function.
[optional string1, optional string2 [, optional string3]] : Brackets may be nested into other brackets. In this example, no string is mandatory, but if the optional string 1 is used then optional string 2 is mandatory but not optional string 3. Optional string 3 is not allowed if optional string 1 is not used.
string1 | string2 : Character "|" (pipe) is used to notify an exclusive choice between the two strings. Here we have the choice between string1 and string2 but not both.
{...} : Instruction bloc.
Description
This function executes the script included in the block following the cursor statement for each row of the data source specified by the DataFeed and DataFeedType parameters, replacing script variables (%variable_name%) by the corresponding values of the DataFeed where the variable_name matches the column name.
Parameters
DataFeed (String) : The value of this parameter depends on the value of the DataFeedType parameter like descripbed in the following table.
SQL query DataZone Code Context code of the memory cache : allowed values Not applicable except when cursor is called after SSH_ShellCommand function. You should then set the context code used when calling this function. Directory file list Expanded directory tree file and directory list XML file scan SQL query table data result set Ini file scan
DataFeedType (String) : Type of the DataFeed :
SQL : This type creates a cusor iterating through the rows returned by a SQL query. Variables replaced in the script correspond to the column names of the query result set.
DATAZONEPHYS : This type creates a cursor iterating through the list of the physical table and view names of a database connection or the file names of a directory. The replaced script variable is %PHYSNAME%.
OBJECT : This type creates a cursor iterating through the rows of the memory cache of the DataStudio repository tables. The replaced script variables will correspond to the column names of the cache (generally equal to the table column names of the repository database tables).
STDOUT : This type creates a cursor iterating through the lines returned by the call to "EXTERN" function. The replaced script variables will be %LINENO% (line number) et %STRLINE% (line content).
DIR et RECURSDIR : These types create cursors iterating through the file and directory list of a base directory (recursively or not). Full paths, file or directory names, file size, file attributes, file creation date, file last update date, file last access date, directory, relative file or directory path (relative to the DATAFEED directory), and a flag indicating whether it is a directory or not are respectively set in the following script variables : %FULLPATH%, %FILENAME%, %FILESIZE%, %FILEATTR%, %FILECRDATE%, %FILEMODDATE%, %FILEACCDATE%, %DIR%, %RELDIR%, et %ISDIR% (see note below).
XML : This type creates iterating through the tag and attribute sequences of an XML file. The replaced script variables will be %TYPE% (0:Beginning of tag, 1:End of tag, 2:Attribute),%NAME% (tag name).,%VALUE%(tag string value), %LEVEL% and %PATH% (full path of the tag : root/node1/node2/...).
SQLTAB : This type creates a cursor iterating through the rows of a memory SQL table instanciated by a call to "CREATE_SQLTAB" function. Variables replaced in the script correspond to the column names of the memory SQL table column names.
INIFILE : This type creates a cursor iterating through the variables defined in a .ini file. The DATAFEED parameter syntax is : file_name.ini|file_name.ini[]|file_name.ini[section_name]
file_name.ini : iterate through all variables in the file,
file_name[] : iterate through the section name list
file_name[section] : iterate through variables of the specified section.
The replaced script variables will be %SECTION%,%VAR%,%VALUE%.
Options (String) :
TOSQL=0|1|2 is set to indicate the wanted SQL variable formatting. 0 : no formatting, 1 : simple quote SQL formatting, 2 : no quotes SQL formatting.
NBFETCH=number of rows fetched a one time. This parameter allows memory usage management. The more the number of rows the more the memory is used.
RESTRICTED=0|1 (not applicable except for DataFeedType=XML) indicated if variables %PATH% and %LEVEL% are set (do not set to increase scanning speed).
AUTOFILTER=Tag1;Tag2;Tag3... (not applicable except for DataFeedType=XML) filter cursor to limit output to the listed end of tags.
SOURCEDATAZONE : (not applicable except for DataFeedType=SQL) sets the query to run on the specified DataZone instead of the current DataZone. Be careful to not use @ZONE.OBJET syntax in this query.
Example
CURSOR("select ZONECODE from DATAZONE","SQL")
{
Message("ZONECODE=","%ZONECODE%");
}
CURSOR("ZONE_ASCII","DATAZONEPHYS")
{
Message("fichier =","%PHYSNAME%");
}
CURSOR("DATAOBJ","OBJECT")
{
Message("code objet=","%OBJCODE%");
}
CURSOR("c:\test","DIR")
{
Message ("%FULLPATH% | %FILENAME% | %FILESIZE% | %FILEATTR% | %DIR% | %RELDIR% | %ISDIR%");
}
Cursor("%XMLFILE%","XML","RESTRICTED=0")
{
Message("Level=%LEVEL%");
Message("%LEVEL%,Name=%NAME% , Path=%PATH% , Value= %VALUE%, Type=%TYPE%");
Add(LINENO,1);
Affect('%NAME%','%VALUE%',"PARAMREPLACE=1");
if("( '%NAME%'='Index_Name' )")
{
Message("Trade %Trade_Id% Type %Product_Type% , Family = %Family%, Action = %Action% , Trade_Cur.=%Trade_Cur.% , Product_Description %Product_Description%");
}
}
SSH_ShellCommand("myhost","mylogin","mypassword","","ls -l","TOTO",
"PUBKEYOPENSSHFILE=pubkey.pub,AUTHMODE=KEY,PRIVKEYPEMFILE=privkey.pem,
FINGERPRINT=12345678912345678912345678912345");
cursor("TOTO","STDOUT")
{
message("%LINENO% : %STR%");
}
Note on file attributes :
File attributes a given as a 15 character string like "RHSVDAINTPZCOXE". When the attribute is not set it is replaced by a dash '-'.
Description
This function ends the current job. If the RetVal parameter is negative, the job and the project end with in an error state, otherwise, depending on the Option parameter, the end of the job may end the folder and/or end the project and/or end the datastudio server.
Parameters
RetVal (Integer) : Job return value. If this value is negative, the job ends in the error state and ends the whole project,
Message (String) : Message to display in the execution monitoring,
Option (String) : The option string syntax is : "[ENDFOLDER=0|1,][ENDPROJECT=0|1,][ENDSERVER=0|1,]".
Example
EndJob(-1); //end project in error state -1
EndJob(10,"Empty object","ENDPROJECT=1"); //end project
Description
This function executes the script from the Code parameter value.
Parameters
Code (String) : Script code to execute
Example
Option("ESCAPE","True");
Eval("message(\"Test\");");
Description
This function runs a new DataStudio project with the associated parameters waiting or not for the execution completion.
Parameters
ProjectCode (String) : Project code to execute,
Options (String) :
WAIT=[0|1] Sets if the function call waits for the end of the project execution. The WAIT option is not mandatory. Its default value is 1,
HIDE=[0|1] Sets if the new project panel is visible when DataStudio is in interactive GUI mode. The HIDE option is not mandatory. Is default value is 0.
POSTID=VariableName sets VariableName variable with a project session identifier to be used by WAITFOR_EXEC_PROJECT function to be able to synchronize later when WAIT option has been set to 1.
NOSESSIONRECORD=[0|1] sets if this project execution session data must not be saved in the repository to speed up execution.
ParamN (String) : Project parameter. The parameter syntax is : "PARAM_NAME=value",
Example
Exec_Project("SubProjet1","WAIT=0","PARAM1=P1", "PARAM2=P2");
Exec_Project("SubProjet1","PARAM1=1","PARAM2=P2");Exec_Project("SubProjet1","POSTID=ID","PARAM1=1","PARAM2=P2");
WaitFor_Exec_Project("POSTID=%ID");
Description
This statement lets the user easily create loops.
Parameters
Init (String) : One DataStudio loop initializing instruction (without the ending semi-colon),
LoopEnd (String) : A DataStudio condition statement inside parenthesis (see Set_Filter filter syntax). The loop ends when this condition is false.
Iteration (String) : DataStudio instruction (without the ending semi-colon ";" character) to execute at each loop iteration,
Example
For("affect(VAR,0)","(%VAR%<%NBLINE%)","add(VAR,1)")
{
Affect_LastSql("VAL",%VAR%,"COL1");
Message(%VAL%);
}
Description
This syntaxe creates a new user function. A user function is a set of instructions executed sequentially. Functions may have input parameters. Variables created inside a function are destroyed when leaving the function except otherwise specified by the Option function.
Functions must have been definied in the job to be called. It is possible to group functions together in a job and include it in some other script with the Include function.
Once defined, function can be called just like native DataStudio functions.
Parameters
Param1 (String) : User defined parameter. The name of the parameter can be freely chosen. There can be as many parameters as needed.
Example
function definition
Function datawarehouse_history_cleanup(date)
{
delete TABLE1 where LTIMESTAMP<%date%;
delete TABLE2 where LTIMESTAMP<%date%;
delete TABLE3 where LTIMESTAMP<%date%;
delete TABLE4 where LTIMESTAMP<%date%;
return("return value");
}function call
datawarehouse_history_cleanup("'11/10/2003'");
message("%RETURNVALUE%");
Description
Jumps to the Label instruction input parameter.
Parameters
Label (String) : Name of the label to jump to,
Example
affect("VAR1","0"); //set VAR1 to value 0
:start; // ":start" label
Message("Var ","%VAR1%"); //display the variable value in the execution monitoring
add("VAR1","1"); //increment variable by 1
If("select 1 where %VAR1%<10"); //boucle test
goto("start"); //go to "start" label
Description
This function tests the Condition parameter. If the condition is true, the next instruction is executed (the next instruction can be an instruction block inside a pair of braces "{...}" grouping instructions), or else, if the condition is false, the next instruction is the one referenced by the Label parameter or by the offset counted in instructions starting from the "If" function.
The "If" instruction can be associated with an Else instruction that indicates the instruction or block instruction to be executed if the condition is false.
Parameters
Condition (String) : The condition can be a SQL query, a table name, a locally evaluated condition (statement included in parenthesis) with the filter syntax and based on the first row of the last executed select SQL query (see Affect_LastSQL) or the current cursor row (when parameter Type="CURSOR'). When it is a query, the condition is true when the query result set contains at least one row. In the case of a table, the condition is true if the table exists.
InstructionOffset (Integer) : Offset counted in number of instructions starting from the if function for the next instruction when the condition is false. The default value is 2,
Label(String) : Lebel of the next instruction when the condition is false (see example),
Type (String) : Tells the condition type. This parameter can be set to the following values: "QUERY", "!QUERY", "TABLE", "!TABLE", et "CURSOR"
"QUERY" : The condition is true when the SQL query result set contains at least one row,
"!QUERY" : The condition is true when the SQL query result set does not contain any row,
"TABLE" : The condition is true when the table exists in the current DataZone,
"!TABLE" : The condition is true when the table does not exist in the current DataZone,
"CURSOR" : This type is only valid for locally evaluated conditions (statement included in parenthesis). The variables names defined by the cursor containing the if function are then usable as column names by the condition statement (see CURSOR). The values of the columns therefore correspond to the current row of the cursor. This type allows for high througput compared to common variables %VARIABLE% in datastudio scripts expanded to their value.
Example
Examples using labels and offsets
If("TABLE1",":NEXT","TABLE");
drop table TABLE1;
:NEXT;
create table TABLE1 (COL1 varchar(50));
If("select 1 where exists(select 1 from sysobjects where name='TABLE1')",2,"QUERY");
drop table TABLE1;
create table TABLE1 (COL1 varchar(50));Affect(VAR1,0);//initialize loop variable to 0
:START; //label
If("(%VAR1%<100)",":NEXT"); // loop ending condition
Add(VAR1,1);//count loop iterations
... //instructions
... //instructions
Goto("START");//loop
:NEXT; // loop ending label
//loop endselect 100 VAR2 from dual;
If("(VAR2<100)",":NEXT");
... //instructions
... //instructions
:NEXT;Example using des instruction blocks :
//conditional log table cleanup
select count(*) NB from TABLE_LOG;
If("(NB>100)") //use of the NB column of the last executed SQL query first row
{
delete TABLE_LOG;
MessageBox("Cleanup ok");
}
Else
{
MessageBox("Nothing to cleanup");
}Example using a cursor block :
CURSOR("test.xml","XML","TOSQL=1") { if("(TYPE=0 and PATH='/ORDERS/ORDER/REF')",,"CURSOR") { affect("REF","%VALUE%"); } if("(TYPE=0 and PATH='/ORDERS/ORDER/DATE')",,"CURSOR") { affect("DATE","%VALUE%"); } if("(TYPE=1 and PATH='/ORDERS/ORDER')",,"CURSOR") { insert into ORDERS ( %REF%, TO_DATE(%DATE%,'DD/MM/YYYY')); } }
Description
This functions end a user function and sets %RETURNVALUE% variable to the input parameter value.
Parameters
Value(String) : The value of this parameter will be used to the the special %RETURNVALUE% variable once the userfunction ended.
Example
Function datawarehouse_cleanup(date)
{
delete TABLE1 where LTIMESTAMP<%date%;
delete TABLE2 where LTIMESTAMP<%date%;
delete TABLE3 where LTIMESTAMP<%date%;
if("(%GLOBALE_VERSION_INFOCENTRE%<10)");
return("3");
delete TABLE4 where LTIMESTAMP<%date%;
}
Description
This function sets or replaces the error handling function. An error handling function is a function with no input parameter automatically called by DataStudio when an error happens.
Cette fonction permet de mettre en place un gestionnaire d'erreur spécifique. Un gestionnaire d'erreur est une fonction sans arguments qui est invoquée par le système quand une erreur se produit.
Parameters
FunctionName (String) : Name of a defined function with no input parameter,
Example
//definition of the error handling function
Function error_handler()
{
Get_Variable("ERRSTR","ERROR","MESSAGE");
MessageBox(%ERRSTR%);
}//set the error handler
Set_Error_Handler("error_handler");
Description
This function sets the maximum duration of the current folder execution time. If the execution time exceeds the specified time, the project is stopped and an error is generated.
Parameters
Delay (Integer) : maximum duration of the folder execution time in milliseconds,
Example
SetTimeout(100000); //the maximum duration of the folder is set to 100 seconds
Description
This function suspends the execution in a job for the specified delay.
Parameters
Delay (Integer) : Suspension duration in milliseconds,
Example
Sleep(1000); //wait for 1 second
Description
This function executes the SQL select query returning strings. Every row from the result set is then interpreted and executed as if it was a DataStudio instruction.
The string returned from the query must not include the instruction ending character (a semi colon ";"). Returned rows must not contain more than one instruction.
Parameters
Title (String) : Query title,
SelectQuery (String) : Select query returning a multi-row result set with one or more string columns. When there are more than one string column, the column are concatenated to form a DataStudio instruction.
Options (String) : One option is available : "AUTOSEP=0|1", allowing or not the ending semi colon character.
Example
Sql_Dynamic("Dynamic query","select 'insert toto values (''Ok'')' from dual"); //this example works on oracle
//sql_dynamic can also be used for any datastudio function :
//this example works on sybase
Sql_Dynamic("toto","select 'create_data_object(ZONE1, ',name,',Lib,Null,',name,' )' from sysobjects where type='U'");In the previous example, if the query returns 3 rows and 1 column containing TABLE1, TABLE2, TABLE3 the corresponding result set will be :
'create_data_object(ZONE1,','TABLE1',',Lib,Null,','TABLE1',')'
'create_data_object(ZONE1,','TABLE2',',Lib,Null,','TABLE2',')'
'create_data_object(ZONE1,','TABLE3',',Lib,Null,','TABLE3',')'
Which is equivalent to the following DataStudio script :
create_data_object(ZONE1,TABLE1,Lib,Null,TABLE1);
create_data_object(ZONE,TABLE2,Lib,Null,TABLE2);
create_data_object(ZONE1,TABLE3,Lib,Null,TABLE3);
Description
This function sets the stop-on-error flag that tells if the job is stopped is case of an error. If the flag is true, when a error happens, the whole project stops, thejob, the folder containig the job and the project are then in an error state.
Parameters
Flag (Integer) : Value tu set. 0 = deactivated option , 1 = activated option.
Example
Stop_On_Error(1);
Directives are indications given to the parser to change the way the interpreter understands the rest of the script. Directives are not instructions and are therefore read once at parsing time (loops dont work on directives).
Description
This function create a string replacement value at parsing time.
Parameters
Code (String) : String to replace
Value (String) : Replacement value.
Example
Define("MESSAGE1","This is a message");
Affect("VAR",1);
If("(%VAR%=1)")
{
Messagebox("MESSAGE1"); // Display a message box with a defined message by the define function
}
Description
This function includes a job inside another job. The included job is then part of the current job and is interpreted at the same time than just as if there only was one job.
This function allows including libraries of functions (job grouping a set of user functions).
Parameters
JobCode (String) : Job code to include.
Example
Include("LIB_DATAWAREHOUSE");
Description
This function activates or deactivates script interpreter options.
Parameters
Code (String) : Option code to set. Possible codes are : "ESCAPE", "PARAMCHECK", "MAXINCDEPTH", "NOPARAMPARSE" ,"KEEPFCTAFFECT"
ESCAPE : The possible values are "True" (activated) or "False" (deactivated). This option (default value : deactivated) tells the interpreter to use escape sequences in character strings. The escape character is '\' the backslash character.
Characters following the escape character are not interpreted. It is then easy to insert a double quote character ' " ' in a string without it being interpreted as the end of the string.
The special sequence '\n' inserts an end-of-line character without the script editor going to the next line.PARAMCHECK : The possible values are "True" (activated) or "False" (deactivated). This option (default value : activated) controls the parameter check in function calls to spot errors more easily when working on scripts. When in production the parameter check can be deactivated and some execution time can be saved.
MAXINCDEPTH : The possible values are any positive integer (default value : 20). This option sets the maximum include depth of a job. For instance, when a job that already includes another job is included in a third job, include depth is 2. Exceeding maximum include depth raises an exception.
NOOBJPARSE : The possible values are 1 or 0. This option sets the @zone.objet datastudio special script syntax interpretation on or off. Default value is on.
Warning : When this option is activated, data transfer become impossible through the @zone.objet datastudio special script syntax.
NOPARAMPARSE : The possible values are 1 or 0. This option sets the %VARIABLE% datastudio special script syntax on or off. Default value is on.
KEEPFCTAFFECT : The possible values are 1 or 0. If set to 0, variables created or modified inside a user function are lost once outside the function. When set to 1, variables created or modified inside a user function are kept. Default value is off.
Value (String) : Value to set.
Example
Option("ESCAPE","True");
Affect("MESSAGE1","This\nis a message");
Messagebox("MESSAGE1"); // Display a message box with a 2 lines message
Description
This function closes alls excel files opened with Open_Excel_File function.
Parameters
Example
CLOSE_EXCEL_ALLFILES();
Description
This function closes an excel file open with Open_Excel_File and allows for saving or discarding changes.
Parameters
ExcelNo (Integer) : Identifier of the excel file return by the Open_Excel_File function,
CloseAction (String) : Closing action. The possible values are : SAVE to save changes or DISCARD to discard changes.
Example
OPEN_EXCEL_FILE("FILENO",""c:\temp\file1.xls");
CLOSE_EXCEL_FILE("%FILENO%","SAVE");
Description
This function create a named area in an excel file.
Parameters
File(String) : Full path to the excel file,
Zone (String) : Named area name,
Sheet(String) : Excel sheet name,
Definition (String) : Area definition written like $A$1:$B$2.
Example
CREATE_EXCEL_NAMEDZONE("c:\temp\file1.xls","TESTZONE3","NEWSHEET","$B$3:$F$10");
Description
This function creates a sheet in an excel file.
Parameters
File(String) : Full path to the excel file,
Sheet(String) : Sheet name,
Example
Create_Excel_Sheet("c:\temp\fichier1.xls","NEWSHEET");
Description
This function drops a named area in an excel sheet.
Parameters
File(String) : Full path to the excel file,
Zone (String) : Area name.
Example
Drop_Excel_Namedzone("c:\temp\file1.xls","TESTZONE3");
Description
This function drops an excel sheet.
Parameters
File(String) : Full path to the excel file,
Sheet(String) : Sheet name.
Drop_Excel_Sheet("c:\temp\file1.xls","NEWSHEET");
Description
This function opens an excel file and return the correponding numeric identifier. All opened file must be closed by Close_Excel_File function.
Parameters
VarName (String) : Name of the variable to set with the opened excel file numeric identifier,
FileName (String) : Full path to the excel file.
Example
OPEN_EXCEL_FILE("FILENO",""c:\temp\file1.xls");
CLOSE_EXCEL_FILE("%FILENO%","SAVE");
Description
This function changes the style of a cell range. Any change must be saved by calling the Close_Excel_File function with the SAVE option set to alter the file.
Parameters
ExcelNo (Integer) : Identifier returned by the Open_Excel_File function,
Sheet (String) : Name of the sheet containing the cells.
Range (String) : Cell range definition string. Use the following syntax : $x1$y1:$x2$y2 with x1 and x2 the excel column name (1 ou 2 characters) and y1 et y2 the excel line numbers.
Style (String) : Style to apply to the cell range. Use the following syntax : "[StyleName=StyleValue,]..."
The style names and the corresponding possible valuesare the following :
Example
Set_Excel_Style("%EXCELID%","Sheet9","$V$11:$AF$11","BorderBottomStyle=Thin,BorderTopStyle=Thin,BorderLeftStyle=Thin,BorderRightStyle=Thin");
Description
This function defines a write mask used when the DataZone containing the DataObject has the InPlace indicator set. The write mask is a set of cell range defined by their line and column numbers and letters of the upper left and lower right corner. All cells not included in the write mask are not written.
Parameters
ZoneCode (String) : Zone code,
ObjCode (String) : DataObject code,
MaskCol (String) : Column name of the query containing the list of cell range definitions,
MaskQuery (String) : SQL query returning the cell range list with write permission. Every returned row must contain a cell range defined with the following syntax : r1:c1:r2:c2 where r1 and c1 are the row and column numbers of the upper left corner, and r2 and c2 are the row and column numbers of the lower right corner. The range borders are included in the range. Row and column numbering start with number 1 and are relative to the upper left corner of the excel sheet.
Example
Set_Excel_Writemask("EXCEL_ZONE","OBJ1","RANGE","select '1:1:1:3' RANGE from dual union all select '4:1:4:3' RANGE from dual");
Description
This function restores a file saved in a table with BinImport function.
Parameters
File (String) : Full path of the file to restore,
Table (String) : Table name containing the data saved with BinImport function,
ContextKeyCol (String) : Context column name in the table,
ContextKeyVal (String) : Context value,
NumKeyCol (String) : Ordering column name in the table,
DataCol (String) : Column name containing the file data,
Example
BinExport("c:\temp\image1_new.jpg","MESIMAGES","IMAGENAME","Image1","COLNUM","COLDAT");
Description
This function stores a binary file in a database table whatever its format. The file can then be restored with the BinExport function.
The file is cut into small chunks. The chunks are converted to the base64 encoding, a database compatible character set, and saved in a column of a table.
Parameters
File (String) : Full path of the file to store,
Table (String) : Name of the table where the file will be stored (imported). This table must at least have one context column (e.g. : a column of type varchar(50)), one ordering column (e.g. : a column of type numeric(N), float, integer, ...) and a data column (e.g. : a column of type varchar(1024)). For performance reasons, it is better to use large sized columns for data (1900 for Sybase version >= 12.5, 4000 for oracle version >= 8.0),
ContextKeyCol (String) : Context column name of the import table,
ContextKeyVal (String) : Context value in the context column,
NumKeyCol (String) : Ordering column in the import table,
DataCol (String) : Column name containing the file data,
Example
create table MYIMAGES ( IMAGENAME varchar(32), COLNUM numeric(6), COLDAT varchar(1900));
create unique index MYIMAGES_PK on MYIMAGES ( IMAGENAME,COLNUM);
delete from MYIMAGES where IMAGENAME='Image1';BinImport("c:\temp\image1.jpg","MYIMAGES","IMAGENAME","Image1","COLNUM","COLDAT");
Description
This function copies a file to a destination. The copy is extended to secured or non-secured URLs and for ftp, http, https, scp protocols.
Parameters
Source (String) : URL or DataObject defining a file to copy.
Destination (String) : URL or DataObject defining a destination file path.
Login (String) : Login name when connecting to ftp or http source ou destination (to copy a file from and to an ftp server, the file must be copied locally to be then copied to the remote destination),
Password (String) : Password corresponding to the Login to connect to the ftp ou http source server,
Option (String) : Option string. Syntax is as follows : "[FTPMODE=IMAGE|ASCII] [,APPEND=0|1] [,RETRY=n] [,PASSIVE=0|1] [,LDAPPROTOCOLV=2|3] [,AUTHMODE=KEY|PASSWORD] [,PUBKEYOPENSSLFILE=file.pub] [,PRIVKEYPEMFILE=file] [,FINGERPRINT=fingerprint]"
FTPMODE sets the FTP transfer mode. In IMAGE mode the file is transfered as is. In ASCII mode, the transfer adapts the file content converting from the source charset to the destination charset (this mode is only interesting for transfering text files). The default value is IMAGE,
APPEND sets the behavior when the destination file already exists. APPEND=0 : the file is overwritten. APPEND=1 : the file content is appended to the end of the existing file. The default value is APPEND=0,
RETRY sets the maximum transfer try count for FTP URLs. The default value is RETRY=1 (try only one time),
PASSIVE sets passive mode for the FTP transfer. The default value is PASSIVE=0.
LDAPPROTOCOLV sets the LDAP protocol version (Version 2 or 3). The default value is LDAPPROTOCOLV=3.
AUTHMODE sets the authentication mode with the server for scp protocol. AUTHMODE=PASSWORD authentication uses login and password. AUTHMODE=KEY authentication uses key and passphrase. The default value is AUTHMODE=PASSWORD.
PUBKEYOPENSSLFILE sets the public key file in ssh format (.pub) containing the user public key for authentication when connecting to the server with the scp protocol.
PRIVKEYPEMFILE sets the private key file in PEM format for encryption for the scp protocol. If this file is password protected, the password shoud be set in the password parameter.
FINGERPRINT sets the fingerprint to authentify the server for the scp protocol. This string is returned by SSH_GetFingerPrint function.
Example
Copy_File("ftp://ftp.gnu.org/README","c:\temp\readme.txt","anonymous",,"FTPMODE=IMAGE"); //it is possible to set login and password.
Copy_File("https://sourceforge.net/","c:\temp\test.html");
Copy_File("ldap://ldap.math.jussieu.fr/dc=math,dc=jussieu,dc=fr?email?sub","c:\temp\toto.ldap");
ssh_getfingerprint("host","fingerprint");
copy_file("scp://host/dirpath/filename", "c:\temp\filename2", "login", "password", "AUTHMODE=KEY,PUBKEYOPENSSLFILE=pubkey.pub,PRIVKEYPEMFILE=privkey.pem,FINGERPRINT=%fingerprint%");
Description
This function create an archive file containing all the files in the directory and all subdirectories. These files can then be extracted by Extract_Archive function
Parameters
ArchiveFile (String) : Full path of the archive file to create.
Directory (String) : Full path of the archive directory to archive.
Example
Create_Archive("c:\temp\archive1.arc","c:\exportdatabase\base1");
Description
This function creates a directory.
Parameters
Directory (String) : Full path of the directory to create.
Options (String) : Option string. The syntax is as follows : "[FORCE=1 | FORCE=0]"
FORCE sets the force mode. All non existent directories in the path are created. The defult value is "FORCE=0".
Example
Create_Directory("c:\temp\dir1\dir2","FORCE=1");
Description
This function deletes a file.
Parameters
FilePath (String) : Full path of the file to delete.
Example
Delete_File("c:\temp\readme.txt");
Description
This function tests the existence of a directory.
Parameters
Param (String) : Name of the variable that will be created to store the result value : 1 if the directory exists, 0 if the directory doesn't exist.
Path (String) : Full path of th directory.
Example
Dir_Exists(WINDOWS,"c:\windows");
If("(%WINDOWS%!=0)");
Message("Dir Windows exists");
Description
This function extracts to a directory all files from an archive file created by Create_Archive function.
Cette fonction permet d'extraire tous les fichier d'un fichier achive crée par la fonction Create_Archive dans un Répertoire..
Parameters
ArchiveFile (String) : Full path of the archive file,
Directory (String) : Full path of the restoring directory.
Example
Extract_Archive("c:\temp\archive1.arc","c:\exportbase");;
Description
This function compares two files line by line and store the result into a third file easily readable as a DataObject. This file contains hte modification type as the first column (D : deleted line or last value for a modified line, I : inserted line or new value in case of a modification).
Parameters
File1 (String) : Chemin complet du premier fichier à comparer ( peut comporter des références de datazones/dataobject)
File2 (String) : Chemin complet du deuxième fichier à comparer ( peut comporter des références de datazones/dataobject)
ResultFile (String) : Chemin complet du fichier résultat de la comparaison ( peut comporter des références de datazones/dataobject).
Separator (String) : Separateur pour les colonnes ajoutées
Options (String) : La syntaxe de la chaîne d'option est la suivante : "[FILEPOS=0|1,][LINENO=0|1,][CHECKSUM=0|1]"
Example
File_Diff("@TEXTE.yesterday_file.txt","@TEXTE.today_file.txt","@TEXTE.result_file","TAB","FILEPOS=0,LINENO=0,CHECKSUM=0");
Description
This function tests the existence of a file.
Parameters
Param (String) : Name of the variable that will be created to store the result value : 1 if the file exists, 0 if the file doesn't exist,
Fichier (String) : Full path of the file.
Example
File_Exists(AUTOEXEC,"c:\autoexec.bat");
If("(%AUTOEXEC%!=0)");
Message("Autoexec exists");
Description
This function reads a BLOB field and saves it in a file.
Warning : This function works on all RDBMS having BLOB fields like ORACLE.
Parameters
Query (String) : Select query that return the BLOB field,
File (String) : Name of the file where the BLOB content is to be stored,
Example
Read_Blob("select CHBLOB from TSTBLOB where KEY='AUTOEXEC.BAT'",'c:\temp\autoexec.bat') ;
Description
This function reads a file and store the content in a DataStudio variable.
Parameters
Param (String) : Name of the variable that will contain the file content,
File (String) : Name of the file to read,
Options (String) : Option string. The syntax is as follows : "[CONTENTTYPE=BINARY|TEXT]"
CONTENTTYPE sets the type of content of the file. When the type is BINARY, the content will be converted to base64 charset otherwise is it stored as is,
Example
Read_File("FILECONTENT","c:\temp\sample.xls","CONTENTTYPE=BINARY");
Write_File("%FILECONTENT%","c:\temp\samplecopy.xls","CONTENTTYPE=BINARY");
Description
This function deletes a directory.
Parameters
Directory (String) : Full path of the directory to be deleted.
Options (String) : Option string. The syntax is as follows : "[FORCE=1 | FORCE=0]"
FORCE sets the force mode. When "FORCE=0", a directory containing files and subdirectories will be still deleted with all its content. The default value is "FORCE=0".
Example
Remove_Directory("c:\temp\dir1\dir2","FORCE=1");
Description
This function write the content of a file in a BLOB field.
Warning : This function works only on RDBMS having BLOB fields like ORACLE.
Parameters
Query (String) : Insert or update Query writing in a BLOB field. The identifier of the BLOB content to write is any identifier starting with a colon character like :param or :fblob etc.,
File (String) : Full path of the file to read for the BLOB content,
Options (String) : Option string. The syntax is as follows "TYPE=BLOB|CLOB". The default value is TYPE=BLOB.
Example
Drop_Table(TSTBLOB);
// Sybase query
create table TSTBLOB (CLEF varchar(64), CHBLOB IMAGE);
// Oracle query
create table TSTBLOB (CLEF varchar(64), CHBLOB BLOB);Write_Blob("insert into TSTBLOB(CLEF,CHBLOB) values('AUTOEXEC.BAT',:fblob)","c:\autoexec.bat");
Description
This function writes a file with the content of a variable.
Parameters
Param (String) : Variable name,
File (String) : Full path of file to write,
Options (String) : Option string. The syntax is as follows :"[CONTENTTYPE=BINARY|TEXT]"
CONTENTTYPE sets the content type of the variable. "CONTENTTYPE=BINARY" : the content of the variable represents bianry data and will therefore be decoded from base64 charset. "CONTENTTYPE=TEXT" the content of the variable will be written to the file as is.
Example
Read_File("FILECONTENT","c:\temp\sample.xls","CONTENTTYPE=BINARY");
Write_File("%FILECONTENT%","c:\temp\samplecopy.xls","CONTENTTYPE=BINARY");
Description
This function merges HTML template file fields with the data returned by the select query that must return only one row. The file template must conform to the following format :
It must contain identifiers included in brackets (e.g. : [FIELD1]). These identifiers inside brackets must correspond to column name of the select query in order to be merged.
These identifiers will all be replaced if there are more than one in the template.
Parameters
OutputCharset (String) : Charset name of the HTML file (template and output file),
TemplateFile (String) : Full path of the template file,
OutputFile (String) : Full path of the result html file,
SelectStatement (String) : Select query,
MultiSelectStatement (String) : This optional parameter is a query returning strings representing select queries allowing to merge with as many queries as needed. One of the columns of this select query must contain the query strings to be executed to be merged with the HTML template optimizing source and destination file access time. When this parameter is set, the SelectStatement must be set to the column name of the multiselect statement query containing the queries.
Options (String) : Option string. The syntax is as follows : "[CHARREF=0|1]",
CHARREF sets non ascii character writing to the output file. "CHARREF=0" write source string characters as is. "CHARREF=1" write source string characters replacing them with HTML character references (e.g. ">" character is replaced by ">"). The default value is 1,
Example
//only one select query
Html("UTF-8","C:\data\dev\rtf\rtf\fields_merge.html","C:\data\dev\rtf\rtf\fields_merge_res.html","select 'John' FIRSTNAME, 'Walker' MIDDLENAME from dual","","CHARREF=0");//several queries
Html("UTF-8","C:\data\dev\rtf\rtf\fields_merge.html","C:\data\dev\rtf\rtf\fields_merge_res.html","EXTRACT_QUERIES","select EXTRACT_QUERIES from AGGREGATE_LIST where FLAG like '%D%'");
Description
This function merges HTML template file tables with the data returned by the select query returning one or more rows. The file template must conform to the following format :
It must contain table and table column identifiers included in double brackets (e.g. : [[FIELD1]]). These identifiers inside brackets must correspond to column name of the select query in order to be merged. The first column identifier must be preceded by the table identifier and separatedd by a dot(e.g. : [[TAB.COL1]])
The tables will be replaced as many times as they are used in the template file. The merge is done by evaluating several times the template so as to allow the merge of nested tables. When using multi-select queries, if the first query returns a table values containing themselves table indentifiers and the second returns corresponding table values, these values will replace the nested table identifiers and so on.
Parameters
OutputCharset (String) : Charset name of the HTML file (template and output file),
TemplateFile (String) : Full path of the template file,
OutputFile (String) : Full path of the result HTML file,
TabId (String) : Table identifier (allows differencing between different tables in the same template),
SelectStatement (String) : Select query,
MultiSelectStatement (String) : This optional parameter is a query returning strings representing select queries allowing to merge with as many queries as needed. One of the columns of this select query must contain the query strings to be executed to be merged with the HTML template optimizing source and destination file access time. When this parameter is set, the TabId must be set to the column name of the multiselect statement query containing the queries.
Options (String) : Option string. The syntax is as follows : "[CHARREF=0|1]",
CHARREF sets non ascii character writing to the output file. "CHARREF=0" write source string characters as is. "CHARREF=1" write source string characters replacing them with HTML character references (e.g. ">" character is replaced by ">"). The default value is 1,
Example
HTML table as written in the template file : <table><tr><td>[[TABLE1.COL1]]</td><td>static value</td><td>[[COL2]]</td><td>[[COL3]]</td></tr></table>
Html_Tab("UTF-8","C:\data\dev\rtf\rtf\table_merge.html","C:\data\dev\rtf\rtf\table_merge_res.html","TABLE1","select COL1,COL2,COL3 from TABLE_MERGEHTML");
Description
This function merges RTF template file fields with the data returned by the select query that must return only one row. The file template must conform to the following format :
It must contain identifiers included in brackets (e.g. : [FIELD1]). These identifiers inside brackets must correspond to column name of the select query in order to be merged.
These identifiers will all be replaced if there are more than one in the template.
The definition of some value for a property named DSRTFPARSERVERSION2 in the RTF template file imposes the use of version 2 of the parser whatever the value of the PARSER2FROMWORDVERSION option.
Parameters
TemplateFile (String) : Full path of the template file,
OutputFile (String) : Full path of the result html file,
SelectStatement (String) : Select query,
MultiSelectStatement (String) : This optional parameter is a query returning strings representing select queries allowing to merge with as many queries as needed. One of the columns of this select query must contain the query strings to be executed to be merged with the HTML template optimizing source and destination file access time. When this parameter is set, the TabId must be set to the column name of the multiselect statement query containing the the table id and the SelectStatement to the column name containing the corresponding select queries.
Options (String) : Option string. The syntax is as follows : "[PARSER2FROMWORDVERSION=NEVER|ALL|Microsoft Word XX.Y.ZZZZ]",
PARSER2FROMWORDVERSION specifies the version of the parser to use by comparing the passed version value to the version value included by word in the RTF template file. If the version read from the RTF file is less than the passed value then the version 1 (intended for RTF files generated by Word97) of the parser is used otherwise the version 2 (intended for RTF files generated by Word 2007) is used.
ALL specifies to use version 2 of the parser for all the template files whatever the version,
NEVER specifies to use version 2 of the parser for all the template files whatever the version,
Microsoft Word XX, Y, ZZZZ where XX, Y, ZZZZ are to be replaced with numeric values, specifies the version of Word from which to version 2 of the parser. Before Word 2002 RTF files do not the version of Word used to generate the RTF file so the parser version 1 is used. Word 2002 versions set this parameter with the value "Microsoft Word 10.0.XXXX". Word 2003 versions set this parameter to the value "Microsoft Word 11.0.XXXX". Word 2007 versions set this parameter to the value "Microsoft Word 12.0.XXXX",
Example
//only one select query
Rtf("C:\data\dev\rtf\rtf\fields_merge.rtf","C:\data\dev\rtf\rtf\fields_merge_res.rtf","select 'John' FIRSTNAME, 'Walker' LASTNAME from dual");//several queries
Rtf("C:\data\dev\rtf\rtf\fields_merge.rtf","C:\data\dev\rtf\rtf\fields_merge_res.rtf","EXTRACT_QUERIES","select EXTRACT_QUERIES from AGGREGATE_LIST where FLAG like '%D%'");
Description
This function merges RTF template file tables with the data returned by the select query returning one or more rows. The file template must conform to the following format :
It must contain table and table column identifiers included in double brackets (e.g. : [[FIELD1]]). These identifiers inside brackets must correspond to column name of the select query in order to be merged. The first column identifier must be preceded by the table identifier and separatedd by a dot(e.g. : [[TAB.COL1]])
The tables will be replaced as many times as they are used in the template file.
the definition of some value for a property named DSRTFPARSERVERSION2 in the RTF template file imposes the use of version 2 of the parser whatever the value of the PARSER2FROMWORDVERSION option.
Parameters
TemplateFile (String) : Full path of the template file,
OutputFile (String) : Full path of the result RTF file,
TabId (String) : Table identifier (allows differencing between different tables in the same template),
SelectStatement (String) : Select query,
MultiSelectStatement (String) : This optional parameter is a query returning strings representing select queries allowing to merge with as many queries as needed. Two of the columns of this select query must contain the table identifiers and the query strings to be executed to be merged with the RTF template optimizing source and destination file access time. When this parameter is set, the TabId must be set to the column name of the multiselect statement query containing the the table id and the SelectStatement to the column name containing the corresponding select queries.
Options (String) : Option string. The syntax is as follows : "[PARSER2FROMWORDVERSION=NEVER|ALL|Microsoft Word XX.Y.ZZZZ]",
PARSER2FROMWORDVERSION specifies the version of the parser to use by comparing the passed version value to the version value included by word in the RTF template file. If the version read from the RTF file is less than the passed value then the version 1 (intended for RTF files generated by Word97) of the parser is used otherwise the version 2 (intended for RTF files generated by Word 2007) is used.
ALL specifies to use version 2 of the parser for all the template files whatever the version,
NEVER specifies to use version 2 of the parser for all the template files whatever the version,
Microsoft Word XX, Y, ZZZZ where XX, Y, ZZZZ are to be replaced with numeric values, specifies the version of Word from which to version 2 of the parser. Before Word 2002 RTF files do not the version of Word used to generate the RTF file so the parser version 1 is used. Word 2002 versions set this parameter with the value "Microsoft Word 10.0.XXXX". Word 2003 versions set this parameter to the value "Microsoft Word 11.0.XXXX". Word 2007 versions set this parameter to the value "Microsoft Word 12.0.XXXX",
Example
Rtf_Tab("C:\data\dev\rtf\rtf\tables_merge.rtf","C:\data\dev\rtf\rtf\tables_merge_res.rtf","TABLE1",select COL1,COL2,COL3 from TABLE_RTFMERGE");
Description
This function displays a message in the log file and the project monitoring.
Parameters
Message (String) : Message to display,
Example
Message("Processing1 ended"); //display static message
Message(%VAR%); //display dynamic message from a variable content
Description
This function displays a messagebox.
Parameters
Message (String) : Message to display,
Title (String) : Message box title,
Flags (String) : Display options of the message box. These options are the numerical values from the MessageBox Windows API (uType parameter), and can be combined with a logical OR on their values.
Return (String) : Variable name to be created with the clicked button of the message box.
The possible values are the following :
Flags Output value Label Value Label Value MB_OK 0 IDOK 1 MB_OKCANCEL 1 IDCANCEL 2 MB_ABORTRETRYIGNORE 2 IDABORT 3 MB_YESNOCANCEL 3 IDRETRY 4 MB_YESNO 4 IDIGNORE 5 MB_RETRYCANCEL 5 IDYES 6 MB_CANCELTRYCONTINUE 6 IDNO 7 IDCLOSE 8 MB_ICONHAND 16 IDHELP 9 MB_ICONQUESTION 32 IDTRYAGAIN 10 MB_ICONEXCLAMATION 56 IDCONTINUE 11 MB_ICONASTERISK 64 MB_USERICON 128 MB_ICONWARNING 56 MB_ICONERROR 16 MB_ICONINFORMATION 64 MB_ICONSTOP 16 Example
MessageBox("Hello world ?","Test","33", "RETURN"); // 33 = (MB_OKCANCEL OR MB_ICONQUESTION)
If ("%RETURN%=1") // (IDOK)
{
[...]
}
Description
This function opens a Data Viewer window displaying the result of the query parameter.
Parameters
Requete (String) : Select query to execute,
Title (String) : Window title of the query result set,
Chaine d'options (String) : Option string. The syntax is as follows : "[WAIT=0|1][,TIMEOUT=0|1]".
WAIT sets if the function call waits for the closing of the window before returning ("WAIT=1"). The default value is "WAIT=0" : do not wait for window closing.
TIMEOUT=[time in seconds] sets the wait timeout in the case WAIT=1. If the user did not close the window before the specified number of seconds, it is automatically closed and the processing continued. The default value is TIMEOUT=0 (no timeout).
Example
Open_SQL("select * from TABLE1","Query result","WAIT=1"); //Display a static message with the query result set
Description
This function copies a file to a destination. The copy is extended to secured or non-secured URLs and for ftp, http, https, scp protocols.
Parameters
Source (String) : URL or DataObject defining a file to copy.
Destination (String) : URL or DataObject defining a destination file path.
Login (String) : Login name when connecting to ftp or http source ou destination (to copy a file from and to an ftp server, the file must be copied locally to be then copied to the remote destination),
Password (String) : Password corresponding to the Login to connect to the ftp ou http source server,
Option (String) : Option string. Syntax is as follows : "[FTPMODE=IMAGE|ASCII] [,APPEND=0|1] [,RETRY=n] [,PASSIVE=0|1] [,LDAPPROTOCOLV=2|3] [,AUTHMODE=KEY|PASSWORD] [,PUBKEYOPENSSLFILE=file.pub] [,PRIVKEYPEMFILE=file] [,FINGERPRINT=fingerprint]"
FTPMODE sets the FTP transfer mode. In IMAGE mode the file is transfered as is. In ASCII mode, the transfer adapts the file content converting from the source charset to the destination charset (this mode is only interesting for transfering text files). The default value is IMAGE,
APPEND sets the behavior when the destination file already exists. APPEND=0 : the file is overwritten. APPEND=1 : the file content is appended to the end of the existing file. The default value is APPEND=0,
RETRY sets the maximum transfer try count for FTP URLs. The default value is RETRY=1 (try only one time),
PASSIVE sets passive mode for the FTP transfer. The default value is PASSIVE=0.
LDAPPROTOCOLV sets the LDAP protocol version (Version 2 or 3). The default value is LDAPPROTOCOLV=3.
AUTHMODE sets the authentication mode with the server for scp protocol. AUTHMODE=PASSWORD authentication uses login and password. AUTHMODE=KEY authentication uses key and passphrase. The default value is AUTHMODE=PASSWORD.
PUBKEYOPENSSLFILE sets the public key file in ssh format (.pub) containing the user public key for authentication when connecting to the server with the scp protocol.
PRIVKEYPEMFILE sets the private key file in PEM format for encryption for the scp protocol. If this file is password protected, the password shoud be set in the password parameter.
FINGERPRINT sets the fingerprint to authentify the server for the scp protocol. This string is returned by SSH_GetFingerPrint function.
Example
Copy_File("ftp://ftp.gnu.org/README","c:\temp\readme.txt","anonymous",,"FTPMODE=IMAGE"); //it is possible to set login and password.
Copy_File("https://sourceforge.net/","c:\temp\test.html");
Copy_File("ldap://ldap.math.jussieu.fr/dc=math,dc=jussieu,dc=fr?email?sub","c:\temp\toto.ldap");
ssh_getfingerprint("host","fingerprint");
copy_file("scp://host/dirpath/filename", "c:\temp\filename2", "login", "password", "AUTHMODE=KEY,PUBKEYOPENSSLFILE=pubkey.pub,PRIVKEYPEMFILE=privkey.pem,FINGERPRINT=%fingerprint%");
Description
This function reads data on one or more DDE (Dynamic Data Exchange) servers in asynchronous (Hot Link) or in synchronous (Cold Link) mode and stores the values in a table;
The values are store 10 by 10 in the table.
The Environment setting parameter "Technical"/"DDE Use Async SQL" sets the table update mode synchronous or asynchronous. The synchronous update is used when the DDE server is reliable and fast and when the database is slow so as to not slow down the data exchange due to the database response time.
Parameters
Application (String) : DDE application identifier,
Topic (String) : DDE service identifier,
Table (String) : Table name of the table driving the exchange,
ItemCol (String) : Column name of the table containing the DDE DATA identifier,
ValCol (String) : Column name of the table where the read value is to be stored,
Options (String) : Option string. The syntax is as follows : "[WAITDATA=n] [,WAITCONNECT=n] [,FORCEREQUEST=True|False] [,FORMAT=UNICODE] [,OPENLINKAFTER=True|False] [,REFRESHDATAONSETITEM=True|False] [,FREEHANDLEAFTERTRANSACTION=True|False] [,NBREQ=n]"
OPENLINKAFTER specifies to open DDE exchange after loading the whole link table. The exchange is then asynchronous and all link are read at the same time. The default value is "False".
NBREQ specifies the maximum number of parallel read link when is asynchronous mode (Hot Link). The table is then loaded by blocs of size NBREQ. If NBREQ=1 reading is synchronous (Cold Link). This option has no effect if OPENLINKAFTER=True. The default value is read in the environment setting parameter "Technical/Number of parallel DDE Requests"
WAITCONNECT specifies the number of milliseconds*10 to wait for between to DDE update messages when receiving a value corresponding to a DDE link when OPENLINKAFTER=False and NBREQ>1. There are a total of 10 tries. This parameter has no effect when OPENLINKAFTER=True.
FORCEREQUEST specifies if the query storing the DDE values is synchronous (FORCEREQUEST=True) when the connection is asynchronous (Cold Link). This parameter has no effect if NBREQ=1 or if OPENLINKAFTER=True. The default value is FORCEREQUEST=False.
REFRESHDATAONSETITEM has the same effect as FORCEREQUEST at a lower level in the DDE protocol. The default value is REFRESHDATAONSETITEM=True.
WAITDATA=n specifies n the number of milliseconds to wait for the DDE data update messages (Hot Link) for DDE link read in parallel (not more than NBREQ). This parameter has no effect if NBREQ=1 or if OPENLINKAFTER=True. The default value is WAITDATA=100.
FORMAT specifies the encoding format for strings in the DDE protocols. The possible values are : UNICODE and TEXT. The default value is FORMAT=TEXT.
FREEHANDLEAFTERTRANSACTION is a reserved parameter of the DDE protocol. The default value is FREEHANDLEAFTERTRANSACTION=True.
Example
//prepare data to read
create table DDE_INTERFACE (
ITEM varchar(100),
VALUE varchar(100)
);
insert into DDE_INTERFACE values('!Feuil1!L1C1',null); //set table to read the first cell of the Sheet1 of an excel file as excel is a DDE server (see the DDE server application documentation for naming)//read data
DDEImport("Excel.Sheet.8","F:\Temp\dataentry.xls","DDE_INTERFACE","ITEM","VALUE","NBREQ=1"); //Excel.Sheet.8 is the published name of excel (see the DDE server application documentation for naming)//display data
select VALUE from DDE_INTERFACE;
Affect_LastSql(VALUE,VALUE); //put VALUE returned from last sql query in VALUE variable
Message(%VALUE%); //display read value from excel thanks to the DDE protocol
Description
This function gets the data returned by an LDAP URL. LDAP URLs format is defined by RFC 2255. The data returned are a list of DN (Distinguished Name) corresponding to the element path in the LDAP server tree, a list of class names and a list of attributes for each DN.
These lists are inserted in the specified tables after deleting their content or creating them when they don't exist.
Parameters
LdapUrl (String) : LDAP URL respecting one of the two following format :
ldap://<hostname>:<port>/<base_dn>?<attributes>?<scope>?<filter>@<datazone>.<DataObject>?<attributes>?<scope>?<filter> <base_dn> : DN of the base entry point in the tree to start the search from. <attributes>: Attributes to read <scope> : reading depth in the DIT from the <base_dn> : "base" | "one" | "sub" <filter> : search filter, default is (objectClass=*) <datazone> : LDAP type DataZone code <DataObject> : DataObject name in the previous DataZone Examples : ldap://ldap.netscape.com/ou=Sales,o=Netscape,c=US?cn,tel,mail?scope=sub?(objetclass=person) ldap://ldap.point-libre.org/cn=Manon,ou=Contact,o=point-libre.org @ActiveDirectory.DC_example_DC_com?sn,telephoneNumber,physicalDeliveryOfficeName?sub?(Description=Desc)DNTable (String) : Name of the DN Table (Storing the unique object path in the directory tree) to write.
ClassTable (String) : Name of the Classes table (The class list defines the object structure assuming the knowledge of the listed class structures) to write. Warning : put * or nothing in the attribute part of the URL to get the class list.
AttributeTable (String) : Name of the attribute table to write (types and values) to write. For an object, several values can be set by type.
Example
Use_Data_Zone("technical");
Ldap_Search("ldap://ldap.math.jussieu.fr:389/dc=math, dc=cnrs, dc=fr??sub?(objectClass=*)","DNS","CLASSES","TYPES");
Ldap_Search("@ActiveDirectory.DC_example_DC_com?sn,telephoneNumber,physicalDeliveryOfficeName?sub?(Description=Desc)","DNS","CLASSES","TYPES");
Description
This function reads the last received emails on a POP3 or IMAP server.
Parameters
Dir (String) : Full path where the attached files will be stored,
FilterFrom (String) : From field filter string. If this string matches whole or part of the from field, the email is received. When this filter is null, all the emails not yet received (all the one following the last received mail in reception order) are received,
Options (String) : Option string. The syntax is as follows : "[NBRETRY=n][,RETRYDELAY=n],[,NBJDEL=n][,HEADERS=0|1|false|true] [,BODY=0|1|false|true][HOST=host][LOGIN=login][PASSWD=passwd][,DELONLY=0|1]"
NBRETRY specifies the numbre of retries when receiving email from the server (some servers don't easily accept connections) before throwing a receiving error. The default value for this parameters is read from the environment settings "Outgoing Mail Server/Number of Retries",
RETRYDELAY specifies the delay in milliseconds between 2 receive tries. The default value is 0,
NBJDEL specifies the number of days of history to keep in the email server.
HEADERS=1 specifies to receive only email headers. MSGBODY and MSGATTACH from the READMAIL will be null.
HOST specifies the POP3 server host if different from default set in environment settings "Outgoing Mail Server/Server Host"
LOGIN specifies the login name if different from default set in environment settings "Outgoing Mail Server/Login"
PASSWD specifies the password if different from default set in environment settings "Outgoing Mail Server/Password"
BODY=1 specifies to get MSGBODY and MSGATTACH fields of the emails specified by the FilterFrom query parameter.
DELONLY specifies to do nothing else but suppress emails from the server.
PROTOCOL specifies the protocol : pop3 ou imap. The default value is pop3.
FLAGS specifies connection flags. These flags can be the following : /secure,/ssl,/validate-cert,/novalidate-cert,/tls,/tls-sslv23,/notls,/readonly,loser.
MAILBOX_NAME specifies the mailbox name. The default value is INBOX.
Example
Readmail("c:\temp",,"HOST=pop3.xxx.fr,LOGIN=XXXX,PASSWD=passwd");
OPEN_SQL("select * from READMAIL where RECVDATE>=%DATE%");
// example 2 with header reading and body filtering
Readmail("c:\temp",,"HOST=pop3.xxx.fr,LOGIN=xxxx,PASSWD=****,HEADERS=1");
Readmail("c:\temp","select MSGID,MSGDATE from READMAIL where MSGSUBJECT like '%pres%' and MSGBODY is null","HOST=pop3.xxx.fr,LOGIN=xxxxxx,PASSWD=****,NBJDEL=-1,BODY=1");
Description
This function sends an email to an SMTP server.
The SMTP server setting is done through the environment settings "Outgoing Mail Server/Server Host", "Outgoing Mail Server/Login", "Outgoing Mail Server/Password", "Outgoing Mail Server/Port".
Parameters
Destination (String) : Destination addresses. The syntax is as follows : "email_address1[,email_address2] [,email_address3]...". It is possible to put as many addresses as needed,
CC (String) : Carbon Copy destination addresses. The syntax is as follows : "email_address1[,email_address2][,email_address3]...". It is possible to put as many addresses as needed,
CCC (String) : Hidden Carbon Copy addresses. The syntax is as follows : "email_address1[,email_address2] [,email_address3]...". It is possible to put as many addresses as needed,
Sujet (String) : Email subject,
Message (String) : Email message. The syntax is as follows : "message[<SQL>: sql_query][<FILE>:file_path]"
<SQL>: sql_query : The identifier <SQL>: followed by sql_query statement inserted in the message specifies to execute and output the query statement. The message is then dynamic. The query result is formatted in the mail as a table with a title line containing column names. This identifier must be a the end of the message,
<FILE>: file_path : The identifier <FILE>: followed by file_path statement inserted in the message specifies to insert an external file content in the message like an HTML signature for instance,
Files (String) : List of files to attach to the email. The syntax is as follows : "file_path1[,file_path2] [,file_path3]...". It is possible to put as many files as needed,
Options (String) : Option string. The syntax is as follows : "[NBRETRY=n] [,RETRYDELAY] [,FROMADDR=from_address] [,FROMNAME=from_fullname] [,MSGTYPE=TEXT|HTML] [,HOST=hostname] [,LOGIN=login] [,PASSWD=password]"
NBRETRY specifies the number of sending retries (some email servers don't easily accept connections) before throwing a sending error. The default value is read from the environment settings "Outgoing Mail Server/Number of Retries" parameter,
RETRYDELAY specifies the dealy between 2 email sending tries. The default value is 0,
FROMADDR specifies the from address displayed in the received email From field. The default value is read from the environment settings "Outgoing Mail Server/From Address" parameter,
FROMNAME specifies name displayed in the received email From field. The default value is read from the environment settings "Outgoing Mail Server/From Complete Name" parameter,
MSGTYPE specifies the body type of the message : TEXT (default value) or HTML,
HOST specifies an SMTP server host address (IP or name).The default value is read from the environment settings "Outgoing Mail Server/Server Host" parameter,
LOGIN specifies the login name to use when connecting to the SMTP server. The default value is read from the environment settings "Outgoing Mail Server/Login" parameter,
PASSWD specifies the password to use when connecting to the SMTP server. The default value is read from the environment settings "Outgoing Mail Server/Password" parameter,
Example
SendMail("user1@orga1.com,user2@orga2.com","user3@orga3","","DataStudio Projet Progress","The project XXX started <SQL>: select * from TABLE1");
SendMail("user1@orga1.com,user2@orga2.com","user3@orga3","","DataStudio Projet Progress","The project XXX started <SQL>: select * from TABLE1 <FILE>:c:\signature.html","MSGTYPE=HTML");
Description
This function connects to an ssh server and return its public key fingerprint. This fingerprint can then be used to authenticate this server in the other ssh functions.
Parameters
Server (String) : Name or IP address of the ssh server host,
VariableName (String) : Variable name that will be create with the fingerprint value of the server,
Example
ssh_getfingerprint("192.168.100.53","fingerprint");
ssh_shellcommand("192.168.100.53","login","passphrase","",
"ls -l
ps -ef",
"TOTO","PUBKEYOPENSSHFILE=pubkey.pub,AUTHMODE=KEY,PRIVKEYPEMFILE=privkey.pem,FINGERPRINT=%fingerprint%");
Description
This function connects to an ssh server, eventually sets environment variables, executes the script and stores the output.
Parameters
Server (String) : Name or IP address of the ssh server host,
User (String) : User name authorized on the server,
Password (String) : User password or passphrase of the private key when in KEY authentication mode,
EnvironnementVariable (String) : List of environment to be set before executing the script. Variables are seperated by commas and written as env_varname=value,
Script (String) : Script to execute on the server. The instructions are seperated by end-of-line characters or semi-colon characters,
ContextStdOut (String) : Context name used to read the resulting output with Cursor function,
Options (String) : Option string. The syntax is as follows : "PUBKEYOPENSSHFILE=file.pub, PRIVKEYPEMFILE=file.pem, FINGERPRINT=string, AUTHMODE=KEY|PASSWORD"
PUBKEYOPENSSHFILE specifies an SSH format file containing the user public key (this file content looks like :ssh-rsa AAAAB3Nz...VnzT rsa-key-20070919) to authenticate against,
PRIVKEYPEMFILE specifies the communication encryption user private key PEM format file (OPENSSL),
FINGERPRINT specifies to check server authenticity against the fingerprint previously obtained by a call to SSH_GetFingerPrint function
AUTHMODE specifies the authentication mode : KEY for private/public key encryption, PASSWORD for login/password. The default value is PASSWORD.
Example
ssh_shellcommand("192.168.100.53","login","passphrase","",
"ls -l
ps -ef",
"TOTO","PUBKEYOPENSSHFILE=pubkey.pub,AUTHMODE=KEY,PRIVKEYPEMFILE=privkey.pem,FINGERPRINT=%fingerprint%");
Description
This function adds or replaces a column definition in a DataObject.
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
Code (String) : Column code,
Label (String) : Column label,
Order (Integer) : Order number of the column,
Datatype (String) : Column datatype.
For a file DataObject the possible values are : "Integer", "Float", "Currency", "Date", "String", "Order",
For a database DataObject the possible values are : "Unknown", "String", "Smallint", "Integer", "Word", "Boolean", "Float","Numeric", "Currency", "BCD", "Date", "Time", "DateTime", "Bytes", "VarBytes", "AutoInc", "Blob", "Memo", "Graphic", "FmtMemo", "ParadoxOle", "DBaseOle", "TypedBinary", "Cursor ","FixedChar", "WideString", "Largeint", "ADT", "Array", "Reference", "DataSet","Order",
StartPos (Integer) : Offset from the begining of the line of the field in characters for fixed position files, else 0
Length (Integer) : Field length in characters for fixed position files. This length is also used set the temporary database image table string field size when the datatype is "String", else 0,
Prec (Integer) : This parameter is valid for "Numeric" type only, and specifies the total number of digits of the number, else 0. Do not set a non-zero value for this parameter if the field is not of "Numeric" type,
Dec (Integer) : Number of digits for the decimal part for a numeric datatype, else 0,
Nulls (Booléen) : Allow nulls or not. Possible values are : "Yes" or "No",
Format (String) : Format string for text files,
Index (Booléen) : Column is part of the primary key. Possible values are : "Yes" ou "No".
Example
Add_Replace_Column("ZONE_TEXTE1", "FIC1", "COL1", "Colonne 1", 0,"Integer", 0, 0, 0, "Yes", "d","Yes"); //Add or replace column COL1
Description
This function checks the validity of a DataObject,
Contrôle que le Data Object est toujours valide, that is to say if it still exists an it is still accessible.
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
Variable (String) : Name of the variable to create and set with the returned check result,
If check is OK, the value is 0 otherwise the value can be one of the following :
1 : Invalid DataZone
8 : Physical object not found on system
16 : Invalid DataObject description
Options (String) : String d'option. The syntax is as follows :: "[SAVE=0|1][,REDOCHECK=0|1][,ERROR=0|1][,WARN=0|1]"
SAVE specifies to save the result in database (in the repository) . The default value is SAVE=0.
REDOCHECK specifies to redo the check. When REDOCHECK=0, the check return value is read from the database (repository). The default value is REDOCHECK=1.
ERROR specifies to raise an error when the check return an invalid result value. The default value is ERROR=0.
WARN specifies to output a warning message when the check returns an invalid value. The default value is WARN=0.
Example
Check_Data_Object("TEXT_ZONE1", "FILE1","OBJCHK");
Message("The DataObject check result is ",%OBJCHK%);
Description
This function checks the validity of a DataZone (the directory exists and is accessible, the database connexion is ok, the xml file is accessible and valid);
Parameters
ZoneCode (String) : DataZone code,
Variable (String) : Name of the variable to create and set with the returned check result. If check is OK, the value is 0 otherwise the value is 1,
Option (String) : Option string. La syntaxe est la suivante : "[SAVE=0|1][,REDOCHECK=0|1][,ERROR=0|1][,WARN=0|1]"
SAVE specifies to save the result in database (in the repository) . The default value is SAVE=0.
REDOCHECK specifies to redo the check. When REDOCHECK=0, the check return value is read from the database (repository). The default value is REDOCHECK=1.
ERROR specifies to raise an error when the check return an invalid result value. The default value is ERROR=0.
WARN specifies to output a warning message when the check returns an invalid value. The default value is WARN=0.
Example
Check_Data_Zone("ZONE1","ZONE1CHK","WARN=1");
Message("The datazone check is ",%ZONE1CHK%);
Description
This function copies the column description from one DataObject to another.
Parameters
ZoneCode (String) : DataZone code of the source DataObject,
ObjectCode (String) : DataObject code of the source DataObject,
DestZoneCode (String) : Destination zone code,
DestObjectCode (String) : Destination object code that will have the same column description as @ZoneCode.ObjectCode,
Example
Copy_Columns("TEXTE_ZONE1", "FILE1","TEXT_ZONE1", "FILE2");
Description
This function copies a DataObject from one DataZone to another (including the data).
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
DestZoneCode (String) : Destination DataZone code,
DestObjectCode (String) : Destiantion DataObject code,
WhereClause (String) : Where clause to filter the data transfered.
Example
Copy_Data_Object("TEXT1_ZONE", "FILE1","RDBMS_ZONE", "TABLE4");
COPY_DATA_OBJECT("RDBMS_1","FOLDER","RDBMS_TEST", "FOLDER","where ID<1000");
Description
This functions counts the number of rows of a DataObject. It is optimized by DataZone type (much faster than the query select count(*) from @Zone.Objet that implies a data transfer).
Warning : In the case of non-RDBMS objects, the function counts the lines of the physical media and it may be different from the line count returned by select count(*) from @Zone.Objet that count the lines from the database image. The latter may have been modified and not yet flushed with FLUSH_DATA or USE_DATA_ZONE function.
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
Variable (String) : Name of the variable to create and set with the returned number of lines,
Chaine d'options (String) : Option string. The syntax is as follows : [METHOD=PHYSICAL|AUTO]
METHOD specifies if the lines are always counted for the physical underlying media (METHOD=PHYSICAL) or if they are counted from the up-to-date media (METHOD=AUTO). The default value is AUTO.
Example
Count_Data_Object("TEXT_ZONE1", "FILE1","COUNTF");
Message("The DataObject number of lines is ",%COUNTF%);
Description
This function creates a new DataObject in a DataZone.
The creation can be logical and physical (creation of a file or a table) or just logical.
Warning : The objects in an XML DataZone are automatically created when creating the DataZone (see Create_Data_Zone function).
Parameters
ZoneCode (String) : DataZone code,
Code (String) : DataObject code to create
Label (String) : DataObject label to create
Separator (String) : Field separator when DataZone is of type ASCII. The possible values are : "TAB", "SPACE","POSITION","1 free character"
Source (String) : Data source when creating physical objects. When this parameter is set, the DataObject creation is both logical and physical, otherwise it is only logical. This parameter set the content and structure of the created DataObject. The source can be one of the following :
The query result (structure and data) will be copied in a table or a file named as the DataObject code or as setted by the PHYSNAME option parameter when present and a corresponding DataObject will be created.
The object will be copied in a table or a file named from the object code or as setted by the PHYSNAME option parameter when present. This type of loading is faster than the previous select query method.
The object is copied like in the previous case. In addition, the transfered data is filtered (only for RDBMS objects). The filtering is processed by the source RDBMS. To filter data in memory, and notably for non-RDBMS see Set_Filter function. This type of loading is faster than the previous select query method.
Options (String) : Option string. The syntax is as follows : "[PHYSNAME=physical_name,][AUTODEF=True|False,][RECREATE=True|False,][SOURCETYPE=OBJECT|QUERY,][SOURCEDATAZONE=ZONECODE,][EOL=DOS|UNIX|CR,][HEADPOS=n,][DATAPOS=n,][FORCENOTEXISTS=True|False][,QUOTED=DOUBLEQUOTE|SIMPLEQUOTE|<one character>]".
PHYSNAME specifies the filename or the table name when it is different from the DataObject object code,
Warning : For files in "Excel" type DataZones, a special syntax allows to specify any part of a sheet (see example) :
file: Name of the excel file including its extension,
sheet : Name of the excel file sheet,
zone : Named area identifier referencing a cell range in the excel file,
x1 : represents the column name of the cell range upper left corner (one or two characters),
y1 : represents the line number of the cell range upper left corner,
x2 : represents the column name of the cell range lower right corner (one or two characters),
y2 : represents the line number of the cell range lower right corner,
EOL specifies the end-of-line character. The possible values are : DOS(CR+LF),UNIX(LF) ou CR. When this parameter is not specified, the value used is read from the File EOL environment settings parameter,
HEADPOS specifies the header line position from the begining of the file (integer greater than or equal to zero) or -1 if there are no header. When this parameter is not specified, the value used is read from the File Head Position environment settings parameter,
DATAPOS specifies the first data line position from the begining of the file. When this parameter is not specified, the value used is read from the File Data Position environment settings parameter,
AUTODEF specifies to guess the column definition when the DataZone is a of "FILE" type. The file formatting analysis depends on the following environment settings : "GUI/Maximum memory used by ascii file preview format helper (kb)"and "GUI/Number of lines in ascii file preview format helper". In the case of "RDBMS" category DataZones, the definition is automatically read (and not guessed).
SOURCETYPE specifies if the SOURCE is a query (SOURCETYPE=QUERY) or if it is a DataObject (SOURCETYPE=QUERY) like @ZONECODE.OBJCODE.
SOURCEDATAZONE specifies a DataZone different from the current one. Warning : When using this option, the query cannot contain DataObjects (@ZONE.OBJET)
RECREATE specifies to recreate the object when it already exists (RECREATE=True). If this option is not set and the DataObject already exists, an error is generated.
FORCENOTEXISTS specifies to ignore the file non-existence when creating the DtaaObject and to not generate an error.
QUOTED specifies the field delimiter. DOUBLEQUOTE for double quotes, SIMPLEQUOTE for simple quotes or a free character.
Example
Create_Data_Object("ZONE_SGBD1","TABLE1","Object number 1"); //logical creation of a DataObject corresponding to TABLE1,
Create_Data_Object("TEXT1_ZONE","FILE1","File object number 1",";","","PHYSNAME=file1.txt,AUTODEF=True"); //logical creation of an object corresponding to a text file in the directory of the DataZone TEXT1_ZONE.
Create_Data_Object("RDBMS_ZONE1","TABLE2","Objet number 1",,"select * from TABLE1 where COL1 like '%LI%'"); //Physical creation of TABLE2 and the corresponding object as a query on TABLE1. TABLE2 must not pre-exist,
Create_Data_Object("RDBMS_ZONE1","TABLE2","Objet number 1",,"select * from TABLE1 where COL1 like '%LI%'","PHYSNAME=TABLE3"); //Physical creation of TABLE3 and the corresponding object named TABLE2 as a query on TABLE1. TABLE3 must not pre-exist,
Create_Data_Object("TEXT_ZONE1","FILE2","Objet number 1",";","select * from TABLE1 where COL1 like '%LI%'","PHYSNAME=new_file.txt",QUOTED=DOUBLEQUOTE); //Physical creation of file new_file.txt with fields separated by a semi-colon and delimited by a double quote and the corresponding object named FILE2 as a query on table TABLE1. The file new_file.txt must not pre-exist,
Create_Data_Object("ZONE_EXCEL2","TABLE_COORD","table in the middle of th sheet","","","HEADPOS=-1,DATAPOS=-1,PHYSNAME=test_1.xls|Feuil7!$G$23:$I$23);
Description
This function creates a new DataZone.
Parameters
Code (String) : DataZone code,
Label (String) : DataZone label,
Category (String) : DataZone category. The possible values are summarized in the table below,
Type (String) : DataZone type. The possible values depend on the category. The possible values are summarized in the table below :
Path (String) : Physical resource access path string.
Note 1 : Pour access to a local database the SERVER parameter can be omitted.
Note 2 : The DB parameter is only needed when the RDBMS uses this notion.
THe ALIAS part of the path can be one of the following values depending on the Type parameter :
ConnectString (String) : String displayed in the DataZone definition window.
Option (String) : Option string : The syntax is as follows : "OPTION1=Value1[,OPTION2=Value]...". The possible option names depend on the category and the type of the DataZone. The following table summarizes the possibilities :
AUTONAME (default value : 1) : specifies to automatically generate a DataObject code, otherwise the code is the physical name of the object. The possible values are 0 and 1,
BULK (default value : 0) : specifies to use bulk loading when transfering data. This option works only with the following databases : Oracle, Sybase, SqlServer and DB2. The possible values are 0 and 1,
CASE (the default value depends on the database type) : specifies how to transform identifier character case. The possible values are : 0=No transformation, 1=Convert to upper case, 2=Convert to lowe case, 3=No transformation but the RDBMS is not case-sensitive.
DIRECTACCESS (default value : 0) : specifies to activate the direct loading into the DataViewer without transfering data into a temporary table to speed up viewing in the case of excel and text files.
EMPTYSTRMODE (default value : 2) : specifies how to manage an empty string : 0=Empty string, 1=Null value, 2=Automatic value depending on the RDBMS.
INSERTMETHOD (the default value depends on the database type) specifies the transfer technique used for DataObjects automatic temporary tables (when using @Datazone.DataObject syntax). The possible values are : 0=Bind :insert lines by he means of bind query pointing to an array of values in memory, 1=Union : insert lines using blocks of one line inserts assembled with the union all statement, 2=Loop : insert lines using multiple queries inserting each one line but sent together to the server and seperated by the SQL database dependent end of statement (go for sybase, semi-colon for oracle etc.), 3=x : do not use, 4=Union + Bind : insert lines using the combination of method 0 and 1, union all blocks of bind queries poiting to an array of values in memory, 5=Loop + Bind : insert lines using the combination of method 1 and 2, send multiple seperated queries all pointing on array of values in memory,
ISOLATION (default value : 1) specifies the transaction isolation level (ansi levels). The possible values are the following : 0=Read uncommited (dirty read), 1=Read commited, 2=Repeatable read, 3=Serializable,
MAXVARCHAR (the default value depends on the database type) specifies the maximum varchar type field size for the database. This value is used when replacing memo type fields by varchar type fields when the database has some limitations. It is also used to truncate varchar type fields when transfering data. The possible values are all positive integers.
NBFETCH (default value : 256) specifies the number of lines to read at a time in on fetch. The possible values are all positive integers.
NBODBCTABLEFETCH (default value : 1) specifies the number of tables returned by the ODBC API. The possible values are all positive integers.
NOREPETITIONCOLS (default value : empty string) specifies the comma separated names list of the XML tags that the structure discovery algorithm will not consider as possible first column for a table. For instance, let the following tag structure be :
<table>
<line>
<property><name>PROP1</name><value>VAL1</value></property>
<property><name>PROP2</name><value>VAL2</value></property>
<property><name>PROP3</name><value>VAL3</value></property>
</line>
<line>
<property><name>PROP5</name><value>VAL5</value></property>
<property><name>PROP6</name><value>VAL6</value></property>
<property><name>PROP7</name><value>VAL7</value></property>
</line>
</table>The structure analysis algorithm identifying table structures based on repeated line schema will find 2 tables
PROP1 VAL1 PROP2 VAL2 PROP3 VAL3
PROP5 VAL5 PROP6 VAL6 PROP7 VAL7 If the option is set to "NOREPETITIONCOLS=property"
the algorithm will only find one table because it is not allowed to consider that the property tag can start a repeating xml section.
PROP1 VAL1 PROP2 VAL2 PROP3 VAL3 PROP5 VAL5 PROP6 VAL6 PROP7 VAL7 Here the column names are modified by the discovery algorithm in order for all column names to be unique. The possible values are all the tag names separated by commas as defined by the previous list syntax.
PREFETCH (default value: 32) : specifies the prefetch value to optimize fetch performance. The possible values are all positive integers,
QUOTEDID (the default value depends on the database type) : specifies to put double-quotes around table and column identifiers when generating queries. The possible values are 0 and 1,
READONLY (default value : 0) : specifies a read only DataZone to prevent automatic data transfer mechanism to write to the DataZone (DataViewer updates, when using the "@DataZone.DataObject" syntax and temporary table creation since use_data_zone cannot point to the read only DataZone.
REMOVEQUOTESONEMPTYSTRINGS (default value : 0) : specifies to remove quotes for DataObject with QUOTED option set around empty strings,
REMOVEQUOTESONNULLS (default value : 1) specifies to remove quotes for DataObject with QUOTED option set around null strings,
REQNOLINES (default value : "where 1=0" ) specifies the where condition of the query "select 1 from <table> ..." that return only the result set structure and no lines. The possible values are all the valid "where" statements never returning a line.
REQPING (the default value depends on the database type) specifies the "ping" query. This query must be an always valid query to test the connection. For instance "select 1 from dual" fro ORACLE. The possible values are all always valid queries.
VIEWALLTABLES (default value : 0) specifies if the connection lists all accessible database table and views objects or only owned database objects. The possible values are 0 and 1.
VIEWTHREAD (default value : 1) specifies background data fetch in the DataViewer window. Possible values are 0 and 1.
VALIDATION (default value : 1) specifies the deactivation of XML file validation against a schema or DTD file. The possible values are 0 and 1.
XMLDEFCOLSIZE (default value : 50) specifies the default size of the data XML column. The possible values are all positive integers.
XMLDEFVIRTUALCOLSIZE (default value : 10) specifies the default size of virtual XML column. The possible values are all positive integers.
Example
Create_Data_Zone("ZONE1", "Zone de test", "SGBD", "ORACLE", "USR=scott,PWD=tiger,SRV=TEST.WORLD,ALIAS=ORACLE","scott/*******@TEST.WORLD","BDE=0");
Create_Data_Zone("SGBD_OTHER","Connection access ODBC","SGBD","OTHER","ALIAS=ACCESS_ODBC");
Create_Data_Zone("INTERNET_XML","Zone XML","INTERNET","XML2","http://www.monsite.fr/temp/attributes.xml");
Description
This function creates a temporary image table pool fir an RDBMS DataZone. These tables already created will be used instead of creating new temporary tables when needed for the transfer which is a slower process.
Parameters
ZoneCode (String) : DataZone code of the DataObject,
ObjectCode (String) : DataObject code,
ImageZoneCode (String) : DataZone code where the data are transfered and where the temporary tables are to be pooled,
ImageTableName (String) : Pool table prefix in the destination DataZone.
Example
Create_TmpPool("TEXT_ZONE1", "FILE1","TECHNIQUE","TMPTEXT_FILE1");
Description
This function creates a DataStudio user. This function only works when logged in as an Administrator type user.
Parameters
Username (String) : User name,
Password (String) : User password,
Type (String) : User type. The possible values are :
- "ADMIN",
- "DESIGN",
- "USER",
- "EXPLOIT".
Warning : The default value is "ADMIN",
Label (String) : User label. The default value is null,
Example
Create_User("Operator1","op1","EXPLOIT");
Description
This function frees all the input datazone associated resources used (with a call to Use_Data_Zone or when transfering an XML object) by the folder of the current job or not in use. A datazone is used when calling Use_Data_Zone function or when transferring data from or to an Internet/XML type object.
The DataZone must be of RDMS type (excluding ODBC type) or Internet/XML.
The resource release corresponds to server disconnection for RDBMS and parsing resources (mainly memory) for internet zones.
If a query is executed after the call to this function without calling Use_Data_Zone, the last connected DataZone is re-connected
Parameters
Code (String) : DataZone code,
Example
Disconnect_Data_Zone("ZONE1");
Description
This function drop a DataObject. The Physical option drop the underlying physical media (table or file). The Logical option only drops the DataObject definition in the DataStudio repository.
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
Mode (String) : Drop mode. The possible values are : "Physical", "Logical", "Both". "Physical" : drop the table or the file correponding to the object, "Logical" : only drop the DataStudio object, "Both" : drop both logical and physical object.
Example
Drop_Data_Object("TEXT_ZONE1", "FILE1","Both");
Description
This function drops a DataZone.
Parameters
Code (String) : DataZone code.
Option (String) : Option string. The possible values are : "RECURSE" : drop the datazone and all the dataobjects owned in logical mode.
Example
Drop_Data_Zone("ZONE1");
Description
Drop the temporary table pool associated with the DataObject.
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
Example
Drop_Tmp_Pool("TEXT1_ZONE", "FILE1","Both");
Description
This function writes back to the physical media the temporary data generated when using non RDBMS source.
Parameters
ZoneCode (String) : DataZone code.
ObjCode (String) : DataObject code. If the object code is not set all objects written will be flushed.
Option (String) : Option string. The possible values are : "TESTFLUSH=NONE|WARNING|ERROR" to specify the behavior when no objects were written. The default value is WARNING.
Example
Use_data_zone(SGBD);
insert into @ASCII.OBJ1 (COL1) values('XXXXX');
Flush_Data("ASCII","OBJ1");
Description
This function imports all objects from a DataStudio repository xml export file.
Parameters
File (String) : Full path of the exported XML file,
Example
ImportFull("c:\temp\exp.xml");
Description
Insert lines in a DataObject coming from a source.
Parameters
ZoneCode (String) : DataZone code,
Code (String) : DataObject code,
Source (String) : Line source statement when the parameter is set,
Options string (String) ; Option string. The syntax is as follows : "[APPEND=0|1,][SOURCETYPE=OBJECT|QUERY,][SOURCEDATAZONE=ZONECODE][,SOURCEDATAZONE=ZONECODE][,MAPPING=NONE|AUTO|AUTOCI|AUTONOERR|AUTOCINOERR|MANUAL;COLX=COLY..]".
APPEND specifies if the source lines are added to the existing ones (APPEND=1) or if the source lines replace the existing ones (APPEND=0). The default value is "APPEND=1".
SOURCETYPE : Specifies the source type : "QUERY" the source is a SQL query. "OBJECT" the source is a DataObject written @ZONECODE.OBJCODE.
SOURCEDATAZONE: specifies the execute the query on a DataZone different from current. Warning, when using this option, the query must not contain dataobject syntax as @ZONE.OBJECT.
MAPPING: specifies the link between source and destination columns.
- NONE : Use column numbers.
- AUTO : Use case sensitive column names(COLCODE) . All destination columns must be in source.
- AUTOCI : Use case insensitive column names(COLCODE) . All destination columns must be in source.
- AUTONOERR : Use case sensitive column names(COLCODE). The destination columns not present in source are not filled.
- AUTOCINOERR :Use case insensitive column names(COLCODEThe destination columns not present in source are not filled.
- MANUAL;COLDEST1=COLSOURCE1;COLDEST2=COLSOURCE2... : Manual columns specification in the form Destination = Source separated by a semicolon.
Example
Insert_Data_Object("ZONE_SGBD1","TABLE1","select * from TABLE2","SOURCETYPE=QUERY");
Description
This function opens the DataViewer and displays the DataObject content.
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
Chaine d'options (String) : Option string. The syntax is as follows : "[WAIT=0|1,][TIMEOUT=0|1,]".
WAIT specifies to wait for the user to close the DataViewer window (WAIT=1) before returning. The dafault value is WAIT=0 (no WAIT).
TIMEOUT=[number of seconds] specifies, in the case WAIT=1, to automatically close the DataViewer after the delay expires if the user didn't close the window. The default value is TIMEOUT=0 (no timeout).
Example
Open_Data_Object("ZONE_TEXTE1", "FIC1");
Description
This function checks that RDBMS connections to the input DataZone are still valid and tries to reconnect invalid ones.
Parameters
ZoneCode (String) :DataZone code,
Variable (String) : Variable name to create and set with check result value.
If the check is OK the result is a positive integer or zero, otherwise the result is a negative integer (reconnections could not have been completed).
Example
Ping_Data_Zone("ZONE1","VAR1");
if("(%VAR1%<0)")
{
Message("The ping failed");
}
Description
This function renames DataObject changing its code and possible the name of the underlying physical media.
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
NewObjectCode (String) : New DataObject code,
Mode (String) : Renaming mode. The possible values are : "Physical", "Logical","Both"(default). "Physical" specifies to rename the physical object (table or file), "Logical" to rename only the DataObject code, "Both" to rename the DataObject and the physical media",
Example
Rename_Data_Object("TEXT_ZONE1", "FILE1","FILE2","Both");
Description
This function attaches a filter to a DataObject. It works like a SQL Where clause. (e.g. : NAME like '%TOTO%' and PRICE>150'). This filter is applied when transfering data from a DataZone to another.
Parameters
ZoneCode (String) : DataZone code,
ObjectCode (String) : DataObject code,
FilterCode (String) : Filtre code needed to drop the filter,
FilterText (String) : String specifying the a filtering statement. The syntax is as follows : "<condition>". The syntax elements are the following:
<condition> a condition is an expression that evaluates to true or false
<value> <comp> <value> comparison between 2 values with a comparison operator
<value> IN <value_list> presence of a value in a list ( <condition> ) a condition between parenthesis <condition> AND <condition> "logical and" between 2 conditions <condition> OR <condition> "logique or" between 2 conditions NOT <condition> opposite of the condition result <comp> = equals operator LIKE like operator. This operator compares a value to a string describing a value with % wildcard character (ex : '%toto%'). Wildcard character can be replaced by any string < less than operator <= less than or equal operator > greater than operator >= greater than or equal operator != not equal to operator <value> a value that can be a string, an integer, or a floating point number. It can be a literal or the result of a function or of an operation. <string> string type value composed of alpha numerical characters between to simple or double quotes (e.g. :'abf154')
<integer> string type value composed of numerical characters only (e.g. : 58465) <float> string type value composed of numerical characters only and a dot as a decimal separator (e.g. :456.248) <col> string type value describing the name of a column of the DataObject on which the filter is applied. The filter will be evaluated for each of the lines of the DataObject and the column is then replaced by each of the line values for this column.The data type corressponds to tthe column data type of the DataObject. strpos(<string> , <string>) function returning the starting position of the second string in the first one as an integer.
strlen (<value>) function returning the size of the string value as an integer
substr(<string> , <integer1> , <integer2>) function returning the substring of the input string value starting at integer1 position and of size integer2
concat(<string> , <string>) function returning the concatenation of the two input string
upper(<chaine>) function returning the input string where each character has been converted to its upper case equivalent
lower(<chaine>) function returning the input string where each character has been converted to its lower case equivalent rtrim(<chaine>) function returning the input string without trailing blanks
ltrim(<chaine>) function returning the input string without leading blanks trim(<chaine>) function returning the input string without trailing and leading blanks <value> + <value> sum of 2 values (values must be numbers) <value> - <value> difference of 2 values (values must be numbers) <value> * <value> multiplication of 2 values (values must be numbers) <value> / <value> division of a value by another (values must be numbers) - <value> opposite of a value (values must be numbers) <value> ^ <value> a value power another (values must be numbers) (<value>) a value between parenthesis is a value <value_list> a list of values sql(<chaine>) function returning a list of values of the type returned by the SQL query input string
(<value_list>) a list of values between parenthesis is a list of values (<value>,<value>, ....) values separated by commas and included in parenthesis is static list of literal values
Example
Set_Filter(ZONE,OBJECT,FILTER1,"COL0 in sql('select NAME from NAMES') OR COL LIKE 'COMP%_%'");
Description
This function signals to DataStudio that the repository has been modified and that part of the memory cache should be reloaded.
The DataStudio repository is loaded in a memory cache so as to speed up access to data. When a job updates the repository with a SQL query without using a repository function, the cache is not up-to-date anymore and it may create random dysfunctions. It is then necessary to use this function.
Warning : Updating the repository using queries is discouraged because it can result in repository corruption and loss of data.
Parameters
Contexte (String) : Identfiers to specifies wich parts of the cache are to be reloaded. The possible values are : "DATAOBJ", "DATAZONE", "OBJDEF", "PROJECT", "PROJDEF", "FOLDER", "FOLDDEF", "JOB", "PARAM", "PARAMVAL", "PARAMDEF", "SCRIPT", "SCRIPTSR", "PROJSESS", "FOLDSESS", "JOBSESS", "PARAMSES", "PROJTREE", "MATCHING", "MATCHGRP", "MATCHREL", "USER", "USERIGHT". These values correspond to the repository tables manually updated.
Example
update DATAOBJ set OBJCODE='TEST2' where OBJCODE='TEST1'; //manual repository update
Signal_DBChanges("DATAOBJ"); //cache synchronization
Description
This function sets the synchronization (done with Flush_Data ou Use_Data_Zone or automatically at the end of a job) mode between physical objects and their temporary image in another DataZone when using @ZONE.OBJECT syntax in queries.
In such queries, DataStudio creates temporary tables and transfers data if necessary. Some of these queries will modify data (e.g. : update @ZONE.OBJ set XXX=0). Usually the updated data then needs to be rewritten to its original media but in some cases not. In complex queries, it can be impossible for DataStudio to determine if data has been modified.
Parameters
ZoneCode (String) : DataZone code,
ObjCode (String) : DataObject code,
Mode (String) : Identifier specifying the synchronization mode. The possible values are : "ALLWAYS" : always, "NEVER" the object is never rewritten, "AUTO" automatic mode analyzing the query. The default value is read from the environment settings "Scripts/DataObject default synchronization mode" parameter. Normally this value should be left to "Auto" in the environment settings.
When "AUTO" mode is set, if the query analysis fails, an error is raised except if the environment settings "Scripts/synchronization unknow query syntax behavior" parameter is set to a value different from 0 (Error).
Warning : If the DataZone is READONLY (see DataZone options), the function has no effect, mode "NEVER" is chosen.
Example
Synchronize_data("ZONE1","OBJ1","NEVER");
update @ZONE1.OBJ1 set COL1=0
flush_data(); // the object ZONE1.OBJ1 is not written
Description
Specify the DataZone as the processing RDBMS DataZone. The DataZone must be of RDBMS type.
SQL instructions following this function call will then be executed on this DataZone.
When changing the working DataZone, all the objects previously used in writing mode are synchronized back to their physical media (See FLUSH_DATA function for manual synchronization).
When the DataZone is not specified by the Use_Data_Zone function the default working DataZone is the repository connection.
Parameters
Code (String) : DataZone code
Example
Use_Data_Zone("ZONE1");
Description
This function call the BDoc-Batch engine from the Business&Document company to generate RTF format documents. After using this function one time, it isn't possible to change the DirConfig and DirExe parameters without restarting DataStudio.
Parameters
DirConfig (String) : Full path of the directory containing the BDoc-Batch engine configuration file CONFIG.CFG,
DirExe (String) : Full path of the directory containing the binary files of BDoc-Batch RTF engine,
Domain (String) : Domain defined in the BDoc-Batch resources,
Model (String) : Model defined in the BDoc-Batch resources,
Version (String) : Model version,
SourceFile (String) : Full path of the XML file containing the data to merge,
ResultFile (String) : Full path of the output RTF format file,
Option (String) : Option string. Reserved for future usage.
Example
BDoc_AssembleDoc("C:\bdoc-batch","C:\bdoc-batch","DOMAIN1","MODEL1","VERSION1","c:\files_xml\dat.xml","c:\rtf\mail.rtf","");
Description
This function activates or deactivates the AutoCommit mode on the current RDBMS server.
Warning : When AutoCommit is deactivated, the commit or rollback must be done manually in the job scripts risking interlocking data access on the RDBMS server.
Parameters
Flag (String) : Etat du mode à positionner. "True" le mode est activé, "False" le mode est désactivé,
Example
AutoCommit("False"); //set Autocommit to false, go to transactional mode
Description
This function opens a new transaction on the curretn DataZone.
Parameters
Aucun
Example
BeginTran(); //new transaction
Description
This function sends a commit to the current working DataZone for the current opened transaction.
Parameters
none
Example
Commit(); //send commit
Description
This function creates a SQL memory table containing the last query data result set or the data events. It wil then be identified by the SqlTabName parameter.
Parameters
SqlTabName : Memory table identifier
SOURCE=LASTSQL|DATAEVENTS specifies the origin. The default value is LASTSQL.When SOURCE=DATAEVENTS columns are EVTLINE,EVTKIND,EVTSTAGE,EVTCOL,EVTVAL1,EVTVAL2.
Example
select LASTNAME,FIRSTNAME from PERSONS;
Create_SqlTab("PERSONS");
Sql_Bind("PERSONS","insert into PERSONS2 (LASTNAME,FIRSTNAME) values(:LASTNAME,:FIRSTNAME)");
Description
This function drops the SqlTab object.
Parameters
SqlTab : Sql table identifier.
Example
Drop_SqlTab(PERSONS);
Description
This function executes a SQL drop instruction for the input table with no error message even if the table doesn't exist.
Parameters
Table (String) : Name of the table to drop,
Example
Drop_Table("TABLE1");
Description
This function renames the table.
Parameters
OldName (String) : Old table name,
NewName (String) : New table name,
Example
Rename_Table("TABLE1","TABLE2"); //TABLE1 is renamed TABLE2
Remarques
This function works on ORACLE, SYBASE, MSSQL, MYSQL but may not work on other RDBMS.
Description
This function executes a rollback on the current connection.
Parameters
None
Example
Rollback(); //rollback the current transaction
Description
This function modifies the system options on the current RDBMS connection (see Use_Data_Zone to select a working DataZone)
Parameters
Option (String) : Option name to modify. The possible values are : "FORCEBIND" and "BULK",
Value (Booléen) : New option value. The possible values are "True" ou "False",
Example
SetOpt("FORCEBIND","True");
Description
This function executes a SQL query on the current working DataZone (see Use_Data_Zone to select a DataZone). This function is implicitly called by DataStudio when the instruction is just the SQL query and makes no function call.
Parameters
Titre (String) : Query title,
Requete (String) : Query to execute.
Example
Sql("query 1","select ACCOUNTNO from ACCOUNTS");
Description
This function executes a SQL query bound to a current working DataZone SqlTab memory table. For each line of the SqlTab table, the query is executed using, for the bound variables, the values read in the line. Bound variables are the column names of the SqlTab table preceded by a semi-colon (:columnname).
Parameters
SqlTab (String) : Name of the SqlTab table.
Query (String) : Bound query to execute.
Handler (String) : Optional error handler function (see Sql_Bind_Cascade function).
Example
select LASTNAME,FIRSTNAME from PERSONS;
Create_SqlTab("PERSONS");
Sql_Bind("PERSONS","insert into PERSONS2 (LASTNAME,FIRSTNAME) values(:LASTNAME,:FIRSTNAME)");
Description
This function executes bound queries in cascade. Each query result set is used bound to the next query and so on through the use of bind variable in Query N that are the column names of the N-1 query preceded by a semi-colon. A handler error function parameter allows to catch errors and adapt the error handling line by line and DataZone by DataZone.
Usually, all queries are select queries and the last one is an update or insert query.
Parameters
DataZone1 (String) : First DataZone where to execute the first query,
Query1OrDataObject1(String) : When the DataZone1 is of RDBMS type, this parameter specifies the select query to be bound to the next query. For the other types, this parameter specifies a DataObject code that will be read line by line and be bound to the next query.
DataZone[n] (String) : DataZone where to execute the Query[n]. This DataZone must be of type RDBMS,
Query[n] (String) : Bind type query. The variable (:columnname) are set to the values returned by the preceding select query.
Handler[n] (String) : Error handler string describing the DataStudio behavior in case of an error raised in the Query[n]. The syntax is as follows : "[IGNORE|ABORT|SQL:RequeteSql|SCRIPT:NomFonctionScript|SQLTAB:IdSqlTab]"
- "IGNORE" : Standard handler that ignores errors. The lines with errors won't be transfered.
- "ABORT" : Standard handler that stops the transfer at the first encountered error.
- "SQL:RequeteSql" : Error stream redirection on a bind query using the sange bind variable as the corresponding query. The bind variables corresponding to columns of Query[n-1] will be set with the Query[n-1] values causing the error on Query[n], added with :ERRMSG (error message label), :ERRNO (error number of the message), :LINENO (line number of the select query).
- "SCRIPT:NomFonctionScript" : Specific error handler function. The function must be defined in the script. When this function is called, the varaible %columnname% are created with the values used by Query[n] causing the error added with %ERRMSG% (message label), %ERRNO% (error number), %LINENO% (line number in the select returned result set).
- "SQLTAB:IdSqlTab" : A line is inserted in a SqlTab memory table, setting the columns with the corresponding bind variables used in the Query[n] based on the column names and added with columns ERRMSG (message label), ERRNO (error number), LINENO (line number in the select returned result set). The SQLTAB must have been created with Create_SqlTab function.
Example
Function f_data_exception()
{
Message("Erreur : %ERRMSG% sur %LASTNAME% %FIRSTNAME%");
}Sql_Bind_Cascade(
"DATAZONESOURCE","select LASTNAME,FIRSTNAME from PERSONS",
"DATAZONEDEST","insert into PERSONS2 (NOM,PRENOM) values(:LASTNAME,:FIRSTNAME)",
"SCRIPT:f_data_exception");Sql_Bind_Cascade(
"DATAZONESOURCE","select LASTNAME,FIRSTNAME from PERSONS",
"DATAZONEDEST","insert into PERSONS2 (LASTNAME,FIRSTNAME) values(:LASTNAME,:FIRSTNAME)",
"SQL:insert into PERSONS2_ERROR values(:LASTNAME,:FIRSTNAME)");Sql_Bind_Cascade(
"DATAZONESOURCE","select LASTNAME,FIRSTNAME from PERSONS",
"DATAZONEDEST","insert into PERSONS2 (LASTNAME,FIRSTNAME) values(:LASTNAME,:FIRSTNAME)",
"SQLTAB:ERRORTAB");
Description
This function updates a SqlTab memory table inserting or updating a line.
Parameters
SqlTab (chaine) : SqlTab memory table identifier.
Line (chaine) : Line number to update. Use -1 for the last row, -2 to insert a line. If the line number goes beyond the number of lines, missing lines will be added.
Column (chaine) : Column name to update.
Value (chaine) : Value to set.
Example
select LASTNAME,FIRSTNAME from PERSONS where 1=0;
Create_SqlTab("PERSONS");
Update_SqlTab("PERSONS","-2","LASTNAME","Smith");
Update_SqlTab("PERSONS","-1","FIRSTNAME","John");
Update_SqlTab("PERSONS","-2","LASTNAME","Doe");
Update_SqlTab("PERSONS","-1","FIRSTNAME","John");
Description
This function sets a lock on a resource and blocks all further lock on this resource until it is released. The resource is unlocked by a call to Unlock function
Parameters
ResourceType (String) : Resource type. Any value can be set. The following values DATAOBJECT,DATAZONE,PROJECT are used by DataStudio designer windows.
Resource (String) : Resource code.
Scope (String) : Lock scope. The possible values are : LOCAL (default value) only block the current instance of tha DataStudio, GLOBAL (not implemented) blocks all instances of DataStudio connected to the same workspace.
TimeOut (Integer) : Timeout in milliseconds. If the wait lasts longer than the time specified by the timeout, an error is raised. The default value is 0 (infinite wait)
Example
Lock("EXEC","CRITICAL_SECTION1",LOCAL,10000);
Drop_Table(TABLE1);
create table TABLE1 ( COL1 varchar(10));
Unlock("EXEC","CRITICAL_SECTION1");
Description
This function unlocks a resource locked by Lock function.
Parameters
ResourceType (String) : Resource type. Any value can be set. The following values DATAOBJECT,DATAZONE,PROJECT are used by DataStudio designer windows.
Resource (String) : Resource code.
Scope (String) : Lock scope. The possible values are : LOCAL (default value) only block the current instance of tha DataStudio, GLOBAL (not implemented) blocks all instances of DataStudio connected to the same workspace.
Example
Lock("EXEC","SECTION_CRITIQUE1",LOCAL,10000);
Drop_Table(TABLE1);
create table TABLE1 ( COL1 varchar(10));
Unlock("EXEC","SECTION_CRITIQUE1");
Description
This function wait for the existence of a file.
Parameters
File (String) : Full path of the file to wait for.
Options (String) : Option string. The syntax is as follows: "[TIMEOUT=n][,ERROR=1 | ERROR=0][,SLEEP=n]"
TIMEOUT specifies the number of seconds to wait before returning a timeout. The default value is "TIMEOUT=60".
ERROR sets the error mode on timeout. If the timeout delay is exceeded and ERROR=1 (default value) the current job ends with an error and so does the project. If ERROR=0 then hte current job ends without executing further instructions but the project goes on,
SLEEP specifies the time between two tests of existence.
Example
WaitFor_File("c:\temp\readme.txt","ERROR=1,TIMEOUT=10");
Description
This function wait for a Query to return non empty result set.
Parameters
Query (String) : Query to execute,
Options (String) : Option string. The syntax is as follows : "[TIMEOUT=n][,ERROR=1 | ERROR=0][, SLEEP=n]"
TIMEOUT specifies the number of seconds to wait before returning a timeout. The default value is "TIMEOUT=60".
ERROR sets the error mode on timeout. If the timeout delay is exceeded and ERROR=1 (default value) the current job ends with an error and so does the project. If ERROR=0 then hte current job ends without executing further instructions but the project goes on,
SLEEP specifies the time between two tests of the query result set.
Example
WaitFor_SQL("select 1 from SYNCHRO_TABLE","ERROR=1,TIMEOUT=10");
Description
This function execute an external program through a call to the command line interface.
Parameters
Title (String) : Title to display in the command line console window title bar,
Binary(String) : Full path of the binary file (any binary file or any file whose extension is associated to a program) with the parameters,
Options (String) : Option string. The syntax is as follows : "[WAIT=True|False] [,NOCMD=0|1] [,EXITCODE=VARIABLE] [,CONSOLE=True|False][,TIMEOUT=n][,HIDE=0|1][,DOSSYNTAX=True|False]"
WAIT specifies to wait for the end of the execution of the external program when WAIT=True. The default value is WAIT=False.
CONSOLE (applicable when WAIT=True) specifies to open a console window when CONSOLE=True. When CONSOLE=False, the console is opened if the file is a batch command file (*.bat or *.cmd). The default value is CONSOLE=True. When the file is an executable ".exe" (binary), CONSOLE=False should be used to avoid the need for putting double-qoutes around the exe file path.
NOCMD specifies the opposite of the CONSOLE option. The command console wil not be opened if :
(CONSOLE=False OR NOCMD=True) and WAIT=True.
EXITCODE specifies to create a variable and to set it with the process exit code and to not generate an error when the exit code is different from 0. EXITCODE is not initially set and an exit code different from 0 raises an error.
DOSSYNTAX (applicable when WAIT=True and WorkDir parameter not set) specifies to call external programs with DOS syntax (e.g. : c:\program files\prog.exe becomes c:\progra~1\prog.exe). The default value is DOSSYNTAX=True.
HIDE (applicable when WAIT=True CONSOLE=0 and, DOSSYNTAX=False or WorkDir parameter set) specifies to hide the console window in console mode.
TIMEOUT (applicable when (Console=False OR NoCmd=True) AND Wait=True AND DOSSYNTAX=True AND WorkDir parameter not set) specifies the maximum execution time of the external program.
On the UNIX platform, the only working options are WAIT and NOCMD.
NOCMD detaches the son process executing the command when WAIT=True.WorkDir (String) : Working directory for the externam program.
Example
Exec("Launch batch processing","c:\batch_dir\batch1.bat","WAIT=True,Console=True"); //Run a windows script in a command line console and wait for the end of the execution.
Exec("Launch the Microsoft Word macro",
"C:\Program Files\Microsoft Office\Office\winword.exe /t "C:\Mes Documents\Projet2.dot" /mmacrotest",
"Wait=1,CONSOLE=False");
Description
This function executes an instruction block in another scripting language.
Parameters
Language (String) : Language of the instruction block. The possible values are : "php","py","batch"standing for php, python and batch languages.
Options (String) : Option string. The syntax is as follows: "[OUTFILE=FILENAME][,NOPARAMPARSE=0|1|True|False] "
OUTFILE=FileName specifies to redirect the standard output of the external scripting engine to file FileName. If this option is not set, use Cursor function (with type set to STDOUT) to read the output of the scripting engine.
NOPARAMPARSE specifies to deactivate the interpretation of %PARAM% syntax (NOPARAMPARSE=1).
Example
Message("Php");
Extern("php")
{echo "RequestId=",$RequestId,"\n";
phpinfo();
$varout = "after";
$f = fopen("out.txt", "wa+");
fwrite($f,"test in php\n");
fclose($f);}
Cursor("","STDOUT")
{
Message("%LINENO%=%STR%");}
Description
This function — on Windows — kills a process.
Parameters
ProgNome (String) : Name of the process to kill,
Options (String) : Option string. The syntax is as follows : "[FORCE=0|1]"
FORCE specifies if the process is asked to close (FORCE=0, défaut) or if it is terminated (FORCE=1),
Example
KillProcess("winword.exe","FORCE=0");
Description
This function reads parameters in the windows registry.
Parameters
Parameter (String) : Name of the variable to create and to set to the value read,
Path (String) : Path of the folder containing the parameter to read. The syntax is as follows : "HKCU|HKLM|HKCR\folder_path".
HKCU stands for HKEY_CURRENT_USER, HKLM stands for HKEY_LOCAL_MACHINE ,HKCR stands for HKEY_CLASSES_ROOT. folder_path corresponds to the folder nodes name sequence separated by a backslash leading to the parameter.
Name (String) : Name of the parameter to read.
Example
ReadReg("PARAMREG","HKCU\software\data\testWriteReg",
"Name1");
Message(" The value read is ",%PARAMREG%")
Description
This function writes in the windows registry. The written value is of type string.
Parameters
Path (String) : Path of the folder containing the parameter to read. The syntax is as follows : "HKCU|HKLM|HKCR\folder_path".
HKCU stands for HKEY_CURRENT_USER, HKLM stands for HKEY_LOCAL_MACHINE ,HKCR stands for HKEY_CLASSES_ROOT. folder_path corresponds to the folder nodes name sequence separated by a backslash leading to the parameter.
Warning :on Windows NT/2000 , there may be write rights restrictions to some registry paths letting only HKCU with write access.
Name (String) : Name of the registry parameter to update
Value (String) : Value to set.
Example
WriteReg("HKCU\software\data\testWriteReg",
"Name1","value1");
Description
This function increments the input variable. It only works on integer variables.
Parameters
Variable (String) : Variable name to increment,
Increment (Integer) : Integer positive or negative value to be added to the variable.
Example
affect(VAR1,0); //set 0 to variable VAR1
:debut; //label ":debut"
Message("Var ",%VAR1%); //display the variable value in the execution monitoring
add(VAR1,1); //increment variable by 1
If(select 1 where %VAR1%<10); //end loop test
goto(debut); //goto ":debut" label
Description
This function creates variable from input variable name and set it with the input value.
It is used for simple variables as for structured variables.
Structured variables are variables whose name starts with a dot. A structured variable value is not a string like with simple variables, it is a set of simple variables or lists each associated with a type and with a tree-like access. The access to the variables (tree leafs) is done through the path in the tree. Structured variable only work with Web Services.
Parameters
Variable (String) : Variable name to create or update; or path to the variable in the structure variable to create or update.
The path syntax is as follows : .<variable_name>.<element_name_level_1>.[...].<element_name_level_n>
An element can be simple or list (type "ARRAY"). In the latter case, the element position must be specified. For instance [...].<element_name_array>[<position>][...]
Valeur (String) : Value to be setted, or path (see above) of a structured variable value to get the value from,
Option : Option string. The syntax is as follows : [TOSQL=0|1][TODATE=FmtDate][ERROR=0|1][WARN=0|1][[GET=VALUE|TYPE|VARTYPE|SIZE|SELECTED|ELEMENTS]|[SET=HEADER]]
TOSQL=1 specifies to format the variable content as an SQL string (mainly add quotes around the string and double the quotes inside the string).
TODATE=FormatDate specifies to read the string as a date of the specified format and to output a corresponding SQL statement of date type to be used in further SQL statements.
ERROR=0 specifies to raise an exception when the variable is not found or when the option is invalid (for structured variables).
WARN=1 specifies to send a warning instead of an error when ERROR=0.
GET=xxx specifies the part of the structured variable to read. The possible values are as follows :
- VALUE gets the value of the selected element. Use only on leaf elements,
- TYPE gets the structure type of the selected element. The types can be one of the following :
- NULL, an uninitialized leaf element. Elements of this type cannot be neither read nor written,
- LEAF, a leaf element,
- COMPLEX TYPE, a non-leaf element containing other elements,
- NULL COMPLEX TYPE, a non-initialized non-leaf element ,
- ARRAY, a list element, leaf or not. Its size can be known with GET=SIZE option value,
- UNION, a non-leaf element containing one of the elements defined by the element union. This element can be determined by GET=SELECTED option value.
- VARTYPE gets the data type of the element.renvoie le type fonctionnel de l'élément ; like the XSD defined type in the case of structures representing SOAP messages,
- SIZE gets the size of an ARRAY typed element,
- SELECTED gets the element selected in a UNION typed element,
- ELEMENTS gets the list of elements of a "COMPLEX TYPE" typed element. The list returned is a comma separated lits of values.
SET specifies to add a structured variable to another structured variable as the header when SET=HEADER (only possible value) section when the latter represents a SOAP message.
Example
Affect(VAR1,1000); //Create and set variable VAR1 to an integer value
select * from TABLE1 where COL1=%VAR1%; //use variable VAR1 in a queryAffect(VAR1,'%TEST%'); //Create and set variable VAR1 to an string value
select * from TABLE1 where COL1 like %VAR1%; //use variable VAR1 in a queryAffect(VARDATE,"31/12/2010","TODATE=dd/mm/yyyy");
insert into TABLE2 (COLDATE) values(%VARDATE%);Affect(".varname.sublevel.leaf","a value"); //set the leaf variable ".varname.sublevel.leaf" of the ".varname" structure variable
Affect("my_leaf", ".varname.sublevel.leaf","GET=VALUE"); //get the value of the variable ".varname.sublevel.leaf" of the structured variable ".varname".
Affect("my_leaf", ".varname.array[2].leaf","GET=VALUE"); //get the value of the element "leaf" of the third element of the array ".varname.array" of the structure variable ".varname".
Affect("type_of_element", ".varname.sublevel.element","GET=TYPE"); //get the type of an element of a structure variable.
Affect(".auth_query_message", ".security_header","SET=HEADER"); //set the header section of ".auth_query_message" stuctured variable with ".security_header" structured variable content.
Description
This function creates a variable containing the comma separated column name list of the last query executed in the job. The variable can then be used in the script with the syntax %VARIABLE_NAME% where VARIABLE_NAME is the value of the Varname parameter.
Parameters
Varname (String) : Varable name that will be used in the following script instructions of the job,
Type (String) : Reserved for future use,
Value (String) : Reserved for future use.
Example
select * from TEST_TABLE; //executed a select query returning values
Affect_LastColumns("TEST1"); //Create TEST1 variable
Message(%TEST1%); //display TEST1 parameter in the monitoring
Description
This function creates a variable set to the value read from the specified line and column of the last query executed in the job. The variable can then be used in the script with the syntax %VARIABLE_NAME% where VARIABLE_NAME is the value of the Varname parameter.
Parameters
Varname (String) : Name of the variable to create,
Line (Integer or NBLINES or NBCOLS) : Line number in the result set returned by the last executed query. The default value is LINE=0, when Line=NBLINES the value is the number of lines of last query, when Line=NBCOLS the value is the number of columns.
Col (String) : Column name in the result set returned by the last executed query. The default value is the first column.
Options (String) : Option string. The systax is as follows : "TOSQL=0|1|2"
TOSQL specifies the type of SQL formatting to apply to the variable content, 0 : no SQL formatting, 1 : SQL formatting with quotes, 2 : SQL formatting without quotes. The default value is TOSQL=0 (no formatting).
DAFAULTVALUE=StringValue specifies then default value when then line number is invalid or then last query has no lines, in this case if DEFAULTVALUE is not specified, the function emits an error.
Example
Select count(*) CO, avg(VAL1) ME from TABLE1; //query returning one line
Affect_LastSql(COUNTER,CO); //create variable COUNTER
Affect_lastsql(MEAN,ME); //create variable MEAN
insert into my_result values(%COUNTER%,%MEAN%); //use variables in a query.select TESTVALUES from TEST_TABLE order by TESTNAMES; //execute a query returning values
Affect_LastSql("TEST1",0,"TESTVALUES"); //create variable TEST1 as the first returned line and "TESTVALUES" column
Affect_LastSql("TEST2",1); //create variable TEST2 comme as the second returned line and the first column (default value) since the query only returns one column.
Affect_lastsql("TEST3",2,"TESTVALUES") ; //create variable TEST3 as the third returned line and "TESTVALUES" column
Message(%TEST1%); //display TEST1 variable content in the monitoring
Message(%TEST2%); //display TEST2 variable content in the monitoring
Message(%TEST3%); //display TEST3 variable content in the monitoring
Description
This function encrypts the input value using the input Key.
Parameters
Variable (String) : Varaible to create and set with the encrypted value,
Value (String) : Value to encrypt,
Key (String) : Encrypting key,
Example
Crypt(CRPASSWD,%PASSWORD%,"Key1");
Description
This function decrypts the input value using input Key.
Parameters
Variable (String) : Varaible to create and set with the decrypted value,
Value (String) : Value to decrypt,
Kay (String) : Encrypting key,
Example
Decrypt(PASSWORD,%CRPASSWD%,"Key1");
Description
This function reads a value in the specified context.
Parameters
Param (String) : Name of the variable to create and set with the read value,
Context (String) : Context code. The possible values are the following :
ENV : Operating system environment variables added with DataStudio environment variables (The name and values of the available variables in this context are visible in the System Informations window),
ERROR : Error variables. The variables available in this context are "MESSAGE" (last error message) et "ERRNO" (last error number),
USER_PARAM : User variables. The name of the available variables in this context are those defined in the User variable window,
GLOBAL_PARAM : Global variables. The name of the available variables in this context are those defined in the Global variables window,
WORKSPACEINFO : Workspace variables defined in the initialization file datastud.ini.
SESSION : Session variables. These variables are created with Set_Variable function.
CodeInContext(String) : Name of the variable in the context,
Example
Get_Variable(USERNAME,"ENV","USERNAME");
Message("The windows user is : %USERNAME%");
Description
This function returns a random integer.
Parameters
Return (String) : Variable to create and set with the result value,
Max (Integer) : Maximum value for the range of values. The default value is 100,
Example
Random("randval", 42); // Return a random number between 0 and 41
Description
This function creates or updates a value in a context.
Parameters
Param (String) : Name of the variable to create or update,
Context (String) : Context code containing the variable. The possible values are the following :
ENV : Operating system environment variables added with DataStudio environment variables (The name and values of the available variables in this context are visible in the System Informations window). When the variable name doesn't exist, a new operating system environment variable is created. This feature can be used to drive external programs using environment variables such as PHP for instance.
GLOBAL_PARAM : Global variables. The name of the available variables in this context are those defined in the Global variables window. When the variable name doesn't exist, a new global variable is created and is available for instructions executing after this function returns,
USER_PARAM : User variables. The name of the available variables in this context are those defined in the User variable window. When the variable name doesn't exist, a new user variable is created and is available for instructions executing after this function returns,
JOB : Job user and system parameters in projects. The possible values correspond to the following syntax :
<folder_code>.<job_code|[job number]>.<USER|SYSTEM>.<parameter_code>.
The possible system variable available exist only for reporting type jobs and are the following : CRYSTALOUTPUTSELECTION, CRYSTALOUTPUTPRINTER, CRYSTALOUTPUTDEFPRINTER, CRYSTALPRINTRANGE, CRYSTALNUMBEROFCOPIES, CRYSTALCOLLATECOPIES, CRYSTALOUTPUTSCREEN, CR, DIF, WORD, RECORD, RTF, CSV, TAB, CHSV, ASCII, TABTEXT, WKS, WKS1, WKS2, EXCEL21, EXCEL30, EXCEL40, EXCEL50, EXCEL50TAB, EXCEL70, EXCEL70TAB, EXCEL80, EXCEL80TAB, ODBC, HTML, HTML4, MSIE2, NS2, PDF, XML,
CRYSTALOUTPUTSELECTION : Comma separated list without any spaces of file format names to generate (see the list of format names below). When file names are already stored for these formats they will be used otherwise a file name based on the job code and the appropriate file extension will be created. Use specific format variables to specify the file name for a given output format,
CRYSTALOUTPUTPRINTER : The possible values are the printer names as listed in the windows printer management window. To deactivate the printer output set this parameter with an empty string value,
CRYSTALOUTPUTDEFPRINTER : The possible values are the printer names as listed in the windows printer management window. This will modify the default printer seen by DataStudio when printing files relying on the default printer for some of the driver settings (such as PDF format),
CRYSTALPRINTRANGE : The possible values are ALL or a string conforming to the following syntax : <first_page>-<last_page> where <first_page> and <last_page> are to be replaced by their correponding values,
CRYSTALNUMBEROFCOPIES : Number of copies to print,
CRYSTALCOLLATECOPIES : 1 to sort output otherwise 0,
CRYSTALOUTPUTSCREEN : 1 to dynamically display the report preview window otherwize 0,
The following variables specify to add the format to the output list and set the file name for this format as the value of the parameter (e.g. : WORD=test.doc) :
CR : Seagate Crystal Reports
DIF : Data Interchange Format
WORD : Word for Windows
RECORD : Record Style (column of values)
RTF : Rich Text Format (RTF)
CSV : Comma Separated Values (CSV)
TAB : Tab Separated Values
CHSV : Character Separated Values
ASCII : Text (ASCII)
TABTEXT : Tab Separated Text
WKS : Lotus 1-2-3 (WKS)
WKS1 : Lotus 1-2-3 (WK1)
WKS2 : Lotus 1-2-3 (WK3)
EXCEL21 Excel 2.1
EXCEL30 : Excel 3.0
EXCEL40 : Excel 4.0
EXCEL50 : Excel 5.0
EXCEL50TAB : Excel 5.0 Tabular
EXCEL70 : Excel 7.0
EXCEL70TAB : Excel 7.0 Tabular
EXCEL80 : Excel 8.0
EXCEL80TAB : Excel 8.0 Tabular
ODBC : ODBC
HTML : HTML
HTML4 : HTML4
MSIE2 : Microsoft Internet Explorer 2 HTML
NS2 : Netscape 2 HTML
PDF : PDF
XML : XML
SESSION : permet d'ecrire une variable valable pendant la session du projet.
Value (String) : New value for the variable.
Options (String) : Option string. The syntax is as follows : "[LISTSEPARATOR=character][,USER=user_name]"
LISTSEPARATOR : separating character for lists. The defult value is the comma,
USER : specifies the user when the context parameter is set to USER_PARAM when different from the current user.
Example
Set_Variable("USER_INFOCENTRE_SYS","ENV","datawharehouse2");
Set_Variable("USER_INFOCENTRE","GLOBAL_PARAM","datawharehouse2");
Set_Variable("USER_INFOCENTRE","USER_PARAM","datawharehouse.data.com","USER=linux");
Set_Variable("USER_VALIDES","GLOBAL_PARAM","linux,Admin");
Set_Variable("PARAM_1","USER_PARAM","linux;Admin","USER=scott,LISTSEPARATOR=;");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.CRYSTALOUTPUTSELECTION","JOB","CR,WORD");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.CRYSTALOUTPUTPRINTER","JOB","Dell Laser Printer 1700n");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.CRYSTALPRINTRANGE","JOB","ALL");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.CRYSTALPRINTRANGE","JOB","1-3");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.CRYSTALNUMBEROFCOPIES","JOB","2");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.CRYSTALCOLLATECOPIES","JOB","1");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.CRYSTALOUTPUTSCREEN","JOB","1");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.CR","JOB","dynamic2.rpt");
Set_Variable("REPORTING1.REPORT_TEST.SYSTEM.WORD","JOB","dynamic2.doc");
Set_Variable("REPORTING1.REPORT_TEST.USER.PARAM1","JOB","3");
Set_Variable("REPORTING1.[0].USER.PARAM1","JOB","3");
Description
This function add data to a project output. Project output is only available when run from a Web Service SOAP access (command line or through an API).
Parameters
Value (String) : Value to add and returned to the caller (except for SQL and FILE type).
Name (String) : Name of the value to add (for SQL type, base name).
Type (String) : Value type to add. The possible values are :
- INTEGER : The value is converted to integer before sending,
- DECIMAL : The value is converted to decimal before sending,
- FILE : The value is a file path. The file is read and the content is sent with the filename,
- SQL : The value is a SQL query. The query is executed and the result set is sent. The line names are [Name]:[line number].
Options (String) : Option string: The only option is COLNAMES and is applicable when TYPE=SQL. When COLNAMES=1 a header line listing the column names named [Name] is added sent.
Example
SOAP_ADD_RETURNDATA("Test","string", "STRING");
SOAP_ADD_RETURNDATA("1","one", "INTEGER");
SOAP_ADD_RETURNDATA("3.1416","pi", "DECIMAL");
SOAP_ADD_RETURNDATA("select SYSDATE from dual","sql", "SQL");
SOAP_ADD_RETURNDATA("select * from IOUSERS","sql2", "SQL", "COLNAMES=1");
Description
This function calls a SOAP remote function previously set in the repository.
Parameters
QueryParam (Variable composite) : Structured variable name correponding to the message to send to the SOAP server.
ResponseParam (Variable composite) : Structured variable to receive the response message from the SOAP server.
SoapAction (String) : SoapAction field of the SOAP protocol needed with some servers. The default value is the one found in the WSDL file.
ErrorVarName (String) : Structured variable to set with the error message returned by the server (accessible in an error handler for instance). This varstructure variable is Soap_Fault typed message. The default variable is .fault.
Example
SOAP_CALL(".echo_query_message", ".echo_response_message");
function soap_handler()
{AFFECT("type", ".fault.detail.fault", "GET=VARTYPE,ERROR=0");}
MESSAGE("Type: %type%");
IF ("('%type%' like '%%CalcException')")
{AFFECT("content",".fault.detail.fault.message");}
ENDJOB(1, "CalcException caught: %content%");
IF ("('%type%' like '%%RandomException')")
{AFFECT("content",".fault.detail.fault.reason");}
AFFECT("dt",".fault.detail.fault.time");
ENDJOB(1, "RandomException caught: %content% at %dt%");
GET_VARIABLE("ERRSTR","ERROR","MESSAGE");
AFFECT("faultstring", ".fault.faultstring", "ERROR=0");
MESSAGE("faultstring is %faultstring%");
ENDJOB(-1, "Unhandled %ERRSTR%");
SET_ERROR_HANDLER("soap_handler");
SOAP_CALL(".calculator_query_message", ".calculator_response_message", "Calculator", ".fault");
Description
This function creates a structured variable from a SOAP message code stored in the repository.
Parameters
SoapMessage (String) : Soap message code to create the variable from.
ParamName (String) : Structured variable name to create. Structured variable names must begin with a dot (.).
Example
CREATE_WS_PARAM("test_dedi.echo.echo_query", ".echo_query_message");
AFFECT(".echo_query_message.input", "ping !");
Description
This function imports a function implemented in an external DLL.
Parameters
Declaration (String) : The declaration syntax is as follows :
Public|Private Declare Pascal1|Pascal2|C Sub|Function function_name Lib "dll_path"
Alias "nom_alias" ([ [Optional] ByVal|ByRef [ParamArray] parameter_name [()] As type]) [ As type ]Public : When in public scope a function can be used by any job of a project (not implemented).
Private : When in private scope the function can only be used by the job where it is declared (not implemented).
Pascal1 : Calling convetion. The parameters are stacked from the last one to the first one and unstacked by the called function.
Pascal2 : Calling convetion. The parameters are stacked from the first one to the last one and unstacked by the called function.
C : Calling convetion. The parameters are stacked from the first one to the last one and unstacked by the caller.
Sub : Specifies that the function doesn't return any value.
Function : Specifies that the function returns a value. In this case after the parameter declaration (after the closing parameter parenthesis) the return type must be specified.
function_name : name of the function to be used in the script.
dll_path : full path to the dll file.
nom_alias : function name in the dll file. Warning : names are case sensitive.
Optional : specifies taht the parameter is optional. When a parameter is optional then any parameter following must also be optional.
ByVal : specifies to pass parameters by value.
ByRef : specifies to pass parameters by reference.
ParamArray : only as the last key word in a parameter list to specify a variable number of parameters (not implemented).
parameter_name : name of the parameter.
() : specifies that the argument is of type array (not implemented).
type : parameter type. The possible values are: Boolean, Integer, Long, Single, Double, Date, String
Type (String) : The only possible value of tis parameter is "FILE". The Declaration parameter is then the full path of a file containing declarations of dll function to import, one line by function.
Example
function declaration
Option("ESCAPE""1");
DllDeclare("declare pascal1 function comp lib \"C:\\data\\dev\\datastudio\\version\\1.2.1.062\\src\\DLL\\client\\client.dll\" Alias \"comp\" (ByRef filename1 As String,ByRef filename2 As String,ByVal buffersize As Integer) ByVal As Integer");
function call
see DllExec.
Description
This function calls a previously declared external dll imported function by DllDeclare function.
Parameters
Func (String) : Function to call.
RetVarname (String) : Variable to create and set to the function call returned value.
... (String) : Values to set input parameters. There must be as much parameters as needed.
Example
DllExec("comp","res1","c:\file1","c:\file2","10000000");
Description
This function creates a COM ActiveX object from its class identifier.
Parameters
ProgID (String) : Class name of hte ActiveX object as it referenced by Windows (see the provider documentation of the ActiveX COM object),
Options (String) : Option string. The syntax is as follows: "[CONTAINER=0|1] [,WIDESTRING=0|1] [,WIDTH=n] [,HEIGHT=n]"
CONTAINER specifies that the ActiveX object is a graphical control that needs to be displayed (CONTAINER=1) in a window owned by the calling process,
WIDESTRING specifies to use unicode strings (WIDESTRING=1) or ANSI (WIDESTRING=0) for the parameters,
WIDTH specifies the width of container window when displayed,
HEIGHT specifies the height of container window when displayed,
CLSID specifies the ActiveX CLSID (CLSID=1) instead of using the ProgID for the ProgID parameter; The CLSID must then be used in all subsequent references to this object until it is destroyed,
Example
OLECreate("MediaPlayer.MediaPlayer.1","CONTAINER=1,WIDTH=600,HEIGHT=400,WIDESTRING=1");
OLECreate("{22D6F312-B0F6-11D0-94AB-0080C74C7E95}", "CONTAINER=1,WIDTH=600,HEIGHT=400,WIDESTRING=1,CLSID=1");
Description
This function frees all resources associated with the specified ActiveX COM object created by OLECreate function.
Parameters
ProgID (String) : Class name of hte ActiveX object as it referenced by Windows (see the provider documentation of the ActiveX COM object),
Example
OLECreate("MediaPlayer.MediaPlayer.1","CONTAINER=1,WIDTH=600,HEIGHT=400,WIDESTRING=1"); OLEDestroy("MediaPlayer.MediaPlayer.1");
Description
This function calls a predefined function of an ActiveX object. If the the object is not a graphical ActiveX the function returns an error.
Parameters
ProgID (String) : Class name of hte ActiveX object as it referenced by Windows (see the provider documentation of the ActiveX COM object),
Action (String) : Name or number of a predefined action. The possible values are the following :
ovPrimary : generally this action activates the object in the owning application,
ovShow : asks the ActiveX to show itself to input values or for disply purposes,
ovOpen : asks an object to show to set values or to display itself in another windows than the owning application. For activeX object not supporting "In Place" activation, this action has the same effect as ovShow,
ovHide : for object supporting "In Place" activation, asks an object to hide,
ovUIActivate : activates an object supporting "In Place" activation along with all the GUI tools going with it including all menus, tool bars and the name of the containing window. An object not supporting "In Place" activation returns an error,
ovInPlaceActivate : activates an object supporting "In Place" activation along without all the GUI tools going with such as menus, tool bars and the name of the containing window. An object not supporting "In Place" activation returns an error,
ovDiscardUndoState : asks an object to free memory associated with undo functions without deactivating the ActiveX object
Example
OLECreate("MediaPlayer.MediaPlayer.1","CONTAINER=1,WIDTH=600,HEIGHT=400,WIDESTRING=1"); OLEDestroy("MediaPlayer.MediaPlayer.1");
Description
This function calls the function of an ActiveX object and returns the value in a DataStudio variable.
Warning : Before calling this function, the activeX object must have been created in memory by the function OLECreate with the same ProgId identifier.
Parameters
ProgIDorDispatch(String) : Class name of hte ActiveX object as it referenced by Windows (see the provider documentation of the ActiveX COM object). This parameter can also be of Dispatch type as returned by OLEFunction or OlePropertySet functions,
Function (String) : Name of the function to execute,
ReturnVar (String) : Naem of the variable to create and set with the returned value,
ValN (String) : Value of the Nth parameter of the function to call,
TypeN (String) : Type of the Nth parameter of the function to call. The possible values are : "Integer", "Float", "Date", "String",
Example
//Use the media player to listen to audio files from a index file of type Advanced Stream Redirector audio_file_redirector.asx. This file contains the following text content and c:\temp\audio.wav is a music file
<ASX version="3.0">
<Entry>
<Ref href="c:\temp\audio.wav"/>
<Title>Like Humans Do</Title>
<PARAM NAME="Producer" Value="Jane Doe"/>
</Entry>
</ASX>
//destroy any previous instance of the ActiveX MediaPlayer object
OleDestroy("MediaPlayer.MediaPlayer.1");
//create an activeX object correponding to the windows MediaPlayer
OleCreate("MediaPlayer.MediaPlayer.1" ,"Container=1, Width=600, Height=400,WIDESTRING=1");
//set the FileName property. This triggers the music playing
OlePropertySet("MediaPlayer.MediaPlayer.1","FileName","c:\temp\audio_file_redirector.asx");
//read the "Producer" parameter from the first entry of the asx file and set the RET parameter
OleFunction("MediaPlayer.MediaPlayer.1","GetMediaParameter","RET","1","Integer","Producer","String");
// Display this value in the monitoring. The displayed value is "Jane Doe"
Message('%RET%);
Description
This function executes an ActiveX object procedure.
Warning : Before calling this function, the activeX object must have been created in memory by the function OLECreate with the same ProgId identifier.
Parameters
ProgIDorDispatch(String) : Class name of hte ActiveX object as it referenced by Windows (see the provider documentation of the ActiveX COM object). This parameter can also be of Dispatch type as returned by OLEFunction or OlePropertySet functions,
Procedure (String) : Name of the procedure to execute,
ValN (String) : Nth procedure parameter value,
TypeN (String) : Nth procedure parameter type. The possible values are : "Integer", "Float", "Date", "String",
Example
OleDestroy("InternetExplorer.Application.1");
OleCreate("InternetExplorer.Application.1","Container=0,WIDESTRING=1");
OleProcedure("InternetExplorer.Application.1","Navigate","www.data.fr",String);
Description
This function reads an ActiveX object property.
Warning : Before calling this function, the activeX object must have been created in memory by the function OLECreate with the same ProgId identifier.
Parameters
ProgIDorDispatch(String) : Class name of hte ActiveX object as it referenced by Windows (see the provider documentation of the ActiveX COM object). This parameter can also be of Dispatch type as returned by OLEFunction or OlePropertySet functions,
Property (String) : Property name to read,
Param (String) : Name of the variable to create and set with the property value read,
Example
OLECreate("MediaPlayer.MediaPlayer.1","CONTAINER=1,WIDTH=600,HEIGHT=400,WIDESTRING=1"); OlePropertySet("MediaPlayer.MediaPlayer.1","FileName","g:\tst.mp3"); //start playing music file g:\tst.mp3
Sleep(10000); //wait 10s
OlePropertyGet("MediaPlayer.MediaPlayer.1","CurrentPosition",POSITION); // read the property CurrentPosition
Message('%POSITION%); // Display de value of the property in the monitoring
Description
This function sets an ActiveX object property.
Warning : Before calling this function, the activeX object must have been created in memory by the function OLECreate with the same ProgId identifier.
Parameters
ProgIDorDispatch(String) : Class name of hte ActiveX object as it referenced by Windows (see the provider documentation of the ActiveX COM object). This parameter can also be of Dispatch type as returned by OLEFunction or OlePropertySet functions,
Property (String) : Name of the property to update,
Value (String) : New property value. Dates must be formatted in conformance with the regional settings of the host,
Type (String) : Property type. The possible values are : "Integer", "Float", "Date", "String",
Example
OLEPropertySet("MediaPlayer.MediaPlayer.1","CurrentPosition","50","Float");
Description
This function adds an XML node with all its descendants to a destination XML node.
Parameters
DestDoc (String) : Full path of the destination XML file in which the node is added,
DestNode (String) : Node path in the XML file to which the source node is added.
The path is represented by the sequence of node names leading to the destination node separated by a slash character '/' (e.g. : "node1/node2/node3").
DestCondNode (String) : Relative node path to a descendant of DestNode node whose value is to be compared with DestVal parameter to distinguish between 2 nodes having the same path.
An attribute is expressed by its name between brackets (e.g. : [attribute1])
Example of DestCondNode parameter values : "node4/node5[attribut1]" or "node6" or "[attribute2]".
The DestCondNode path is a relative path. This means that the correponding full path to the DestCondNode=DestNode + DestCondNode. If DestNode is "node1/node2/node3" and DestCondNode is "node4/node5[attribute1]" then the full DestCondNode path is "noeud1/noeud2/noeud3/node4/node5[attribute1]".
DestVal (String) : Node or attribute value to compare DestCondNode to. If a node or attribute has the path specified by DestCondNode and has the same value than DestVal then, the parent of this node having the DestNode path is selected as the node to which the source node is to be added.
SrcDoc (String) : Full path to the XML source file from which is read the node to be added,
SrcNode (String) : Path of the node to be copied from the xml file.
The path is represented by the sequence of node names leading to the destination node separated by a slash character '/' (e.g. : "node1/node2/node3").
SrcCondNode (String) : Relative node path to a descendant of SrcNode node whose value is to be compared with SrcVal parameter to distinguish between 2 nodes having the same path. See DestCondNode for the usage of this parameter,
SrcVal (String) : Node or attribute value to compare SrcCondNode to. If a node or attribute has the path specified by SrcCondNode and has the same value than SrcVal then, the parent of this node having the SrcNode path is selected as the node to be added.
Options (String) : Option string. The syntax is as follows : "[INSERT=TOP] [,TABLE=table_name] [,ORDER=col_order] [,MEMORY=0|1][,MEMORYDEST=0|1]"
INSERT specifies to add the fragment at the begining of the node list (when tere are more than one addition or when the destination node already contains son nodes). The default value is to add at the end of the node list.
TABLE specifies to read the parameter values from the table table_name. This table must have as many string typed columns as the number of input parameters of the AddXmlFragment function. In table mode, the input parameter values are replaced by the column names of the table. This mode optimizes the resources when multiple additions are done on the the same destination xml file (The file is then parsed and opened only once).
ORDER specifies the column name of table_name table used for ordering the additions of the xml fragments in table mode.
MEMORY specifies the origin of the XML fragment. When MEMORY=1, the fragment are read from memory storage (cf. Load_Mem_Xml) instead of file. The SrcDoc parameter is use to specify the memory storage by ID. The default value is MEMORY=0 meaning file storage.
MEMORYDEST specifies the origin of the destination XML file. When MEMORYDEST=1, the XML file is in memory (cf. Load_Mem_Xml) instead of file. The DestDoc parameter is use to specify the memory storage by ID. The default value is MEMORYDEST=0 meaning file storage.
Example
//create a table interface
create table XMLFRAG_INTERF
(
DESTDOC varchar(100) null,
DESTPATH varchar(100) null,
DESTCOND varchar(100) null,
DESTVAL varchar(100) null,
SOURCEDOC varchar(100) null,
SOURCEPATH varchar(100) null,
SOURCECOND varchar(100) null,
SOURCEVAL varchar(100) null
);//add to node root/foo where root/foo[bar]=15 from the xml file c:\Xml\dest.xml, the root2 node from xml file c:\Xml\src.xml where root2/foo2=18
insert into TSTXMLFRAG( DESTDOC , DESTPATH , DESTCOND , DESTVAL , SOURCEDOC , SOURCEPATH , SOURCECOND , SOURCEVAL )
values ( 'c:\Xml\dest.xml','root/foo','[bar]','15', 'c:\Xml\src.xml','root2','foo2','18');AddXmlFragment( DESTDOC , DESTPATH , DESTCOND , DESTVAL , SOURCEDOC , SOURCEPATH , SOURCECOND , SOURCEVAL , "TABLE=XMLFRAG_INTERFACE,INSERT=TOP");
//The previous instructions are technically equivalent to calling the function and giving the parameter values directly.
AddXmlFragment( 'c:\Xml\dest.xml' , 'root/foo' , '[bar]' , '15' , 'c:\Xml\src.xml' , 'root2' , 'foo2' , '18' , "INSERT=TOP");
Description
This function deletes a node a all its descendants from an xml file.
Parameters
DestDoc (String) : Full path of the destination XML file in which the node is deleted,
DestNode (String) : Path to the node to delete in the XML file.
The path is represented by the sequence of node names leading to the destination node separated by a slash character '/' (e.g. : "node1/node2/node3").
DestCondNode (String) : Relative node path to a descendant of DestNode node whose value is to be compared with DestVal parameter to distinguish between 2 nodes having the same path.
An attribute is expressed by its name between brackets (e.g. : [attribute1])
Example of DestCondNode parameter values : "node4/node5[attribut1]" or "node6" or "[attribute2]".
The DestCondNode path is a relative path. This means that the correponding full path to the DestCondNode=DestNode + DestCondNode. If DestNode is "node1/node2/node3" and DestCondNode is "node4/node5[attribute1]" then the full DestCondNode path is "noeud1/noeud2/noeud3/node4/node5[attribute1]".
DestVal (String) : Node or attribute value to compare DestCondNode to. If a node or attribute has the path specified by DestCondNode and has the same value than DestVal then, the parent of this node having the DestNode path is selected as the node be deleted.
Options (String) : Option string. The syntax is as follows : "[TABLE=table_name] [,ORDER=col_order]"
TABLE specifies to read the parameter values from the table table_name. This table must have as many string typed columns as the number of input parameters of the AddXmlFragment function. In table mode, the input parameter values are replaced by the column names of the table. This mode optimizes the resources when multiple deletions are done on the the same destination xml file (The file is then parsed and opened only once). See AddXmlFragment function for examples using table mode.
ORDER specifies the column name of table_name table used for ordering the deletions of the xml fragments in table mode.
Example
DelXmlFragment( 'c:\Xml\dest.xml' , 'root/foo' , '[bar]' , '15');
Description
This function signs a memory XML document (by adding a new XML fragment to the document) with the envelope transformation specified by W3C (http://www.w3.org/Signature/).
The XML document is normalized with the canonicalization method specified and hashed with the hashing method specified. The hash and the encrypted hash (with the chosen signature method and the private key) are added to the document.
The memory XML document is created by Load_Mem_Xml function.
Parameters
IdXml (Integer ou String) : Identifier produced by Load_Mem_Xml function,
ParentNodeName (String) : Node name to which the signature fragment is to be added,
CanonicalisationMethod (String) : The possible values are : C14N_NOC, C14N_COM, C14NE_NOC, C14NE_COM.
These codes correspond to the following standard URI :
C14N_NOC = http://www.w3.org/TR/2001/REC-xml-c14n-20010315,
C14N_COM = http://www.w3.org/TR/2001/REC-xml-c14n-20010315#WithComments,
C14NE_NOC = http://www.w3.org/2001/10/xml-exc-c14n#,
C14NE_COM = http://www.w3.org/2001/10/xml-exc-c14n#WithComments,SignatureMethod (String) : The possible values are : DSA, RSA, HMAC. Only RSA method is implemented,
HashMethod(String) : The possible values are : SHA1, MD5, SHA224, SHA256, SHA384, SHA512.
Option (String) : Option string. The su=yntax is as follows : "[RSAPRIVKEYPEMFILE=file.pem][,RSAPRIVKEYPASSWORD=password]".
RSAPRIVKEYPEMFILE specifies the private key file in PEM format,
RSAPRIVKEYPASSWORD specifies the private key file passphrase when it is protected,
Example
EnvelopeSign_Mem_Xml("%XML_DOCUMENT%","ParentNode","C14N_NOC","RSA","SHA1","RSAPRIVKEYPEMFILE=C:\PrivateKey.pem");
Description
This function loads in memory multiple XML files coming from a select SQL query. The query must return a column of names and a column of content. The content can be splitted into multiple lines (in this case lines must be ordered by the query).
Parameters
Name (String) : Name of the name column,
Body (String) : Name of the content column,
Query (String) : SQL query returning the XML contents and the names,
Stropt (String) : One option is supported : CREATEPARAMS. When CREATEPARAMS=1, variables named from the names of the Name column are created and set with the memory XML identifiers. The default value is CREATEPARAMS=0,
Example
insert into XMLDATA 'FR1', '<?xml version="1.0" encoding="ISO8859-1" ?>', 1 from dual;
insert into XMLDATA 'FR1', '<export>', 2 from dual;
insert into XMLDATA 'FR1', ' <data>1</data>', 3 from dual;
insert into XMLDATA 'FR1', ' <data>2</data>', 4 from dual;
insert into XMLDATA 'FR1', '</export>', 5 from dual;
Load_Mem_Xml("NAME", "BODY", "select NAME, BODY from XMLDATA order by NUM", "CREATEPARAMS=1");
Message("Fragment: %FR1%");
[...]
Purge_Mem_Xml();
Description
This function frees all memory allocated by memory XML functions for storing the XML fragments.
Example
Purge_Mem_Xml();
Description
This function replaces a node from an XML file with a node and all its descendants from a source file.
Parameters
DestDoc (String) : Full path of the destination XML file in which the node is replaced,
DestNode (String) : Node path in the XML file of the node to be replaced.
The path is represented by the sequence of node names leading to the destination node separated by a slash character '/' (e.g. : "node1/node2/node3").
DestCondNode (String) : Relative node path to a descendant of DestNode node whose value is to be compared with DestVal parameter to distinguish between 2 nodes having the same path.
An attribute is expressed by its name between brackets (e.g. : [attribute1])
Example of DestCondNode parameter values : "node4/node5[attribut1]" or "node6" or "[attribute2]".
The DestCondNode path is a relative path. This means that the correponding full path to the DestCondNode=DestNode + DestCondNode. If DestNode is "node1/node2/node3" and DestCondNode is "node4/node5[attribute1]" then the full DestCondNode path is "noeud1/noeud2/noeud3/node4/node5[attribute1]".
DestVal (String) : Node or attribute value to compare DestCondNode to. If a node or attribute has the path specified by DestCondNode and has the same value than DestVal then, the parent of this node having the DestNode path is selected as the node be replaced.
SrcDoc (String) : Full path to the XML source file from which is read the node to be copied,
SrcNode (String) : Path of the node to be copied from the xml file.
The path is represented by the sequence of node names leading to the destination node separated by a slash character '/' (e.g. : "node1/node2/node3").
SrcCondNode (String) : Relative node path to a descendant of SrcNode node whose value is to be compared with SrcVal parameter to distinguish between 2 nodes having the same path. See DestCondNode for the usage of this parameter,
SrcVal (String) : Node or attribute value to compare SrcCondNode to. If a node or attribute has the path specified by SrcCondNode and has the same value than SrcVal then, the parent of this node having the SrcNode path is selected as the node to be copied.
Options (String) : String d'option. La syntaxe est la suivante : "[TABLE=table_name] [,ORDER=col_order] [,MEMORY=0][,MEMORYDEST=0]"
TABLE specifies to read the parameter values from the table table_name. This table must have as many string typed columns as the number of input parameters of the AddXmlFragment function. In table mode, the input parameter values are replaced by the column names of the table. This mode optimizes the resources when multiple additions are done on the the same destination xml file (The file is then parsed and opened only once).
ORDER specifies the column name of table_name table used for ordering the additions of the xml fragments in table mode.
MEMORY specifies the origin of the XML fragment. When MEMORY=1, the fragment are read from memory storage (cf. Load_Mem_Xml) instead of file. The SrcDoc parameter is use to specify the memory storage by ID. The default value is MEMORY=0 meaning file storage.
MEMORYDEST specifies the origin of the destination XML file. When MEMORYDEST=1, the XML file is in memory (cf. Load_Mem_Xml) instead of file. The DestDoc parameter is use to specify the memory storage by ID. The default value is MEMORYDEST=0 meaning file storage.
Example
ReplaceXmlFragment( 'c:\Xml\dest.xml' , 'root/foo' , '[bar]' , '15' , 'c:\Xml\src.xml' , 'root2' , 'foo2' , '18');
Description
This function decrypts a node from a memory XML document (replacing the encrypted node and its descendants with the decrypted content) as specified in W3C specifications (http://www.w3.org/TR/xmlenc-core/).
The private key and its passphrase makes it possible to restore and read the document as it was originally.
The memory XML document is created by Load_Mem_Xml function.
Parameters
IdXml (Integer ou String) : Identifier generated by the Load_Mem_Xml function,
PrivateKey (String) : Full path of the private key file in PEM format corresponding to the public key used to encrypt the XML node,
PassPhrase (String) : Password to read the private if it is protected.
Example
STDDecrypt_Mem_Xml("%XML_DOCUMENT%","C:\privkey.pem","");
Description
This function encrypts a memory XML document node (replacing the node to encrypt and all its descendants with an encrypted XML fragment) as specified by W3C (http://www.w3.org/TR/xmlenc-core/).
A symmetric key is generated and then used to encrypt the content using the chosen algorithm. This encrypted content and the encrypted generated key (using the public key and the chosen key encrypting algorithm) are added to the document replacing the initial node to encrypt.
The memory XML document is created by Load_Mem_Xml function.
Parameters
IdXml (Integer ou String) : Identifier generated by the Load_Mem_Xml function,
NodeName (String) : Name of the node to encrypt,
EncryptingMethod (String) : Symmetric encrypting method to encrypt the content. The possible values are : 3DES_CBC, KW_3DES, AES128_CBC, KW_AES128, AES192_CBC, KW_AES192, AES256_CBC, KW_AES256.
KeyEncryptingMethod (String) : Asymmetric public key/private key key encrypting method to encrypt the automatically generated symmetric key used to encrypt the content. Th e possible values are : DSA, RSA, HMAC. Only RSA method is implemented,
PublicKey (String) : Full path to the file containing the public key in PEM format corresponding to the private key of the encrypted XML document receiver.
Option (String) : Option string. The syntax is as follows : "[ENCRYPTCONTENT=1|0]".
ENCRYPTCONTENT=1 specifies to encrypt the node content and not the node itself,
Example
STDEncrypt_Mem_Xml("%XML_DOCUMENT%","Node","AES128_CBC","RSA_OAEP_MGFP1","C:\pubkey.pem","ENCRYPTCONTENT=1");
Description
This function loads a memory XML document dataobject with data from a SQL query. The names of the columns must match the equivalent dataobject column names.
Parameters
XmliId (String) : Identifier of the memory XML document to load,
Objcode (String) : DataObject name to load inside the XML document. This name is the name automatically generated by the xml structure discovery algorithm looking for table like (lines repeated at least once) structures (see Create_Data_Zone).
Sql (String) : Select SQL query,
StrOpt (String) : One option exists : TRACEXMLOBJECT=1 specifies to dump the content of the XML document before and after update. The default value is 0.
Example
UPDATE_MEM_XML("%site%","site"," select 'RAF' authority, 'Royal Air Force' display_name, from dual ","TRACEXMLOBJECT=1");
Description
This function validates an XML document structure and content against a DTD or a Schema (*.xsd).
Parameters
DestDoc (String) : Full path of the XML file or memory XML document identifier (created by LOAD_MEM_XML function),
VarName (String) : Name of the variable to create and set with the result value. The returned value is 1 if the document is valid otherwise it is 0,
Options (String) : Option string. The syntax is as follows : "[MEMORY=0|1][,EXTERNALSCHEMALOCATION=namespace fichier.xsd][,EXTERNALNONAMESPACESCHEMALOCATION=fichier.xsd]"
MEMORY specifies that the type of DestDoc parameter. MEMORY=1 DestDoc is an identifier of a memory XML document. The default value is 0 (DestDoc is an XML file path),
EXTERNALSCHEMALOCATION specifies to validate the document against an external schema file specifying the namespace name. The string must contain the namespace name followed by the schema file URI (leave a whitespace in between),
EXTERNALNONAMESPACESCHEMALOCATION specifies to validate the document against an external schema file without specifying the namespace name. The string is the schema file URI,
Example
ValidateXmlDocument("%XML_DOCUMENT%","ISDOCVALID","MEMORY=1,EXTERNALSCHEMALOCATION=http://www.company.com/MessageImpl C:\MessageImpl.xsd");
Description
This function checks the validity of a memory XML document signature (included in the XML document) as specified by W3C (http://www.w3.org/Signature/).
The memory XML document is created by Load_Mem_Xml function.
Parameters
IdXml (Integer or String) : Identifier generated by Load_Mem_Xml function,
NomVariable (String) : Variable name to create and set with the result value. 1 if ok otherwise 0,
Option (String) : Option string. The syntax is as follows : "[RSAPUBKEYPEMFILE=fichier.pem]".
RSAPUBKEYPEMFILE specifies the path to the public key PEM format file corresponding to the private key used to sign the document,
Example
VerifySignature_Mem_Xml("%XML_DOCUMENT%","ISSIGOK","RSAPUBKEYPEMFILE=C:\PublicKey.pem");
Message("Signature ok = %ISSIGOK%");