Android Parcelable Example

http://prasanta-paul.blogspot.com/2010/06/android-parcelable-example.html

PHP dependency injection

http://www.potstuck.com/2009/01/08/php-dependency-injection/

MS Office 2007 Bulgarian Proofing Tools

http://www.kaldata.com/forums/index.php?showtopic=44797

Itzhak Perlman plays Klezmer

MySQL stored procedure examples

MySQL stored procedure, examle 1:
- check for valid input parameter
- declare handler for db error (1062 => duplicate key)
- insert data
- return status

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');

MySQL stored procedure, example 2:
- optional p_search_criteria parameter; not used if NULL, but used to search with %LIKE% if not NULL
- pagination with prepared statement, because LIMIT cannot use input parameters directly
- return result from query directly

DROP PROCEDURE IF EXISTS test_proc_2;

DELIMITER $$
CREATE PROCEDURE test_proc_2 (
        IN p_search_criteria VARCHAR(100),
        IN p_page INT,
        IN p_page_size INT
    )
BEGIN
  main: BEGIN

    IF p_page IS NULL OR p_page < 1 THEN
        SET p_page = 0;
    ELSE
        SET p_page = p_page - 1;
    END IF;

    IF p_page_size IS NULL OR p_page_size < 1 THEN
        SET p_page_size = 20;
    END IF;

    PREPARE STMT FROM 'SELECT * FROM test_table 
                       WHERE ? IS NULL OR test_column_1 LIKE ? OR test_column_2 LIKE ? 
                       LIMIT ?, ?';
    SET @c1 = p_search_criteria;
    SET @c2 = CONCAT('%', p_search_criteria, '%');
    SET @start = p_page * p_page_size;
    SET @size = p_page_size;

    EXECUTE STMT USING @c1, @c2, @c2, @start, @size;
    DEALLOCATE PREPARE STMT;
  END main;
END;
$$
DELIMITER ;

# CALL test_proc_2('searched text', 1, 10);

PHP PDO with MySQL stored procedures - problem with selecting OUT parameter

One day lost in discovering how to call and afterwards get stored procedure OUT parameters, finally resolved.

Stored Procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `mydb`.`proc_name` $$
CREATE PROCEDURE `mydb`.`proc_name`(
    IN i_myInput INT,
    OUT o_statusCode INT,
    OUT o_statusMsg VARCHAR(255))
BEGIN
    IF i_myInput IS NULL THEN
        SET o_statusCode = 1;
        SET o_statusMsg  = 'Error: input parameter is NULL';
    ElSE
        SET o_statusCode = 0;
        SET o_statusMsg  = 'OK';
    END IF;

    SELECT NULL as `result`;
END $$

DELIMITER ;

PHP Code:

# create PDO DB object
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");

$input = 5;
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();
$proc->closeCursor(); # !!!!! the line that resolved the issue

$output = $mydb->query("select @o_code, @o_message")->fetch(PDO::FETCH_ASSOC);
var_dump($output); // array('@o_code'=>'0', 'o_message'=>'OK')

LESZEK MOŻDŻER / LARS DANIELSSON / ZOHAR FRESCO