Category Archives: Database

Application running on Docker container – Part 2 – Database

This is a continuation of our previous post regarding a simple application running inside a Docker container. This post focuses on the database part of the application stack. Yes, we run the database in a separate container! MariaDB I’m using … Continue reading

Posted in Database, Linux, Web Development | Tagged , , | Leave a comment

Liquibase – Source control for your database

Many PHP, Python and Ruby frameworks already have their database source control feature either built-in or supported as a plugin. In our case, we didn’t use (PHP) CodeIgniter’s built-in database migration tool nor use any migration tool at all until … Continue reading

Posted in Database, MySQL, Web Development | Tagged , , , , | Leave a comment

MySQL – Locking reads using transaction

Here is a quick post before I hit work on a Sunday afternoon. I was trying to create some sort of locking mechanism designed to trigger the same tasks from multiple clients but only one should be executed/run. I tried … Continue reading

Posted in Database, MySQL, Web Development | Tagged , | Leave a comment

Default to utf8 encoding for MySQL 5.5

Been banging my head several times trying to set the correct configuration for setting utf8 as default encoding for my MySQL database. Searching the web shows old configuration that is not compatible with MySQL 5.5. However, I’ve found one that … Continue reading

Posted in MySQL | Tagged , , | Leave a comment

SQL Server – Generating Date Range With CTE

My job was to create a report for monthly totals of a specific year. It will compute monthly totals from April 1 of that year to March 31 of the next year and must return exactly 12 rows for that … Continue reading

Posted in SQL, SQL Server | Tagged , , , , , | 2 Comments

SQL Server – Using Temporary Tables

The original query was almost perfect – it displays the daily totals for a given month. However, specs changed and they need to add the grand total to the last row. Then another specs for the MAX and MIN rows. … Continue reading

Posted in SQL, SQL Server | Tagged , , , , | Leave a comment

SQL Server – Dynamic Order By Clause

Yes, I’m too busy for SQL scripting job these recent weeks. I have a union select query that needed to be sorted per union but since it is not allowed to have order by in my unions, I have to … Continue reading

Posted in SQL, SQL Server | Tagged , , , , | Leave a comment

SQL Server Import/Export to CSV

There are tools that will help you in importing or exporting CSV files to and from SQL Server databases. However, using them will slow you down for a factor of 20 because of those click, next, next, next wizard thing. … Continue reading

Posted in SQL, SQL Server | Tagged , , , , , , | Leave a comment

SQL Server – Select Nth Row

Seems like I’m now stuck with .NET development and the current tasks where developing SQL scripts. One of the spec was to return the first record, second record and third record as columns for a row. I know how to … Continue reading

Posted in SQL, SQL Server | Tagged , , , , , , , , | Leave a comment

SQL Server – Dynamic Where Clause

Things can be neatly done in application level by concatenating where clause however, there are times when you are out of that application and all you can do is SQL Query. I have found a good resource to do so … Continue reading

Posted in Database, SQL | Tagged , , , , | Leave a comment