Have you ever wanted to run a prepare and execute select sql statement that you have in PHP in IBM i navigator’s “Run SQL”?

take for example this PHP:

<?
$options = array('i5_lib' => 'MYLIB' ,'i5_libl' => 'MYLIB', 'i5_naming' => DB2_I5_NAMING_ON,'autocommit' => DB2_AUTOCOMMIT_OFF, 'cursor' => DB2_SCROLLABLE);
$db2Connection = db2_connect('DBNAME','USER', 'PASSWORD', $options);
if (!$db2Connection) { echo "false – Connection failed.";exit(); }
$sql_query="SELECT ALBUM.*
FROM ALBUM
WHERE ID = ?";
$sql_statement = db2_prepare($db2Connection, $sql_query);
if($sql_statement ===false) {echo 'Prepare Failed – Error: ' . db2_stmt_error() . "|".db2_stmt_errormsg() ;}
$ID=1;
db2_bind_param($sql_statement, 1, "ID", DB2_PARAM_IN);
db2_execute($sql_statement);
$row = db2_fetch_assoc($sql_statement);
var_dump($row);

view raw
db2_prepare_execute_select_query.php
hosted with ❤ by GitHub

This is how you can create the Album table and then create a stored procedure to test the SQL select prepared statement

CREATE OR REPLACE PROCEDURE MYLIB.Album_Fetch_By_ID (IN ParameterID INTEGER)
DYNAMIC RESULT SETS 1
SET OPTION ALWBLK = *ALLREAD
BEGIN
DECLARE sql_statement VARCHAR(1000);
DECLARE sql_query VARCHAR(1000);
DECLARE ibmicmd_addlible_mylib CHAR ( 300 ) ;
DECLARE sql_exception_continue_message CHAR(20);
DECLARE cursor_with_resultset CURSOR FOR sql_statement;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET sql_exception_continue_message = 'ok';
CALL QSYS . QCMDEXC ('ADDLIBLE MYLIB' ) ;
SET sql_query = ' SELECT ALBUM.*
FROM ALBUM
WHERE ID = ?' ;
PREPARE sql_statement FROM sql_query ;
OPEN cursor_with_resultset USING ParameterID;
RETURN ;
END;
CALL MYLIB.Album_Fetch_By_ID(4);

view raw
A SQL SELECT PREPARE and EXECUTE (OPEN CURSOR).sql
hosted with ❤ by GitHub

/* Creating table MYLIB.ALBUM */
DROP Table MYLIB.ALBUM;
CREATE TABLE MYLIB.ALBUM (
ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO ORDER, NO CYCLE, NO MINVALUE, NO MAXVALUE, CACHE 20) NOT HIDDEN ,
ARTIST VARCHAR (100) NOT NULL NOT HIDDEN ,
TITLE VARCHAR (100) NOT NULL NOT HIDDEN ,
PRICE DECIMAL(5, 0) NOT NULL NOT HIDDEN ,
PRIMARY KEY (ID)
) NOT VOLATILE ;
LABEL ON TABLE MYLIB.ALBUM IS 'Test table for ZF2' ;
INSERT INTO MYLIB.ALBUM (ARTIST, TITLE, PRICE)
VALUES ('The Military Wives', 'In My Dreams',1);
INSERT INTO MYLIB.ALBUM (ARTIST, TITLE, PRICE)
VALUES ('Adele', '21',2);
INSERT INTO MYLIB.ALBUM (ARTIST, TITLE, PRICE)
VALUES ('Bruce Springsteen', 'Wrecking Ball (Deluxe)',3);
INSERT INTO MYLIB.ALBUM (ARTIST, TITLE, PRICE)
VALUES ('Lana Del Rey', 'Born To Die',4);
INSERT INTO MYLIB.ALBUM (ARTIST, TITLE, PRICE)
VALUES ('Gotye', 'Making Mirrors',5);
GRANT SELECT,INSERT,UPDATE,DELETE ON MYLIB.ALBUM TO MYUSER;
SELECT * FROM MYLIB.ALBUM;

view raw
Create Table Script.sql
hosted with ❤ by GitHub