Differences

This shows you the differences between two versions of the page.

--- database_adaptor [2019/11/07 16:08]
montse removed
+++ database_adaptor [2025/04/03 13:57] (current)
ale
@@ Line 1: / Line 1: @@
-====== Excel File Adaptor ======
+====== Database Adaptor ======
 ===== Setup =====
-In the initialization process, the Excel Adaptor will try to read and analyze the Excel sheet indicated by the NumSheet parameter.
+In the initialization process the Database Adaptor requests the necessary data to establish the connection with the destination database, and tries to make the connection. If it fails, the process is aborted.
-The adapter will call the GetExcelSheet function, in this way the user can operate on the data without drawing a GetExcelSheet message in the diagram.
 ===== Initialization Parameters =====
-  * **FilePath:** Represents the full path and name of the Excel file that represents this instance of the Excel adaptor.
+  * **DbType:** indicates the provider or type of database to which we want to connect.
-  * **NumSheet:** Indicates the number of the sheet within the Excel workbook on which we want to work.
+  * **Host:** represents the IP address or the machine's DNS name where the database resides.
-  * **Template:** If checked-in, the file will be created or overwriten in any case, no matter if the checkbox 'Create/Overwrite' has been checked-in.
+  * **Port:** the port number that listens for incoming connections to the database.
-  * **Template File:** This is the name of the template file used for creating the excel file for testing. In case the Template checkbox (see above) is checked-in, this field is mandatory.
+  * **Ssl:**  when we check the checkbox, the security protocol is activated.
-  * **Create/Overwrite:** When the checkbox is checked-in, TAST will create a new file or overwrite it if the file already exists.
+  * **DbName:** name of the instance or database service.
+  * **User:** user name.
+  * **Password:** user password.
 ===== Functions =====
