Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Testing Dates from a MySQL Database with JMeter

DZone's Guide to

Testing Dates from a MySQL Database with JMeter

Read this article in order to follow a tutorial on how to test dates from a MySQL database with JMeter.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

 In my previous blog post "Creating and Testing Dates in JMeter - Learn How," I showed how to create dates for testing APIs in Apache JMeter™. This article will show how to test the creation of tables with dates and how to test data retrieval from a database by dates. By testing these actions in JMeter, you can ensure your APIs correctly write to the database, delete from it, and so on.

To work with dates, the MySQL database will be used. In the database, the following data types exist for the Date and Time:

Data Types Description
DATE

Supported range: from '1000-01-01' to '9999-12-31'

Display Format: 'YYYY-MM-DD'

DATETIME

Supported range: from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

Display format: 'YYYY-MM-DD HH:MM:SS[.fraction]

TIME

Supported range: from '-838:59:59.000000' to '838:59:59.000000'

Display format: HH:MM:SS[.fraction]

TIMESTAMP

Supported range: from '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC

Display format: 'YYYY-MM-DD HH:MM:SS[.fraction]


Fraction is the value of microseconds with an accuracy of 6 digits.

In order to create a table in a database with date and time fields, you need to do the following:

1. Configure the connection to the database

Configure the connection as described in the blog post "MySQL Database and JMeter - How to Test Your Connection".

2. Add a JDBC Request 

Thread Group -> Right Click -> Add -> Sampler -> JDBC RequestQuery Type -> Update Statement

In this step, we will send a query to the database to create a table.

In the JDBC Request, add the following code:

The code shown above does the following:

CREATE TABLE — The operator that creates the table in the database

sakila — Database name. Choose the name your database has.

for_test — The name of the table to be created

DATE_TEST DATE:

  • DATE_TEST — Column name in the table
  • DATE — The data type for the values that will be stored in the column

DATETIME_TEST DATETIME(3):

  • DATETIME_TEST — Column name in the table
  • DATETIME — The data type for the values that will be stored in the column
  • (3) — The accuracy to which microseconds will be displayed. In our case, the microseconds will be displayed with an accuracy of up to 3 digits

TIME_TEST TIME(2):

  • TIME_TEST — Column name in the table
  • TIME — The data type for the values that will be stored in the column
  • (2) — The accuracy to which microseconds will be displayed. In our case, the microseconds will be displayed with an accuracy of up to 2 digits

TIMESTAMP_TEST TIMESTAMP(6):

  • TIMESTAMP_TEST — Column name in the table
  • TIMESTAMP — The data type for the values that will be stored in the column
  • (6) — The accuracy to which microseconds will be displayed. In our case, the microseconds will be displayed with an accuracy of up to 6 digits

TIMESTAMP_WITHOUT_MICROS TIMESTAMP:

  • TIMESTAMP_WITHOUT_MICROS — Column name in the table
  • TIMESTAMP — The data type for the values that will be stored in the column. For this column, microsecond accuracy is not specified. In this case, the values in this column will not display microseconds.

3. Add a View Results Tree

Thread Group -> Right Click -> Add -> Listener -> View Results Tree

This step will display the request that JMeter sends to the database and the response that it receives from the database.

After starting the test, we will see a result, as shown in the image below:

0 updates means that we successfully created the table in the database

If we go to the database, we will also see the created table, as shown in the image below:

In order to add rows with date and time to the created table, i.e filling in the columns with data, you need to do the following.

4. Add a JDBC Request

Thread Group -> Right Click -> Add -> Sampler -> JDBC RequestQuery Type -> Update Statement

In the JDBC Request, add the following code:

The code shown above does the following:

INSERT INTO — The statement that allows adding data to a table

sakila — Database name

for_test — The name of the table that data will be added to

VALUES — Specifies the values to add to the table

('2018-04-30', '2014-09-08 18:52:07.769', '18:52:07.769', '2014-09-08 18:52:07.769111', '2014-09-08 18:52:07') — The values to be written to the columns, in our case.

2018-04-30 — will be written inthe column DATE_TEST

2014-09-08 18:52:07.769 — will be written in the DATETIME_TEST column, etc.

When adding date and time values to the database, the following rules exist:

  • Values must be passed only in the format that the corresponding data type supports. For example, if the DATE data type is displayed in the 'YYYY-MM-DD' format, then the value should be passed in the same form (2018-04-30). If you pass the value in the form 30-04-2018, you will get an error of the following kind: "Data truncation: Incorrect date value: '30 -04-2018' for column 'DATE_TEST' at row 1".
  • If the value is passed as a string (the value is specified in single quotes), then you can use different delimiters. For example, '2018/04/30', '2018@04@30' or '20180430'. Regardless of which separator is used, the value of the date and time in the database will only be displayed in the format that the data type supports, which is "-".
  • If the value is passed as a number (the value is specified without single quotes), then a delimiter is not used. For example, if we need to add the value '2018-04-30' in numerical form, then it must be passed as 20180430. After adding to the database, this value will be displayed as 2018-04-30. If you want to add the date and time ('2018-09-08 18:52:07') in numerical form, then the value is passed as 20180908185207.
  • For columns with data type DATETIME and TIMESTAMP, values can be passed without specifying a time. For example, if we pass a value of type 2018-04-30 (without time), then in the database this value will be displayed in the form 2018-04-30 00: 00: 00.000
  • For columns with data type DATE, we can pass the value with the time, but in this case the time will not be displayed in the database. For example, if we pass the value in the form 2018-04-30 18:00:54.234, only 2018-04-30 will be displayed in the database

