Swimlane Relational Databases
17 min
this connector allows the swimlane turbine platform to retrieve data, modify data and execute stored procedures from relational databases capabilities this connector is compatible with the following databases microsoft sql server postgresql mysql oracle although the connector was tested against the mentioned databases, it is possible to use it with other databases as long as the engines are compatible to modify data in a database (i e , insert, etc ), use the task run modification query to retrieve data from a database (i e , select), use the task run query to execute stored procedures, use the task run stored procedure asset information the asset requires a username , password , host , port , and database name database type & database engine additionally, the asset requires an engine dialect to supply the database type if supplying a database type, the connector will use the default dialects for the corresponding database ex postgresql will default to postgresql+pg8000 available types are ms sql server, postgresql, mysql, oracle it is possible to provide the specific dialect to use in case its needed available dialects are database available recommended postgres postgresql, postgresql+pg8000 postgresql+pg8000 mysql mysqlconnector mysqlconnector oracle oracle, oracle+cx oracle oracle microsoft sql server mssql+pyodbc, mssql+pymssql mssql+pymssql note in order to use mssql+pyodbc, you must install the package "pyodbc" in turbine tasks setup and filtering the task "direct query" allows inputs to be passed as wild cards any "?" present will be replaced, in order, with supplied "parameters " example query = select id from person where id = %s or id < %s parameters = \["10","20"] the following query will be executed select id from person where id = 10 or id < 20 configurations asset authentication for databases configuration parameters parameter description type required database type database type available ms sql server, postgresql, mysql, oracle string required username username to connect to the database string required password password string required host server host string required database name name of the database string required port host port to use number optional database dialect database dialect this will override the database type supplied see readme for available example postgresql+pg8000 string optional actions run modification query run a query that modifies the database and does not return information example insert input argument name type required description query string required query using parameters is acceptable ('select from foo where id = %s or id = %s') parameters need to be supplied through the parameter input in order parameters array optional a list of parameters should be in order output parameter type description result string result of the operation query executed string output field query executed row count number count value example \[ { "result" "success", "query executed" "insert into example1 values (2, 'bob');", "row count" 1 } ] run query runs the supplied query un constrained and returns the result only for queries that return information example select input argument name type required description query string required query using parameters is acceptable ('select from foo where id = %s or id = %s') parameters need to be supplied through the parameter input in order parameters array optional a list of parameters should be in order to csv boolean optional when true, returns a csv as an attachment output parameter type description result array result of the operation query executed string output field query executed row count number count value file object file file name string name of the resource file string output field file example \[ { "result" \[ {} ], "query executed" "select from table1", "row count" 1 } ] run stored procedure retrieve data from a stored procedure input argument name type required description stored procedure name string required name of the stored procedure to run stored procedure params array optional parameters in order that need to be executed with the stored procedure to csv boolean optional when true, returns a csv as an attachment output parameter type description result array result of the operation query executed string output field query executed row count number count value file object file file name string name of the resource file string output field file example \[ { "result" \[ {} ], "query executed" "select from table1", "row count" 1 } ]