Versions Compared

Version Old Version 1 New Version Current
Changes made by

Former user

wiki

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Activities

...


Image Added


Connect to

...

MySQL

This activity connects to a Microsoft SQL MySQL Host. The Output of this activity should be used as input for all Connection parameters used for activities placed under Microsoft SQL MySQL category.

Activity Parameters

The Connect to MSSQL MySQL activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Authentication (combo box control): the authentication method. For SQL Authentication the Username and Password parameters are mandatory.For Windows authentication they are optional.
  • Database (text box editor type): the database name to connect to.Instance (text box editor type (type: string): the instance database name to connect to, in this example "FlowsterMySQLActivities".
  • Password (password editor type) (type: string): the password for the provided username. In the example from the screenshot there is used the user root with no password, which leads to a blank Password field.
  • Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the sql server. (This parameter is used only when the user wants to send an encripted encrypted password from outside the taskworkflow, for example Flowster User Portal)Portal).
  • Port (text box editor type) (type: int32): the port number.
  • Server Name (text box editor type) (type: string): the name or IP of the SQL MySQL Server to connect to.
  • Username (text box editor type) (type: string): the the username of the SQL MySQL Server Administrator. In our example, user root is used.

The Read Only Output variable are the possible output values that the activity will provide:

  • Connection (type: object): outputs a SQL connection. The Output of this activity should be used as input for all Connection parameters used for activities placed under Microsoft SQL MySQL category.
 

As mentioned above, the values for all the required parameters can be provided by IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Connect to MSSQL activity:Image RemovedImage Added



Disconnect from

...

MySQL

This activity disconnects from a Microsoft SQL MySQL Host.

Activity Parameters

The Disconnect from MSSQL MySQL activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Connection (text box editor type) (type: object): the output from a Connect to MSSQL MySQL activity.

...




Add User Access to Database

...

The Add User Access to Database activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • CSV Roles (text box editor type): the database-level roles for the user access. Overrides the 'Database-level role' parameter. The values must be entered with the format: "Role1,Role2,Role3...".
  • Database - level Role (value selector list): the roles that will be added to the user. The roles will be selected from a list, after clicking the ... button. The list will display all possible roles that can be assigned to a SQL userHost Name (value selector list) (type: string): the hostname that the user can connect from. In this case localhost will be used, so that the user can connect to MySQL Admin from the same machine where the server is installed.
  • Username for Access (text box editor type) (type: string): the name of the user that will receive permissions.
 

As mentioned above, the values for all the required parameters can be provided by IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Add User Access to Database activity:

Image Removed

 

 

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: boolean): outputs True or False if the execution was successful or not.

Image Added


Execution: the activity will search if the user already exists in the user table from the mysql database (MySQL system database). If found, it will grant access to the database where the activity is connected:


Image Added



 

Backup Database

This activity creates a backup .bak file of a an .sql file backup for the selected database.

Activity Parameters

The Backup Database activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

...

  • Backup Path (text box editor type) (type: string): the path where the backup file will be created.
  • Media File Name (text box editor type): the new media a media name. (E.g. 'C_SQLServerBackups').Name  (type: string): the name of the backup file that will be created. Should be an .sql extension file (E.G. FlowsterMySQLActivities.sql, where the file name is the same with the database's).
  • MySQLDump.exe Path (text box editor type): the name  (type: string): the path of the backup set. Names can have a maximum of 128 characters. (E.g. 'Full Backup of Database').
 

As mentioned above, the values for all the required parameters can be provided by IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Backup Database activity:

Image Removed

 

  • mysqldump.exe, or select it with the File Browser. If using XAMPP, the default path is C:\xampp\mysql\bin\mysqldump.exe.
  • Password (password editor type) (type: string): the password for the provided username.
  • Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the sql server. (This parameter is used only when the user wants to send an encrypted password from outside the workflow, for example Flowster Portal).

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: string): outputs the path of the backup file.
 

Image Added




Delete Row(s) from Table

This activity deletes Row(s) from a SQL Server database table.

...

The Delete Row(s) from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Table (text box editor type) (type: string): the table name where the row(s) will be deleted.
  • Where Statement (text box editor type) (type: string): the Where statement to filter the row(s) that will be deleted. For example: Column_name='Test'.

