List of Bank Holidays For England in SQL Format

List of Bank Holidays For England in SQL Format

Get Social!

First off let’s create a table to store the bank holiday values. You may need to adjust this slightly depending on your SQL server technology being used (this was tested on MySQL/ MariaDB Server) but this is standard SQL dialect and should work on any RDBMS that respects the current SQL standards.

The next step is to insert the bank holiday values below. This table is currently for 2012 up to 2019 for England and Wales.

For a more up to date list, please see my github page.

Please submit a PR if you have collated a list yourself, or have any updates to an existing list. The repository currently covers the UK, but I’d be more than happy to accept a PR for other countries! 


MySQL/ MariaDB Error Code: 1329. No data – zero rows fetched, selected, or processed

Category : How-to

Get Social!

The above error can occur when calling a cursor results in no rows, or no more rows if called in a loop. Whilst the error message is descriptive about what has happened physically, you may wish to catch the error so that you can do something else, or simply replace the generic database error with something more meaningful. 

If you’re not sure what I’m talking about, run the following code on a MySQL or MariaDB database:

The response is:

In order to trap the error we need to define a CONTINUE HANDLER and DECLARE a variable for the CONTINUE HANDLER to set. We can then manage the No data exception simply by checking the variable.

Following on from the above example, we’ve introduced a variable ch_done. When this variable is set to 1 then the last cursor to be FETCHed returned No data. If it returns a zero then data was returned and all is well.

Remember, if you call multiple cursors in a row, you may need to reset the ch_done back to 0.


How to exit from a MySQL/ MariaDB Stored Procedure/ Function Prematurely

Category : How-to

Get Social!

MySQL and MariaDB enable you to define your own error conditions and to report back to the SQL client both a return code and an error message. As soon as you raise the condition then MySQL/ MariaDB will halt any further execution of the code and report the error back to the client. This can help the user calling the function understand what went wrong, rather than seeing a generic database error message.

The above code defines a custom condition with an error code of 45000 which is the suggested user defined error code. Other error codes are available, which you may have seen, but are reserved by the database server to use for specific database error events – it’s best not to mix your user defined messages with these. The function then checks if the error_flag has been set and, if it has, halts further code execution and returns the error “An error occurred!” to the client.

View from MySQL Workbench

You could simplify this by just calling the below code at the point the error is detected, if you are already catching an error event in your stored procedure or function by simply using the below code without the error_flag declaration.


View Table Sizes in MySQL/ MariaDB Databases

Get Social!

MySQL and MariaDB present a bunch of queryable objects that give you all sorts of insights into what’s happening with the database. 

The size of data stored in tabels is one such thing that can be easily queried directly in SQL, providing you have SELECT access to the information_schema. 

Create the two below views in a schema of your choice:

See schema_size and table_size for more info.

You can now query each view to get the size, in megabytes, of a whole schema or individual table respectivley. 

  • schema_size – displays the aggregated size of each schema in the database.
  • table_size – lists all tables in the database. You can add a filter to the table_schema column to limit the output.

MySQL/ MariaDB Table Size

Get Social!

Create a MySQL or MariaDB view to show the size of each table in the database:

See database size for more information.


MySQL/ MariaDB Schema Size

Get Social!

Create a MySQL or MariaDB view to show the aggregated size of each schema in the database:

See database size for more information.


Visit our advertisers

Search

Quick Poll

Do you use GlusterFS in your workplace?

Visit our advertisers