-  * **addSheet:** Adds a new sheet with the name that the user puts as parameter.
+  * **connect_DB():** tries to connect to the configured database. Returns true if it succeeds or false otherwise. On the properties of the object in the Database Adaptor there are some parameter boxes to do the configuration about the Database we are going to use. There will be, once the function is executed, a log with different messages if the connection was a succeed or not.
-  * **deleteSheet:** Allows the user to delete an Excel sheet of the current workbook. Using the sheet name or index as input parameters. It returns a Boolean to indicate the operation result. If the deleted sheet was the active sheet in that moment, then the first sheet in the book will be the new active sheet.
+  * **dBExecuteStatement(Statement):** executes the SQL that it's contain in the parameter (statement). Allows the user to modify the data in the destination database. This function allows you to write SQL statements like Insert, Delete, Update and Merge in the statement parameter. The function will replace the variables for their values in the execution time, that will return a TastInteger with the number of rows that were changed. (Input parameter: The sentence for example:” INSERT INTO x (a, b, c) VALUES (d, e, f)” Output parameter: Integer with the results).
+  * **dBExecuteStatementFromFile:** executes the statements found in the file whose path is defined in the PathToFileWithStatements parameter. Returns an integer with the total number of executed statements.
-  * **generateFileAsEvidence(): ** Generates a copy of the Excel file in its current state to add as evidence.
+  * **dBGetQueryData(Query):** executes the SQL Select statement contained in the Query parameter, and returns the results as a TastTableData variable to the executor, which allows access and work with the data in later steps of the diagram. The Query parameter contains the Select statement to execute in the database. It is possible to use variables created in the diagram within the Query parameter, the function will substitute the variables for their values at run time.(Input parameter (Query), Output parameter (OutputTable)).
-  * **getCellValue (CellReference):** This function returns the value of a cell with the reference of a said cell (e.g.: C7, C12, D2…); works with uppercase and lower case.
-  * **getColumnByRowSearch():** Return a TastDataString, that contains the value of the indicated column to retrieve, if the value matches with searchColumn parameter. As imput parameters there are (SearchRow, SearchValue y ColumnToRetrieve).
+  * **disconnect_DB():** tries to disconnect from the database. Returns true if it succeeds or false otherwise. (As the function connect_DB this function returns an error message if the disconnection fails otherwise returns a succeed message).
-  * **getExcelSheet(SheetNum):** Reads the Excel worksheet indicated by the NumSheet parameter and returns the results as a variable of type TastTableData to the executor to allow working with the data in later steps of the diagram.
+  * **getNumColumns():** returns the number of columns contained in the TastTableData that was generated by the execution of the dBGetQueryData function. It requires the execution of the dBGetQueryData function in previous steps of the diagram. (Don’t need an input parameter, so it’s a function that just return the number of columns of the selected table (with TastTableData)).
+  * **getNumRows():** returns the number of rows contained in the TastTableData that was generated by the execution of the dBGetQueryData function. It requires the execution of the dBGetQueryData function in previous steps of the diagram. (Don’t need an input parameter, so it’s a function that just return the number of rows of the selected table (with TastTableData)).
-  * **getNumColumns():** Returns the number of columns of the TastTableData generated when reading the Excel sheet. When you introduce new columns in the Excel, you have to put before a setSheet so that it counts the new columns.
+  * **getOraError():** provides the ORA error code of the executed SQL query.
-  * **getNumRows():** Returns the number of rows of the TastTableData generated when reading the Excel sheet. When you introduce new rows in the Excel, you have to put before a setSheet so that it counts the new rows.
+  * **getSQLSTATE():** provides the SQL status.
-  * **getNumValueSearchOnColumn:** Allows the user to search a value on  the selected column and it returns the number of times the value appears in the column. In the mapping the user puts the value to find in SearchValue field and the number  of column in Column field.
+  * **getTableValue(RowNum, ColNum):** returns the value contained in the cell indicated by the parameters Row and Col. It requires the execution of the dBGetQueryData function in previous steps of the diagram. (Input parameters are the row and the column you need to display, and the output should be the information contained there).
-  * **getRowByColumnSearch():** Returns a TastTableData, that contains the subset of rows in which the column has the indicated value. As imput parameters we have with SearchColumn and SearchValue.
+  * **tableRecordCount(TableName, WhereClausule):** performs a SQL query using the SQL Count (*) function. The tableName parameter represents the name of the table or view to be used for the Query, and the whereClausule parameter represents the SQL where  clause that we must apply. Returns the number of rows counted by the sentence.
-  * **getTableValue(RowNum, ColNum):** Returns the value contained in the cell indicated by the Position parameter that is filled with the RowNum and ColNum values.
+  * **transfromSelectIntoInsert:** transforms the result of the select entered as a parameter into an insert.
-  * **getTableValueByColumnName():** Allows to retrieve the value in a cell of an excel file, selecting as parameters the name of the column (ColName) and the numbers of rows to retrieve (RowNum).
-  * **getValuesbyPosition():** Returns an array of values corresponding to the cell positions indicated by the parameters.
-  * **newFile(FilePath):** Creates or overwrites a file, with the path and the name indicated by the parameter filePath".\\ If the file doesn´t exist, TAST will create it, as an empty file.\\ If the file exists, TAST will delete the current file, and it will create a new empty one.
-  * **setCellValue (CellReference, value):** This function sets the value of a cell with the reference of a said cell (e.g.: C7, C12, D2…), works with uppercase and lower case.
-  * **setColumnValues():** Generates the Column values in the Auxiliary Excel file, based on the value indicated by the parameters Column (Input parameters are ColNum and ValueCell), value1, value2, etc.
-  * **setHeader():** Generates the headers in the row 1 Excel file," based on the value indicated by the parameters ValueHeader1, ValueHeader2, etc.
+===== Managing Variables in the database Adaptor =====
-  * **setRowValues():** Generates the Row values in the Auxiliary Excel file, based on the value indicated by the parameters Row, (Input parameters are RowNum and ValueCell), value1, value2, etc.
+The management of variables in the DB Adaptor is an important topic, especially it is important how the variables are handled within the predefined function of the query.
-  * **setSheet:** Allows the user to select the sheet which will be active in the adaptor. Once you set up the sheet as active, all the functions of reading and writing executed, will work on the selected sheet. (Imput parameter “Name”).
+  * Instead of supporting parameters, its better to substitute them using this syntax, with Data being the variable to replace,'"+ #Data +"' (single quote, double quotes).\\ \\ For example:\\ \\     SELECT '" + #Data + "'    FROM dual
-  * **setTableValue():** Generates the cell value in the Auxiliary Excel file, based on the value indicated by the parameters RowNum, ColNum, ValueCell.
+  * In addition, queries have often used an alias that is enclosed in quotes.\\ \\ For example:\\ \\    SELECT domain FROM dual as //"Domain"//
-  * **vTableLookUp() :** Returns a TastDataString, that contains the values of the indicated column to retrieve, for every row that matches the condition. The imput parameters here are ColToSearch, ValueToSearch y ColToRetrieve.
+  * If complex queries are manipulated, with several aliases in which you want to substitute several variables, it works by building in a javascript the complete query, being necessary to jump the quotes "" of the aliases with this symbol: \ \\ \\ For example:\\ \\ SELECT domain FROM dual as \// "Domain" \//
-  * **vTableLookUpArray():** Returns a TastRowTableData, which is a list of values located on the indicated column to retrieve, for every row that matches the condition. .The imput parameters here are ColToSearch, ValueToSearch y ColToRetrieve.

TAST DokuWiki

User Tools

Site Tools

Differences

Page Tools