MySQL bind-address

While I try to keep things simple, sometimes eliminating options and explanations comes back to haunt me. After posting how to open a Fedora firewall port for a LAMP stack, somebody got trapped by my instructions for installing MySQL on Fedora. They got stuck because they had the following setting in their my.cnf file:

bind-address=localhost.localdomain

I’d suggested using that bind-address value for a DHCP VMware Fedora installation in Step #7. I was trying (more...)

Open Fedora Port 80

After installing the LAMP stack on Fedora, you need to open port 80 in the Firewall to access the PHP programs on the Fedora instance from external servers. You can open a firewall port by launching the firewall-config application as the root user with the following syntax:

firewall-config

The firewall-config utility opens the following dialog message:

FedoraFirewall1

Click on the Ports tab, and you’ll see the following:

FedoraFirewall2

Click on Add button to add a port (more...)

Fedora Install LAMP

My students wanted an extra credit assignment, so I thought a LAMP configuration and test would be appropriate. The only problem was I hadn’t added it to their course VMware instance. So, here are the instructions to install Apache2, PHP, and MySQLi for a complete LAMP stack.

The post builds on my Fedora Install of MySQL and MySQL Workbench on Fedora posts from last year. It also presumes that you’ve installed a studentdb database but (more...)

Lowercase Table Names

A student posed the question about why table names are case sensitive. That’s because case insensitive table names are the default installation, as qualified in the MySQL documentation. You can verify that with the following query:

SELECT CASE
         WHEN @@lower_case_table_names = 1 THEN
           'Case insensitive tables'
         ELSE
           'Case sensitive tables.'
         END AS "Table Name Status";

The default value returned is:

+------------------------+
| Table Name Status      |
+------------------------+
| Case sensitive tables. |
+------------------------+
 (more...)

PostgreSQL Types

PostgreSQL like Oracle supports record data types but unlike Oracle, PostgreSQL doesn’t support collections of record data types. Here’s an example of how to define a PostgreSQL composite data type, and how to use it as a column’s data type.

CREATE TYPE address_type AS
( street_address  VARCHAR
, city            VARCHAR
, state           VARCHAR
, zip_code        VARCHAR );

Then, you define an ADDRESS table, like:

CREATE TABLE address
( address_id      SERIAL
, address_struct  ADDRESS_TYPE ) (more...)

PostgreSQL Auto IDs

PostgreSQL’s approach to automatic numbering, is simpler than Oracle, MySQL, and Microsoft SQL Server. For example, you have a two-step process with Oracle, MySQL, and Microsoft SQL Server. First, you create an Oracle table with the GENERATED AS IDENTITY clause, a MySQL table with the AUTO_INCREMENT clause, and a Microsoft SQL Server table with the IDENTITY(1,1) clause. Then, you need to write an INSERT statement like:

  1. Oracle’s INSERT statement excludes the auto-incrementing column from the (more...)

i-Blason Prime

I replaced my OtterBox Defender with an i-Blazon Prime case when upgrading to the iPhone 6. It appeared a good option, and for 4 months it was fine. Yesterday, the latch broke and my iPhone fell to the ground as a walked. Fortunately, my iPhone landed flat, the i-Blazon plastic case absorbed the impact, and my iPhone was undamaged.

Here’s a photograph of the damaged case:

i-BlasonAnnotated

While I wanted to order only the belt portion (more...)

SQL Server XQuery

I promised my students an example of writing xquery statements in Microsoft SQL Server. This post builds on two earlier posts. The first qualifies how to build a marvel table with source data, and the second qualifies how you can create an XML Schema Collection and insert relational data into an XML structure.

You can query a sequence with xquery as follows:

DECLARE @x xml;
SET @x = N'';
SELECT @x.query( (more...)

Insert into XML Column

Working through Chapter 7 of the Querying Microsoft SQL Server 2012 book for Microsoft’s Exam 70-461, I found the XML examples incomplete for my students. I decided to put together a post on how to create:

  • An XML Schema Collection type.
  • A table that uses an XML Schema Collection as a column’s data type.
  • An example on how you can transfer the contents of a table into the XML Schema Collection.

This post assumes you (more...)

Functions bar NDS

My students asked if you could embed an OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in (more...)