After starting the test, we will see a result, as shown in the image below:

1 updates means that we successfully added data to the table.

If we go to the database and execute SELECT * FROM sakila.for_test, we'll see the result, as shown in the image below:

In order to get data from a database about a specific date or time, you need to do the following

5. Add a JDBC Request

Thread Group -> Right Click -> Add -> Sampler -> JDBC RequestQuery Type -> Select Statement

In the JDBC Request, add the following code:

The code shown above does the following:

SELECT — The statement to retrieve data from a table

* — Selects all columns from the table. If you want to select a specific column, then * must be replaced by the name of the column

FROM — The statement for indicating where data will be received from. In our case, the data will be retrieved from sakila.for_test

sakila — Database name

for_test — The name of the table we need to retrieve the data from

WHERE — The condition for selecting data. In our case, the condition is DATETIME_TEST = '2018-09-08 18:52:07.769'

DATETIME_TEST — The name of the column in the table for_test

'2018-09-08 18:52:07.769' — The value that is contained in the DATETIME_TEST column

After starting the test, we will see a result, as shown in the image below:

The image above shows the result of getting data from the database, where the value in the DATETIME_TEST column is '2018-09-08 18:52:07.769'

In order to select data from a table without specifying, for example, the month, date or time, you need to do the following:

6. Add a JDBC Request

Thread Group -> Right Click -> Add -> Sampler -> JDBC RequestQuery Type -> Select Statement

In the JDBC Request, add the following code:

The code shown above does the following:

SELECT — The statement to retrieve data from a table

* — Select all columns from the table. If you want to select a specific column, then * must be replaced by the name of the column

FROM — The statement for indicating where data will be received from. In our case, the data will be retrieved from sakila.payment

sakila — Database name

payment — The name of the table from which need to retrieve the data (created when configuring the MySQL database)

WHERE — The condition for selecting the data. In our case, the condition is payment_date like '2005-05-25%'

like — The statement that performs the search the for data in the table according to a specific template. In our case, the template is '2005-05-25%' in the column payment_date

% — This means that any character can appear after the value 2005-05-25. The % character can also be used at the beginning of the value, for example, %05-25%

After starting the test, we will see a result, as shown in the image below:

The image above shows the result of getting data from the database, where the column payment_date contains '2005-05-25'

In order to get data from a table for a specific date range or time, you need to do the following

7. Add a JDBC Request

Thread Group -> Right Click -> Add -> Sampler -> JDBC RequestQuery Type -> Select Statement

In the JDBC Request, add the following code:

The code shown above does the following:

SELECT — The statement to retrieve data from a table

* — Select all columns from the table. If you want to select a specific column, then * must be replaced by the name of the column

FROM — The statement for indicating where data will be received from. In our case, the data will be retrieved from sakila.payment

sakila — Database name

payment — The name of the table from which need to retrieve the data

WHERE — The condition for selecting the data. In our case, the condition is payment_date between '2005-05-25 00:00:40' and '2005-05-25 00:43:11'

between and — The statement that searches for data in a table in the specified range. In our case, data will be received where the column payment_date contains values that fall within the range from '2005-05-25 00:00:40' to '2005-05-25 00:43:11' (inclusive).

order by — The statement that sorts in a specified column. In our case, the sorting will be performed on the payment_date column in the order of the increase, from earlier to latest.

After starting the test, we will see a result, as shown in the image below:

The image above shows the result of getting data from the database, where the column payment_date contains values that fall within the range from '2005-05-25 00:00:40' to '2005-05-25 00:43:11' (inclusive).

In order to get data from a table for a specific date range or time, but do not include the beginning and end values of the range, you need to do the following

8. Add a JDBC Request

Thread Group -> Right Click -> Add -> Sampler -> JDBC RequestQuery Type -> Select Statement

In the JDBC Request, add the following code:

The code shown above does the following:

SELECT — The statement to retrieve data from a table

* — Select all columns from the table. If you want to select a specific column, then * must be replaced by the name of the column

FROM — The statement for indicating where data will be received from. In our case, the data will be retrieved from sakila.payment

sakila — Database name

payment — The name of the table from which we need to retrieve the data

WHERE — The condition for selecting data. In our case, the condition is payment_date > '2005-05-25 00:00:40' and payment_date < '2005-05-25 00:43:11'

payment_date — The name of the column in the table payment

order by — The statement that sorts in the specified column. In our case, the sorting will be performed on the payment_date column in the order of the increase.

After starting the test, we will see a result, as shown in the image below:

The image above shows the result of getting data from the database, where the column payment_date contains values that fall into the range greater than '2005-05-25 00:00:40', but less than '2005-05-25 00:43:11'

For working in the MySQL database with dates and time, there are a lot of functions that you can read by clicking on the link.

Follow our Database testing with JMeter series:

Then, you can share your tests and work on them with your team members, run your test in the cloud, and get insightful reports that show KPIs in real-time and over time.

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
performance testing ,apache jmeter ,api testing ,database connection ,creating dates ,database testing ,mysql database ,load testing ,database

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}