database_adaptor
Differences
This shows you the differences between two versions of the page.
|
database_adaptor [2019/11/07 16:12] montse created |
database_adaptor [2025/04/03 13:57] (current) ale |
||
|---|---|---|---|
| Line 8: | Line 8: | ||
| ===== Initialization Parameters ===== | ===== Initialization Parameters ===== | ||
| - | * **Host:** Represents the IP address or the machine's DNS name where the database resides. | + | * **DbType:** indicates the provider or type of database to which we want to connect. |
| - | * **Port:** The port number that listens for incoming connections to the database. | + | * **Host:** represents the IP address or the machine's DNS name where the database resides. |
| - | * **DbName:** Name of the instance or database service. | + | * **Port:** the port number that listens for incoming connections to the database. |
| - | * **User:** User name. | + | * **Ssl:** when we check the checkbox, the security protocol is activated. |
| - | * **Password:** User password. | + | * **DbName:** name of the instance or database service. |
| - | * **DbType:** Indicates the provider or type of database to which we want to connect. | + | * **User:** user name. |
| + | * **Password:** user password. | ||
| ===== Functions ===== | ===== Functions ===== | ||
| - | * **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. | + | * **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. |
| - | * **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). | + | * **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). |
| - | * **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)). | + | * **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. |
| - | * **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). | + | * **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)). |
| - | * **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)). | + | * **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). |
| + | |||
| + | * **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)). | + | * **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)). |
| + | |||
| + | * **getOraError():** provides the ORA error code of the executed SQL query. | ||
| + | |||
| + | * **getSQLSTATE():** provides the SQL status. | ||
| + | |||
| + | * **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). | ||
| + | |||
| + | * **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. | ||
| + | |||
| + | * **transfromSelectIntoInsert:** transforms the result of the select entered as a parameter into an insert. | ||
| + | |||
| - | * **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). | ||
| - | * **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. | ||
| ===== Managing Variables in the database Adaptor ===== | ===== Managing Variables in the database Adaptor ===== | ||
| Line 37: | Line 50: | ||
| 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. | 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. | ||
| - | * 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 | + | * 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 |
| - | * In addition, queries have often used an alias that is enclosed in quotes. For example:\\ \\ SELECT domain FROM dual as //"Domain"// | + | * In addition, queries have often used an alias that is enclosed in quotes.\\ \\ For example:\\ \\ SELECT domain FROM dual as //"Domain"// |
| - | * 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" \// | + | * 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" \// |
database_adaptor.1573143163.txt.gz · Last modified: 2019/11/07 16:12 by montse
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 3.0 Unported
