MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL’ Category

Signal from a procedure

without comments

As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:

  IF add_numbers(1,3) > 3 THEN
    ...
  ELSE
    ...
  END IF;

You can’t call procedures inside an IF statement, but you can call the procedure and use a single OUT-mode (pass-by-reference) parameter from the procedure in a subsequent IF statement. You can implement a a wait procedure like that with the following example.

The example first creates two tables, the road_runner and coyote tables:

-- Drop road_runner table if exists.
DROP TABLE IF EXISTS road_runner;
 
-- Create roadrunner table.
CREATE TABLE road_runner
( road_runner_id    int unsigned  auto_increment primary key
, road_runner_text  varchar(20)
, CONSTRAINT road_runner_nk UNIQUE (road_runner_text));
 
-- Drop coyote table if exists.
DROP TABLE IF EXISTS coyote;
 
-- Create coyote table.
CREATE TABLE coyote
( coyote_id       int unsigned  auto_increment primary key
, coyote_text     varchar(20)
, road_runner_id  int unsigned
, CONSTRAINT coyote_nk UNIQUE (coyote_text));

The following creates a procedure that:

  • Writes data to two tables when the values are unique, returning a value of zero when it works.
  • Writes data to neither table when the values to either table are non-unique, returning a value of one when it fails.

The procedure uses a 0 as a false value and a 1 as a true value. The use of a 0 and 1 for truth is a consistent approach for languages where they don’t support a Boolean data type.

-- Change the delimiter to a "$$"
DELIMITER $$
 
-- Drop the paired procedure.
DROP PROCEDURE IF EXISTS two_table$$
 
-- Create the paired procedure.
CREATE PROCEDURE two_table
(IN    pv_road_runner_text  varchar(20)
,IN    pv_coyote_text       varchar(20)
,  OUT pv_confirm_it        int)
BEGIN
  /* Declare a variable to hold a sequence value for an 
     auto incrementing value. */
  DECLARE lv_road_runner_id  int unsigned;
 
  /* Declare a condition variable for attempting to write
     a non-unique record to a table. */
  DECLARE duplicate CONDITION FOR 1062;
 
  /* Declare an event handler for a duplicate condition 
     variable, rollback transaction, and set 1 as a false
     condition. */
  DECLARE EXIT HANDLER FOR duplicate
    BEGIN
      ROLLBACK to all_or_none;
      SET pv_confirm_it = 1;
    END;
 
  /* Start the transaction. */
  START TRANSACTION;
 
  /* Set the save point for a multiple table transaction. */
  SAVEPOINT all_or_none;
 
  /* Insert into road runner table. */
  INSERT INTO road_runner
  (road_runner_text)
  VALUES  
  (pv_road_runner_text);
 
  /* Capture the auto incrementing sequence value as a 
     local variable. */
  SET lv_road_runner_id := last_insert_id();
 
  /* Insert into the coyote table. */
  INSERT INTO coyote
  (coyote_text
  ,road_runner_id)
  VALUES  
  (pv_coyote_text
  ,lv_road_runner_id);
 
  /* Commit the record. */
  COMMIT;
 
  /* Set the control variable to a true value. */
  SET pv_confirm_it := 0;
 
END;
$$
 
DELIMITER ;

You can test the pass-by-reference procedure with the following code:

-- Set a control variable with a null value.
SET @sv_control := null;
 
-- Query the results from a join between the road_runner and coyote tables.
SELECT   *
FROM     road_runner r INNER JOIN coyote c
ON       r.road_runner_id = c.road_runner_id;
 
-- Call the two_table procedure with unique results.
CALL two_table('Road Runner 1','Coyote 1',@sv_control);
 
-- Query the control variable result from the second call to the
-- two_table procedure.
SELECT @sv_control AS "1st Insert";
 
-- Reset the value for a second test.
SET @sv_control := 0;
 
-- Call teh two_table procedure with unique results.  
CALL two_table('Road Runner 2','Coyote 1',@sv_control);  
 
-- Query the results from a join between the road_runner and coyote tables.  
SELECT   *
FROM     road_runner r INNER JOIN coyote c
ON       r.road_runner_id = c.road_runner_id;
 
-- Query the control variable result from the second call to the
-- two_table procedure.
SELECT   @sv_control AS "2nd Insert";

I hope this helps anybody trying to implement a pass-by-reference procedure with a control variable. You can find other examples in Chapter 14 of Oracle Database 11g and MySQL 5.6 Developer Handbook (pp. 446-449 and 450-451).

Written by maclochlainn

February 16th, 2014 at 5:22 pm