As mentioned above, the values for all the required parameters can be provided by IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Delete Row(s) from Table activity:

Image Removed

...

  • ID>2, where all rows with an ID higher than 2 will be deleted.

Execution: the activity will delete all the rows from a table according to the given Where statement:

  • the initial table content:

    Image Added

  • the table content after deletion:

    Image Added


 

Insert Row(s) into Table

This activity inserts Row(s) on a SQL MySQL Server database table.

Activity Parameters

The Insert Row(s) into Table activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Fields (text box editor type): the column  (type: string): the column headers that you want to be returned with the select commandinsert the new values in. If you enter more than one you should separate them with ','. For example: Name,Surname,StatusCompany.
  • Table (text box editor type) (type: string): the table name where the row(s) will be inserted.
  • Values (text box editor type) (type: string): the values for specified fields in Fields parameter. If you enter more than one you should separate them with ','. If you have more values each should be placed in the same order as you add the fields.For example: Test,1May,James,Amazon Prime.

The Read Only Output variable are the possible output values that the activity will provide:

  • Output Last ID (type: string): outputs the last row inserted ID.
 

As mentioned above, the values for all the required parameters can be provided by IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Insert Row(s) into Table activity:

Image Removed

 

 

Image Added


Execution: the activity will insert the new row into the selected table and will output the ID of the added row. In the database, the row can be seen at the last position:


Image Added

 



Remove User Access from Database

...

The Remove User Access from Database activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

...

  • CSV Roles Username to be Removed (text box editor type): the database-level roles that will be removed from the user. Overrides the 'Database-level role' parameter. The values must be entered with the format: "Role1,Role2,Role3...".
  • Database - level Role (value selector list): the roles that will be deleted from the user. The roles will be selected from a list, after clicking the ... button. The list will display all possible roles that can be assigned to a SQL user.
  • Username for Access (text box editor type): the name of the user that will have the permissions revoked.
 

As mentioned above, the values for all the required parameters can be provided by IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Add User Access to Database activity:

Image Removed

  •  (type: string): the name of the user that will have denied permissions.
 

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: boolean): outputs True or False if the execution was successful or not.

Image Added



 

Restore Database

This activity restores a Database from a backup .bak sql file.

Activity Parameters

The Restore Database activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Backup File Path (text box editor type): the path  (type: string): the path of the backup file or select it with the File Browser. Should be a .bak extension file (E.G 'C:\BackupsFolder\BackupFile.bak' ).
 

As mentioned above, the values for all the required parameters can be provided by IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Restore Database activity:

Image Removed

...

  • The .sql file path can be manually written or selected by clicking the Image Added button. In this example the backup file has the same name as the database's.
  • MySQL.exe Path (text box editor type) (type: string): the path of the mysql.exe, or select it with the File Browser. If using XAMPP, the default path is C:\xampp\mysql\mysql.exe.
  • Password (password editor type) (type: string): the password for the provided username.
  • Password Binding (text box editor type) (type: string): bind to a variable containing the encrypted password for the sql server. (This parameter is used only when the user wants to send an encrypted password from outside the workflow, for example Flowster Portal).
 

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: boolean): outputs True or False if the execution was successful or not.

Image Added


 

Run query

This activity runs a query on a SQL Server database.

...

The Run Query activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Query (text box editor type) (type: string): the SQL query string. For example: CREATE Table. "SELECT Name, Company FROM Names where ID>1 and ID<4 order by name ASC"

    Image Added

  • Script (text box editor type) (type: string): the query content if a binding to a variable is used ($using). If set and if the $using:variable syntax is used, this parameter will take precedence over the Query parameter. For example, the $using:DemoString syntax will take the value stored in the DemoString variable and will execute it:

    Image Added

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: collection<collection<string>>): outputs a collection of returned rows.To use the elements of the collection, use ForEachFactory activity.
  • Output Columns (type: int32): outputs the number of columns returned.
  • Output Rows (type: int32): outputs the number of rows returned.
 

As mentioned above, the values for all the required parameters can be provided by IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Run Query activity:

