Archive for the ‘LAMP’ Category
Common Lookup Tables 2
Last October I posted an example and description of a common_lookup
table. It was intended to show how common_lookup
tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.
This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.
Let’s start with data stored in join between two tables – the member
and contact
tables. The internal lookup uses the customers name from the contact
table to find the membership account information in the member
table.
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT m.account_number , m.member_type -- A fk to common_lookup table. , m.credit_card_number , m.credit_card_type -- A fk to common_lookup table. , c.first_name , c.middle_name , c.last_name , c.contact_type -- A fk to common_lookup table. FROM member m INNER JOIN contact c ON m.member_id = c.member_id WHERE c.first_name = 'Harry' AND c.middle_name = 'James' AND c.last_name = 'Potter'\G |
It returns the results on the left, while a set of joins against the common_lookup
table returns the results on the right (both use the \G
in SQL Monitor to display the data vertically).
The member_type
, credit_card_type
, and contact_type
columns in the data set on the left hold foreign key values. They’re copies of values found in the primary key column of the common_lookup
table. You have the option of using these values to connect the data through a join or through function calls. A join requires three copies of the common_lookup
table and yields the data displayed on the right above. The query to get the meaningful business information from the common_lookup
table is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT m.account_number , cl1.common_lookup_meaning , m.credit_card_number , cl2.common_lookup_meaning , c.first_name , c.middle_name , c.last_name , cl3.common_lookup_meaning FROM member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN common_lookup cl1 ON cl1.common_lookup_id = m.member_type INNER JOIN common_lookup cl2 ON cl2.common_lookup_id = m.credit_card_type INNER JOIN common_lookup cl3 ON cl3.common_lookup_id = c.contact_type WHERE c.first_name = 'Harry' AND c.middle_name = 'James' AND c.last_name = 'Potter'\G |
The data returned from any query is symmetrical, which means all columns return the same number of rows. The results of the preceding query are the business results. Although, they’re not what you’d want to display in a web form that presents the ability to change values, like the member, credit card, or contact types. You need to get that information by using the foreign key as a parameter to a function call, and in this case three function calls. One for each of the foreign keys from the original query results. The result is an asymmetric collection of data would look like the following conceptually with Lily Luna Potter as the customer (note the green row is the symmetrical return set from the preceding query):
The f(x)
represents a generic function call where the x
substitutes for the foreign key value as a lookup key to the primary key value of the common_lookup
table. The function in this case is a query that returns a two column list. One column holds the primary key value of the (common_lookup_id
) and the other holds the customer facing description (from the common_lookup_meaning
column).
These steps describe the process:
- Use the natural key (the user name) to find the data in the
contact
table. - Use the
member_id
foreign key column in thecontact
table to link to the same name column primary key in themember
table by joining the two rows. - Use the foreign keys in the new row (combining columns from the
contact
andmember
tables) as call parameters to a PHP function that returns all possible foreign key values and their business descriptions in a web form.
The vertical choices displayed above map to OPTION
tag elements of an HTML SELECT
tag. The blue highlighted value contains an instruction, Select Type, in the display of an HTML OPTION
tag, and it holds a null as the value of the VALUE
attribute for the OPTION
tag. The other displayed rows are the possible values. The green highlighted value is the currently selected value and the yellow highlighted values are alternate possibilities for an end-user to select. The logic for that process is in the PHP get_common_lookup
function below.
Having discussed the structure of the data and problem, we’ll present the result in a dynamically constructed web form below. The display form shows a member account with customer information.
You should note that the primary and foreign keys aren’t displayed because they’re irrelevant to the business process. Primary and foreign keys only serve to support relationships when we use surrogate keys as the primary key of a table. Only the meaningful information from the common_lookup
table are displayed in the preceding form. Behind the web form, the primary and foreign key values are critical to maintaining anomaly free table data.
Each of the drop downs contains the full selection of possibilities from the common_lookup
table, and an end-user could choose a new value by clicking on any of the drop down fields. For example, the following displays the selection of a type of credit card:
The user can click on the drop down, and then navigate from the selection to a new selection. Assuming we don’t change anything, submitting the form should transmit the foreign key column values. The following shows that’s exactly what it does:
As you can see from the screen shot it works easily. Below is the PHP code for a MySQL credentials file and the get_lookup
function. The function lets you find an existing value or returns a set of unique values for you to choose from.
You should rename the following credentials.php
file as MySQLCredentials.inc
for it to work in the file below it.
Assuming you’ve implemented the credentials.php
file as the MySQLCredentials.inc
file, you can now implement the following file. The get_common_lookup
function returns a <SELECT>
tag with a list embedded of <OPTION>
tags with values; one <OPTION>
tag is selected when the foreign key matches a valid primary key value in the common_lookup
table; and no <OPTION>
tag is selected when the foreign key doesn’t match a a valid primary key value in the common_lookup
table. The last possibility means a user must choose a new valid value for the foreign key column when the foreign key column is constrained as a mandatory or not null column.
The code for the web form is a bit more complex, as shown below. It contains three separate calls to the modified get_common_lookup
function (on lines 104, 111, and 126). Each call to the get_common_lookup
function selects the list of possible values and highlights the value associated with the foreign key value.
Here’s the web form code. You should note that it only returns a single row of data from the query by using a natural key from the contact table.
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 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | <html> <header> <title>Select Option Sample</title> <style type="text/css"> /* Class tag element styles. */ .box {border:1px solid;padding:0px;width:392px;background-color:silver;} .bottomBox {border-left:1px solid;border-right:1px solid;border-bottom:1px solid;padding:5px;width:380px;background-color:silver;} .middleBox {border:1px solid;padding:5px;width:380px;background-color:silver;} .topBox {border-left:1px solid;border-right:1px solid;border-top:1px solid;padding:5px;width:380px;background-color:silver;} .button {margin:5px;background-color:lightblue;font-weight:bold;align:right;} .clear {clear:left;} .dropDown {min-width:50px;display:block;float:left;text-align:left;color:black;} .formDownLabel {width:90px;display:block;float:left;margin:5px;text-align:right;vertical-align:bottom;color:black;} .formAcrossLabel {width:80px;display:block;float:left;padding-bottom:0px;margin:5px 5px 0px;text-align:left;vertical-align:bottom;font-style:italic;font-size:90%;color:black;} .formInput {min-width:150px;margin:5px;text-align:left;} .formShortInput {width:80px;margin:5px;text-align:left;} .title1 {margin-left:0px;font-weight:bold;font-style:italic;font-size:125%;color:black;} .title2 {margin-left:5px;font-weight:bold;font-style:italic;font-size:105%;color:black;} </style> </header> <body> <?php // Include libraries. include_once("MySQLCredentials.inc"); include_once("library.inc"); // Define a HTML page string. $out = ''; // Declare input variables. $first_name = (isset($_GET['first_name'])) ? $_GET['first_name'] : $first_name = "Harry"; $middle_name = (isset($_GET['middle_name'])) ? $_GET['middle_name'] : $middle_name = "James"; $last_name = (isset($_GET['last_name'])) ? $_GET['last_name'] : $last_name = "Potter"; // Declare output variables. $member_account_number = null; $credit_card_number = null; // Declare lookup input and output (initialized as nulls to suppress warnings) variables. $member_table = 'member'; $member_type = 'member_type'; $member_type_id = null; $credit_card_type = 'credit_card_type'; $credit_card_type_id = null; $contact_table = 'contact'; $contact_type = 'contact_type'; $contact_type_id = null; // Assign credentials to connection. $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE); // Check for connection error and print message. if ($mysqli->connect_errno) { print $mysqli->connect_error."<br />"; print "Connection not established ...<br />"; } else { // Initial statement. $stmt = $mysqli->stmt_init(); // Declare a static query. $sql = "SELECT m.account_number\n" . ", m.member_type\n" . ", m.credit_card_number\n" . ", m.credit_card_type\n" . ", c.first_name\n" . ", c.middle_name\n" . ", c.last_name\n" . ", c.contact_type\n" . "FROM member m INNER JOIN contact c\n" . "ON m.member_id = c.member_id\n" . "WHERE c.first_name = ?\n" . "AND c.middle_name = ?\n" . "AND c.last_name = ?\n"; // Prepare statement. if ($stmt->prepare($sql)) { $stmt->bind_param("sss",$first_name,$middle_name,$last_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 { // This query only returns one row, and an empty block follows the while logic. $stmt->bind_result($member_account_number, $member_type_id, $credit_card_number, $credit_card_type_id, $first_name, $middle_name, $last_name, $contact_type_id); while ($stmt->fetch()) {} } } // Print the query form. $out .= '<form method="post" name="myForm" action="submitItemType.php">'; $out .= '<label class="title1">Membership Account Information</label><br />'; $out .= '<div class="box">'; $out .= '<div class="topBox">'; $out .= '<label class="formDownLabel">Account #</label><input class="formInput" type="text" value="'.$member_account_number.'" />'; $out .= '<select name="member_type" size="1" onChange="change(this.form.member_type)">'; // Get dynamic membership type lookup string fragment. $out .= get_common_lookup($member_table, $member_type, $member_type_id); $out .= '</select><br />'; $out .= '<label class="formDownLabel">Credit Card #</label><input class="formInput" type="text" value="'.$credit_card_number.'" />'; $out .= '<select name="credit_card_type" size="1" onChange="change(this.form.credit_card_type)">'; // Get dynamic credit card type lookup string fragment. $out .= get_common_lookup($member_table, $credit_card_type, $credit_card_type_id); // Print the closing HTML table tag. $out .= '</select><br />'; $out .= '</div>'; $out .= '<div class="middleBox">'; $out .= '<label class="formDownLabel"> </label>'; $out .= '<label class="formAcrossLabel">First Name</label><label class="formAcrossLabel">Middle Name</label><label class="formAcrossLabel">Last Name</label><br class="clear" />'; $out .= '<label class="formDownLabel">Customer</label><input class="formShortInput" type="text" value="'.$first_name.'" />'; $out .= '<input class="formShortInput" type="text" value="'.$middle_name.'" />'; $out .= '<input class="formShortInput" type="text" value="'.$last_name.'" /><br />'; $out .= '<label class="formDownLabel">Type</label>'; $out .= '<select style="margin:5px" name="contact_type" size="1" onChange="change(this.form.contact_type)">'; // Get dynamic membership type lookup string fragment. $out .= get_common_lookup($contact_table, $contact_type, $contact_type_id); $out .= '</select><br />'; $out .= '</div>'; $out .= '<div class="bottomBox">'; $out .= '<input class="button" style="margin-left:300px" name="submit" type="submit" value="Submit">'; $out .= '</div>'; $out .= '</form>'; $out .= '</body>'; $out .= '</html>'; print $out; ?> </body> </html> |
The submit button simply runs another web page that prints the actual values for the drop down selections. Here’s the code to print that:
1 2 3 4 5 6 7 8 9 10 11 12 | <html> <head> </head> <body> <?php // Print the surrogate key values for the common_lookup_id column. print "MEMBER_TYPE -> [".$_POST['member_type']."]<br />"; print "CREDIT_CARD_TYPE -> [".$_POST['credit_card_type']."]<br />"; print "CONTACT_TYPE -> [".$_POST['contact_type']."]<br />"; ?> </body> </html> |
I hope this helps those who are interested in sorting how to implement a common_lookup
table.
MySQL Workbench Scripts
It’s always interesting when somebody asks why they got an error message, and especially sweet when you’re working on something related that lets you answer the question. They were using MySQL Workbench and wanted to know why they couldn’t open a SQL script file by clicking on the Scripting menu option.
As I explained to the individual who asked, you should always click the Edit SQL Script link in the SQL Development section of the MySQL Workbench home page to work on SQL scripts. The Scripting menu option supports Python and Lua plug-ins development and scripts.
They did the following initially, which led down the rabbit warren and left them stumped because they don’t know anything about Python or Lua. This is provided to those who choose to experiment with this advanced feature of MySQL Workbench.
That presents you with a chooser dialog and it lets you pick any type of file. (You may wonder, as I did, why they didn’t restrict it to .py
and .lua
file extensions, which would preclude opening a .sql
file. I actually logged an enhancement request to see if the development team may agree with me.) You get the following message when you choose something other than a Python or Lua script. You can click on any of the reduced size screen shots to enlarge them and make them readable.
As you may note, the dialog says the activity is unsupported by provides no cancellation button. Click the OK button and the unsupported file is loaded into a tab that is useless. All you can do is click to close the tab and dismiss the window.
After you dismiss (by clicking the x) the non-editable .sql
file, you need to click on the Open Script file icon shown below.
This chooser really should open where the default is for the MySQL Workbench application script files but it doesn’t. It opens in the last accessed directory. You need to navigate to where your Python or Lua scripts are stored, which is the following directory on Windows:
C:\Users\<user_name>\AppData\Roaming\MySQL\Workbench\scripts |
Please note that on a Windows system you can’t chose this directory option because it’s protected. You must enter the navigation bar and type it. Then, you should see any scripts that you saved from within MySQL Workbench.
The ReadFile.py
below contains a rather simplistic and static program that reads a file and prints it to console (it’s small and fits in the screen). Obviously, it dispenses with a bunch to keep it small but check a Python website or book for the right way to manage a try block and handle exceptions.
Here’s the ReadFile.py
file shown in the preceding and next screen shots. For those new to Python, watch out because tabs aren’t equivalent to spaces. I made a change in the script below to display the trailing semicolon because one of my students asked about it.
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 |
Life’s funny, and you can never please everyone. The latest question, “Why did I choose to use substrings when suppressing line returns from the print()
function is easier?” Simple answer because the approach differs between Python 2.7 and 3.0 and I didn’t want this post to have a lot of Python nuance.
Python 2.7 (compatible with MySQL Workbench 5.2):
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 |
Python 3.0 (not-compatible with MySQL Workbench 5.2)
You should take note that both version require a print statement on line #8. Line #6 above shows that Python 2.7 uses a comma to suppress the line return, and below line #6 shows Python 3 requires you set end
equal to an empty string. Line #8 below also has a set of empty parentheses, which works in Python 3.x but not in Python 2.7. Python 2.7 would print the parentheses unless you put an empty string inside of them, like a print('')
statement.
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 |
Hopefully, everyone concurs the parsing was simpler than explaining all these Python nuances. Although, it’s nice somebody was so curious.
If your script complies with the Python 2.7 rules (that’s what is deployed in MySQL Workbench), click the lighting bolt and your code will run and display the results. That’s shown in the last screen shot.
If you’re interesting in developing plug-ins, check this summary page or this nice example of executing a query to text. Although, rumor has it that certain features may mature over the next year …
Naturally, I hope this helps those experimenting but personally it’s a cool advanced feature of the MySQL Workbench.
PHP/MySQL Query
Somebody wanted an example of how to handle column values using PHP to query a MySQL database. While I thought there were enough examples out there, they couldn’t find one that was code complete.
Well, here’s one that works using a static query. If you want to use a prepared statement, check this earlier post.
<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> |
It prints the following image:
While you shouldn’t embed CSS, I’ve done it to keep this as simple as possible. You can also use the procedural approach to the MySQLi library, like this:
<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> |
It produces the same output as the object oriented approach with one exception the title of the web page.
Hope this helps some folks.
Implicit Commit Functions?
Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT
, UPDATE
, and DELETE
. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT
statement or a Data Definition Language (DDL) statement (CREATE
, ALTER
, DROP
, or RENAME
) inside a MySQL function. The actual error message displayed is:
ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER. |
While an explicit COMMIT
is obvious when placed inside a function, the implicit COMMIT
statement isn’t obvious unless you know a DDL statement generates one. This means you can’t include any DDL statement inside a stored function.
The following example shows how a DDL statement creates an immediate implicit COMMIT
. It requires two sessions and Transaction Control Language (TCL) statements.
In session one, create a table, start a transaction scope, and insert one row into the table:
-- Create a table. CREATE TABLE message ( message_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , message_text VARCHAR(20)); -- Start a transaction context. BEGIN WORK; -- Insert a row into the MESSAGE table. INSERT INTO message (message_text) VALUES ('Implicit Commit?'); |
In session two, the empty set is displayed when you query the MESSAGE
table. Returning to session one, add a new column to the MESSAGE
table with this statement:
ALTER TABLE message ADD (sent DATETIME); |
The ALTER
statement automatically makes current any pending data changes, effectively committing the record from session one. You can return to the second session and query the table you get the following results:
+------------+------------------+------+ | message_id | message_text | sent | +------------+------------------+------+ | 1 | Implicit Commit? | NULL | +------------+------------------+------+ |
When you embed DML statements inside functions, they rely on automatic commit behaviors or an external transaction scope. This function compiles and returns 1 when successful and 0 when unsuccessful, which effectively controls all error conditions within the function:
CREATE FUNCTION modifies_data ( pv_message CHAR(20) ) RETURNS INT MODIFIES SQL DATA BEGIN /* Declare Boolean-like variables as FALSE. */ DECLARE lv_return_value INT DEFAULT FALSE; DECLARE lv_error_value INT DEFAULT FALSE; /* Declare a generic exit handler to reset error control variable to true. */ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET lv_error_value := TRUE; /* Insert statement with auto commit enabled. */ INSERT INTO message (message_text) VALUES (pv_message); /* True unless the CONTINUE HANDLER disables the error control variable. */ IF lv_error_value = FALSE THEN SET lv_return_value := TRUE; END IF; /* Return local variable. */ RETURN lv_return_value; END; $$ |
With auto commit enabled, any call to the function in a query writes a row to the table. If you start a transaction and in the scope of the transaction test the function in one session and query the table in another you’ll see that transaction control can be managed outside the function. You can also manage the transaction control inside a stored procedure, which holds all the TCL commands. An example follows:
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 | CREATE PROCEDURE test_modifies_data ( pv_message CHAR(20) ) MODIFIES SQL DATA BEGIN /* Declare a control variable that manages transaction success or failure. */ DECLARE lv_success_value INT DEFAULT FALSE; /* Start a transaction context. */ START TRANSACTION; /* Set a SAVEPOINT in the transaction context. */ SAVEPOINT before_transaction; /* Call the function. */ SET lv_success_value := modifies_data(pv_message); /* Check the status of the control variable, and commit or rollback the transaction. */ IF lv_success_value = TRUE THEN COMMIT; ELSE ROLLBACK TO before_transaction; END IF; END; $$ |
Test the two with the following call to the procedure:
CALL test_modifies_data('Agent Coulson'); |
Answers my question and I hope it helps others.
WebSockets Introduction
The topic of WebSockets came up today, and it seems a good idea to qualify WebSockets in the blog beyond simply pointing folks to Kaazing. HTML5’s WebSockets are a great fix for the half-duplex world of AJAX. They certainly have the potential to be more scalable than current Comet solutions, and present a real-time communication alternative. The latest draft of The WebSocket API was published earlier this month.
If you’re new to WebSockets, you may want to review Peter Lubbers’ PowerPoint presentation from the International Conference on Java Technology, 2010 (or the updated version he provided via a comment to this post). That is probably shorter than watching the multiple parts posted in 10-minute segments on YouTube.
More or less the PDF file of the presentation covers how HTTP is half-duplex not full duplex, and why it doesn’t support real-time data in a browser. It points to AJAX (Asynchronous JavaScript and XML) as a programming nightmare – or more precisely a workaround to a limitation of HTTP/HTTPS. It also covers the idea of COMET programming or frameworks as complex and incomplete attempts to simulate full duplex or bi-directional communication on the web.
As Peter Lubbers qualifies, AJAX and COMET solutions don’t scale against high transaction volumes or concurrency because their header traffic overwhelms the actual data transfers. This reality occurs more or less because browsers only implement unidirectional communication through a request and acknowledgement model and send large header sequences compared to small data footprints.
Peter Lubbers presents three types of HTTP solutions in the presentation:
- Polling involves periodic requests to the server for updated information, and it is the backbone of many Ajax applications that simulate real-time communication. The HTTP message headers involved in polling are frequently larger than the transmitted data; and while polling works well in low-message rate situations it doesn’t scale well. It also involves opening and closing many connections to the server and hence database needlessly in some cases.
- Long Polling or asynchronous-polling requests the server to keep the connection open for a set period of time. It doesn’t solve problems with high-message rates situations of polling in general because it creates a continuous loop of immediate polls and each poll, like ordinary polling messages, sends mostly HTTP headers not data.
- Streaming architecture opens a socket but can cause problems with proxy and firewall servers, create cross-domain issues due to browser connection limits, and periodically pose overhead to flush streams that have built up in the communication channel. Streaming solutions reduce HTTP header traffic but at a cost in overhead to the server.
Websockets are designed to fix these issues. The most interesting thing about polling, long polling, streaming, or Websockets is they require the same careful attention to how databases validate user access and serve up content. When the HTML5 standard nears completion, they’ll be a great need for database connection solutions, like Oracle’s Data Resident Connection Pooling.
By the way, here are some great video links for learning HTML5.
Fixing my.cnf on Fedora
Working with a Fedora 16 VM for my students (next term) and found that the MySQL Server’s my.cnf
file worked with a Linux socket as opposed to a listener port, and that several configuration options where missing from the file. Here’s the default /etc/my.cnf
file after the package installation from the Red Hat site:
[mysqld] # Settings user and group are ignored when systemd is used. # If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
Without rebuilding the log files, this seemed like the cleanest replacement for the MySQL Server my.cnf
for a development instance running on Fedora 16. If you’ve other suggestions, please let me know.
[mysqld] # Settings user and group are ignored when systemd is used. # If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd # Default directory. datadir=/var/lib/mysql # The TCP/IP Port the MySQL Server listens on. # ------------------------------------------------------------ # Find the machine's IP address with this command run as # the root user and use the port number specified in the # my.cnf file: # [root@localhost ~]# netstat -an | grep 3306 # ------------------------------------------------------------ bind-address=nnn.nnn.nnn.nnn port=3306 # The Linux Socket the MySQL Server uses when not using a listener. # socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # The default storage engine that will be used when creating new tables. default-storage-engine=INNODB # Set the SQL mode to strict. sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # Set the maximum number of connections. max_connections=100 # Set the number of open tables for all threads. table_cache=256 # Set the maximum size for internal (in-memory) temporary tables. tmp_table_size=26M # Set how many threads should be kept in a cache for reuse. thread_cache_size=8 # MyISAM configuration. myisam_max_sort_file_size=100G myisam_sort_buffer_size=52M key_buffer_size=36M read_rnd_buffer_size=256K sort_buffer_size=256K # InnoDB configuration. innodb_data_home_dir=/var/lib/mysql innodb_additional_mem_pool_size=2M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=1M innodb_buffer_pool_size=25M innodb_log_file_size=5M innodb_thread_concurrency=8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid |
As always, I hope this helps somebody.
MySQL and Java Tutorial
This demonstrates how to create an Java infrastructure for reading and writing large text files to a MySQL database. The example provides:
- A
FileIO.jar
library that lets you enter MySQL connection parameters through aJOptionPane
, and a customizedJFileChooser
to filter and read source files from the file system. - A
mysql-connector-java-3.1.14-bin.jar
file, which is MySQL’s library for JDBC communication with the MySQL Databases.
The steps to compiling and testing this code are qualified below:
- Download and install the Java Software Development Kit (JSDK) for Java 6.
- Create a
C:\JavaTest
folder on Windows, or a/JavaTest
directory from some mount point of your choice. - Download and position the
mysql-connector-java-3.1.14-bin.jar
andFileIO.jar
files in theJavaTest
directory. - Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:
set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH% set CLASSPATH=C:\JavaDev\Java6\mysql-connector-java-3.1.14-bin.jar;C:\JavaDev\Java6\FileIO.jar;. |
You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod
command as 755
.
- Copy the
WriteReadCLOBMysql.java
code from the bottom of this posting and also put it into theJavaTest
directory. - Compile the
WriteReadCLOBMysql.java
source code with thejavac
utility, as shown below:
javac WriteReadCLOBMysql.java |
After you compile it, you should run it as follows:
java WriteReadCLOBMysql |
- Before running the code, you’ll need to seed (
INSERT
) a row that meets the desired hard coded criteria. It requires anITEM_TITLE
value of'The Lord of the Rings - Fellowship of the Ring'
and anITEM_SUBTITLE
of'Widescreen Edition'
in theITEM
table. - When it runs, you’ll see the following tabbed
JOptionPane
.
You need to enter the following values before clicking the OK button:
- Host: The
localhost
key word, orhostname
of your physical machine running the database. - Port: The
port
that the MySQL Listener is running on (the default value is3306
). - Database: The Oracle TNS Alias, which is
sampledb
for the full database sample database. - UserID: The
user
name with permissions to the database entered that can access anITEM
table. - Password: The
password
for the user’s account.
In the JFileChooser
, select a file to upload to the database.
You should see what you uploaded displayed in a JFrame
.
Java Source Code Program ↓
The drop down unfolds the WriteReadCLOB.java
source code.
The following program has dependencies on the FileIO.jar file. You need to download it and put it in your $CLASSPATH
for Linux or Mac OS X or %CLASSPATH%
for Windows.
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 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 | // -------------------------------------------------------------------- // WriteReadCLOBMysql.java // by Michael McLaughlin // // This code demonstrates reading an image file and displaying // the image in a JLabel in a JFrame. // // The UPDATE and SELECT statements have dependencies on the // create_store.sql script. // -------------------------------------------------------------------- // Java Application class imports. import java.awt.Dimension; import java.awt.Font; import java.awt.GridLayout; import java.io.Reader; import javax.swing.JFrame; import javax.swing.JLabel; import javax.swing.JOptionPane; import javax.swing.JPanel; import javax.swing.JScrollPane; import javax.swing.JTextArea; // Generic JDBC imports. import java.sql.*; // Mysql JDBC import. import com.mysql.jdbc.Driver.*; import java.io.File; import java.sql.Connection; import java.sql.DriverManager; // Include book libraries (available at publisher website). import plsql.jdbc.DataConnectionPane; import plsql.fileio.FileIO; // -------------------------------------------------------------------/ public class WriteReadCLOBMysql extends JFrame { // Define database connections. private String host; private String port; private String dbname; private String userid; private String passwd; // Define data connection pane. private DataConnectionPane message = new DataConnectionPane(); // Construct the class. public WriteReadCLOBMysql (String s) { super(s); // Get database connection values or exit. if (JOptionPane.showConfirmDialog(this,message ,"Set Oracle Connection String Values" ,JOptionPane.OK_CANCEL_OPTION) == 0) { // Set class connection variables. host = message.getHost(); port = message.getPort(); dbname = message.getDatabase(); userid = message.getUserID(); passwd = message.getPassword(); // Print connection to console (debugging tool). message.getConnection(); // Create a JPanel for data display. ManageCLOB panel = new ManageCLOB(); // Configure the JPanel. panel.setOpaque(true); setContentPane(panel); // Configure the JFrame. setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); setLocation(100,100); pack(); setVisible(true); } else System.exit(1); } // -------------------------------------------------------------------/ private class ManageCLOB extends JPanel { // Define display variables. private String clobText; private JScrollPane scrollPane; private JTextArea textArea; // -----------------------------------------------------------------/ public ManageCLOB () { // Set layout manager. super(new GridLayout(1,0)); // Assign file read to String. clobText = FileIO.openFile(FileIO.findFile(this)); // Insert record before querying it. if (clobText.length() > 0) { if (insertClob(host,port,dbname,userid,passwd,clobText)) clobText = getQuery(host,port,dbname,userid,passwd); else clobText = null; } else System.exit(2); // Construct text area and format it. textArea = new JTextArea(clobText); textArea.setEditable(false); textArea.setFont(new Font(Font.SANS_SERIF,Font.PLAIN,14)); textArea.setLineWrap(true); textArea.setRows(10); textArea.setSize(400,100); textArea.setWrapStyleWord(true); // Put the image in container, and add label to panel. scrollPane = new JScrollPane(textArea); add(scrollPane); } // ---------------------------------------------------------------/ private Boolean insertClob(String host,String port,String dbname ,String user,String pswd,String fileString) { try { // Define connection. DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + dbname, user, pswd); // Create statement. PreparedStatement prest; String sql = "UPDATE item SET item_desc = ? WHERE item_title = 'The Lord of the Rings - Fellowship of the Ring' AND item_subtitle = 'Widescreen Edition'"; prest = conn.prepareStatement(sql); prest.setString(1,fileString); // Execute query. if (prest.execute()) conn.commit(); // Close resources. prest.close(); conn.close(); // Return CLOB as a String data type. return true; } // End of connection try-block. catch (SQLException e) { if (e.getSQLState() == null) { System.out.println( new SQLException("mysql Client Net8 Connection Error.", "mysql-" + e.getErrorCode() + ": Incorrect Net8 thin client arguments:\n\n" + " database name [" + dbname + "]\n", e.getErrorCode()).getSQLState()); // Return an empty String on error. return false; } else { System.out.println(e.getMessage()); // Return an empty String on error. return false; }}} // -----------------------------------------------------------------/ private String getQuery(String host,String port,String dbname ,String user,String pswd) { // Define method variables. char[] buffer; int count = 0; int length = 0; String data = null; String[] type; StringBuffer sb; try { // Define connection. DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + dbname, user, pswd); // Define metadata object. DatabaseMetaData dmd = conn.getMetaData(); // Create statement. Statement stmt = conn.createStatement(); // Execute query. ResultSet rset = stmt.executeQuery( "SELECT item_desc " + "FROM item " + "WHERE item_title = " + "'The Lord of the Rings - Fellowship of the Ring'"+ "AND item_subtitle = 'Widescreen Edition'"); // Get the query metadata, size array and assign column values. ResultSetMetaData rsmd = rset.getMetaData(); type = new String[rsmd.getColumnCount()]; for (int col = 0;col < rsmd.getColumnCount();col++) type[col] = rsmd.getColumnTypeName(col + 1); // Read rows and only CLOB data type columns. while (rset.next()) { for (int col = 0;col < rsmd.getColumnCount();col++) { if (type[col] == "CLOB") { // Assign result set to CLOB variable. Clob clob = rset.getClob(col + 1); // Check that it is not null and read the character stream. if (clob != null) { Reader is = clob.getCharacterStream(); // Initialize local variables. sb = new StringBuffer(); length = (int) clob.length(); // Check CLOB is not empty. if (length > 0) { // Initialize control structures to read stream. buffer = new char[length]; count = 0; // Read stream and append to StringBuffer. try { while ((count = is.read(buffer)) != -1) sb.append(buffer); // Assign StringBuffer to String. data = new String(sb); } catch (Exception e) {} } else data = (String) null; } else data = (String) null; } else { data = (String) rset.getObject(col + 1); }}} // Close resources. rset.close(); stmt.close(); conn.close(); // Return CLOB as a String data type. return data; } catch (SQLException e) { if (e.getSQLState() == null) { System.out.println( new SQLException("mysql Client Net8 Connection Error.", "mysql-" + e.getErrorCode() + ": Incorrect Net8 thin client arguments:\n\n" + " database name [" + dbname + "]\n", e.getErrorCode()).getSQLState()); // Return an empty String on error. return data; } else { System.out.println(e.getMessage()); return data; }} finally { if (data == null) System.exit(1); }}} // -----------------------------------------------------------------/ public static void main(String[] args) { // Define window. WriteReadCLOBMysql frame = new WriteReadCLOBMysql("Write & Read CLOB Text"); }} |
Configure Fedora on VMWare
It seems Fedora is always a bit of work. You begin by mistakenly downloading Fedora Live, which isn’t really the product but a run-time demonstration product. After finding the product, if you choose a full installation, there are post installation steps to complete. The first time you launch it in VMWare, you’ll see a Gnome 3 Failed to Load error dialog like this:
I suspected that installing VMWare Tools would fix that, and it did. However, your entry account doesn’t have “sudoers” permissions. You must add them before you can run VMWare Tools. There are six steps to enable your user with the sudoers permissions and four others to configure the standard installation:
- Navigate to the Applications menu choice in the upper left hand corner. You’ll get the following drop-down menu. Click on Other menu item to launch a dependent floating menu.
- The following floating menu displays to the right. Click on Users and Groups menu item at the bottom of the list.
- The choice from the floating menu prompts account validation. Enter your password and click the OK button.
- After validating your password, the User Manager dialog opens. Click on the single user that should be installed –
mclaughlinm
. Click the Properties button to change the groups assigned to the user.
- The User Properties dialog opens with the default User Data tab clicked. Click on the Groups tab to add the user to the
wheel
group as a property of your user.
- Scroll down through the list of groups and click the
wheel
group check box. Like the Mac OS, the wheel group provides “sudoer” privileges. Click the OK button to assign the group to the user.
- Navigate to the VMWare Menu, choose Virtual Machine and in the drop down menu Install VMWare Tools. This will mount a virtual CD in the Fedora virtual machine.
- Navigate to the Places menu choice and then Computer. Inside the Computer, choose the VMware Tools from the Devices section and you’ll see the following:
- Open a terminal session by choosing Applications, within the drop down choose System Tools, and then launch a Terminal session. You can then run the VMWare Toolkit by following these instructions:
cd /media/VMware\ Tools cp VMwareTools-8.4.7-416484.tar.gz /tmp cd /tmp gunzip VMwareTools-8.4.7-416484.tar.gz tar -xvf VMwareTools-8.4.7-416484.tar cd vmware-tools-distrib sudo ./vmware-install.pl |
The last step requires that you reply to a set of prompts. If you’d like to accept the default at one time, you can use the following command:
sudo ./vmware-install.pl --default |
If you find limited access to the system after installing or upgrading VMWare Tools, you may have packages in the caught between start and finish. You can clean them up with the following syntax, as the root
user:
sudo yum-complete-transaction |
- In the terminal session you should configure three files to make sure your networking works. I found that the dialogs failed to set one key element, so it’s simply easier to do this manually. Rather than using
sudo
, you should open aroot
shell with the following command:
sudo sh |
Enter your user’s password:
[sudo] password for mclaughlinm: |
You should use vi
to edit and save the resolv.conf
file with appropriate domain, search, and nameserver values. The values below work for VMWare when the gateway IP address is 172.16.123.2
.
# Generated by NetworkManager
domain localdomain
search localdomain
nameserver 172.16.123.2 |
Using vi
, edit the /etc/sysconfig/network
file to include an appropriate gateway IP address, like so:
NETWORKING=yes HOSTNAME=localhost.localdomain GATEWAY=172.16.123.2 |
The last file to fix is /etc/sysconfig/network-scripts/ifcfg-eth0
file. This is the file that isn’t completely configured by the GUI component (it fails set the ONBOOT
value to yes
).
DEVICE=eth0 HWADDR=00:0c:29:31:ef:46 ONBOOT=yes NM_CONTROLLED=yes BOOTPROTO=dhcp TYPE=Ethernet DNS1=172.16.123.2 USERCTL=no PEERDNS=yes IPV6INIT=no |
You reset networking with the following command:
/etc/rc.d/init.d/network restart |
As always, I hope this helps a few folks.
Create a synonym in MySQL
A friend wanted to know how to create a synonym to a library of stored functions and procedures in MySQL. I had to deliver the sad news that you can’t create a synonym in MySQL 5.0, 5.1, or 5.5. You need to write a wrapper function or procedure.
A synonym (available in Oracle) would eliminate the need to call a function from another database through the component selector (or period). That’s because it maps a name to the reference and name instead of requiring you to include it with each call. The following shows a call from outside of the lib
database:
SELECT lib.demo('Hello World!') AS "Statement"; |
He asked how to do it, so here’s how you do it below. Assume the following for this example:
- You have a
lib
database where you define all your functions, procedures, and tables. - You have a
app
database that supports web connections. - You don’t want to allow web users to connect directly to the
lib
database because access to the tables should only be available through the functions and procedures.
This base function is simplified to avoid interaction with table data but illustrate the technique of definer rights functions. The function takes a string of up to 20 characters and returns it enclosed in double quotes. To mimic these behaviors, as the root
user, you should create the app
and lib
databases, the dev
and web
users, and grant privileges to the dev
user to act in the lib
database and the web
user to act in the app
database. The example below does this as the root
user, but in real life don’t use a trivial password like the example:
/* Create the two databases. */ CREATE DATABASE app; CREATE DATABASE lib; /* Create the two users, the developer can only connect locally. */ CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev'; CREATE USER 'web'@'%' IDENTIFIED BY 'web'; /* Grant privileges to be a definer in both databases. */ GRANT ALL ON app.* TO 'dev'@'localhost'; GRANT ALL ON lib.* TO 'dev'@'localhost'; /* Grant privileges to any function or privilege in the APP database to the WEB user. */ GRANT EXECUTE ON app.* TO 'web'@'%'; |
After creating and granting all the appropriate privileges, here are the steps to create the test case.
- You create and test the function as the
dev
user in thelib
database.
/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */ DELIMITER $$ /* Create a function that echoes back the string with double quotes. */ CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20) BEGIN RETURN CONCAT('"',str,'"'); END; $$ /* Reset the DELIMITER value. */ DELIMITER ; /* Query the function. */ SELECT demo('Ciao amico!') AS "Statement"; |
- You create and test the wrapper function as the
dev
user in theapp
database.
/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */ DELIMITER $$ /* Create a function that echoes back the string with double quotes. */ CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20) BEGIN RETURN lib.demo(str); END; $$ /* Reset the DELIMITER value. */ DELIMITER ; /* Query the function. */ SELECT demo('Ciao amico!') AS "Statement"; |
If you’re wondering why a GRANT
wasn’t required from the lib
database to the app
database, it’s because the dev
user has access to both databases and defined both objects.
- You can test the wrapper function as the
web
user in theapp
database.
SELECT demo('Yes, it works!') AS "Statement"; |
This is the closest to a synonym for a function or procedure that is possible. I know this solves his problem and hope it solves a couple others too.
PHP leveraging PL/SQL
Somebody wanted another example of how to leverage a true/false condition from a PL/SQL stored function in PHP. The first key is that you write the function as if you were using it in SQL not PL/SQL. That means you return a NUMBER
data type not a PL/SQL-only BOOLEAN
data type.
Here’s the schema-level PL/SQL function:
CREATE OR REPLACE FUNCTION like_boolean ( a NUMBER, b NUMBER ) RETURN NUMBER IS /* Declare default false return value. */ lv_return_value NUMBER := 0; BEGIN /* Compare numbers and return true for a match. */ IF a = b THEN lv_return_value := 1; END IF; /* Return value. */ RETURN lv_return_value; END; / |
Here’s the PHP that leverages the PL/SQL in an if-statement on line #24:
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 | <?php // Capture local variables when provided. $thingOne = (isset($_GET['thingOne'])) ? $_GET['thingOne'] : 1; $thingTwo = (isset($_GET['thingTwo'])) ? $_GET['thingTwo'] : 1; // Open a connection. if(!$c = oci_connect("student","student","localhost/xe")) { die; } else { // Parse a statement. $s = oci_parse($c,"BEGIN :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo); END;"); // Bind input and output values to the statement. oci_bind_by_name($s,":returnValue",$returnValue); oci_bind_by_name($s,":thingOne",$thingOne); oci_bind_by_name($s,":thingTwo",$thingTwo); // Execute the statement. if (@oci_execute($s)) { // Print lead in string. print "[".$thingOne."] and [".$thingTwo."] "; if ($returnValue) print "are equal.<br />"; else print "aren't equal.<br />"; } // Clean up resources. oci_close($c); } ?> |
If you run into a parsing error, which is infrequent now. You can wrap the multiple row PL/SQL anonymous block call with this function. It strips tabs and line returns. Alternatively, you can put all the lines of PL/SQL on a single line.
// Strip special characters, like carriage or line returns and tabs. function strip_special_characters($str) { $out = ""; for ($i = 0;$i < strlen($str);$i++) if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) && (ord($str[$i]) != 13)) $out .= $str[$i]; // Return pre-parsed SQL statement. return $out; } |
If you run into a parsing problem on Oracle XE 10g, you can wrap the PL/SQL call like the following. Alternatively, you can place the entire anonymous PL/SQL block on a single line without embedded tabs or return keys..
10 11 12 13 | $s = oci_parse($c,strip_special_characters( "BEGIN :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo); END;")); |
Hope that answers the question and helps some folks.