MacLochlainns Weblog

SELECT name, db_unique_name FROM v$database;

SELECT name, db_unique_name FROM v$database;

C:\> netstat -an | findstr /C:1158
  TCP    0.0.0.0:1158           0.0.0.0:0              LISTENING

C:\> netstat -an | findstr /C:1158 TCP 0.0.0.0:1158 0.0.0.0:0 LISTENING

C:\> emctl status dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

C:\> emctl status dbconsole Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

C:\> emctl start dbconsole

C:\> emctl start dbconsole

C:\> emctl stop dbconsole

C:\> emctl stop dbconsole

C:\>set ORACLE_HOSTNAME=localhost
 
C:\>set ORACLE_UNQNAME=orcl
 
C:\>emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory C:\app\McLaughlinM\product\11.2.0\dbhome_1/localhost_orcl/sysman/log

C:\>set ORACLE_HOSTNAME=localhost C:\>set ORACLE_UNQNAME=orcl C:\>emctl status dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0 Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved. https://localhost:1158/em/console/aboutApplication Oracle Enterprise Manager 11g is running. ------------------------------------------------------------------ Logs are generated in directory C:\app\McLaughlinM\product\11.2.0\dbhome_1/localhost_orcl/sysman/log

https://localhost:1158/em

https://localhost:1158/em

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# Import socket library.
use IO::Socket;
 
# Create new socket.
my $sock = new IO::Socket::INET(
                 LocalAddr => 'mclaughlinmysql',
                 LocalPort => '5500',
                 Proto => 'tcp',
                 Listen => 5500,
                 Reuse => 5500);
 
# Kill the program when socket not created.
die "Could not create socket: $!\n" unless $sock;
 
# Set socket to listen for incoming request and loop while waiting.
my $new_sock = $sock->accept();
while(<$new_sock>) {
print $_; }
 
# Close the socket.
close($sock);

# Import socket library. use IO::Socket; # Create new socket. my $sock = new IO::Socket::INET( LocalAddr => 'mclaughlinmysql', LocalPort => '5500', Proto => 'tcp', Listen => 5500, Reuse => 5500); # Kill the program when socket not created. die "Could not create socket: $!\n" unless $sock; # Set socket to listen for incoming request and loop while waiting. my $new_sock = $sock->accept(); while(<$new_sock>) { print $_; } # Close the socket. close($sock);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# Import socket library.
use IO::Socket;
 
# Create new socket.
my $sock = new IO::Socket::INET(
                 PeerAddr => 'mclaughlinmysql',
                 PeerPort => '5500',
                 Proto => 'tcp');
 
# Kill the program when socket not created.
die "Could not create socket: $!\n" unless $sock;
 
# Send string to socket.
print $sock "Hello there!\n";
close($sock);

# Import socket library. use IO::Socket; # Create new socket. my $sock = new IO::Socket::INET( PeerAddr => 'mclaughlinmysql', PeerPort => '5500', Proto => 'tcp'); # Kill the program when socket not created. die "Could not create socket: $!\n" unless $sock; # Send string to socket. print $sock "Hello there!\n"; close($sock);

perl server.pl

perl server.pl

perl client.pl

perl client.pl

C:\Users\<user_name>\AppData\Roaming\MySQL\Workbench\scripts

C:\Users\<user_name>\AppData\Roaming\MySQL\Workbench\scripts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# ReadFile.py
# Written in MySQL Workbench 5.2.41
 
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Parse string to avoid reading line return.
    if not line[len(line) - 1:len(line)] == ";":
      print(line[0:len(line) - 1])
    else:
      print(line)
    if not line:
        break

# -*- coding: utf-8 -*- # MySQL Workbench Python script # ReadFile.py # Written in MySQL Workbench 5.2.41 import os f = open("c:\\Data\\MySQL\\query.sql",'rU') while True: line = f.readline() # Parse string to avoid reading line return. if not line[len(line) - 1:len(line)] == ";": print(line[0:len(line) - 1]) else: print(line) if not line: break

1
2
3
4
5
6
7
8
9

import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line),
    if not line:
        print
        break

import os f = open("c:\\Data\\MySQL\\query.sql",'rU') while True: line = f.readline() # Suppress line return. print(line), if not line: print break

1
2
3
4
5
6
7
8
9

