1*** Settings ***
2Suite Setup       Connect To Database    ibm_db_dbi    ${DBName}    ${DBUser}    ${DBPass}    ${DBHost}    ${DBPort}
3Suite Teardown    Disconnect From Database
4Resource          DB2SQL_DB_Conf.txt
5Library           DatabaseLibrary
6
7*** Test Cases ***
8Create person table
9    ${output} =    Execute SQL String    CREATE TABLE person (id decimal(10,0),first_name varchar(30),last_name varchar(30));
10    Log    ${output}
11    Should Be Equal As Strings    ${output}    None
12
13Execute SQL Script - Insert Data person table
14    Comment    ${output} =    Execute SQL Script    ./my_db_test_insertData.sql
15    ${output} =    Execute SQL Script    ../test/my_db_test_insertData.sql
16    Log    ${output}
17    Should Be Equal As Strings    ${output}    None
18
19Execute SQL String - Create Table
20    ${output} =    Execute SQL String    create table foobar (id integer , firstname varchar(20) )
21    Log    ${output}
22    Should Be Equal As Strings    ${output}    None
23
24Check If Exists In DB - Franz Allan
25    Check If Exists In Database    SELECT id FROM person WHERE first_name = 'Franz Allan';
26
27Check If Not Exists In DB - Joe
28    Check If Not Exists In Database    SELECT id FROM person WHERE first_name = 'Joe';
29
30Table Must Exist - person
31    Table Must Exist    person
32
33Verify Row Count is 0
34    Row Count is 0    SELECT * FROM person WHERE first_name = 'NotHere';
35
36Verify Row Count is Equal to X
37    Row Count is Equal to X    SELECT id FROM person;    2
38
39Verify Row Count is Less Than X
40    Row Count is Less Than X    SELECT id FROM person;    3
41
42Verify Row Count is Greater Than X
43    Row Count is Greater Than X    SELECT * FROM person;    1
44
45Retrieve Row Count
46    ${output} =    Row Count    SELECT id FROM person;
47    Log    ${output}
48    Should Be Equal As Strings    ${output}    2
49
50Verify person Description
51    [Tags]    db    smoke
52    Comment    Query db for table column descriptions
53    @{queryResults} =    Description    SELECT * FROM person fetch first 1 rows only;
54    Log Many    @{queryResults}
55    ${output} =    Set Variable    ${queryResults[0]}
56    Should Be Equal As Strings    ${output}    ['ID', DBAPITypeObject(['NUM', 'DECIMAL', 'DEC', 'NUMERIC']), 12, 12, 10, 0, True]
57    ${output} =    Set Variable    ${queryResults[1]}
58    Should Be Equal As Strings    ${output}    ['FIRST_NAME', DBAPITypeObject(['CHARACTER VARYING', 'CHAR VARYING', 'VARCHAR', 'STRING', 'CHARACTER', 'CHAR']), 30, 30, 30, 0, True]
59    ${output} =    Set Variable    ${queryResults[2]}
60    Should Be Equal As Strings    ${output}    ['LAST_NAME', DBAPITypeObject(['CHARACTER VARYING', 'CHAR VARYING', 'VARCHAR', 'STRING', 'CHARACTER', 'CHAR']), 30, 30, 30, 0, True]
61    ${NumColumns} =    Get Length    ${queryResults}
62    Should Be Equal As Integers    ${NumColumns}    3
63
64Verify Query - Row Count person table
65    ${output} =    Query    SELECT COUNT(*) FROM person;
66    Log    ${output}
67    Should Be Equal As Strings    ${output}    [(2,)]
68
69Verify Query - Row Count foobar table
70    ${output} =    Query    SELECT COUNT(*) FROM foobar;
71    Log    ${output}
72    Should Be Equal As Strings    ${output}    [(0,)]
73
74Verify Query - Get results as a list of dictionaries
75    [Tags]    db    smoke
76    ${output} =    Query    SELECT * FROM person;    \    True
77    Log    ${output}
78    Should Be Equal As Strings    &{output[0]}[first_name]    Franz Allan
79    Should Be Equal As Strings    &{output[1]}[first_name]    Jerry
80
81Insert Data Into Table foobar
82    ${output} =    Execute SQL String    INSERT INTO foobar VALUES(1,'Jerry');
83    Log    ${output}
84    Should Be Equal As Strings    ${output}    None
85
86Verify Query - Row Count foobar table 1 row
87    ${output} =    Query    SELECT COUNT(*) FROM foobar;
88    Log    ${output}
89    Should Be Equal As Strings    ${output}    [(1,)]
90
91Verify Delete All Rows From Table - foobar
92    Delete All Rows From Table    foobar
93
94Verify Query - Row Count foobar table 0 row
95    Row Count Is 0    SELECT * FROM foobar;
96
97Drop person and foobar table
98    Execute SQL String    DROP TABLE person;
99    Execute SQL String    DROP TABLE foobar;
100