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