@DataProvider being a MySQL DataBase.

[Big O' Notation]

Hello everyone,
I see that the provided @DataProvider example in Cedric's Book works fairly well where the return is:

            return new Object[][]
            {
                    { lower-1, lower, upper, false},
                    { lower, lower, upper, true },
                    { lower+1, lower, upper, true},
                    { upper, lower, upper, true},
                    { upper+1, lower, upper, false},
            };

I was wondering if anyone had a working example where the DataSource is a DataBase, such as Oracle, or better yet, MySQL. If so, can you please post the example. It would be very helpful!

Thanks,
-Big ONotation

[Big O' Notation]

Just to clarify a bit more, for now the goal is just print the values of the database to the console.

Thanks again!
-Sheeraz M.

[Cédric Beust ♔]

Hi Big'O,

Love your name :-)

You're not giving enough details to provide a thorough answer, so the basic idea is basically how you want to spread the work between your data provider and your test method.  A possible approach is to have the data provider access the database and pass Java objects to your test methods.  You can also have your data provider simply return table names.  Or it can return tuples of (table name, column1, column2) and the test method will fetch those from the database.

Does this help?

--
Cédric

On Thu, Mar 6, 2008 at 3:39 PM, Big O' Notation <bigono...@gmail.com> wrote:

[Cédric Beust ♔]

Hi Big'O,

Love your name :-)

You're not giving enough details to provide a thorough answer, so the basic idea is basically how you want to spread the work between your data provider and your test method.  A possible approach is to have the data provider access the database and pass Java objects to your test methods.  You can also have your data provider simply return table names.  Or it can return tuples of (table name, column1, column2) and the test method will fetch those from the database.

Does this help?

--
Cédric

On Thu, Mar 6, 2008 at 3:39 PM, Big O' Notation <bigono...@gmail.com> wrote:

[Big O' Notation]

Hello Cedric,
Really appreciate your response. I was actually trying to figure out the following:

a) How could we connect to the Database?
b) Can the TestNG annotations subsititude JDBC??)

In order to conduct some Data-Driven testing, I generated a code where TestNG connects to a MySQL database, and allows Data-Driven testing. Here is the code that everyone could refer to. (The @Test annotated method will need modification based on the number of columns, and the Data Types of those columns):

====================================================================

import org.testng.annotations.*;
import java.util.*;
import java.sql.*;


@Test (groups = { "MySQL_Demo" })
public class TestingMySQL
{

    @Test(dataProvider = "MySQL-provider")
    public void mySQL_data_echo(String a, String b)
    {
        System.out.println("The values in the database are: " + a + " " + b );
    }


    @DataProvider(name = "MySQL-provider")
        public String [][] mySQL_Data()
        {
            // Connect to the database.
       
            int rowCount = 0;
            int columnCount = 0;
            String myData [][] = null;

            try
            {
                Class.forName("com.mysql.jdbc.Driver");
                String url = "jdbc:mysql://localhost/mysql_db";
                Connection con = DriverManager.getConnection(url, "uname", "pword");
               
                // Execute the SQL statement
                Statement stmt = con.createStatement();
                ResultSet resultSet = stmt.executeQuery("SELECT * from data_Table");
               
                // Get Column count
                ResultSetMetaData resultSet_metaData= resultSet.getMetaData();
                columnCount = resultSet_metaData.getColumnCount();
               
                // Get Row Count
                while( resultSet.next() )
                    rowCount++;

                //Initialize data structure
                myData = new String [rowCount][columnCount];
               
                resultSet.beforeFirst();
               
               
                //populate data structure
                for(int row=0; row<rowCount; row++)
                {
                    resultSet.next();
                   
                    for(int col=1; col<=columnCount; col++)
                        myData[row][col-1] = resultSet.getString(col);
                }

            stmt.close();
                con.close();
   
            }

            catch (Exception e)
            {
                e.printStackTrace();
            }
           
            return myData;
           
        }
}


====================================================================

Two additional relevent questions I now have are as follows:

a) Is there an alternative way to conduct the above functionality using TestNG when working with MySQL?
b) The above example would work for other databases as well, such as Oracle, DB2, etc. I was wondering, how would I use the above logic for an excel spreadsheet? Any existing example?

Thanks a bunch for your assistance in advance!

Regards,
Sheeraz M.

[Cédric Beust ♔]

Hi Sheeraz,

Your code is perfectly valid to test database contents.  Data Providers provide a nice abstraction that allow your tests to focus on testing the objects that your application will be working with while leaving aside the infrastructure details.

There are several libraries that allow you to read Excel spreadsheets, such as POI (I never used it, it's just the first name that comes to my mind), and I suspect the code for such a data provider would be fairly straightforward to write...

--
Cédric

[total-qa]

Hi All,

If anyone require the working example on this scenario. please refer to the link mentioned below:

http://total-qa.com/dataprovider-mysql-database-javaseleniumreal-time-examples/

Regards,

total-qa.com