Skip to main content

Appium Blog Series – Part 3 – Using Appium automation script to Check SQLite DB Content of a Device!

Appium is an Open Source mobile application user-interface (UI) testing framework, and is used to automate native, hybrid and web mobile apps. It enables cross-platform mobile application testing by using common API for both Android and iOS platform test scripts, thereby enhancing code-reusability.

Appium provides flexibility of writing and running automation tests in any language such as, Java, C#, PHP, Ruby, Python, Perl, Objective-C.

In the following example, we will demonstrate how to connect to Android SQLite database via Appium script using core Java, JDBC, and run SQL Queries to perform data validation checks.

About SQLite database:

SQLite is the default database engine on Android, which is an open source, lightweight transactional database that stores app data to a text file with a file extension .db, on the device. To access this .db file via Appium scripts, we first need to pull the .db file to our local machine. The steps to achieve this are listed below:

  • Download eclipse plugin, com.questiod.sqlitemanager.jar and save in your \eclipse\dropins folder and restart eclipse.
  • Download latest version of sqlite-jdbc-x.x.x.jar, and add it to java build path in eclipse, by right clicking on project > Properties > Java Build Path > Libraries > Add External JARs >
  • I have a sample sqlite database on my device with name book.db, and it has 1 table Title with 4 records as shown below. Connect Android Device to laptop/computer In USB Debugging mode.
    appium-series3-01
  • In eclipse, click DDMS icon shown below, and select book.db file as shown in below file location on device, click import icon highlighted below to pull this file from device and save it to a folder in pc.
    appium-series3-02

The following code snippet opens a jdbc connection to sqlite database book.db, and run’s select sql query to fetch data from Title table and print results to eclipse console.

Connection con = null;
Class.forName("org.sqlite.JDBC");
//create a jdbc connection to book.db located in below file path
con = DriverManager.getConnection("jdbc:sqlite:E:\\test\\book.db");
con.setAutoCommit(false);

Statement sqlstmt = null;
//using above connection, create a statement object
sqlstmt = con.createStatement();
//run select sql query on Title table, and store results in Resultset.
ResultSet res = sqlstmt.executeQuery("select * from Title;");
//iterate for all data in Title table and print it. 
while (res.next()) {
    int BookId = res.getInt("BookId");
    String BookName = res.getString("BookName");
    String BookAuthor = res.getString("BookAuthor");
    System.out.println("BookId = " + BookId);
    System.out.println("BookName = " + BookName);
    System.out.println("BookAuthor = " + BookAuthor);
    System.out.println();
}
res.close();
sqlstmt.close();
con.close();

 

Jagannath S

Jagannath S

Jagannath is Senior Test Leader in Trigent with more than 15 years of experience. He holds B.E (CSE) from Bangalore university and is PMI certified PMP holder. He has global experience in areas of Test Architecture, Test Strategy, Test Delivery, Leadership for varied business domains like BFSI, Retail, Telecom, News & Media, Supply Chain Management, Consumer Electronics, and has extensive project experience in areas of Performance testing, Automation testing, Functional testing, ETL Datawarehouse testing.