Image RemovedImage Added

Execution: In the Tracking Data there can be seen the returned results:

Image Added


The same result can be seen when executing the same SQL Select query from PHPMyAdmin:

Image Added




Select Row(s) from Table

...

The Select Row(s) from Table activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image RemovedImage Added

Parameters:

  • Distinct (combo box control)  (type: string): whether the returned values should be distinct or not.
  • Fields (text box editor type) (type: string): the column headers that you want to be returned with the select command. If you enter more than one you should separate them with ','. If all fields needs to be returned, then just use "*". The " * " has the same role as used in the SQL select all statement: select * from table
  • Limit (text box editor type) (type: string): limit the number of results. If the field is left blank, then the activity will return all the rows from the table
  • Order By (text box editor type) (type: string): the column header that you want to result to be ordered by. For example, if "ID" is inserted, then the values returned will be ordered depending on the value set in the ID column. If no value is provided, the activity will return the values in the same order as taken from the table. 
  • Order by Direction (combo box control) (type: string): choose to order the resulted rows in ASC Ascending or DESC Descending direction. It will be ignored if Order By is not set.
  • Table (text box editor type) (type: string): the table name on which to run the select.
  • WHERE (text box editor type) (type: string): the Where statement. For example: ID>2, where ID is a column header.

...

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: collection<collection<string>>): outputs a collection of collections of strings containing the selected rows.To use the elements of the collection, use Get Element From Collection activity.
  • Output Columns (type: int32): outputs the number of columns returned.
  • Output Rows (type: int32): outputs the number of rows returned.
 

As mentioned above, the values for all the required Image Added


A more complex example, with fields, orders and statements can be seen in the image below:

Image Added

Execution: In the Tracking Data there can be seen the returned results:

Image Added


The same result can be seen when executing the same SQL Select query from PHPMyAdmin:

Image Added




Export from Database

This activity exports data from a Database into an XML or a CSV file.

Activity Parameters

The Export from Database activity parameters can be provided by manual inserted values or via IN arguments or variables. Below is an example of predefined arguments, ready to be used by the Select Row(s) from Table activity:Image Removed:

Image Added

Parameters:

  • Destination Path (combo box control) (type: string): the path where the export (csv or xml) file will be created.
  • File Name (text box editor type) (type: string): the name of the XML file or CSV file that will be created. E.g. 'MySQLTableExport'.
  • File Type (combo box control) (type: string): the type of the file that will be created. For each of them a different set of parameters will be displayed.
  • Overwrite (combo box control) (type: string): whether to overwrite the file if it already exists.
  • Root Name (text box editor type) (type: string): the name of the root for the .xml file. This field should be completed only when XML is selected in the File Type field. As syntax, for example, xml_root can be used.
  • Table (text box editor type) (type: string): the table(s) from where the export will be made (accepts multiple values separated by comma).

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: boolean): outputs True or False if the execution was successful or not.
 

Image Added


Execution: the activity will create an export file of the table(s) settled in the Table field, in the specified folder:


Image Added




Import into Database

This activity imports data into a Database from an XML or a CSV file.

Activity Parameters

The Import into Database activity parameters can be provided by manual inserted values or via IN arguments or variables:

Image Added

Parameters:

  • File Type (combo box control) (type: string): the type of the file that will be imported from. Flowster Studio provides by default CSV and XML types.
  • First Row Header (combo box control) (type: string): whether the CSV file includes the header as a first row or not. If, for example, it is used a CSV file obtained via the Export From Database activity, then leave True for this parameter (the Export From Database activity also exports the column names on the first row of the file).
  • Import File Path (text box editor type) (type: string): the path where the file will be selected from, or choose it with the file browser, by clicking the Image Added button.
  • Table (text box editor type) (type: string): the table name where the file will be imported. If left empty, it will take the 'Name' of the CSV File or the 'Root Name' from the XML File.

The Read Only Output variable are the possible output values that the activity will provide:

  • Output (type: boolean): outputs True or False if the execution was successful or not.
 

Image Added


Execution: the activity will import the lines found in the given file:

Image Added

  • the initial table content:
    Image Added

  • the table content after import:

    Image Added