Somebody wanted to know how to write a basic PostgreSQL PL/pgSQL function that returned a full name whether or not the middle name was provided. That’s pretty simple. There are principally two ways to write that type of concatenation function. One uses formal parameter names and the other uses positional values in lieu of the formal parameter names.
Since you’ll probably test the two approaches, I’ve also provided a conditional drop statement for the full_name (more...)
Somebody wants to know how you can write a PL/SQL solution that mimics the fall through of a switch statement because PL/SQL doesn’t support a switch statement. It’s a question that I found interesting because there wasn’t a need for it when I figured out what he wanted to accomplish. Essentially, he wanted to know how to implement a nested loop where the first loop runs in ascending order and the nested loop runs in (more...)
I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that
system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a
system_user name. The alternative, would be to convert the
system_user table (more...)
First of all, Happy New Years!
IEEE Spectrum published a ranking of the most popular programming languages. Computational journalist Nick Diakopoulos wrote the article. While it may surprise some, I wasn’t surprised to find SQL in the top ten.
Nick weighted and combined 12 metrics from 10 sources (including IEEE Xplore, Google, and GitHub) to rank the most popular programming languages.
- Compiled programming languages (Java [#1], C [#2], C++ [#3], C# [#4], Objective-C [#16])
- Interpreted (more...)
Somebody ran into the following error message trying to query the
innodb_sys_foreign_cols tables from the
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
It’s easy to fix the error, except you must grant the process privilege. It’s a global privilege and it should only be granted to super users. You grant the privilege (more...)
Somebody wanted to know how to find any non-unique indexes in MySQL. The query takes a session variable with the table name and returns the non-unique indexes by column names. It uses a correlated subquery to exclude the table constraints.
You set the session variable like this:
SET @sv_table_name := 'member_lab';
You can query the indexes result with the following query:
, s. (more...)
It seems that the most popular PHP frameworks are: Laravel (1st), Phalcon (2nd), and Symphony2 (3rd). I found the following graphic (from December 2013) that highlights popularity by percentage of the market (though I can’t (more...)
I finally upgraded from VMware Fusion 6 to VMware Fusion 7 to take advantage of the new features. It was interesting to upgrade the Windows 7 virtual machine because of the unique failure message it raised. The message said it was incompatible, and that I should navigate to:
Virtual Machine -> Settings -> Compatibility -> Upgrade
The Upgrade button checks the Allow upgrading the virtual hardware for this virtual machine checkbox. You will get prompted (more...)
I demonstrated a number of SQL approaches to reading object types in Appendix B of the Oracle Database 12c PL/SQL Programming book. For example, the easiest one to construct and return the results from a
TO_STRING member function uses the
SELECT TREAT(base_t() AS base_t).to_string() AS "Text"
However, it seems that I could have provided one more. Here’s an example of how you can (more...)
If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. You can’t drop a unique index for a primary key without dropping the primary key constraint that indirectly created it.
The following query (more...)