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); |
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); |
/* 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; |