Database testing : Test stored procedures and triggers

Test stored procedures and triggers with sample input data.

Language: Java

Framework: JUnit using JDBC

copy
1import org.junit.BeforeClass;2import org.junit.Test;34import java.sql.*;56public class DatabaseTest {78 private static final String DRIVER = "com.mysql.jdbc.Driver";9 private static final String DATABASE_URL = "jdbc:mysql:/​/​localhost:3306/​myDatabase";10 private static final String USERNAME = "user";11 private static final String PASSWORD = "password";1213 private static Connection connection;1415 @BeforeClass16 public static void setUp() {17 try {18 Class.forName(DRIVER);19 connection = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);20 } catch (ClassNotFoundException | SQLException e) {21 System.out.println("Failed to connect to database");22 e.printStackTrace();23 }24 }2526 @Test27 public void testStoredProcedures() {28 try {29 CallableStatement statement = connection.prepareCall("{CALL myProcedure(?, ?, ?)}");30 statement.setInt(1, 1);31 statement.setString(2, "John");32 statement.setString(3, "Smith");33 ResultSet resultSet = statement.executeQuery();34 while (resultSet.next()) {35 /​/​ do something with the data36 }37 resultSet.close();38 statement.close();39 } catch (SQLException e) {40 System.out.println("Failed to test stored procedures");41 e.printStackTrace();42 }43 }4445 @Test46 public void testTriggers() {47 try {48 Statement statement = connection.createStatement();49 ResultSet resultSet = statement.executeQuery("SELECT * FROM myTable WHERE status = 'completed'");50 while (resultSet.next()) {51 /​/​ do something with the data52 }53 resultSet.close();54 statement.close();55 } catch (SQLException e) {56 System.out.println("Failed to test triggers");57 e.printStackTrace();58 }59 }6061 /​/​ Uncomment the following code to connect to a remote client with desired capabilities62/​/​ public static WebDriver connectToRemoteClient() {63/​/​ WebDriver driver = null;64/​/​ DesiredCapabilities capabilities = DesiredCapabilities.chrome();65/​/​ try {66/​/​ driver = new RemoteWebDriver(new URL("http:/​/​localhost:4444/​wd/​hub"), capabilities);67/​/​ } catch (MalformedURLException e) {68/​/​ System.out.println("Failed to connect to remote client");69/​/​ e.printStackTrace();70/​/​ }71/​/​ return driver;72/​/​ }7374 /​/​ Assumptions:75 /​/​ - The database is running locally on port 330676 /​/​ - The database is named myDatabase77 /​/​ - The user credentials to access the database are stored in variables USERNAME and PASSWORD78 /​/​ - There is a stored procedure called myProcedure that takes in 3 parameters: id (int), firstName (varchar), and lastName (varchar)79 /​/​ - There is a table called myTable with a column called status80 /​/​ - The stored procedure myProcedure returns a ResultSet81}

Language: Python

Framework: Pytest

copy
1# Assumptions:2# 1. MySQL database is used3# 2. Database credentials are stored in a separate configuration file4# 3. Sample input data for stored procedures and triggers are available in a separate file56# Code to connect to local database7import pymysql89config = {10 'user': 'username',11 'password': 'password123',12 'host': 'localhost',13 'database': 'test_database'14}1516def connect_to_local_database():17 conn = pymysql.connect(**config)18 return conn1920# Code to connect to remote client with desired capabilities (e.g. Firefox browser)21# Refer to documentation for the desired capabilities of the remote client2223# Sample test case to test stored procedures and triggers24def test_stored_procedures_and_triggers():25 # Connect to local database26 conn = connect_to_local_database()27 cursor = conn.cursor()2829 # Read sample input data for stored procedures and triggers30 with open('input_data.txt', 'r') as f:31 input_data = f.read().splitlines()3233 # Test stored procedures34 for procedure in input_data:35 cursor.callproc(procedure)36 conn.commit()37 # Perform asserts to verify the output of stored procedures38 assert cursor.rowcount == 13940 # Test triggers41 for trigger in input_data:42 cursor.execute(trigger)43 conn.commit()44 # Perform asserts to verify the output of triggers45 assert cursor.rowcount == 14647 # Close connection to local database48 cursor.close()49 conn.close()

Disclaimer: Following code snippets and related information have been sourced from GitHub and/or generated using AI code generation tools. LambdaTest takes no responsibility in the accuracy of the code and is not liable for any damages.

Accelerate Your Automation Test Cycles With LambdaTest

Leverage LambdaTest’s cloud-based platform to execute your automation tests in parallel and trim down your test execution time significantly. Your first 100 automation testing minutes are on us.

Try LambdaTest

Power Your Software Testing with AI and cloud

Test Intelligently and ship faster. Deliver unparalleled digital experiences for real world enterprises.

Start Free Testing