Stored Procedure Example 1

• check for valid input parameter
• declare handler for db error (1062 => duplicate key)
• insert data
• return status

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

DROP PROCEDURE IF EXISTS test_proc;

DELIMITER $$
CREATE PROCEDURE test_proc (
        IN p_test_id INT,
        IN p_test_string VARCHAR(255)
    )
BEGIN
  main: BEGIN

    # declare handler when duplicate key db error is thrown
    DECLARE EXIT HANDLER FOR 1062 BEGIN
        SELECT 1 AS status_code, 'Duplicate entry for input key values' AS status_message;
    END;

    # check if input parameter is NULL
    IF p_test_id IS NULL OR p_test_id < 1 THEN
        SELECT 2 AS status_code, 'Missing or invalid p_test_id parameter' AS status_message;
        LEAVE main;
    END IF;

    INSERT INTO test_table
    SET test_id = p_test_id,
        test_string = p_test_string;    

    SELECT 0 AS status_code, 'OK' AS status_message;
  END main;
END; 
$$
DELIMITER ;

# CALL test_proc(10, 'test string parameter');

I had to use a MySQL stored procedure written by another team, so here I don’t question the design of the procedure in any way.

My task is to call the procedure and get the OUT parameters after the execution has completed. The call is made with PHP PDO.

This post shows an empty basic game loop in Android, similar to how we used to make game loops on the J2ME platform.

The View implements Runnable and starts a render thread itself. An interesting thing is that we can’t invalidate the View from its own thread, as invalidate must be called from the main UI thread only. Instead we use postInvalidate which sends a message to the UI thread’s message queue. The consequences are that drawing is not performed immediately, but only when the UI thread handles the message.

It’s a little bit tricky to parse and use XML data when tags have different namespaces. We would have to use the namespace string when accessing the objects like below with $dc_namespaces:

XML with two different namespaces

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

<!-- xmldata.xml --> <rootnode xmlns="http://mydomain.com/2010"> <node> <title>This is the first Node</title> <meta> <dc:language>en</dc:language> </meta> </node> <node> <title>This is the second Node</title> <meta> <dc:language>en</dc:language> </meta> </node> </rootnode>

PHP code to access the parsed object:

1
2
3
4
5
6
7
8
9

<?php
$dc_namespace = 'http://purl.org/dc/elements/1.1/';

$data = simplexml_load_file('xmldata.xml');
foreach($data as $node)
{
    $title = $node->title;
    $lang  = $node->meta->children($dc_namespace)->language;
}

A simple example for creating a jQuery function called setPosition which can be used to set the top and left CSS properties of an element.

1
2
3
4
5
6

jQuery.fn.setPosition = function(x, y) {
   this.css("position", "absolute");
   this.css("top", y);
   this.css("left", x);
   return this;
}

The function can be used like this:

1

$('#myid').setPosition(100, 200);

Below is the authenticated request we want to build. It carries a username and password authentication header.

SOAP XML request

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

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <SOAP-ENV:Header> <m:SOAPAuthenticationHeader xmlns:m="http://www.mydomain.com"> <m:username>myusername</m:username> <m:password>mypassword</m:password> </m:SOAPAuthenticationHeader> </SOAP-ENV:Header> <SOAP-ENV:Body> <m:WebServiceMethodName xmlns:m="http://www.mydomain.com"> <m:param1>some value</m:param1> <m:param2>some value</m:param2> <m:param3>some value</m:param3> </m:WebServiceMethodName> </SOAP-ENV:Body> </SOAP-ENV:Envelope>

tnsnames.ora configuration excerpt:

1
2
3
4
5

MYSERVICE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = database_hostname_or_ip.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME=myservice)))

Connect and execute a prepared statement from PHP code.

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

<?php
$oracledb["host"] = "MYSERVICE"; # service name in the tnsnames.ora file
$oracledb["user"] = "myuser"; # username
$oracledb["pass"] = "mypass"; # password
$oracledb["library"] = "OCI";

$connect_id = ocilogon($oracledb["user"], $oracledb["pass"], $oracledb["host"]);

$query = "SELECT * FROM table";
$statement = ociparse($connect_id, $query);
ociexecute($statement);

$result = array();
while(ocifetchinto($statement, $tmp, OCI_ASSOC + OCI_RETURN_NULLS + OCI_RETURN_LOBS))
{
  array_push($result, $tmp);
}

ocifreestatement($statement);

var_dump($result); # result is here

“When you see the right thing, do it — this may look like more work in the short term, but it’s the path of least effort in the long run. If you don’t know what the right thing is, do the minimum necessary to get the job done, at least until you figure out what the right thing is. To do the Unix philosophy right, you have to be loyal to excellence. You have to believe that software design is a craft worth all the intelligence, creativity, and passion you can muster. Otherwise you won’t look past the easy, stereotyped ways of approaching design and implementation; you’ll rush into coding when you should be thinking. You’ll carelessly complicate when you should be relentlessly simplifying — and then you’ll wonder why your code bloats and debugging is so hard. To do the Unix philosophy right, you have to value your own time enough never to waste it. If someone has already solved a problem once, don’t let pride or politics suck you into solving it a second time rather than re-using. And never work harder than you have to; work smarter instead, and save the extra effort for when you need it. Lean on your tools and automate everything you can.

Software design and implementation should be a joyous art, a kind of high-level play. If this attitude seems preposterous or vaguely embarrassing to you, stop and think; ask yourself what you’ve forgotten. Why do you design software instead of doing something else to make money or pass the time? You must have thought software was worthy of your passion once…. To do the Unix philosophy right, you need to have (or recover) that attitude. You need to care. You need to play. You need to be willing to explore.”