sqlinsert

README

SQLQuery Database Activity

This activity provides your flogo application execute database queries.

Installation

flogo install github.com/project-flogo/activity/sqlquery

Configuration

Settings:

Name	Type	Description
dbType	string	The type of database (mysql, oracle, postgres, sqlite, sqlserver) - REQUIRED
driverName	string	The database driver name - REQUIRED
dataSourceName	string	The database DataSource name - REQUIRED
maxOpenConnections	int	Max open connections (default is unlimited)
maxIdleConnections	int	Max idle connections (default is 2)
query	string	The SQL select query - REQUIRED
disablePrepared	bool	Disable prepared statement usage
labeledResults	bool	Return results labeled by column name

Input:

Name	Type	Description
params	map	The query parameters

Output:

Name	Type	Description
columnNames	array	The names of the result columns
results	array	The results

Examples

Query

Simple query that gets all items with ID less than 10, retrieves all the columns. In order to use mysql, you have to import the driver by adding github.com/go-sql-driver/mysql to the app imports section. See github.com/go-sql-driver/mysql for more information on the driver.

{
  "id": "dbquery",
  "name": "DbQuery",
  "activity": {
    "ref": "github.com/project-flogo/contrib/activity/sqlquery",
    "settings": {
      "dbType": "mysql",
      "driverName": "mysql",
      "dataSourceName": "username:password@tcp(host:port)/dbName",
      "query": "select * from test where ID < 10"
    }
  }
}

Named Query

Query with parameters. Parameters are referenced using ':', e.g. :id, regardless of database

{
  "id": "named_dbquery",
  "name": "Named DbQuery",
  "activity": {
    "ref": "github.com/project-flogo/contrib/activity/sqlquery",
    "settings": {
      "dbType": "mysql",
      "driverName": "mysql",
      "dataSourceName": "username:password@tcp(host:port)/dbName",
      "query": "select * from test where ID < :id"
    },
    "input": {
      "params": {
        "id":10
      }
    }
  }
}

Connectin to a database

db, err := sql.Open("postgres", dataSourceName)

connStr := "postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full"
db, err := sql.Open("postgres", connStr)


Postgres connection

"host=localhost port=5432 user=postgres password=mysecretpassword dbname=postgres sslmode=disable"


Executing insert

Executing queries
ExecContext is used for queries where no rows are returned:

result, err := db.ExecContext(ctx,
	"INSERT INTO users (name, age) VALUES ($1, $2)",
	"gopher",
	27,
)

Modifying app import

import (
	"database/sql"

	_ "github.com/lib/pq"
)

Supported Drivers

• MySQL: github.com/go-sql-driver/mysql
• Oracle: github.com/mattn/go-oci8
• Postgres: github.com/lib/pq
• SQLite: github.com/mattn/go-sqlite3
• SQLServer: github.com/denisenkom/go-mssqldb

Documentation

Index

• func New(ctx activity.InitContext) (activity.Activity, error)
• type Activity
  • func (a *Activity) Cleanup() error
  • func (a *Activity) Eval(ctx activity.Context) (done bool, err error)
  • func (a *Activity) Metadata() *activity.Metadata
• type Input
  • func (i *Input) FromMap(values map[string]interface{}) error
  • func (i *Input) ToMap() map[string]interface{}
• type Output
• type Settings

Functions

func New

func New(ctx activity.InitContext) (activity.Activity, error)

Types

type Activity

type Activity struct {
	// contains filtered or unexported fields
}

Activity is a Counter Activity implementation

func (*Activity) Cleanup

func (a *Activity) Cleanup() error

func (*Activity) Eval

func (a *Activity) Eval(ctx activity.Context) (done bool, err error)

Eval implements activity.Activity.Eval

func (*Activity) Metadata

func (a *Activity) Metadata() *activity.Metadata

Metadata implements activity.Activity.Metadata

type Input

type Input struct {
	Params map[string]interface{} `md:"params"`
}

func (*Input) FromMap

func (i *Input) FromMap(values map[string]interface{}) error

FromMap converts the values from a map into the struct Input

func (*Input) ToMap

func (i *Input) ToMap() map[string]interface{}

ToMap converts the struct Input into a map

type Output

type Output struct {
	ColumnNames []interface{} `md:"columnNames"`
	Results     interface{}   `md:"results"`
}

type Settings

type Settings struct {
	DbType          string `md:"dbType,allowed(mysql,oracle,postgres,sqlite,sqlserver), required"`
	DriverName      string `md:"driverName,required"`
	DataSourceName  string `md:"dataSourceName,required"`
	Statement       string `md:"statement,required"`
	MaxOpenConns    int    `md:"maxOpenConnections"`
	MaxIdleConns    int    `md:"maxIdleConnections"`
	DisablePrepared bool   `md:"disablePrepared"`
	LabeledResults  bool   `md:"labeledResults"`
}