Webinar

November 3rd, 2021 12:00 PM EDT
Automation for Field Services & DistributionNovember 3rd, 2021 12:00 PM EDT
Learn how creating a digital workforce can improve your supply chain processes!

Call Stored Procedure

Call stored procedure with name and params.

Arguments

Argument Type Default value Description
namenull
paramsNone
sanstranFalse
param name:procedure name
param params:parameters for the procedure as a list, defaults to None
param sanstran:run command without an explicit transaction commit or rollback, defaults to False

Examples

*** Tasks ***
@{params}     Create List   FirstParam   SecondParam   ThirdParam
@{results}    Call Stored Procedure   mystpr  ${params}

Connect To Database

Connect to database using DB API 2.0 module.

Arguments

Argument Type Default value Description
module_namestr, NoneNone
databasestr, NoneNone
usernamestr, NoneNone
passwordstr, NoneNone
hoststr, NoneNone
portint, NoneNone
charsetstr, NoneNone
config_filestrdb.cfg
autocommitboolFalse
param module_name:
 database module to use
param database:name of the database
param username:of the user accessing the database
param password:of the user accessing the database
param host:SQL server address
param port:SQL server port
param charset:for example, "utf-8", defaults to None
param config_file:
 location of configuration file, defaults to "db.cfg"
param autocommit:
 set autocommit value for connect (only with pymssql atm)

Examples

*** Tasks ***
Connect To Database  pymysql  database  username  password  host  port
Connect To Database  ${CURDIR}${/}resources${/}dbconfig.cfg

Description

Get description of the SQL table

Arguments

Argument Type Default value Description
tablenull
param table:name of the SQL table

Examples

*** Tasks ***
Connect To Database    pymysql  mydb  user  pass  127.0.0.1
${db_description}      Description  mytable

Disconnect From Database

Close connection to SQL database

Examples

*** Tasks ***
Connect To Database    pymysql  mydb  user  pass  127.0.0.1
${result}              Query   Select firstname, lastname FROM table
Disconnect From Database

Execute Sql Script

Execute content of SQL script as SQL commands.

Arguments

Argument Type Default value Description
filenamenull
sanstranFalse
encodingutf-8
param filename:filepath to SQL script to execute
param sanstran:run command without an explicit transaction commit or rollback, defaults to False
param encoding:character encoding of file

Examples

*** Tasks ***
Execute SQL Script   script.sql

Get Number Of Rows

Get number of rows in a table. Conditions can be given as arguments for WHERE clause.

Arguments

Argument Type Default value Description
tablenull
conditionsNone

Get number of rows in a table. Conditions can be given as arguments for WHERE clause.

param table:name of the SQL table
param conditions:
 restrictions for selections, defaults to None

Examples

*** Tasks ***
${count}   Get Number Of Rows  tablename
${count}   Get Number Of Rows  tablename  column1=5 and column2='x'

Get Rows

Get rows from table. Columns and conditions can be set to filter result.

Arguments

Argument Type Default value Description
tablenull
columnsNone
conditionsNone
as_tableTrue

Get rows from table. Columns and conditions can be set to filter result.

param table:name of the SQL table
param columns:name of columns to return, defaults to None means that all columns are returned
param conditions:
 limiting result by WHERE clause, defaults to None
param as_table:if result should be instance of Table, defaults to True False means that return type would be list

Examples

*** Tasks ***
@{res}   Get Rows  tablename  arvo
@{res}   Get Rows  tablename  arvo  columns=id,name
@{res}   Get Rows  tablename  columns=id  conditions=column1='newvalue'
@{res}   Get Rows  tablename  conditions=column2='updatedvalue'

Query

Make a SQL query.

Arguments

Argument Type Default value Description
statementstrnull
assertionstr, NoneNone
sanstranboolFalse
as_tableboolTrue
param statement:
 SQL statement to execute
param assertion:
 assert on query result, row_count or columns. Works only for SELECT statements Defaults to None.
param sanstran:run command without an explicit transaction commit or rollback, defaults to False
param as_table:if result should be instance of Table, defaults to True False means that return type would be list

Examples

*** Tasks ***
@{res}   Query   Select firstname, lastname FROM table
FOR  ${row}  IN  @{RES}
    Log   ${row}
END
@{res}   Query  Select * FROM table  row_count > ${EXPECTED}
@{res}   Query  Select * FROM table  'arvo' in columns
@{res}   Query  Select * FROM table  columns == ['id', 'arvo']

Set Auto Commit

Set database auto commit mode.

Arguments

Argument Type Default value Description
autocommitTrue
param autocommit:
 boolean value for auto commit, defaults to True

Examples

*** Tasks ***
Set Auto Commit             # auto commit is set on
Set Auto Commit   False     # auto commit is turned off