With Flint's JDBC Connector you can execute queries against the mostly used databases in any IT infrastructure.

With this document guide you will be able to work with and use a DB Connector.

Features

  • Query all popular databases : MySQL, Microsoft SQL, Oracle and PostgreSQL
  • Simple DB queries : select, insert, update, delete and procedure
  • Databases with/without passwords accessible
  • Direct access to the query execution results from the remote server machine
  • Ability to set Connector execution timeout
  • Synchronous / Asynchronous execution of the Connector

Connector Configuration

add-db-conn

Configuration parameters - config(JSON)
ParameterDescriptionRequired
databaseThe data source. Name of the database on which actions have to be performedtrue: config/requrest
typeType of Database. Valid types : mysql, postgresql, Microsoft SQL and oracletrue: config/request
targetHost name or IP address of the server on which the database residestrue: config/request
portPort number on which the target server is listeningtrue: config/request
driverName of the driver depending on the type of database specified. For example : for a database type of mysql the driver name is com.mysql.jdbc.Drivertrue: config/request
usernameUsername associated with the database. Not required, if already specified in connector configurationtrue: config/request
passwordPassword associated with the database. Not required, if already specified in connector configurationtrue: config/request
Example
{
  "database": "employee",
  "type" : "mysql",
  "target": "localhost",
  "port": 3306,
  "driver": "com.mysql.jdbc.Driver",
  "username": "root",
  "password": "root",
  "jdbc-url": "jdbc:mysqllocalhost:3306/employee"
}

 

Actions

select

Retrieving data from the database.

Request parameters
ParameterDescriptionrequired
actionOperations to be performed on the data stored in database. Valid actions : select, insert, update, delete and proceduretrue
databaseBasically, is the data source. Name of the database on which actions have to be performed. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
targetHost name or IP address of the server on which the database residestrue
querySpecify the query that you want to performed on the databasetrue
typeType of Database. Valid types : mysql, postgresql, Microsoft SQL and oracle. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
portPort number on which the target server is listening. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
driverName of the driver depending on the type of database specified. For example : for a database type of mysql the driver name is com.mysql.jdbc.Driver. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
usernameUsername associated with the database. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
passwordPassword associated with the database. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
Response parameters
ParameterDescriptionrequired
resultResults as per the query performed on the database. Will reflect the number of rows in database affected in case of insert,delete and update actions. In case of select action, the data retrieved will be displayed in an array format. The result for a procedure action is based on the type of query performedtrue
Example
response=@call.connector("my-db-connector")
              .set("action","select" )
              .set("database","employees")
              .set("type","mysql")
              .set("target","db.example.com")
              .set("port",3306)
              .set("driver", "com.mysql.jdbc.Driver")
              .set("query", "select * from employee")
              .sync

#DB Connector Response Parameters
result=response.get("result") #Query execution results

 

insert

Inserting data into the database

Request parameters
ParameterDescriptionrequired
actionOperations to be performed on the data stored in database. Valid actions : select, insert, update, delete and proceduretrue
jdbc-urlUsed to point to the database to which you wish to connect. URL convention followed :type:target:port/database. Example : jdbc:mysqllocalhost:3306/employee. After specifying the value for this parameter you can skip providing values for target, port, database and typefalse
driverName of the driver depending on the type of database specified. For example : for a database type of mysql the driver name is com.mysql.jdbc.Driver. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
querySpecify the query that you want to performed on the databasetrue
Response parameters
ParameterDescriptionrequired
resultResults as per the query performed on the database. Will reflect the number of rows in database affected in case of insert,delete and update actions. In case of select action, the data retrieved will be displayed in an array format. The result for a procedure action is based on the type of query performedtrue
Example
response=@call.connector("my-db-connector")
              .set("action","insert" )
              .set("jdbc-url" : "jdbc:mysql://db.example.com:3306/my-employess")
              .set("driver", "com.mysql.jdbc.Driver")
              .set("query", "insert into employee (age,lastname,city) values(27,'kennedy','pune')")
              .sync

#DB Connector Response Parameters
result=response.get("result") #Query execution results

 

update