Cleanup a MySQL Schema

with 7 comments

My students required way to remove all their tables, views, and constraints from a MySQL database (or the alias schema). Since they’re using referential or foreign key constraints, I also wrote one procedure to drop all foreign key constraints from a database. There’s also another to drop views. The final stored procedure calls the procedure that drops foreign keys, then calls the procedure to drop views before dropping all the tables.

Here’s the dropForeignKeys stored procedure, but if you want to capture ALTER statements that add these back later please check my follow-up Capture MySQL Foreign Keys post.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Provide a log file debugging statement.
SELECT 'DROP PROCEDURE IF EXISTS dropForeignKeys';
 
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropForeignKeys;
 
-- Provide a log file debugging statement.
SELECT 'CREATE PROCEDURE dropForeignKeys';
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropForeignKeys
( pv_database          VARCHAR(64)
, pv_referenced_table  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name       VARCHAR(64);
  DECLARE lv_constraint_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE foreign_key_cursor CURSOR FOR
    SELECT   rc.table_name
    ,        rc.constraint_name
    FROM     information_schema.referential_constraints rc
    WHERE    constraint_schema = IFNULL(pv_database,database())
    AND      referenced_table_name = pv_referenced_table
    ORDER BY rc.table_name
    ,        rc.constraint_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN foreign_key_cursor;
  cursor_foreign_key: LOOP
 
    FETCH foreign_key_cursor
    INTO  lv_table_name
    ,     lv_constraint_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_foreign_key; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('ALTER TABLE',' ',lv_table_name,' ','DROP FOREIGN KEY',' ',lv_constraint_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_foreign_key;
  CLOSE foreign_key_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

Here’s the dropViews stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- Provide a log file debugging statement.
SELECT 'DROP PROCEDURE IF EXISTS dropViews';
 
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropViews;
 
-- Provide a log file debugging statement.
SELECT 'CREATE PROCEDURE dropViews';
 
-- Change delimiter to create procedure.
DELIMITER $$
 
-- Create procedure.
CREATE PROCEDURE dropViews
( pv_database  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_view_name       VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE view_cursor CURSOR FOR
    SELECT   v.table_name
    FROM     information_schema.views v
    WHERE    table_schema = IFNULL(pv_database, database())
    ORDER BY v.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Open a local cursor. */  
  OPEN view_cursor;
  cursor_view: LOOP
 
    FETCH view_cursor
    INTO  lv_view_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_view; END IF;
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('DROP VIEW',' ',lv_view_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_view;
  CLOSE view_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

Here’s the dropTables stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CREATE PROCEDURE dropTables
( pv_database  VARCHAR(64))
BEGIN
 
  /* Declare local statement variables. */
  DECLARE lv_stmt  VARCHAR(1024);
 
  /* Declare local cursor variables. */
  DECLARE lv_table_name  VARCHAR(64);
 
  /* Declare control variable for handler. */
  DECLARE fetched       INT DEFAULT 0;
 
  /* Declare local cursor. */
  DECLARE table_cursor CURSOR FOR
    SELECT   t.table_name
    FROM     information_schema.tables t
    WHERE    table_schema = IFNULL(pv_database, database())
    ORDER BY t.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Drop the views. */
  CALL dropViews(null);
 
  /* Open a local cursor. */  
  OPEN table_cursor;
  cursor_table: LOOP
 
    FETCH table_cursor
    INTO  lv_table_name;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_table; END IF;
 
    /* Drop the tables. */
    CALL dropForeignKeys(null,lv_table_name);
 
    /* Set a SQL statement by using concatenation. */
    SET @SQL := CONCAT('DROP TABLE',' ',lv_table_name);
 
    /* Prepare, run, and deallocate statement. */
    PREPARE lv_stmt FROM @SQL;
    EXECUTE lv_stmt;
    DEALLOCATE PREPARE lv_stmt;
 
  END LOOP cursor_table;
  CLOSE table_cursor;  
 
END;
$$
 
-- Reset delimiter to run SQL statements.
DELIMITER ;

You put these in a rerunnable script, run it, and then call the dropTables stored procedure. You can pass a database (or schema) name or a null value. When you pass a null value, it uses the current database, like:

CALL dropTables(null);

As always, I hope this helps others.

Written by maclochlainn

February 7th, 2014 at 1:38 am

Mac Mini to the rescue

with 7 comments

In teaching, I had a problem because my students have different base operating systems, like Windows 7, Windows 8, Linux, and Mac OS X. I needed a teaching and lecture platform that would let me teach it all (not to mention support their environments). That meant it had to virtualize any of the following with a portable device:MacMiniConsole

  • Windows 7 or 8 hosting natively an Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
  • Windows 7 or 8 hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
  • Mac OS X hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6
  • Ubuntu hosting a Fedora or Oracle Unbreakable Linux VM (3 or 4 GB) with Oracle Database 11g XE, 11g, or 12c and MySQL Database 5.6

I never considered a manufacturer other than Apple for a laptop since they adopted the Intel chip. Too many of the others sell non-hyperthreaded laptop machines that they market as i5 or i7 64-bit OS machines when they’re not. Some of those vendors disable the hyperthreading facility while others provide motherboards that can’t support hyperthreading. The ones I dislike the most provide a BIOS setting that gives the impression you can enable hyperthreading when you can’t. All Apple devices, MacBook, MacBook Pro, Mac Mini, and Mac Pro do fully support a 64-bit OS and their virtualization.

A MacBook Pro came to mind but the disk space requirements were 1 TB, and that’s too pricey. I went with the Mac Mini because with 16 GB of memory and a 1 TB drive it was only $1,200. Add a wireless keyboard and mighty mouse, and an HDMI and mini-DVI connections, and I had my solution. Naturally, my desktop is a one generation old Mac Pro with 64 GB of memory and 12 TB of disk space, which supports all the virtual machines used for testing. Note to Apple marketing staff: The prior version of the Mac Pro let you pay reasonable (3rd party) prices for the additional memory and disk drives.

The Mac Mini means I can travel anywhere and plug into the console and demo tools and techniques from a myriad set of platforms without the hassle of moving on and off to frequently VM images. It’s a great solution with only one downside, HDMI to DVI sometimes creates purple toned screens. It’s unfortunate because some venues have monitors that don’t support HDMI).

Written by maclochlainn

February 6th, 2014 at 12:17 pm

Open a port on Fedora

with one comment

Since MySQL Workbench 6.0 isn’t available for Fedora, Version 20, I’m having my students install it on their local Windows and Mac OS X operating systems. You can configure the /etc/sysconfig/iptables file to enable port 3306 after installing MySQL on Fedora.

You can open a port by using the firewall-config utility (easy way) or by adding the following line to the /etc/sysconfig/iptables file (Fedora’s instructions on editing iptables [hard way]). The file won’t exist initially, but you can create it by running the following command as the root superuser or sudoer:

shell> service iptables save

You you can run the following commands as the root superuser, which saves the line in the iptables file:

shell> iptables -A INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 3306 -j ACCEPT
shell> iptables-save

After making the change to the /etc/iptables file you can change the firewall by running the following command as the root superuser:

shell> service iptables restart

Just make sure you don’t inadvertently start both iptables and ip6tables as services. You can check that only one is running by using the following commands:

shell> service iptables status
shell> service ip6tables status

MySQL Workbench Configuration

MySQL Workbench Home Page

  1. The first thing you need to do is click on the + symbol in the circle to the right of the MySQL Connections text label. It launches the Setup New Connection dialog.

Setup New Connection

  1. The second thing you need to do is enter a Connection Name, Hostname, Port, and Username. Then, click the Test Connection button.

Connect to MySQL Server

  1. The Test Connection button launches the Connect to MySQL Server dialog. Enter the password for the student user (or whatever user you’re interested in), and then click the OK button.

Connection Parameter Acknowledgment

  1. When the credentials in the Connect to MySQL Server dialog work, you see the following confirmation dialog message. Click the OK button to continue and you’ll see a new VMWare Fedora Instance connection icon.

MySQL Workbench Home Page

  1. Click the VMWare Fedora Instance connection to start a new connection.

Connect to MySQL Server

  1. The VMWare Fedora Instance button launches the Connect to MySQL Server dialog. Like you did when configuring the connection, enter the password for the student user (or whatever user you’re interested in), and then click the OK button. It launches an interactive panel that lets you run, edit, or save the SQL script file.

MySQL Workbench

  1. Type the following two lines in the Query1 panel (at least if you have a studentdb database:

    USE studentdb;
    SELECT DATABASE();

Written by maclochlainn

January 18th, 2014 at 11:51 pm

Fedora Install of MySQL

with 16 comments

I built a new image on VMWare Fusion for my class, which required installing MySQL 5.6 on Fedora, Version 20. If you don’t know how to add your user to the sudoers list, you should check this older and recently updated blog post.

  1. Download the MySQL Yum Repository and launch the downloaded RPM.
  1. Install MySQL on Fedora, Version 20, which you can find with the following command:
shell> rpm -qa | grep mysql
mysql-community-release-fc20-5.noarch

The fc20-5 changes with point releases, but assuming that you’re installing the fc20-5 release:

shell> sudo yum localinstall mysql-community-release-fc20-5.noarch.rpm
  1. Install MySQL on Fedora with the following command:
shell> sudo yum install mysql-server
  1. Start the MySQL service on Fedora with the following command:
shell> sudo service mysqld start
  1. Secure the MySQL installation with the following command:
shell> mysql_secure_installation
  1. Set the MySQL Service to start with the Fedora operating system with the following command (not chkconfig):
shell> sudo systemctl enable mysqld.service

It sets the following two links:

ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/mysql.service'
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/multi-user.target.wants/mysqld.service'

Restart the Fedora operating system to effect the changes.

  1. Reset the MySQL configuration file to enable external connections through Port 3306 with the following changes to the my:

Remark out the socket line, like this:

#socket=/var/lib/mysql/mysql.sock

Add the bind-address and port lines below after you know the actual IP address of the server to the my.cnf file in the /etc directory.

You substitute the actual IP address for the nnn.nnn.nnn.nnn on the bind_address line with the actual IP address returned by the ifconfig command, like this:

shell> ifconfig

Then, add these two lines to the my.cnf file.

bind-address=nnn.nnn.nnn.nnn
port=3306

It’s actually easier to use localhost.localdomain than an IP address when you use DHCP, like:

bind-address=localhost.localdomain
port=3306

If you plan to connect from a host system, like Windows or Mac OS X, to a virtual Linux environment using DHCP, change localhost.localdomain to 0.0.0.0:

bind-address=0.0.0.0
port=3306
  1. Restart the mysqld service with the following syntax:
shell> sudo service mysqld restart

You can check whether MySQL is listening on Port 3306 with this syntax:

shell> sudo netstat โ€“anp | grep 3306

It displays:

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1311/mysqld

Go to this page if you want to install MySQL Workbench.

Written by maclochlainn

January 7th, 2014 at 11:04 pm

Posted in Fedora,Linux,MySQL,VMWare

Tagged with , ,

Excel PowerPivot & DAX

without comments

I’ve worked with every release of Microsoft Excel, and I know it takes effort to keep up to date with certain releases. Clearly, the Data Analysis eXpression (DAX) Language introduced in Excel 2010 went unnoticed by many, which was sad. DAX is truly a powerful extension to the analytical and modeling approaches in Microsoft Excel.

GoldenGateBridge2013MediumI’d like to recommend Microsoft Excel 2013 Building Data Models with PowerPivot to those who haven’t learned how to use DAX in Excel 2010, 2011, or 2013. DAX works with tables but if you don’t use tables, I guess you can skip DAX because you must have infinite time to produce marginal analytical outcomes (tongue in cheek humor). However, if you’re like most folks, you want a book to get you up-to-speed quickly, and that’s what this book will do for you.

Just one caveat if you’re using an Oracle or MySQL database, use the prepackaged analytic functions before you download the data set. You should always pre-select data before applying analytics in Excel. Remember the more refined the data model you start with the easier it is to structure analytical tools to leverage the data model. While DAX is powerful, it doesn’t replace the speed and query optimized behaviors of effective Oracle or MySQL queries.

MySQL Synonym?

with one comment

Somebody asked how to create a SYNONYM in MySQL, which is interesting because MySQL doesn’t support synonyms. I thought the prior entry explained how to do it, but here’s a new post. However, you can create a view in one database that relies on a table in another database.

The following SQL statements create two databases and grant appropriate privileges to the student as the root superuser:

/* Create two databases. */
CREATE DATABASE seussdb;
CREATE DATABASE appdb;
 
/* Grant privileges to a student user. */
GRANT ALL ON seussdb.* TO student;
GRANT ALL ON appdb.* TO student;

Log out from the root superuser and reconnect as the student user. Then, the following code connects to the seuss database and create a hat table; and inserts two rows into the hat table:

/* Use the appdb database. */
USE seussdb;
 
/* Create a hat table. */
CREATE TABLE hat
( hat_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, hat_text  VARCHAR(20));
 
/* Insert two rows into the hat table. */
INSERT INTO hat (hat_text) VALUES ('Thing 1');
INSERT INTO hat (hat_text) VALUES ('Thing 2');

The following code connects to the application database and creates a hat view; and then the code inserts one additional row into the hat table:

/* Connect to the application database. */
USE appdb;
 
/* Create a hat view. */
CREATE VIEW hat AS
  SELECT * FROM seussdb.hat;
 
/* Query the contents of the view, or seuss.hat table. */
SELECT * FROM hat;
 
/* Insert a new row into the hat table. */
INSERT INTO hat (hat_text) VALUES ('Thing 3');
 
/* Query the contents of the view, after insert to the view. */
SELECT * FROM hat;

The results will be the following:

+--------+----------+
| hat_id | hat_text |
+--------+----------+
|      1 | Thing 1  |
|      2 | Thing 2  |
|      3 | Thing 3  |
+--------+----------+

I hope this answers the question on how to mimic the Oracle database’s synonyms. The appdb.hat view acts as a synonym to the seuss.hat table.

Written by maclochlainn

November 24th, 2013 at 11:10 pm

Posted in MySQL,Oracle,sql

Tagged with ,

Site Blocked in Russia?

with 11 comments

This is either too funny :-) or too sad. :-(RussianBlock

An acquaintance sent me this image from a cyber cafe or hotel in Russia. It says that my blog site is prohibited and violates Russian law, and that they’re blocking my site in accordance with the Russian Federal Law of 27.07.2006 No. 149-FZ.

All I can say is, “Wow!” I didn’t know that stuff about writing programs, web pages, and solving generic database and operating system problems was so sensitive. For that matter, I didn’t know what I post would interest any government. I half wonder whether my friend’s pulling my leg.

As I reflect on it, could it be that Oracle post on how to write an encrypted object type? or, how with proper OS credentials how you reset MySQL’s root password? or, how to verify a socket in Perl? Nah, it’s probably the one on how to set a static IP in Windows – that’s truly sensitive stuff. ๐Ÿ˜‰

Does anybody who reads the blog have any clue about what would drive the Russian government to block a technology blog site like mine? My serious guess is that there is somebody who hosts with the same provider who’s done something tacky.

Written by maclochlainn

July 14th, 2013 at 8:13 pm

SQL Like Comparisons

with 4 comments

SQL tidbits are always valuable and highly searched for by newbies (as opposed to reading the SQL documentation). Sometimes we seasoned SQL developers take for granted little things like when a single- or multiple-character wildcard comparison works. It seems we know what newbies don’t. That you need a wildcard comparison operator not simply and equality comparison operator.

The question posed to me was, “Why doesn’t my wildcard comparison work?” Here’s a simplified example of their question.

SELECT 'Valid' AS "Test"
FROM    dual
WHERE  'Treat' = 'Tre_t'
OR     'Treet' = 'Tre_t';

Naturally, the answer is that the equality operator compares the strings based on their exact match (character sensitively in Oracle and character insensitively in MySQL). It needs to be rewritten by replacing the equals (=) comparison operator with the LIKE comparison operator. The following query does that:

SELECT 'Valid' AS "Test"
FROM    dual
WHERE  'Treat' LIKE 'Tre_t'
OR     'Treet' LIKE 'Tre_t'

The same behavior exists for the multiple-character wildcard (%). I hope this helps those looking for this answer.

Written by maclochlainn

July 13th, 2013 at 1:12 pm

Oracle OpenWorld 2013

without comments

I registered yesterday for Oracle OpenWorld 2013, and I’ll look forward to seeing friends there. Having worked in the Oracle 12c beta for a year, I’ll be interested in the presentations. Also, hearing more about Java 7 at JavaOne. On the downside, I’m missing MySQL Connect this year.

Cloud computing offers many possibilities, and container and pluggable databases are a great solution. We’ve two new acronyms with the Oracle 12c release. A containerized database is a CDB, and a pluggable database is a PDB. I’m looking forward to seeing more about the provisioning of PDBs during the conference. If you’re new to the changes, check out CDBs and PDBs in Chapter 17 in the Oracle 12c Concepts manual.

A couple of my favorite new features are Identity and Invisible Columns. If you’re unfamiliar with the new features for application development, let me recommend this Oracle White Paper. Also, for reference I’ve covered identity and invisible columns thoroughly in the Oracle Database 12c PL/SQL Programming book, which will be available in December.

Missing the MySQL Connect 2013 Bus

Unfortunately, travel budgets preclude me attending MySQL Connect 2013 this year (alas, I’ll miss the bus). :-( It was hard because I’d like to see what’s up with MySQL (since I was a closet MySQL user at Oracle before they acquired it). Anyway, if you’re there, make sure you check out MySQL Workbench 6 for me. Also, I’d like to thank Dave Stokes for the AWESOME review he wrote on Amazon.com for my MySQL Workbench: Data Modeling & Development book. Maybe, I’ll get to go to MySQL Connect 2014 next year.

Written by maclochlainn

July 13th, 2013 at 11:34 am