Archive for the ‘bash’ Category
Run X11 Apps on Mac
It’s possible folks didn’t notice but Mac OS X no longer includes XQuartz by default from Maverick forward. You need to download XQuartz and install it. I’d recommend after you install Xcode.
Launch XQuartz and then either use the bash shell it opens or open a Terminal bash shell session. Inside the shell, you might start Secure Shell (ssh) like this:
Mac-Pro-3:~ michaelmclaughlin$ ssh student@192.168.2.170 student@192.168.2.170's password: Last login: Thu Jun 4 14:33:37 2015 [student@localhost ~]$ xclock & [1] 10422 [student@localhost ~]$ Error: Can't open display: |
Granted that’s a trivial error and running the xclock X11 applications isn’t crucial, an error that makes it more important is the following from Oracle’s old Designer/2000 application:
FRM-91111: Internal Error: window system startup failure. FRM-10039: Unable to start up the Form Builder. |
This is the desired behavior. Secure shell (ssh) can’t run it unless you make the connection with the -Y flag. You should use the following syntax:
Mac-Pro-3:~ michaelmclaughlin$ ssh -Y student@192.168.2.170 student@192.168.2.170's password: Last login: Tue Jun 9 14:56:55 2015 from 192.168.2.1 /usr/bin/xauth: file /home/student/.Xauthority does not exist [student@localhost ~]$ xclock & [1] 10760 |
You can safely ignore the .Xauthority does not exist warning message because it’ll create a .Xauthority file and store the magic cookie after the warning message. You should see the xclock program running in the upper left hand corner of your console, like:
It’s terrific that you don’t get a font warning like you typically would using UTF-8 on Linux. Nice that the Mac OS fonts are so well done that there isn’t a raised exception.
Using xclock or xeyes isn’t very useful as a rule, but this method also lets you run any of the Linux GUI applications. For example, the following gedit command lets you run the gedit utility from a Mac OS console. If you’ve installed the gedit plug-ins, you also can use the Terminal console on the remote system.
The process sequence for the command-line is shown below:
1030 1 /usr/sbin/sshd -D - The root process launches the ssh daemon 3145 1030 sshd: student [priv] - The sshd launches a ssh session to manage a student ssh session 3152 3145 sshd: student@pts/1 - The ssh session launched to manage the ssh session 3166 3152 -bash - The bash shell launched by connecting through the ssh session 3240 3166 gedit - The gedit command issued inside a ssh session 3166 3240 gnome-pty-helper - Launching the gedit session across X11 3169 3240 /bin/bash - Launching the Terminal session inside the gedit session across X11 3269 3884 ps -ef - Command run inside the gedit Terminal session |
Hope that helps those who want to use X11 applications on the Mac OS.
Fedora X11 Install
While working through getting my Mac OS X to work with X11, I stumbled on some interesting errors and misdirection solutions. Like most things, the solution was straightforward. Then, it struck me that I hadn’t installed it on my Fedora image. This blog post show you the errors I got the way to get it to work, and how to install X11 on Fedora.
The first step requires discovering the package. If you remember xclock or xeyes are X-Windows programs, it’s quite easy with this command (though it may take a moment or two to run):
repoquery -q -f */xclock |
It will return something like this:
xorg-x11-apps-0:7.7-7.fc20.x86_64 |
You can then install X11 as a sudoer user with the yum utility like this:
sudo yum -y install xorg-x11-apps |
It should return this to your console:
Loaded plugins: langpacks, refresh-packagekit Resolving Dependencies --> Running transaction check ---> Package xorg-x11-apps.x86_64 0:7.7-7.fc20 will be installed --> Processing Dependency: xorg-x11-xbitmaps for package: xorg-x11-apps-7.7-7.fc20.x86_64 --> Running transaction check ---> Package xorg-x11-xbitmaps.noarch 0:1.1.1-6.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: xorg-x11-apps x86_64 7.7-7.fc20 fedora 305 k Installing for dependencies: xorg-x11-xbitmaps noarch 1.1.1-6.fc20 fedora 37 k Transaction Summary ================================================================================ Install 1 Package (+1 Dependent package) Total download size: 341 k Installed size: 949 k Downloading packages: (1/2): xorg-x11-apps-7.7-7.fc20.x86_64.rpm | 305 kB 00:01 (2/2): xorg-x11-xbitmaps-1.1.1-6.fc20.noarch.rpm | 37 kB 00:00 -------------------------------------------------------------------------------- Total 252 kB/s | 341 kB 00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : xorg-x11-xbitmaps-1.1.1-6.fc20.noarch 1/2 Installing : xorg-x11-apps-7.7-7.fc20.x86_64 2/2 Verifying : xorg-x11-apps-7.7-7.fc20.x86_64 1/2 Verifying : xorg-x11-xbitmaps-1.1.1-6.fc20.noarch 2/2 Installed: xorg-x11-apps.x86_64 0:7.7-7.fc20 Dependency Installed: xorg-x11-xbitmaps.noarch 0:1.1.1-6.fc20 Complete! |
After you install the xorg-x11-apps libraries, you can launch xclock. You should use the following syntax:
xclock & |
It should display something like the following on your console:
The warning message is typically because you’re running something like en_US.UTF-8 mode. You can find suitable X11 character sets by using the following command:
sudo yum search xorg-x11-fonts |
You can install all of them with the following command:
sudo yum -y install xorg-x11-fonts* |
However, at the end of the day the warning doesn’t go way. You should just ignore it.
Hope this helps those who want to install X11 on Fedora.
Bash Arrays & Oracle
Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.
If you don’t know much about bash shell, you should start with the prior post to learn about bash arrays, if-statements, and for-loops. In this blog post I only cover how to implement a bash shell script that runs SQL scripts in silent mode and then queries the database in silent mode and writes the output to an external file.
I’ve copied the basic ERD for the example because of a request from a reader. In their opinion it makes cross referencing the two posts unnecessary.
To run the bash shell script, you’ll need the following SQL files, which you can see by clicking not he title below. There are several differences. For example, Oracle doesn’t support a DROP IF EXISTS syntax and requires you to write anonymous blocks in their PL/SQL language; and you must explicitly issue a QUIT; statement even when running in silent mode unlike MySQL, which implicitly issues an exit.
Setup SQL Files ↓
The actor.sql file:
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 | -- Drop actor table and actor_s sequence. BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('ACTOR','ACTOR_S')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS'; ELSIF i.object_type = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name; END IF; END LOOP; END; / -- Create an actor table. CREATE TABLE actor ( actor_id NUMBER CONSTRAINT actor_pk PRIMARY KEY , actor_name VARCHAR(30) NOT NULL ); -- Create an actor_s sequence. CREATE SEQUENCE actor_s; -- Insert two rows. INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Hemsworth'); INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Pine'); INSERT INTO actor VALUES (actor_s.NEXTVAL,'Chris Pratt'); -- Quit session. QUIT; |
The film.sql file:
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 | -- Drop film table and film_s sequence. BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('FILM','FILM_S')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS'; ELSIF i.object_type = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name; END IF; END LOOP; END; / -- Create a film table. CREATE TABLE film ( film_id NUMBER CONSTRAINT film_pk PRIMARY KEY , film_name VARCHAR(30) NOT NULL ); -- Create an actor_s sequence. CREATE SEQUENCE film_s; -- Insert four rows. INSERT INTO film VALUES (film_s.NEXTVAL,'Thor'); INSERT INTO film VALUES (film_s.NEXTVAL,'Thor: The Dark World'); INSERT INTO film VALUES (film_s.NEXTVAL,'Star Trek'); INSERT INTO film VALUES (film_s.NEXTVAL,'Star Trek into Darkness'); INSERT INTO film VALUES (film_s.NEXTVAL,'Guardians of the Galaxy'); -- Quit session. QUIT; |
The movie.sql file:
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 70 71 72 73 74 75 76 77 78 79 80 81 | -- Drop movie table and movie_s sequence. BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('MOVIE','MOVIE_S')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE ' || i.object_name || ' CASCADE CONSTRAINTS'; ELSIF i.object_type = 'SEQUENCE' THEN EXECUTE IMMEDIATE 'DROP SEQUENCE ' || i.object_name; END IF; END LOOP; END; / -- Create an movie table. CREATE TABLE movie ( movie_id NUMBER CONSTRAINT movie_pk PRIMARY KEY , actor_id NUMBER CONSTRAINT movie_nn1 NOT NULL , film_id NUMBER CONSTRAINT movie_nn2 NOT NULL , CONSTRAINT actor_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) , CONSTRAINT film_fk FOREIGN KEY (film_id) REFERENCES film(film_id)); -- Create table constraint. CREATE SEQUENCE movie_s; -- Insert translation rows. INSERT INTO movie VALUES ( movie_s.NEXTVAL ,(SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor')); INSERT INTO movie VALUES ( movie_s.NEXTVAL ,(SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor: The Dark World')); INSERT INTO movie VALUES ( movie_s.NEXTVAL ,(SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek')); INSERT INTO movie VALUES ( movie_s.NEXTVAL ,(SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek into Darkness')); INSERT INTO movie VALUES ( movie_s.NEXTVAL ,(SELECT actor_id FROM actor WHERE actor_name = 'Chris Pratt') ,(SELECT film_id FROM film WHERE film_name = 'Guardians of the Galaxy')); -- Quit session. QUIT; |
The tables.sql file, lets you verify the creation of the actor, film, and movie tables:
1 2 3 4 5 6 7 8 9 | -- Set Oracle column width. COL table_name FORMAT A30 HEADING "Table Name" -- Query the tables. SELECT table_name FROM user_tables; -- Exit SQL*Plus. QUIT; |
The results.sql file, lets you see join results from actor, film, and movie tables:
1 2 3 4 5 6 7 8 9 10 11 | -- Format query. COL film_actors FORMAT A40 HEADING "Actors in Films" -- Diagnostic query. SELECT a.actor_name || ', ' || f.film_name AS film_actors FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id; -- Quit the session. QUIT; |
If you don’t have a sample test schema to use to test this script, you can create a sample schema with the following create_user.sql file. The file depends on the existence of a users and temp tablespace.
Click the link below to see the source code for a script that let’s you create a sample user account as the system user:
Create sample User SQL File ↓
You can use the dbms_metadata.get_ddl function to discover the existence of the tablespaces. The following SQL syntax returns the SQL DDL statement that created a users or temp tablespace:
1 2 | SET LONG 200000 SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') FROM dual; |
You create the sample database with the following SQL statements:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- Drop the sample user table. BEGIN FOR i IN (SELECT username FROM dba_users WHERE username = 'SAMPLE') LOOP EXECUTE IMMEDIATE 'DROP USER ' || i.username || ' CASCADE'; END LOOP; END; / -- Create the sample user table. CREATE USER sample IDENTIFIED BY sample DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 50M ON users; -- Grant privileges to sample user. GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO sample; |
The following list_oracle.sh shell script expects to receive the username, password, and fully qualified path in that specific order. The script names are entered manually in the array because this should be a unit test script.
This is an insecure version of the list_oracle.sh script because you provide the password on the command line. It’s better to provide the password as you run the script.
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 | #!/usr/bin/bash # Assign user and password username="${1}" password="${2}" directory="${3}" echo "User name:" ${username} echo "Password: " ${password} echo "Directory:" ${directory} # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do sqlplus -s ${username}/${password} @${directory}/${i} > /dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. sqlplus -s ${username}/${password} @${directory}/tables.sql 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read actor_name; do echo $actor_name done # Connect and pipe the query result minus errors and warnings to the while loop. sqlplus -s ${username}/${password} @${directory}/result.sql 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read actor_name; do echo $actor_name done |
The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 29 and 37 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.
You can run the shell script with the following syntax:
./list_oracle.sh sample sample /home/student/Code/bash/oracle > output.txt |
You can then display the results from the output.txt file with the following command:
cat output.txt command: |
It will display the following output:
User name: sample Password: sample Directory: /home/student/Code/bash/oracle Table Name ------------------------------ MOVIE FILM ACTOR Actors in Films ---------------------------------------- Chris Hemsworth, Thor Chris Hemsworth, Thor: The Dark World Chris Pine, Star Trek Chris Pine, Star Trek into Darkness Chris Pratt, Guardians of the Galaxy |
As always, I hope this helps those looking for a solution.
Bash Arrays & MySQL
Student questions are always interesting! They get me to think and to write. The question this time is: “How do I write a Bash Shell script to process multiple MySQL script files?” This post builds the following model (courtesy of MySQL Workbench) by using a bash shell script and MySQL script files, but there’s a disclaimer on this post. It shows both insecure and secure approaches and you should avoid the insecure ones.
It seems a quick refresher on how to use arrays in bash shell may be helpful. While it’s essential in a Linux environment, it’s seems not everyone masters the bash shell.
Especially, since I checked my Learning the Bash Shell (2nd Edition) and found a typo on how you handle arrays in the bash shell, and it’s a mistake that could hang newbies up (on page 161). Perhaps I should update my copy because I bought it in 1998. 😉 It was good then, and the new edition is probably better. The error is probably corrected in the current Learning the Bash Shell, but if not, the following examples show you how to use arrays in loops.
Naturally, these do presume some knowledge of working with bash shell, like the first line always is the same in any bash shell script. That you open an if-statement with an if and close it with a fi, and that you else-if is elif; and that a semicolon between a for-statement and the do statement is required when they’re on the same line because they’re two statements.
If you’re new to bash shell arrays, click on the link below to expand a brief tutorial. It takes you through three progressive examples of working with bash arrays.
Working with bash Arrays ↓
A basic example of working with an array in bash shell is the following list1.sh script:
1 2 3 4 5 6 7 8 9 10 11 12 13 | #!/usr/bin/bash # Print script name. echo $0 #!/usr/bin/bash # Define an array. declare -a cmd=("one" "two" "three") # Call the array elements. for i in ${cmd[*]}; do echo ${i} done |
Line 8 declares the cmd array by assigning three strings. Line 12 returns the elements of the array to the ${i} variable, which lets you manage them one at a time. You use the chmod command to make the list1.sh executable, like this:
chmod 755 list1.sh |
Then, you can run it like this from the present working directory (pwd):
./list1.sh |
It should print:
one two three |
The list2.sh example changes the cmd array declaration from list1.sh. It declares the cmd array as an empty array, and then it assigns elements by index numbers (using a zero-based index), as shown below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #!/usr/bin/bash # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="one" cmd[1]="two" cmd[2]="three" # Call the array elements. for i in ${cmd[*]}; do echo ${i} done |
Lines 7 through 9 assign values to the elements of the cmd array. You would chmod the file, and run the file as qualified above for the list1.sh script.
The last pre-implementation example requires that you create three demonstration scripts, the one.sh, two.sh, and three.sh scripts. You should put them in the same directory as the list3.sh script.
The demonstration scripts should all have the same code, like this:
1 2 3 4 | #!/usr/bin/bash # Print script name. echo $0 |
Line 4 returns command line parameter $0 or ${0}, which is always the command line program’s file name. The file name may be provided as a relative or absolute file name, and if that’s new to you please check out The Linux Command Line: A Complete Introduction (also downloadable as a PDF for free).
The list3.sh script should contain the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #!/usr/bin/bash <code> # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="one.sh" cmd[1]="two.sh" cmd[2]="three.sh" # Call the array elements. for i in ${cmd[*]}; do `pwd`/${i} done |
When you run the list3.sh script from the /home/student/Code/bash directory with a local syntax, the script should return the fully qualified file names of the subshell programs. The output should look like this:
/home/student/Code/bash/one.sh /home/student/Code/bash/two.sh /home/student/Code/bash/three.sh |
The list3.sh script provides the present working directory (pwd) and the one.sh, two.sh, and three.sh scripts return only their executable name. For example, if you ran one.sh with the following syntax:
./one.sh |
It returns
./one.sh |
Only one more trick needs to be qualified before our main MySQL examples. That trick is how you pass parameters to a bash shell script. For reference, this is the part that’s insecure because user command histories are available inside the Linux OS.
Here’s a hello_whom.sh script to demonstrates the concept of parameter passing:
1 2 3 4 5 6 7 8 9 10 | #!/usr/bin/bash # This says hello to the argument while managing no argument. if [[ ${#} = 1 ]]; then echo 'The '${0}' program says: "Hello '${1}'!"' elif [[ ${#} > 1 ]]; then echo 'The '${0}' program wants to know if you have more than one name?' else echo 'The '${0}' program wants to know if you have a name?' fi |
If you need more on how parameters are passed and managed, you can check a prior blob post on Handling bash Parameters, or check the bash help pages. The following leverages bash arrays to run scripts and query the MySQL database from the command line.
You will need the three batch SQL files first, so here they are:
Setup SQL Files ↓
The actor.sql file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop an actor table. DROP TABLE IF EXISTS actor; -- Create an actor table. CREATE TABLE actor ( actor_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , actor_name VARCHAR(30) NOT NULL ); -- Insert two rows. INSERT INTO actor (actor_name) VALUES ('Chris Hemsworth'); INSERT INTO actor (actor_name) VALUES ('Chris Pine'); INSERT INTO actor (actor_name) VALUES ('Chris Pratt'); |
The film.sql file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop a film table. DROP TABLE IF EXISTS film; -- Create a film table. CREATE TABLE film ( film_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , film_name VARCHAR(30) NOT NULL ); -- Insert rows. INSERT INTO film (film_name) VALUES ('Thor'); INSERT INTO film (film_name) VALUES ('Thor: The Dark World'); INSERT INTO film (film_name) VALUES ('Star Trek'); INSERT INTO film (film_name) VALUES ('Star Trek into Darkness'); INSERT INTO film (film_name) VALUES ('Guardians of the Galaxy'); |
The movie.sql file:
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 70 71 72 73 74 75 76 77 78 | -- Use the sampledb database. USE sampledb; -- Disable foreign key checking. SET foreign_key_checks = 0; -- Drop an movie table. DROP TABLE IF EXISTS movie; -- Create an movie table. CREATE TABLE movie ( movie_id int unsigned PRIMARY KEY AUTO_INCREMENT , actor_id int unsigned NOT NULL , film_id int unsigned NOT NULL , CONSTRAINT actor_fk FOREIGN KEY (actor_id) REFERENCES actor (actor_id) , CONSTRAINT film_fk FOREIGN KEY (film_id) REFERENCES film(film_id)); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Hemsworth') ,(SELECT film_id FROM film WHERE film_name = 'Thor: The Dark World')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pine') ,(SELECT film_id FROM film WHERE film_name = 'Star Trek into Darkness')); -- Use scalar subqueries to discover surrogate keys by using the faux natural key. INSERT INTO movie ( actor_id , film_id ) VALUES ((SELECT actor_id FROM actor WHERE actor_name = 'Chris Pratt') ,(SELECT film_id FROM film WHERE film_name = 'Guardians of the Galaxy')); |
The following list_mysql.sh shell script expects to receive the username, password, database and fully qualified path in that specific order. The script names are entered manually because this should be a unit test script. Naturally, you can extend the script to manage those parameters but as mentioned I see this type of solution as a developer machine only script to simplify unit testing. Anything beyond that is risky!
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 | #!/usr/bin/bash # Assign user and password username="${1}" password="${2}" database="${3}" directory="${4}" # List the parameter values passed. echo "Username: " ${username} echo "Password: " ${password} echo "Database: " ${database} echo "Directory: " ${directory} echo "" # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do mysql -s -u${username} -p${password} -D${database} < ${directory}/${i} > /dev/null 2>/dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p${password} -D${database} <<<'show tables' 2>/dev/null | # Read through the piped result until it's empty but format the title. while IFS='\n' read list; do if [[ ${list} = "Tables_in_sampledb" ]]; then echo $list echo "----------------------------------------" else echo $list fi done echo "" # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p${password} -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null | # Read through the piped result until it's empty but format the title. while IFS='\n' read actor_name; do if [[ ${actor_name} = "Actors in Films" ]]; then echo $actor_name echo "----------------------------------------" else echo $actor_name fi done |
The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 33 and 47 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.
You can run this script with the following input parameters from the local directory where you deploy it. The a parameters are: (1) username, (2) password, (3) database, and (4) a fully qualified path to the SQL setup files.
./list_mysql.sh student student sampledb "/home/student/Code/bash/mysql" |
With valid input values, the list_mysql.sh bash script generates the following output, which confirms inputs and verifies actions taken by the scripts with queries:
Username: student Password: student Database: sampledb Directory: /home/student/Code/bash/mysql Tables_in_sampledb ---------------------------------------- actor film movie Actors in Films ---------------------------------------- Chris Hemsworth in Thor Chris Hemsworth in Thor: The Dark World Chris Pine in Star Trek Chris Pine in Star Trek into Darkness Chris Pine in Guardians of the Galaxy |
If you forgot to provide the required inputs to the list_mysql.sh bash script, it alternatively returns the following output:
Username: Password: Database: Directory: ./list_mysql.sh: line 25: /actor.sql: No such file or directory ./list_mysql.sh: line 25: /film.sql: No such file or directory ./list_mysql.sh: line 25: /movie.sql: No such file or directory |
The secure way removes the password at a minimum! The refactored program will require you to manually enter the password for all elements of the array (three in this sample), and twice for the two queries. Here’s the refactored code:
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 | #!/usr/bin/bash # Assign user and password username="${1}" database="${2}" directory="${3}" # List the parameter values passed. echo "Username: " ${username} echo "Database: " ${database} echo "Directory: " ${directory} echo "" # Define an array. declare -a cmd # Assign elements to an array. cmd[0]="actor.sql" cmd[1]="film.sql" cmd[2]="movie.sql" # Call the array elements. for i in ${cmd[*]}; do mysql -s -u${username} -p -D${database} < ${directory}/${i} > /dev/null 2>/dev/null done # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p -D${database} <<<'show tables' 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read list; do if [[ ${list} = "Tables_in_sampledb" ]]; then echo $list echo "----------------------------------------" else echo $list fi done echo "" # Connect and pipe the query result minus errors and warnings to the while loop. mysql -u${username} -p -D${database} <<<'SELECT CONCAT(a.actor_name," in ",f.film_name) AS "Actors in Films" FROM actor a INNER JOIN movie m ON a.actor_id = m.actor_id INNER JOIN film f ON m.film_id = f.film_id' 2>/dev/null | # Read through the piped result until it's empty. while IFS='\n' read actor_name; do if [[ ${actor_name} = "Actors in Films" ]]; then echo $actor_name echo "----------------------------------------" else echo $actor_name fi done |
Please let me know if you think there should be any more scaffolding for newbies in this post. As always, I hope this helps those looking for this type of solution.
SQL Developer – Fedora
This is the continuation of my efforts to stage an awesome Fedora developer’s instance. It shows you how to install Java 1.8 software development kit, which is nice to have. Though you can’t use Java 1.8 officially with Oracle SQL Developer 4.0.3 it is required for Oracle SQL Developer 4.1. Fortunately, the Oracle Product Manager, Jeff Smith has advised us that you can use Java 1.8 JDK with Oracle SQL Developer 4.0.3, and he’s written a comment to the blog post that it runs better with the Java 1.8 SDK.
After you install Oracle SQL Developer 4.0.3 or Oracle SQL Developer 4.1, you can watch Jeff Smith’s YouTube Video on SQL Developer 3.1 to learn how to use the basics of SQL Developer. I couldn’t find an updated version of the video for SQL Developer 4 but I didn’t try too hard.
You use yum as the root user to install Java SDK 1.8, much like my earlier Installing the Java SDK 1.7 and Java-MySQL Sample Program. The following command installs Java 8:
yum install -y java-1.8* |
It produces the following output:
Loaded plugins: langpacks, refresh-packagekit
fedora/20/x86_64/metalink | 18 kB 00:00
mysql-connectors-community | 2.5 kB 00:00
mysql-tools-community | 2.5 kB 00:00
mysql56-community | 2.5 kB 00:00
pgdg93 | 3.6 kB 00:00
updates/20/x86_64/metalink | 16 kB 00:00
updates | 4.9 kB 00:00
(1/2): mysql-tools-community/20/x86_64/primary_db | 21 kB 00:00
(2/2): updates/20/x86_64/primary_db | 13 MB 00:09
updates/20/x86_64/pkgtags
updates
(1/2): updates/20/x86_64/pkgtags | 1.4 MB 00:02
(2/2): updates/20/x86_64/updateinfo | 1.9 MB 00:04
Package 1:java-1.8.0-openjdk-headless-1.8.0.31-1.b13.fc20.x86_64 already installed and latest version
Package 1:java-1.8.0-openjdk-javadoc-1.8.0.31-1.b13.fc20.noarch already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package java-1.8.0-openjdk.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
---> Package java-1.8.0-openjdk-accessibility.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
---> Package java-1.8.0-openjdk-demo.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
---> Package java-1.8.0-openjdk-devel.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
---> Package java-1.8.0-openjdk-src.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository
Size
================================================================================
Installing:
java-1.8.0-openjdk x86_64 1:1.8.0.31-1.b13.fc20 updates 201 k
java-1.8.0-openjdk-accessibility x86_64 1:1.8.0.31-1.b13.fc20 updates 12 k
java-1.8.0-openjdk-demo x86_64 1:1.8.0.31-1.b13.fc20 updates 1.9 M
java-1.8.0-openjdk-devel x86_64 1:1.8.0.31-1.b13.fc20 updates 9.2 M
java-1.8.0-openjdk-src x86_64 1:1.8.0.31-1.b13.fc20 updates 45 M
Transaction Summary
================================================================================
Install 5 Packages
Total download size: 56 M
Installed size: 92 M
Downloading packages:
(1/5): java-1.8.0-openjdk-accessibility-1.8.0.31-1.b13.fc20 | 12 kB 00:00
(2/5): java-1.8.0-openjdk-1.8.0.31-1.b13.fc20.x86_64.rpm | 201 kB 00:02
(3/5): java-1.8.0-openjdk-demo-1.8.0.31-1.b13.fc20.x86_64.r | 1.9 MB 00:03
(4/5): java-1.8.0-openjdk-devel-1.8.0.31-1.b13.fc20.x86_64. | 9.2 MB 00:07
(5/5): java-1.8.0-openjdk-src-1.8.0.31-1.b13.fc20.x86_64.rp | 45 MB 05:05
--------------------------------------------------------------------------------
Total 187 kB/s | 56 MB 05:05
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
Installing : 1:java-1.8.0-openjdk-1.8.0.31-1.b13.fc20.x86_64 1/5
Installing : 1:java-1.8.0-openjdk-devel-1.8.0.31-1.b13.fc20.x86_64 2/5
Installing : 1:java-1.8.0-openjdk-demo-1.8.0.31-1.b13.fc20.x86_64 3/5
Installing : 1:java-1.8.0-openjdk-accessibility-1.8.0.31-1.b13.fc20.x86 4/5
Installing : 1:java-1.8.0-openjdk-src-1.8.0.31-1.b13.fc20.x86_64 5/5
Verifying : 1:java-1.8.0-openjdk-devel-1.8.0.31-1.b13.fc20.x86_64 1/5
Verifying : 1:java-1.8.0-openjdk-demo-1.8.0.31-1.b13.fc20.x86_64 2/5
Verifying : 1:java-1.8.0-openjdk-1.8.0.31-1.b13.fc20.x86_64 3/5
Verifying : 1:java-1.8.0-openjdk-accessibility-1.8.0.31-1.b13.fc20.x86 4/5
Verifying : 1:java-1.8.0-openjdk-src-1.8.0.31-1.b13.fc20.x86_64 5/5
Installed:
java-1.8.0-openjdk.x86_64 1:1.8.0.31-1.b13.fc20
java-1.8.0-openjdk-accessibility.x86_64 1:1.8.0.31-1.b13.fc20
java-1.8.0-openjdk-demo.x86_64 1:1.8.0.31-1.b13.fc20
java-1.8.0-openjdk-devel.x86_64 1:1.8.0.31-1.b13.fc20
java-1.8.0-openjdk-src.x86_64 1:1.8.0.31-1.b13.fc20
Complete! |
Then, you go to Oracle’s SQL Developer 4.0.3 web page or Oracle’s Beta SQL Developer 4.1 web page and download the SQL Developer RPM. At the time of writing, you download the following SQL Developer 4.0.3 RPM:
sqldeveloper-4.0.3.16.84-1.noarch.rpm |
Assuming you download the sqldeveloper-4.0.3.16.84-1.noarch.rpm file to the student user’s account. It will download into the /home/student/Downloads directory. You run the SQL Developer RPM file with the following syntax as the root user:
rpm -Uhv /home/student/Downloads/sqldeveloper-4.0.3.16.84-1.noarch.rpm |
Running the SQL Developer RPM produces the following output:
Preparing... ################################# [100%] Updating / installing... 1:sqldeveloper-4.0.3.16.84-1 ################################# [100%] |
You can now run the sqldeveloper.sh file as the root user with the following syntax:
/opt/sqldeveloper/sqldeveloper.sh |
At this point, it’s important to note that my download from the Oracle SQL Developer 4.1 page turned out to be SQL Developer 4.0.3. It prompts you for the correct Java JDK, as shown below. You may opt to enter the path to the Java JDK 1.8 for SQL Developer 4.1 because until today you downloaded the Oracle SQL Developer 4.0.3 version from the Oracle SQL Developer 4.1 page. Naturally, the Oracle SQL Developer 4.1 instructions say to use the Java 1.8 JDK on the RPM for Linux Installation Notes web page, as shown below:
If you assume from the instructions on the Oracle instruction page above that Oracle SQL Developer 4.0.3 and Oracle SQL Developer 4.1 support Java 1.8 JDK, you may enter the location for the Java JDK 1.8 when prompted. Jeff Smith, the Product Manager wrote this blog post on Oracle SQL Developer 4: Windows and the JDK. Unfortunately, you’ll see the following message if you attempt to run Oracle SQL Developer 4.0.3 with the Java 1.8 SDK at the command-line:
Oracle SQL Developer Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved. Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /root/.sqldeveloper/4.0.0/product.conf /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.31.x86_64 OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=256M; support was removed in 8.0 |
It also raises the following error message dialog:
Text version of Unsupported JDK Version error message:
Running this product is supported with a minimum Java version of 1.7.0_51 and a maximum version less than 1.8.
Update the SetJavaHome in “/root/.sqldeveloper/4.0.0/product.conf” to point to another Java.
This produce will not be supported, and may not run correctly if you proceed. Continue anyway?
The error dialog message tells us that the instructions on the RPM for Linux Installation Notes web page can be misleading. You really need to use the Java JDK 1.7 to be supported officially, but you can safely ignore the error.
If you want a certified component, leave the “Skip This Message Next Time” checkbox unchecked and click the “No” button to continue. At this point, there’s no automatic recovery. You need to open the following file:
/root/.sqldeveloper/4.0.0/product.conf |
You need to change the SetJavaHome parameter in the file to the following:
# SetJavaHome /path/jdk SetJavaHome /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.79-2.5.5.0.fc20.x86_64 |
After making the change, you can re-run the sqldeveloper.sh shell as follows:
/opt/sqldeveloper/sqldeveloper.sh |
It launches the following dialog message:
The installation pauses to ask you if you want to transfer an existing SQL Developer configuration by raising the following dialog. Assuming this is a new installation, the installer won’t find a prior configuration file. You need to click the “No” button to proceed.
The installation continues and launches SQL Developer. The first time launch shows you the following Oracle Usage Tracking dialog. If you don’t want your use monitored, uncheck the “Allow automated usage reporting to Oracle” checkbox. Click the “OK” button to continue.
After dismissing the Oracle Usage Tracking dialog, you see the SQL Developer environment:
After installing SQL Developer in the root account, you can install it as the student user. You use this command as the student user:
/opt/sqldeveloper/sqldeveloper.sh |
It returns the following error because it’s the second installation and SQL Developer doesn’t prompt you to configure the user’s product.conf file with the working JDK location:
Oracle SQL Developer Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved. Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /home/student/.sqldeveloper/4.0.0/product.conf Error: Unable to get APP_JAVA_HOME input from stdin after 10 tries |
You need to edit the /home/student/.sqldeveloper/4.0.0/product.conf file, and add the following line to the file:
# SetJavaHome /path/jdk SetJavaHome /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.79-2.5.5.0.fc20.x86_64 |
Now, you can launch SQL Developer with the following command:
/opt/sqldeveloper/sqldeveloper.sh |
Alternatively, you can add the following alias to the student user’s .bashrc file:
# Set alias for SQL Developer tool. alias sqldeveloper="/opt/sqldeveloper/sqldeveloper.sh" |
You can now launch the SQL Developer tool, like this as the student user:
sqldeveloper |
You see the following when SQL Developer launches:
As always, I hope this helps those trying to sort out installing SQL Developer on a Fedora server.
Add zsh to Fedora
One of my students requested an option to the bash shell. It was interesting to hear that he wanted me to instal the zsh in my Fedora image. There’s only one book that I’m aware of that’s been published on the Z Shell, and it is From Bash to Z Shell.
This post shows how to add the zsh to my Fedora image because I already release a new one for the term without the zsh shell. You use the yum utility as the root user to install the zsh library:
yum install -y zsh |
It should produce an output stream like the following, which required accessing the alternate mirror site:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 pgdg93 | 3.6 kB 00:00 updates/20/x86_64/metalink | 14 kB 00:00 updates | 4.9 kB 00:00 (1/3): mysql-connectors-community/20/x86_64/primary_db | 8.8 kB 00:00 (2/3): pgdg93/20/x86_64/primary_db | 83 kB 00:01 (3/3): updates/20/x86_64/primary_db | 13 MB 00:13 updates/20/x86_64/pkgtags FAILED http://mirror.utexas.edu/fedora/linux/updates/20/x86_64/repodata/1ea83dc402a2bcba53f9b0011ecfa0d579b5a316e4c7f01ec5f1166dcdca138f-pkgtags.sqlite.gz: [Errno 14] HTTP Error 404 - Not Found Trying other mirror. (1/2): updates/20/x86_64/updateinfo | 1.9 MB 00:07 (2/2): updates/20/x86_64/pkgtags | 1.4 MB 00:01 Resolving Dependencies --> Running transaction check ---> Package zsh.x86_64 0:5.0.7-6.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: zsh x86_64 5.0.7-6.fc20 updates 2.5 M Transaction Summary ================================================================================ Install 1 Package Total download size: 2.5 M Installed size: 5.9 M Downloading packages: zsh-5.0.7-6.fc20.x86_64.rpm | 2.5 MB 00:03 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : zsh-5.0.7-6.fc20.x86_64 1/1 Verifying : zsh-5.0.7-6.fc20.x86_64 1/1 Installed: zsh.x86_64 0:5.0.7-6.fc20 Complete! |
Once you’ve installed the zsh, you can configure like you would the bash shell. You make edits to individual .zshrc files and generic changes to the /etc/zshrc file. You can find the documentation to edit the zsh in the User’s Guide to the Z-Shell.
The following is a modified .zshrc file. The changes enable the up-arrow in Oracle’s sqlplus and provides you with a color prompt, like this:
# Source global definitions if [ -f /etc/zshrc ]; then . /etc/zshrc fi # Uncomment the following line if you don't like systemctl's auto-paging feature: # export SYSTEMD_PAGER= # Set the JAVA_HOME path. export JAVA_HOME=/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.75-2.5.4.2.fc20.x86_64 # Set the CLASSPATH path. export CLASSPATH=/usr/share/java/mysql-connector-java.jar:. # User specific aliases and functions . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh # Wrap sqlplus with rlwrap to edit prior lines with the # up, down, left and right keys. sqlplus() { if [ "$RLWRAP" = "0" ]; then sqlplus "$@" else rlwrap sqlplus "$@" fi } # Set the bindkey. bindkey -v bindkey "^R" history-incremental-search-backward export EDITOR="vim" # history stuff HISTFILE=~/.zsh-histfile HISTSIZE=2000 # Set vi as a command line editor. set -o vi autoload -U colors && colors PS1="[%{$fg[red]%}%n%{$reset_color%}@%{$fg[blue]%}%m %{$fg[yellow]%}%~%{$reset_color%}% ]$ " |
The zsh prompt looks like the following:
[student@localhost ~]$
If you’re configured with a bash shell, you can change your shell to a zsh with the following command:
chsh -s /bin/zsh |
As always, I hope this helps those looking for this type of information.
Ruby Thin Web Server
Somebody suggested that I try out thin, “A fast and very simple Ruby web server.” So, I thought it might be interesting to test, and a simplification over Rails to demonstrate an small Ruby MVC pattern.
Installing thin seemed straight forward as a gem installation, like
gem install thin |
The initial install didn’t work out of the box because I’d neglected to install the gcc-c++ library. It raised the following errors:
Fetching: eventmachine-1.0.7.gem (100%)
Building native extensions. This could take a while...
ERROR: Error installing thin:
ERROR: Failed to build gem native extension.
/usr/bin/ruby extconf.rb
checking for main() in -lssl... no
checking for rb_trap_immediate in ruby.h,rubysig.h... no
checking for rb_thread_blocking_region()... yes
checking for ruby/thread.h... yes
checking for rb_thread_call_without_gvl() in ruby/thread.h... yes
checking for inotify_init() in sys/inotify.h... yes
checking for writev() in sys/uio.h... yes
checking for rb_thread_fd_select()... yes
checking for rb_fdset_t in ruby/intern.h... yes
checking for rb_wait_for_single_fd()... yes
checking for rb_enable_interrupt()... no
checking for rb_time_new()... yes
checking for sys/event.h... no
checking for epoll_create() in sys/epoll.h... yes
checking for clock_gettime()... yes
checking for CLOCK_MONOTONIC_RAW in time.h... yes
checking for CLOCK_MONOTONIC in time.h... yes
creating Makefile
make "DESTDIR="
g++ -I. -I/usr/include -I/usr/include/ruby/backward -I/usr/include -I. -DWITHOUT_SSL -DBUILD_FOR_RUBY -DHAVE_RB_THREAD_BLOCKING_REGION -DHAVE_TBR -DHAVE_RUBY_THREAD_H -DHAVE_RB_THREAD_CALL_WITHOUT_GVL -DHAVE_RB_THREAD_CALL_WITHOUT_GVL -DHAVE_INOTIFY_INIT -DHAVE_INOTIFY -DHAVE_WRITEV -DHAVE_WRITEV -DHAVE_RB_THREAD_FD_SELECT -DHAVE_RB_THREAD_FD_SELECT -DHAVE_TYPE_RB_FDSET_T -DHAVE_RB_FDSET_T -DHAVE_RB_WAIT_FOR_SINGLE_FD -DHAVE_RB_TIME_NEW -DOS_UNIX -DHAVE_EPOLL_CREATE -DHAVE_EPOLL -DHAVE_CLOCK_GETTIME -DHAVE_CONST_CLOCK_MONOTONIC_RAW -DHAVE_CONST_CLOCK_MONOTONIC -fPIC -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -mtune=generic -m64 -o ed.o -c ed.cpp
make: g++: Command not found
make: *** [ed.o] Error 127
Gem files will remain installed in /usr/local/share/gems/gems/eventmachine-1.0.7 for inspection.
Results logged to /usr/local/share/gems/gems/eventmachine-1.0.7/ext/gem_make.out |
Naturally, I installed the gcc-c++ library with the yum utility, like this:
yum list gcc-c++ |
It displayed the following log output:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 pgdg93 | 3.6 kB 00:00 updates/20/x86_64/metalink | 14 kB 00:00 updates | 4.9 kB 00:00 updates/20/x86_64/primary_db | 13 MB 00:04 (1/2): updates/20/x86_64/updateinfo | 1.9 MB 00:02 (2/2): updates/20/x86_64/pkgtags | 1.4 MB 00:00 Resolving Dependencies --> Running transaction check ---> Package gcc-c++.x86_64 0:4.8.3-7.fc20 will be installed --> Processing Dependency: libstdc++-devel = 4.8.3-7.fc20 for package: gcc-c++-4.8.3-7.fc20.x86_64 --> Running transaction check ---> Package libstdc++-devel.x86_64 0:4.8.3-7.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: gcc-c++ x86_64 4.8.3-7.fc20 updates 7.2 M Installing for dependencies: libstdc++-devel x86_64 4.8.3-7.fc20 updates 1.5 M Transaction Summary ================================================================================ Install 1 Package (+1 Dependent package) Total download size: 8.7 M Installed size: 25 M Downloading packages: (1/2): gcc-c++-4.8.3-7.fc20.x86_64.rpm | 7.2 MB 00:05 (2/2): libstdc++-devel-4.8.3-7.fc20.x86_64.rpm | 1.5 MB 00:01 -------------------------------------------------------------------------------- Total 1.3 MB/s | 8.7 MB 00:06 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : libstdc++-devel-4.8.3-7.fc20.x86_64 1/2 Installing : gcc-c++-4.8.3-7.fc20.x86_64 2/2 Verifying : gcc-c++-4.8.3-7.fc20.x86_64 1/2 Verifying : libstdc++-devel-4.8.3-7.fc20.x86_64 2/2 Installed: gcc-c++.x86_64 0:4.8.3-7.fc20 Dependency Installed: libstdc++-devel.x86_64 0:4.8.3-7.fc20 Complete! |
After installing the gcc-c++ libraries, I reran the gem utility to install the thin utility. It created three Ruby Gems: eventmachine-1.0.7, daemons-1.2.2.gem, and thin-1.6.3.gem, as shown:
Building native extensions. This could take a while... Successfully installed eventmachine-1.0.7 Fetching: daemons-1.2.2.gem (100%) Successfully installed daemons-1.2.2 Fetching: thin-1.6.3.gem (100%) Building native extensions. This could take a while... Successfully installed thin-1.6.3 Parsing documentation for daemons-1.2.2 Installing ri documentation for daemons-1.2.2 Parsing documentation for eventmachine-1.0.7 Installing ri documentation for eventmachine-1.0.7 Parsing documentation for thin-1.6.3 Installing ri documentation for thin-1.6.3 Done installing documentation for daemons, eventmachine, thin after 11 seconds 3 gems installed |
Having created the Ruby Gems, I followed the thin instruction on the web site, and created the following Ruby web server:
app = proc do |env| [ 200, # Status code { # Response headers 'Content-Type' => 'text/html', 'Content-Length' => '12', }, ['Hello World!'] # Response body ] end # You can install Rack middlewares # to do some crazy stuff like logging, # filtering, auth or build your own. use Rack::CommonLogger run app |
Then, I tested the Ruby web server with the following command:
thin start -R thin.ru |
It displayed the following test server as the result of localhost:3000 URL:
As always, I hope this helps those who land on this page.
Find a string in files
From time to time, folks ask questions about how to solve common problems in Linux or Unix. Today, the question is: “How do I find a list of files that contain a specific string?” There are two alternatives with the find command, and the following sample searches look for files that contain a sqlite3 string literal.
- Search for only the file names:
find . -type f | xargs grep -li sqlite3 |
Or, the more verbose:
find . -type f -exec grep -li sqlite3 /dev/null {} + |
- Search for the file names and text line:
find . -type f | xargs grep -i sqlite3 |
Or, the more verbose:
find . -type f -exec grep -i sqlite3 /dev/null {} + |
Don’t exclude the /dev/null from the verbose syntax or you’ll get the things you lack permissions to inspect or that raise other errors. I don’t post a lot of Linux or Unix tips and techniques, and you may find this site more useful to answer these types of questions:
Unix & Linux Stack Exchange web site
As always, I hope this helps those you land on the blog page.
Python-MySQL Program
This post works through the Python configuration of Fedora instance, and continues the configuration of my LAMP VMware instance. It covers how you add the MySQL-python libraries to the Fedora instance, and provides the students with one more language opportunity for their capstone lab in the database class.
A standard Fedora Linux distribution installs Python 2.7 by default. Unfortunately, the MySQL-python library isn’t installed by default. You can verify the Python version by writing and running the following version.py program before installing the MySQL-python library:
1 2 3 4 5 | # Import sys library. import sys # Print the Python version. print sys.version |
You can run the version.py program dynamically like this from the current working directory:
python version.py |
It will print the following:
2.7.5 (default, Nov 3 2014, 14:26:24) [GCC 4.8.3 20140911 (Red Hat 4.8.3-7)] |
If you modify the program by adding the following first line
1 2 3 4 5 6 7 | #!/usr/bin/python # Import sys library. import sys # Print the Python version. print sys.version |
Provided you’ve set the file permissions to read and execute, you can run the program by simply calling version.py like this from the present working directory:
./version.py |
You can install the MySQL-python library with the yum utility like this:
yum install -y MySQL-python |
It shows you the following output:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 pgdg93 | 3.6 kB 00:00 updates/20/x86_64/metalink | 12 kB 00:00 updates | 4.9 kB 00:00 updates/20/x86_64/primary_db | 13 MB 00:04 (1/2): updates/20/x86_64/updateinfo | 1.9 MB 00:02 (2/2): updates/20/x86_64/pkgtags | 1.4 MB 00:02 Resolving Dependencies --> Running transaction check ---> Package MySQL-python.x86_64 0:1.2.3-8.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: MySQL-python x86_64 1.2.3-8.fc20 fedora 82 k Transaction Summary ================================================================================ Install 1 Package Total download size: 82 k Installed size: 231 k Downloading packages: MySQL-python-1.2.3-8.fc20.x86_64.rpm | 82 kB 00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : MySQL-python-1.2.3-8.fc20.x86_64 1/1 Verifying : MySQL-python-1.2.3-8.fc20.x86_64 1/1 Installed: MySQL-python.x86_64 0:1.2.3-8.fc20 Complete! |
After installing the MySQL-python library, you can call the following mysql_connect.py program from the local directory:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | #!/usr/bin/python # Import sys library. import MySQLdb import sys try: # Create new database connection. db = MySQLdb.connect('localhost','student','student','studentdb') # Query the version of the MySQL database. db.query("SELECT version()") # Assign the query results to a local variable. result = db.use_result() # Print the results. print "MySQL Version: %s " % result.fetch_row()[0] except MySQLdb.Error, e: # Print the error. print "ERROR %d: %s" % (e.args[0], e.args[1]) sys.exit(1) finally: # Close the connection when it is open. if db: db.close() |
Like the version.py program, set the file permissions to read and execute and call , you can run the program by simply calling mysql_connect.py program like this from the present working directory:
./mysql_connect.py |
The mysql_connect.py program displays:
MySQL Version: 5.6.24 |
After verifying the MySQL connection, you can query actual data with the following mysql_queryset.py program:
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 | #!/usr/bin/python # Import sys library. import MySQLdb import sys try: # Create new database connection. db = MySQLdb.connect('localhost','student','student','studentdb') # Create a result set cursor. rs = db.cursor() rs.execute("SELECT item_title FROM item") # Assign the query results to a local variable. rows = rs.fetchall() # Print the results. for row in rows: print row except MySQLdb.Error, e: # Print the error. print "ERROR %d: %s" % (e.args[0], e.args[1]) sys.exit(1) finally: # Close the connection when it is open. if db: db.close() |
You call the mysql_queryset.py file from the present working directory like this:
./mysql_queryset.py |
It prints the following:
('The Hunt for Red October',)
('Star Wars I',)
('Star Wars II',)
('Star Wars II',)
('Star Wars III',)
('The Chronicles of Narnia',)
('RoboCop',)
('Pirates of the Caribbean',)
('The Chronicles of Narnia',)
('MarioKart',)
('Splinter Cell',)
('Need for Speed',)
('The DaVinci Code',)
('Cars',)
('Beau Geste',)
('I Remember Mama',)
('Tora! Tora! Tora!',)
('A Man for All Seasons',)
('Hook',)
('Around the World in 80 Days',)
("Harry Potter and the Sorcerer's Stone",)
('Camelot',) |
You can substantially improve on the behavior of the prior example by handling each row one at a time. The following mysql_query.py program reads through the cursor result set one row at a time:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | #!/usr/bin/python # Import sys library. import MySQLdb import sys try: # Create new database connection. db = MySQLdb.connect('localhost','student','student','studentdb') # Create a result set cursor. rs = db.cursor() rs.execute("SELECT item_title FROM item") # Assign the query results to a local variable. for i in range(rs.rowcount): row = rs.fetchone() print row[0] except MySQLdb.Error, e: # Print the error. print "ERROR %d: %s" % (e.args[0], e.args[1]) sys.exit(1) finally: # Close the connection when it is open. if db: db.close() |
You call the mysql_query.py with the following syntax:
./mysql_query.py |
It returns the following result set:
The Hunt for Red October Star Wars I Star Wars II Star Wars II Star Wars III The Chronicles of Narnia RoboCop Pirates of the Caribbean The Chronicles of Narnia MarioKart Splinter Cell Need for Speed The DaVinci Code Cars Beau Geste I Remember Mama Tora! Tora! Tora! A Man for All Seasons Hook Around the World in 80 Days Harry Potter and the Sorcerer's Stone Camelot |
As always, I hope this helps those looking for this type of solution. The Python tutorial web site teaches you more about the Python Programming Language. You may also find the TutorialsPoint.com site useful while you’re learning and using Python. The MySQLdb User’s Guide teaches more about working writing Python-MySQL library. The MySQLdb implements the Python Database API Specification v2.0.
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 when MySQL is already installed.
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 you need to know how to do that let me know (but it hasn’t changed much from the example at the bottom of this old MySQL 5.1 blog post).
You install Apache2 with the following command as the root user, or with the sudo command as a sudoer-list user:
yum install httpd |
The following displays the results of starting the yum utility to install httpd, and you need to reply with a y to complete the installation:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 pgdg93 | 3.6 kB 00:00 updates/20/x86_64/metalink | 16 kB 00:00 updates | 4.9 kB 00:00 updates/20/x86_64/primary_db | 13 MB 00:04 (1/2): updates/20/x86_64/updateinfo | 1.9 MB 00:02 (2/2): updates/20/x86_64/pkgtags | 1.4 MB 00:01 Resolving Dependencies --> Running transaction check ---> Package httpd.x86_64 0:2.4.10-2.fc20 will be installed --> Processing Dependency: httpd-tools = 2.4.10-2.fc20 for package: httpd-2.4.10-2.fc20.x86_64 --> Processing Dependency: system-logos-httpd for package: httpd-2.4.10-2.fc20.x86_64 --> Running transaction check ---> Package fedora-logos-httpd.noarch 0:21.0.1-1.fc20 will be installed ---> Package httpd-tools.x86_64 0:2.4.10-2.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: httpd x86_64 2.4.10-2.fc20 updates 1.2 M Installing for dependencies: fedora-logos-httpd noarch 21.0.1-1.fc20 fedora 28 k httpd-tools x86_64 2.4.10-2.fc20 updates 79 k Transaction Summary ================================================================================ Install 1 Package (+2 Dependent packages) Total download size: 1.3 M Installed size: 4.0 M Is this ok [y/d/N]: y Downloading packages: (1/3): fedora-logos-httpd-21.0.1-1.fc20.noarch.rpm | 28 kB 00:00 (2/3): httpd-2.4.10-2.fc20.x86_64.rpm | 1.2 MB 00:01 (3/3): httpd-tools-2.4.10-2.fc20.x86_64.rpm | 79 kB 00:00 -------------------------------------------------------------------------------- Total 815 kB/s | 1.3 MB 00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : httpd-tools-2.4.10-2.fc20.x86_64 1/3 Installing : fedora-logos-httpd-21.0.1-1.fc20.noarch 2/3 Installing : httpd-2.4.10-2.fc20.x86_64 3/3 Verifying : httpd-2.4.10-2.fc20.x86_64 1/3 Verifying : fedora-logos-httpd-21.0.1-1.fc20.noarch 2/3 Verifying : httpd-tools-2.4.10-2.fc20.x86_64 3/3 Installed: httpd.x86_64 0:2.4.10-2.fc20 Dependency Installed: fedora-logos-httpd.noarch 0:21.0.1-1.fc20 httpd-tools.x86_64 0:2.4.10-2.fc20 Complete! |
Next, you install php as the root user with the following command:
yum install php |
The following displays when you install php, and you need to reply with a y to complete the installation:
Loaded plugins: langpacks, refresh-packagekit Resolving Dependencies --> Running transaction check ---> Package php.x86_64 0:5.5.22-1.fc20 will be installed --> Processing Dependency: php-common(x86-64) = 5.5.22-1.fc20 for package: php-5.5.22-1.fc20.x86_64 --> Processing Dependency: php-cli(x86-64) = 5.5.22-1.fc20 for package: php-5.5.22-1.fc20.x86_64 --> Running transaction check ---> Package php-cli.x86_64 0:5.5.22-1.fc20 will be installed ---> Package php-common.x86_64 0:5.5.22-1.fc20 will be installed --> Processing Dependency: php-pecl-jsonc(x86-64) for package: php-common-5.5.22-1.fc20.x86_64 --> Running transaction check ---> Package php-pecl-jsonc.x86_64 0:1.3.6-1.fc20 will be installed --> Processing Dependency: /usr/bin/pecl for package: php-pecl-jsonc-1.3.6-1.fc20.x86_64 --> Processing Dependency: /usr/bin/pecl for package: php-pecl-jsonc-1.3.6-1.fc20.x86_64 --> Running transaction check ---> Package php-pear.noarch 1:1.9.5-6.fc20 will be installed --> Processing Dependency: php-xml for package: 1:php-pear-1.9.5-6.fc20.noarch --> Processing Dependency: php-posix for package: 1:php-pear-1.9.5-6.fc20.noarch --> Running transaction check ---> Package php-process.x86_64 0:5.5.22-1.fc20 will be installed ---> Package php-xml.x86_64 0:5.5.22-1.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: php x86_64 5.5.22-1.fc20 updates 2.6 M Installing for dependencies: php-cli x86_64 5.5.22-1.fc20 updates 3.9 M php-common x86_64 5.5.22-1.fc20 updates 1.0 M php-pear noarch 1:1.9.5-6.fc20 updates 343 k php-pecl-jsonc x86_64 1.3.6-1.fc20 updates 34 k php-process x86_64 5.5.22-1.fc20 updates 77 k php-xml x86_64 5.5.22-1.fc20 updates 247 k Transaction Summary ================================================================================ Install 1 Package (+6 Dependent packages) Total download size: 8.2 M Installed size: 32 M Is this ok [y/d/N]: y Downloading packages: (1/7): php-5.5.22-1.fc20.x86_64.rpm | 2.6 MB 00:03 (2/7): php-cli-5.5.22-1.fc20.x86_64.rpm | 3.9 MB 00:03 (3/7): php-common-5.5.22-1.fc20.x86_64.rpm | 1.0 MB 00:00 (4/7): php-pear-1.9.5-6.fc20.noarch.rpm | 343 kB 00:00 (5/7): php-pecl-jsonc-1.3.6-1.fc20.x86_64.rpm | 34 kB 00:00 (6/7): php-process-5.5.22-1.fc20.x86_64.rpm | 77 kB 00:00 (7/7): php-xml-5.5.22-1.fc20.x86_64.rpm | 247 kB 00:00 -------------------------------------------------------------------------------- Total 1.1 MB/s | 8.2 MB 00:07 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : php-cli-5.5.22-1.fc20.x86_64 1/7 Installing : php-process-5.5.22-1.fc20.x86_64 2/7 Installing : php-xml-5.5.22-1.fc20.x86_64 3/7 Installing : 1:php-pear-1.9.5-6.fc20.noarch 4/7 Installing : php-common-5.5.22-1.fc20.x86_64 5/7 Installing : php-pecl-jsonc-1.3.6-1.fc20.x86_64 6/7 Installing : php-5.5.22-1.fc20.x86_64 7/7 Verifying : php-5.5.22-1.fc20.x86_64 1/7 Verifying : php-common-5.5.22-1.fc20.x86_64 2/7 Verifying : php-cli-5.5.22-1.fc20.x86_64 3/7 Verifying : 1:php-pear-1.9.5-6.fc20.noarch 4/7 Verifying : php-process-5.5.22-1.fc20.x86_64 5/7 Verifying : php-xml-5.5.22-1.fc20.x86_64 6/7 Verifying : php-pecl-jsonc-1.3.6-1.fc20.x86_64 7/7 Installed: php.x86_64 0:5.5.22-1.fc20 Dependency Installed: php-cli.x86_64 0:5.5.22-1.fc20 php-common.x86_64 0:5.5.22-1.fc20 php-pear.noarch 1:1.9.5-6.fc20 php-pecl-jsonc.x86_64 0:1.3.6-1.fc20 php-process.x86_64 0:5.5.22-1.fc20 php-xml.x86_64 0:5.5.22-1.fc20 Complete! |
After installing the software, you can set the Apache server to start automatically with the following command:
chkconfig httpd on |
However, that command only starts the Apache server the next time you boot the server. You use the following command as the root user to start the Apache server:
apachectl start |
You can verify the installation with the following command as the root user:
ps -ef | grep httpd | grep -v grep |
It should return:
root 5433 1 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5434 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5435 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5436 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5437 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5438 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND apache 5442 5433 0 17:03 ? 00:00:00 /usr/sbin/httpd -DFOREGROUND |
and, then verify the listening port with the following command as the root user:
netstat -tulpn | grep :80 |
It should return the following when both the Apache server is listening on port 80 and the Oracle multi-protocol server is listening on port 8080:
tcp6 0 0 :::80 :::* LISTEN 5433/httpd tcp6 0 0 :::8080 :::* LISTEN 1505/tnslsnr |
After verifying the connection, you can test it by creating the traditional info.php program file in the /var/www/http directory. The file should contain the following:
1 2 3 | <?php phpinfo(); ?> |
You can test it by opening the Firefox browser and entering the following URL from the Fedora Linux image:
http://localhost/info.php |
It should display the typical diagnostic page. This verifies the configuration of the Apache and PHP servers. The next step verifies whether you have the mysqli library to connect to the MySQL database.
You create a mysqli_check.php script, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | <html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) {
print 'mysqli not installed.'; }
else {
print 'mysqli installed.'; }
?>
</script>
</body>
</html> |
You can test it with the following URL from the local browser:
http://localhost/mysqli_check.php |
If it’s installed you can skip the next step, but if not you need to run yum in expert mode as follows (the check for php-mysql isn’t really necessary because it’s too old a version but good practice):
[root@localhost etc]# yum shell Loaded plugins: langpacks, refresh-packagekit > remove php-mysql No Match for argument: php-mysql > install php-mysqlnd > run --> Running transaction check ---> Package php-mysqlnd.x86_64 0:5.5.22-1.fc20 will be installed --> Processing Dependency: php-pdo(x86-64) = 5.5.22-1.fc20 for package: php-mysqlnd-5.5.22-1.fc20.x86_64 --> Running transaction check ---> Package php-pdo.x86_64 0:5.5.22-1.fc20 will be installed --> Finished Dependency Resolution ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: php-mysqlnd x86_64 5.5.22-1.fc20 updates 293 k Installing for dependencies: php-pdo x86_64 5.5.22-1.fc20 updates 141 k Transaction Summary ================================================================================ Install 1 Package (+1 Dependent package) Total download size: 433 k Installed size: 1.4 M Is this ok [y/d/N]: y Downloading packages: (1/2): php-mysqlnd-5.5.22-1.fc20.x86_64.rpm | 293 kB 00:00 (2/2): php-pdo-5.5.22-1.fc20.x86_64.rpm | 141 kB 00:00 -------------------------------------------------------------------------------- Total 427 kB/s | 433 kB 00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : php-pdo-5.5.22-1.fc20.x86_64 1/2 Installing : php-mysqlnd-5.5.22-1.fc20.x86_64 2/2 Verifying : php-pdo-5.5.22-1.fc20.x86_64 1/2 Verifying : php-mysqlnd-5.5.22-1.fc20.x86_64 2/2 Installed: php-mysqlnd.x86_64 0:5.5.22-1.fc20 Dependency Installed: php-pdo.x86_64 0:5.5.22-1.fc20 Finished Transaction > quit |
You should note that this also installed PDO. One caveat, before you rerun the mysqli_check.php script from a browser, you need to restart the Apache server. You can do that as the root user with the following syntax:
apachectl restart |
You can retest it with the following URL from the local browser:
http://localhost/mysqli_check.php |
At this point you should have everything installed to test your connection the MySQL database. As mentioned, this example extends my instructions for installing MySQL on the Fedora instance.
The following query.php file tests your ability to connect to the MySQL database with the mysqli driver, and it uses the studentdb and video store example from my Oracle Database 11g and MySQL 5.6 Developer Handbook:
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 70 71 72 73 | <html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
/* HTML element styles. */
table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
td {border:solid 1px gray;}
/* Class tag element styles. */
.ID {min-width:50px;text-align:right;}
.Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
// Assign credentials to connection.
$mysqli = new mysqli("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if ($mysqli->connect_errno) {
print $mysqli->connect_error."<br />";
print "Connection not established ...<br />";
}
else {
// Declare a static query.
$query = "SELECT au.system_user_id, au.system_user_name FROM system_user au" ;
// Loop through a result set until completed.
do {
// Attempt query and exit with failure before processing.
if (!$stmt = $mysqli->query($query)) {
// Print failure to resolve query message.
print $mysqli->error."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Print the opening HTML table tag.
print '<table><tr><th class="ID">ID</th><th class="Label">User Role Name</th></tr>';
// Fetch a row for processing.
while( $row = $stmt->fetch_row() ) {
// Print the opening HTML row tag.
print "<tr>";
// Loop through the row's columns.
for ($i = 0;$i < $mysqli->field_count;$i++) {
// Handle column one differently.
if ($i == 0)
print '<td class="ID">'.$row[$i]."</td>";
else
print '<td class="Label">'.$row[$i]."</td>";
}
// Print the closing HTML row tag.
print "</tr>";
}
}
} while( $mysqli->next_result());
// Print the closing HTML table tag.
print "</table>";
// Release connection resource.
$mysqli->close(); }
?>
</script>
</body>
</html> |
This should display the following in the browser:
You can see how to open port 80 for the Apache server in this blog post. If you want to work with blob data types, you’ll also need to use yum to install the php-gd library. You can read my LAMP php-gd library blog post to learn how to install the libraries. As always, I hope a step-by-step approach without assumptions helps those learning MySQL.
Subscribe via RSS