import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line, end = '')
    if not line:
        print()
        break

import os f = open("c:\\Data\\MySQL\\query.sql",'rU') while True: line = f.readline() # Suppress line return. print(line, end = '') if not line: print() break

ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message'

ERROR 1616 (HY000): DELAYED option not supported for table 'message'

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

package vulcantech.vth.server.commands;
 
import java.io.IOException;
import java.net.Socket;
 
import vulcantech.vth.server.combeans.MessageBean;
 
public class MessageHandler implements Handler {
 
  @Override
  public void handleIt(Object... args) {
 
    MessageBean message = (MessageBean) args[0];
    Socket sock = (Socket) args[1];
 
    DatabaseConnection dbconnection = new DatabaseConnection();
 
    String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" 
                  + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" 
                  + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')");
 
    synchronized (this) {
      dbconnection.executeUpdate(update);
    }
 
    try {
      sock.getOutputStream().write(1);
    } catch (IOException e) {
      e.printStackTrace();
    }
    dbconnection.close();
  }
}

package vulcantech.vth.server.commands; import java.io.IOException; import java.net.Socket; import vulcantech.vth.server.combeans.MessageBean; public class MessageHandler implements Handler { @Override public void handleIt(Object... args) { MessageBean message = (MessageBean) args[0]; Socket sock = (Socket) args[1]; DatabaseConnection dbconnection = new DatabaseConnection(); String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')"); synchronized (this) { dbconnection.executeUpdate(update); } try { sock.getOutputStream().write(1); } catch (IOException e) { e.printStackTrace(); } dbconnection.close(); } }

CREATE TABLE application_user
( user_id  int(10) unsigned PRIMARY KEY AUTO_INCREMENT
, user_name  varchar(20) NOT NULL
, user_role  varchar(20) NOT NULL
, user_group_id  int(10) unsigned NOT NULL
, user_type  int(10) unsigned NOT NULL
, first_name  varchar(20)
, middle_name  varchar(20)
, last_name  varchar(20)
, created_by  int(10) unsigned NOT NULL
, creation_date  datetime NOT NULL
, last_updated_by  int(10) unsigned NOT NULL
, last_update_date  datetime NOT NULL
, CONSTRAINT natural_key UNIQUE (user_name)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE application_user ( user_id int(10) unsigned PRIMARY KEY AUTO_INCREMENT , user_name varchar(20) NOT NULL , user_role varchar(20) NOT NULL , user_group_id int(10) unsigned NOT NULL , user_type int(10) unsigned NOT NULL , first_name varchar(20) , middle_name varchar(20) , last_name varchar(20) , created_by int(10) unsigned NOT NULL , creation_date datetime NOT NULL , last_updated_by int(10) unsigned NOT NULL , last_update_date datetime NOT NULL , CONSTRAINT natural_key UNIQUE (user_name) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO application_user VALUES
 ( null, 'potterhj', 'System Admin', 2, 1, 'Harry', 'James', 'Potter', 1, NOW(), 1, NOW())
,( null, 'weasilyr', 'Guest', 1, 1, 'Ronald', null, 'Weasily', 1, NOW(), 1, NOW())
,( null, 'longbottomn', 'Guest', 1, 1, 'Neville', null, 'Longbottom', 1, NOW(), 1, NOW())
,( null, 'holmess', 'DBA', 3, 1, 'Sherlock', null, 'Holmes', 1, NOW(), 1, NOW())
,( null, 'watsonj', 'DBA', 3, 1, 'John', 'H', 'Watson', 1, NOW(), 1, NOW());

INSERT INTO application_user VALUES ( null, 'potterhj', 'System Admin', 2, 1, 'Harry', 'James', 'Potter', 1, NOW(), 1, NOW()) ,( null, 'weasilyr', 'Guest', 1, 1, 'Ronald', null, 'Weasily', 1, NOW(), 1, NOW()) ,( null, 'longbottomn', 'Guest', 1, 1, 'Neville', null, 'Longbottom', 1, NOW(), 1, NOW()) ,( null, 'holmess', 'DBA', 3, 1, 'Sherlock', null, 'Holmes', 1, NOW(), 1, NOW()) ,( null, 'watsonj', 'DBA', 3, 1, 'John', 'H', 'Watson', 1, NOW(), 1, NOW());

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

CREATE FUNCTION set_login(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a local variable to verify completion of the task:
  || ==========================================================
  ||   a. Default value is zero, which means false.
  ||   b. Non-default value is one, which means true.
  || ==========================================================
  */
  DECLARE  lv_success_flag  INT UNSIGNED  DEFAULT 0;
 
  /* Declare local variables to hold the return values from the cursor. */
  DECLARE  lv_login_id  INT UNSIGNED;
  DECLARE  lv_group_id  INT UNSIGNED;
 
  /* Declare a condition variable for zero rows fetched, selected, or processed. */
  DECLARE  no_rows_fetched  CONDITION FOR 1329;
 
  /* Declare a cursor to return an authorized user id. */
  DECLARE authorize_cursor CURSOR FOR
    SELECT   a.user_id
    ,        a.user_group_id
    FROM     application_user a
    WHERE    a.user_name = pv_login_name;
 
  /* Declare a handler for the cursor when it fails to return a row. */   
  DECLARE EXIT HANDLER FOR no_rows_fetched
    BEGIN
      /* The return statement when the function is aborted through an error. */
      RETURN lv_success_flag;
    END;
 
  /* Check whether the input value is something other than a null value. */
  IF pv_login_name IS NOT NULL THEN
 
    OPEN  authorize_cursor;
    FETCH authorize_cursor INTO lv_login_id, lv_group_id;
    CLOSE authorize_cursor;
 
    /* Set the success flag. */
    SET @sv_login_id := lv_login_id;
    SET @sv_group_id := lv_group_id;
 
    /* Check whether the session variables are set. */
    IF NOT ISNULL(@sv_login_id) AND @sv_login_id > 0 AND
       NOT ISNULL(@sv_group_id) AND @sv_group_id > 0 THEN
      SET lv_success_flag := 1;
    END IF;
 
  END IF;
 
  /* Return the success flag. */
  RETURN lv_success_flag;
END;
$$

CREATE FUNCTION set_login(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED BEGIN /* Declare a local variable to verify completion of the task: || ========================================================== || a. Default value is zero, which means false. || b. Non-default value is one, which means true. || ========================================================== */ DECLARE lv_success_flag INT UNSIGNED DEFAULT 0; /* Declare local variables to hold the return values from the cursor. */ DECLARE lv_login_id INT UNSIGNED; DECLARE lv_group_id INT UNSIGNED; /* Declare a condition variable for zero rows fetched, selected, or processed. */ DECLARE no_rows_fetched CONDITION FOR 1329; /* Declare a cursor to return an authorized user id. */ DECLARE authorize_cursor CURSOR FOR SELECT a.user_id , a.user_group_id FROM application_user a WHERE a.user_name = pv_login_name; /* Declare a handler for the cursor when it fails to return a row. */ DECLARE EXIT HANDLER FOR no_rows_fetched BEGIN /* The return statement when the function is aborted through an error. */ RETURN lv_success_flag; END; /* Check whether the input value is something other than a null value. */ IF pv_login_name IS NOT NULL THEN OPEN authorize_cursor; FETCH authorize_cursor INTO lv_login_id, lv_group_id; CLOSE authorize_cursor; /* Set the success flag. */ SET @sv_login_id := lv_login_id; SET @sv_group_id := lv_group_id; /* Check whether the session variables are set. */ IF NOT ISNULL(@sv_login_id) AND @sv_login_id > 0 AND NOT ISNULL(@sv_group_id) AND @sv_group_id > 0 THEN SET lv_success_flag := 1; END IF; END IF; /* Return the success flag. */ RETURN lv_success_flag; END; $$

1
2
3
4
5
6

CREATE FUNCTION get_login_id() RETURNS INT UNSIGNED
BEGIN
  /* Return the success flag. */
  RETURN @sv_login_id;
END;
$$

CREATE FUNCTION get_login_id() RETURNS INT UNSIGNED BEGIN /* Return the success flag. */ RETURN @sv_login_id; END; $$

1
2
3
4
5
6

CREATE FUNCTION get_group_id() RETURNS INT UNSIGNED
BEGIN
  /* Return the success flag. */
  RETURN @sv_group_id;
END;
$$

CREATE FUNCTION get_group_id() RETURNS INT UNSIGNED BEGIN /* Return the success flag. */ RETURN @sv_group_id; END; $$

CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au
WHERE   (au.user_group_id = 1
AND      au.user_group_id = get_group_id()
AND      au.user_id = get_login_id())
OR       get_group_id() = 2
OR      (get_group_id() > 2
AND      au.user_group_id = get_group_id());

CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au WHERE (au.user_group_id = 1 AND au.user_group_id = get_group_id() AND au.user_id = get_login_id()) OR get_group_id() = 2 OR (get_group_id() > 2 AND au.user_group_id = get_group_id());

CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au CROSS JOIN
        (SELECT   get_login_id() AS login_id
         ,        get_group_id() AS group_id) fq
WHERE   (au.user_group_id = 1
AND      au.user_group_id = fq.group_id
AND      au.user_id = fq.login_id)
OR       fq.group_id = 2
OR      (fq.group_id > 2
AND      au.user_group_id = fq.group_id);

CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au CROSS JOIN (SELECT get_login_id() AS login_id , get_group_id() AS group_id) fq WHERE (au.user_group_id = 1 AND au.user_group_id = fq.group_id AND au.user_id = fq.login_id) OR fq.group_id = 2 OR (fq.group_id > 2 AND au.user_group_id = fq.group_id);

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

CREATE VIEW function_query AS
SELECT   get_login_id() AS login_id
,        get_group_id() AS group_id;
 
CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au CROSS JOIN function_query fq
WHERE   (au.user_group_id = 1
AND      au.user_group_id = fq.group_id
AND      au.user_id = fq.login_id)
OR       fq.group_id = 2
OR      (fq.group_id > 2
AND      au.user_group_id = fq.group_id);

CREATE VIEW function_query AS SELECT get_login_id() AS login_id , get_group_id() AS group_id; CREATE VIEW authorized_user AS SELECT au.user_id , au.user_name , au.user_role , CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name FROM application_user au CROSS JOIN function_query fq WHERE (au.user_group_id = 1 AND au.user_group_id = fq.group_id AND au.user_id = fq.login_id) OR fq.group_id = 2 OR (fq.group_id > 2 AND au.user_group_id = fq.group_id);

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
82
83
84
85
86
87
88
89
90
91
92
93
94
95

<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
 
  // Process the input parameter, which should be through a $_POST argument.
  (isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
 
  // 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 dynamic function call.
    $query = "SELECT set_login(?)";
 
    // Attempt preparing statement.
    if (!$stmt = $mysqli->prepare($query)) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }     
    else {
 
      // Bind variable to SQL statement and execute it.
      $stmt->bind_param("s", $input);
      $stmt->execute();
      $stmt->close(); 
    }
 
    // Declare a static query.
    $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_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 Name</th><th class="Label">User Role</th><th class="Label">Full 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>

<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 // Process the input parameter, which should be through a $_POST argument. (isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = ''; // 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 dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $input); $stmt->execute(); $stmt->close(); } // Declare a static query. $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_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 Name</th><th class="Label">User Role</th><th class="Label">Full 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>

http://localhost/stripedquery1.php?user_name=potterhj

http://localhost/stripedquery1.php?user_name=potterhj

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

function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
    $stmt->execute();
    $stmt->close();
    $return = true;
  }
 
  // Return the string.
  return $return;
}

function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_name); $stmt->execute(); $stmt->close(); $return = true; } // Return the string. return $return; }

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

function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
    $stmt->execute();
    $stmt->close();
 
    // True only when query returns a row.
    if (get_login($mysqli)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_name); $stmt->execute(); $stmt->close(); // True only when query returns a row. if (get_login($mysqli)) { $return = true; } } // Return the string. return $return; }

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

function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }     
    else {
 
      // Fetch a row for processing.
      $result = $stmt->get_result();
      $row = $result->fetch_array(MYSQLI_NUM);
    }
 
    // Close the statement cursor.
    $stmt->close();
 
    // True only when query returns a row.
    if (!is_null($row[0]) && ($row[0] > 0)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

function set_login($mysqli, $user_name) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT set_login(?)"; // Attempt preparing statement. if (!$stmt = $mysqli->prepare($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Bind variable to SQL statement and execute it. $stmt->bind_param("s", $user_name); // Attempt query and exit with failure before processing. if (!$stmt->execute()) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Fetch a row for processing. $result = $stmt->get_result(); $row = $result->fetch_array(MYSQLI_NUM); } // Close the statement cursor. $stmt->close(); // True only when query returns a row. if (!is_null($row[0]) && ($row[0] > 0)) { $return = true; } } // Return the string. return $return; }

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

function get_login($mysqli) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT get_login_id()";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->query($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Fetch a SQL statement.
    $row = $stmt->fetch_row();
 
    // Close the statement.    
    $stmt->close();
 
    // True only when query returns a row.
    if (!is_null($row[0]) && ($row[0] > 0)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

function get_login($mysqli) { // Define return string. $return = false; // Declare a dynamic function call. $query = "SELECT get_login_id()"; // Attempt preparing statement. if (!$stmt = $mysqli->query($query)) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // Fetch a SQL statement. $row = $stmt->fetch_row(); // Close the statement. $stmt->close(); // True only when query returns a row. if (!is_null($row[0]) && ($row[0] > 0)) { $return = true; } } // Return the string. return $return; }

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

function get_authorized_user($mysqli) {
 
  // Define return string.
  $out = '';
 
  // Declare a static query.
  $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_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.
      $out .= '<table><tr><th class="ID">ID</th><th class="Label">User Name</th>'
      .       '<th class="Label">User Role</th><th class="Label">Full Name</th></tr>';
 
      // Fetch a row for processing.
      while( $row = $stmt->fetch_row() ) {
 
        // Print the opening HTML row tag.
        $out .= "<tr>";
 
        // Loop through the row's columns.
        for ($i = 0;$i < $mysqli->field_count;$i++) {
 
          // Handle column one differently.
          if ($i == 0)
            $out .= '<td class="ID">'.$row[$i]."</td>";
          else
            $out .= '<td class="Label">'.$row[$i]."</td>";
        }
 
        // Print the closing HTML row tag.
        $out .= "</tr>"; 
      }
    }
  } while( $mysqli->next_result());
 
  // Print the closing HTML table tag.
  $out .= "</table>"; 
 
  // Return an HTML table of the results.
  return $out;
}

function get_authorized_user($mysqli) { // Define return string. $out = ''; // Declare a static query. $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_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. $out .= '<table><tr><th class="ID">ID</th><th class="Label">User Name</th>' . '<th class="Label">User Role</th><th class="Label">Full Name</th></tr>'; // Fetch a row for processing. while( $row = $stmt->fetch_row() ) { // Print the opening HTML row tag. $out .= "<tr>"; // Loop through the row's columns. for ($i = 0;$i < $mysqli->field_count;$i++) { // Handle column one differently. if ($i == 0) $out .= '<td class="ID">'.$row[$i]."</td>"; else $out .= '<td class="Label">'.$row[$i]."</td>"; } // Print the closing HTML row tag. $out .= "</tr>"; } } } while( $mysqli->next_result()); // Print the closing HTML table tag. $out .= "</table>"; // Return an HTML table of the results. return $out; }

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

<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
 
  // Include the credentials file if omitted.
  include_once("striping.inc");
 
  // Process the input parameter, which should be through a $_POST argument.
  (isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
 
  // 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 {
 
    // After setting the session variable, get the striped view result. 
    if (set_login($mysqli,$input)) {
 
      // Print the authorized list.
      print get_authorized_user($mysqli);
 
    }
 
    // Release connection resource.
    $mysqli->close();
  }
?>
</script>
</body>
</html>

<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 // Include the credentials file if omitted. include_once("striping.inc"); // Process the input parameter, which should be through a $_POST argument. (isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = ''; // 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 { // After setting the session variable, get the striped view result. if (set_login($mysqli,$input)) { // Print the authorized list. print get_authorized_user($mysqli); } // Release connection resource. $mysqli->close(); } ?> </script> </body> </html>

<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>

<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>

<html>
<header>
<title>Static Query Procedural 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 = mysqli_connect("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 {
 
    // Initialize a statement in the scope of the connection.
    $stmt = mysqli_stmt_init($mysqli);
 
    // 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($mysqli,$query)) {
 
        // Print failure to resolve query message.
        print mysqli_error($stmt)."<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 = mysqli_fetch_row($stmt) ) {
 
          // Print the opening HTML row tag.
          print "<tr>";
 
          // Loop through the row's columns.
          for ($i = 0;$i < mysqli_field_count($mysqli);$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($mysqli));
 
    // Print the closing HTML table tag.
    print "</table>"; 
 
    // Free system resources.
    mysqli_stmt_free_result($stmt);
 
    // Release connection resource.
    mysqli_close($mysqli); 
  }
?>
</script>
</body>
</html>

<html> <header> <title>Static Query Procedural 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 = mysqli_connect("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 { // Initialize a statement in the scope of the connection. $stmt = mysqli_stmt_init($mysqli); // 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($mysqli,$query)) { // Print failure to resolve query message. print mysqli_error($stmt)."<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 = mysqli_fetch_row($stmt) ) { // Print the opening HTML row tag. print "<tr>"; // Loop through the row's columns. for ($i = 0;$i < mysqli_field_count($mysqli);$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($mysqli)); // Print the closing HTML table tag. print "</table>"; // Free system resources. mysqli_stmt_free_result($stmt); // Release connection resource. mysqli_close($mysqli); } ?> </script> </body> </html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT   t.transaction_account AS "Transaction"
,        LPAD(FORMAT
           (SUM
             (CASE
                WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                     EXTRACT(YEAR FROM transaction_date) = 2011 THEN
                  CASE
                    WHEN t.transaction_type = cl.common_lookup_type THEN
                      t.transaction_amount
                    ELSE
                      t.transaction_amount * -1
                  END
             END),2),10,' ') AS "JAN"
FROM     TRANSACTION t CROSS JOIN common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
AND      cl.common_lookup_type = 'DEBIT'
GROUP BY t.transaction_account;

SELECT t.transaction_account AS "Transaction" , LPAD(FORMAT (SUM (CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND EXTRACT(YEAR FROM transaction_date) = 2011 THEN CASE WHEN t.transaction_type = cl.common_lookup_type THEN t.transaction_amount ELSE t.transaction_amount * -1 END END),2),10,' ') AS "JAN" FROM TRANSACTION t CROSS JOIN common_lookup cl WHERE cl.common_lookup_table = 'TRANSACTION' AND cl.common_lookup_column = 'TRANSACTION_TYPE' AND cl.common_lookup_type = 'DEBIT' GROUP BY t.transaction_account;

mysql> SELECT @@version, @@sql_mode;
+-----------+----------------------------------------------------------------+
| @@version | @@sql_mode                                                     |
+-----------+----------------------------------------------------------------+
| 5.5.23    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------+----------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

mysql> SELECT @@version, @@sql_mode; +-----------+----------------------------------------------------------------+ | @@version | @@sql_mode | +-----------+----------------------------------------------------------------+ | 5.5.23 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)

ERROR 1630 (42000): FUNCTION studentdb.SUM does NOT exist. CHECK the 'Function Name Parsing and Resolution' SECTION IN the Reference Manual

ERROR 1630 (42000): FUNCTION studentdb.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT   t.transaction_account AS "Transaction"
,        LPAD(FORMAT
           (SUM(
              CASE
                WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                     EXTRACT(YEAR FROM transaction_date) = 2011 THEN
                  CASE
                    WHEN t.transaction_type = cl.common_lookup_type THEN
                      t.transaction_amount
                    ELSE
                      t.transaction_amount * -1
                  END
             END),2),10,' ') AS "JAN"
FROM     TRANSACTION t CROSS JOIN common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
AND      cl.common_lookup_type = 'DEBIT'
GROUP BY t.transaction_account;

SELECT t.transaction_account AS "Transaction" , LPAD(FORMAT (SUM( CASE WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND EXTRACT(YEAR FROM transaction_date) = 2011 THEN CASE WHEN t.transaction_type = cl.common_lookup_type THEN t.transaction_amount ELSE t.transaction_amount * -1 END END),2),10,' ') AS "JAN" FROM TRANSACTION t CROSS JOIN common_lookup cl WHERE cl.common_lookup_table = 'TRANSACTION' AND cl.common_lookup_column = 'TRANSACTION_TYPE' AND cl.common_lookup_type = 'DEBIT' GROUP BY t.transaction_account;

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',IGNORE_SPACE'));

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',IGNORE_SPACE'));