Updating data from the database

Request parameters
ParameterDescriptionrequired
actionOperations to be performed on the data stored in database. Valid actions : select, insert, update, delete and proceduretrue
jdbc-urlUsed to point to the database to which you wish to connect. URL convention followed :type:target:port/database. Example : jdbc:mysqllocalhost:3306/employee. After specifying the value for this parameter you can skip providing values for target, port, database and typefalse
driverName of the driver depending on the type of database specified. For example : for a database type of mysql the driver name is com.mysql.jdbc.Driver. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
querySpecify the query that you want to performed on the databasetrue
Response parameters
ParameterDescriptionrequired
resultResults as per the query performed on the database. Will reflect the number of rows in database affected in case of insert,delete and update actions. In case of select action, the data retrieved will be displayed in an array format. The result for a procedure action is based on the type of query performedtrue
Example
response=@call.connector("my-db-connector")
              .set("action","update" )
              .set("jdbc-url" : "jdbc:mysql://db.example.com:3306/my-employess")
              .set("driver", "com.mysql.jdbc.Driver")
              .set("query", "update employee set lastname='subhramanyam',city='delhi' where age = 27")
              .sync

#DB Connector Response Parameters
result=response.get("result") #Query execution results

 

delete

deleting data from the database

Request parameters
ParameterDescriptionrequired
actionOperations to be performed on the data stored in database. Valid actions : select, insert, update, delete and proceduretrue
jdbc-urlUsed to point to the database to which you wish to connect. URL convention followed :type:target:port/database. Example : jdbc:mysqllocalhost:3306/employee. After specifying the value for this parameter you can skip providing values for target, port, database and typefalse
driverName of the driver depending on the type of database specified. For example : for a database type of mysql the driver name is com.mysql.jdbc.Driver. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
querySpecify the query that you want to performed on the databasetrue
Response parameters
ParameterDescriptionrequired
resultResults as per the query performed on the database. Will reflect the number of rows in database affected in case of insert,delete and update actions. In case of select action, the data retrieved will be displayed in an array format. The result for a procedure action is based on the type of query performedtrue
Example
response=@call.connector("my-db-connector")
              .set("action","delete" )
              .set("jdbc-url" : "jdbc:mysql://db.example.com:3306/my-employess")
              .set("driver", "com.mysql.jdbc.Driver")
              .set("query", "delete from employee where age=27")
              .sync

#DB Connector Response Parameters
result=response.get("result") #Query execution results

 

procedure

call procedure/function

Request parameters
ParameterDescriptionrequired
actionOperations to be performed on the data stored in database. Valid actions : select, insert, update, delete and proceduretrue
jdbc-urlUsed to point to the database to which you wish to connect. URL convention followed :type:target:port/database. Example : jdbc:mysqllocalhost:3306/employee. After specifying the value for this parameter you can skip providing values for target, port, database and typefalse
driverName of the driver depending on the type of database specified. For example : for a database type of mysql the driver name is com.mysql.jdbc.Driver. Not required, if already specified in connector configuration. If specified will overwrite the one given in connector configurationtrue
querySpecify the query that you want to performed on the databasetrue
Response parameters
ParameterDescriptionrequired
resultResults as per the query performed on the database. Will reflect the number of rows in database affected in case of insert,delete and update actions. In case of select action, the data retrieved will be displayed in an array format. The result for a procedure action is based on the type of query performedtrue
Example
response=@call.connector("my-db-connector")
              .set("action","procedure" )
              .set("jdbc-url" : "jdbc:mysql://db.example.com:3306/my-employess")
              .set("driver", "com.mysql.jdbc.Driver")
              .set("query", "call simple()")
              .sync

#DB Connector Response Parameters
result=response.get("result") #Query execution results

 

Connector request error handling

Here is how you can handle the connector requests success or failures within your Flintbit. This would help you to take appropriate action if something failed.

if response.exitcode == 0               # 0 is success.
  puts "success"
  # take action in case of success
else                                    # non zero means fail
  puts "fail"
  puts "Reason:" + response.message     # get the reason of failure
  ## Take action in case of failure
end

On this page: