@daharveyjr

Architect, Developer, Web Geek!

September 8, 2014
by daharveyjr
0 comments

cat_db2

DB2 CONCAT (Concatenate) Function

The DB2 CONCAT function will combine two separate expressions to form a single string expression. It can leverage database fields, or explicitly defined strings as one or both expression when concatenating the values together.

The syntax for the CONCAT function is shown here:

-- Using Database Fields
SELECT CONCAT({field_1}, {field_2}) FROM {table_name};

-- Using String Expressions
SELECT CONCAT('{string_1}', '{string_2}') FROM {table_name};

-- Using Database Fields and String Expressions
SELECT CONCAT({field_1}, '{string_2}') FROM {table_name};

To show an example statement in use:

SELECT CONCAT(MFNAME, MFPARTNUMBER) FROM CATENTRY;

The SQL statement above will take and concatenate the values from the MFNAME and MFPARTNUMBER columns in the CATENTRY table.

If we want to add an explicit string (or space) between the two columns, we could leverage the following SQL statement to do so. This query will add a blank space between the two defined expressions.

SELECT CONCAT(CONCAT(MFNAME, ' '), MFPARTNUMBER) FROM CATENTRY;

NOTE: There is a shortcut notation available; however, I’ve found that if DB2 is not configured to enable it you will receive errors should you attempt to use it.

To leverage the CONCAT function shortcut, you’ll leverage a ‘||’ (double pipe) notation, and will result in an SQL query with the following syntax and will produce the same result as the second example query above:

SELECT MFNAME || ' ' || MFPARTNUMBER FROM CATENTRY;

If you want to read the IBM DB2 documentation on the CONCAT scalar function, please visit the DB2 v9.7 Information Center: CONCAT scalar function.

September 1, 2014
by daharveyjr
0 comments

cat_db2

DB2 SUBSTR (Substring) Function

The DB2 SUBSTR function returns a substring of a string. The SUBSTR function takes two arguments (source and start location) with an optional third argument (length).

The syntax for the SUBSTR function is shown here:

-- With Required Parameters
SELECT SUBSTR({field_name}, {start_location});

-- With Optional {string_length} Parameter
SELECT SUBSTR({field_name}, {start_location}, {string_length});

To show an example statement in use:

SELECT SUBSTR(NAME, 5) AS NAME_SUBSTR FROM CATENTDESC;

The above query will take and substring the NAME column in the CATENTDESC table starting with the 5th character, and returning a resulting substring the length of which is defined by the remaining length of the column or source string.

To limit and define the length of the substring returned we can add in the length parameter to the SUBSTR function.

SELECT SUBSTR(NAME, 5, 15) AS NAME_SUBSTR FROM CATENTDESC;

The above query will take and substring the NAME column in the CATENTDESC table starting with the 5th character, and returning a resulting substring that is 15 characters in length.

NOTE: There are a few important considerations to make regarding the DB2 SUBSTR function:

  1. The start location must be an integer between 1 and the length or maximum length of the string, depending upon whether the string is fixed or varying-length. If the start location is out of range it will return a SQLSTATE 22011.
  2. If a length is explicitly specified, the source string is effectively padded on the right with the necessary number of blank characters so that the specified substring of string always exists.

August 18, 2014
by daharveyjr
0 comments

cat_db2

DB2 COALESCE Function

The DB2 COALESCE function returns the value of the first non-NULL expression. The COALESCE function takes a comma separated list of arguments which are evaluated in the order in which they are specified and returns the first non-NULL value found.

The syntax for the COALESCE function is shown here:

-- NUMERIC EXAMPLE
SELECT COALESCE({field1}, {default_value}) FROM {table_name};
-- CHARACTER EXAMPLE
SELECT COALESCE({field1}, '{default_value}') FROM {table_name};

To show an example statement in use:

SELECT COALESCE(MFPARTNUMBER, PARTNUMBER) AS PART_NUM FROM CATENTRY;

The above query will first try to retrieve the MFPARTNUMBER column value from the CATENTRY table. If the MFPARTNUMBER value is NULL, then it will attempt to retrieve the PARTNUMBER column value from the CATENTRY table. If both columns are NULL, the COALESCE function will return NULL.

Now if we want to ensure we never get a NULL value to return, we can manipulate the above query as shown below to provide a default value in the instance that both MFPARTNUMBER and PARTNUMBER columns are NULL.

SELECT COALESCE(MFPARTNUMBER, PARTNUMBER, 'UNKNOWN') AS PART_NUM FROM CATENTRY;

The above query will return the value ‘UNKNOWN’ if the values in both the MFPARTNUMBER and PARTNUMBER columns are NULL.

NOTE: The COALESCE function is also supported on many other database platforms; however, this post was typed strictly to DB2.

August 11, 2014
by daharveyjr
0 comments

cat_mysql

MySQL Find and Replace (REPLACE)

Every had need to do a MySQL find and replace to quickly replace some data in your database? Fear not, MySQL has a nifty little string function, REPLACE, that allows a user to find and replace instances of a search string with a substitution string. The REPLACE function performs a case-sensitive search of a field replacing all instances of the search string found with the substitution string.

The following variables are referenced in the example REPLACE function below:

  • {table_name} : The table name to update.
  • {field_name} : The field name to update.
  • {search_string} : The search string that will be replaced when matched.
  • {substitution_string} : The substitution string that will be replace the search string when a match is found.
UPDATE {table_name} SET {field_name} = REPLACE({field_name}, '{search_string}', '{substitution_string}');

For more information on the MySQL REPLACE function, or to view the MySQL 5.0 documentation on REPLACE, please visit: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

August 8, 2014
by daharveyjr
1 Comment

cat_wordpress

Restore a WordPress Database to Localhost

Working with WordPress, I often will take a production database and restore it to my local server to verify changes and further aid in development. When doing so, I will always run WordPress under a named virtual host, with frequent production backups allowing me to mimic the production environment.

This article will discuss the steps necessary to backup and restore a production MySQL database to a local development environment, and configure it to run against a local named virtual host or URL.

NOTE: This post assumes that a localhost web server and MySQL instance has been setup, and that the web site code exists locally, and the localhost WordPress web site can be accessed.

Step 1. Backup the Production WordPress Database

Begin by backing up the production WordPress database. To backup a production MySQL database to a file from a production server, execute the following command on the command line to generate an SQL file that can be restored on your local development environment.

Replace the following variables within the command shown below, as needed for the production environment:

  • {user} : A MySQL user that with appropriate privileges for the database being backed up.
  • {database_name} : The MySQL database name being backed up.
  • {current_date} : The current date, in YYYYMMDD format. It is recommended to post-fix the database backups with the current date in the YYYYMMDD format, allowing the storage of multiple database backup files.
mysqldump -u {user} -p {database_name} > ~/{database_name}_{current_date}.sql

2. Create the WordPress Database on Localhost

The mysqldump command contained no options to drop and create the database that will restored. This was done purposefully, so if the database already exists, or when a user is created and granted all privileges, that work doesn’t need to be redone on any subsequent restore of the WordPress database.

If you find that you need to create the database on localhost, execute the following SQL statement, replacing the necessary variables:

  • {database_name} : The MySQL database name being created.
CREATE DATABASE {database_name};

3. Restore the WordPress Database on Localhost

To restore the MySQL database locally, download the MySQL backup to the local machine, and then restore it executing the command shown below, replacing the necessary variables in the command as needed for the localhost environment:

  • {user} : A MySQL user that has appropriate access to create and insert data into the database.
  • {database_name} : The MySQL database name just created.
  • {backup_filename} : The absolute path to the MySQL backup file downloaded previously.
mysql -u {user} -p {database_name} < {backup_filename}.sql

4. Create the Local MySQL User and Grant All Privileges

If web site code was downloaded directly from the production server and to avoid having to modify the WordPress configuration file to update the connection information, create the MySQL username and password used on the production server locally and assign it the privileges necessary.

Execute the following SQL statements replacing the variables as identified below to create the new MySQL user and to grant all privileges:

  • {user} : The MySQL user to be created.
  • {password} : The MySQL password for the user being created.
  • {database_name} : The MySQL database name the user is being granted privileges to.
CREATE USER '{user}'@'localhost' IDENTIFIED BY '{password}';

GRANT ALL PRIVILEGES ON {database_name}.* TO '{user}'@'localhost' WITH GRANT OPTION;

5. Modify the WordPress home and siteurl Options

Once the backup has been restored, execute the following SQL statement against the WordPress database to ensure that the URL values recorded in the wp_options table point to a named virtual host or localhost URL, replacing the variables as identified below within the SQL statement shown:

  • {localhost_url} : The local named virtual host or localhost URL used to access the restored WordPress web site.
UPDATE wp_options SET option_value = 'http://{localhost_url}/' WHERE option_name IN ('home', 'siteurl');

6. Verify Restored Database

Verify the restored database by accessing the WordPress web site at the localhost URL specified in the wp_options. Browse around the various pages and posts, and verify that a successful login can be made, and that a redirect is not issued to the production web site, this will confirm the wp_options were successfully set, and the localhost environment is now properly restored with the WordPress database.