[adrotate group=”3,7,9″]
We have recently had to look at the LOB data contained within a database while working with a prospect and having never done this before thought we would share the experience. The prospect has a large number of LOB fields in their database and is looking at the Easycom server as a possible solution so they asked us to help resolve a couple of issues they were having.
There first problem was the query would return no data at all, this was because they did not set the commitment control level as we had mentioned previously when we did the tests for the Display_Journal UDTF, that was a pretty easy fix and after a slight change to their code the query would run and the data would be returned to the script. That is all except for the content of the LOB columns, this would just return a garbled value or end the data display at that point. This is because the data returned by the query for an LOB column is just a pointer to the actual data which can be stored outside of the database. So we then had to run the i5_getblob() function to get the content of the LOB column. Unfortunately the sample code was wrong in the documentation so a call into Aura support put us back on the right track and we can now retrieve the LOB data without any problems. As it turns out the prototype for the function call is correct but the sample code is not.
Here is the test we set up to test the LOB support in Easycom. First we created a file with LOB columns defined, we did have a slight problem because the default journal was not defined in our library but after building the journal manually and journaling the file we saw no other issues. I created this in a STRSQL session I notice that as soon as the first file was journaled in the library consequent creates did not return the same journal issue?
CREATE TABLE CHLIB/TEST (
USERNAME VARCHAR(100) DEFAULT NULL ,
SYSNAME VARCHAR(255) DEFAULT NULL ,
TITLE VARCHAR(100) DEFAULT NULL ,
TEST_VALUE CLOB(1048576) DEFAULT NULL ,
ID NUMERIC(5, 0) DEFAULT NULL ,
YEAR NUMERIC(4, 0) DEFAULT NULL ,
TEMP_ID BIGINT DEFAULT NULL ,
TEST_VARCHAR VARCHAR(5000) DEFAULT NULL )
Next we just wanted to insert a single record so it could be tested. Again I used a STRSQL session to insert the record using this request.
INSERT INTO CHLIB/TEST
(username,sysname,title,test_value,test_varchar) VALUES
('admin1','shield3','boss','some clob text','Some varchar text')
I have a test page that I use for all of my tests that requires a signon to be carried out first and then call what ever functions I am testing. This is the function I wrote to test the LOB data returned.
function get_lob(&$conn) {
// set the transaction level for lob's
if(!i5_transaction(I5_ISOLEVEL_CHG, $conn)) {
echo("Failed to set transaction level CHG");
}
// build the query
$query = "SELECT TITLE,TEST_VALUE,SYSNAME FROM CHLIB/TEST";
// get the record
$result = i5_query($query,$conn);
if(!$result){
echo("Failed to get the data
" .$query);
$_SESSION['ErrMsg'] = "Error code: " .i5_errno($result) ." Error message: " .i5_errormsg($result);
}
$rec = i5_fetch_assoc($result);
// dump out the record data
var_dump($rec);
// get the LOB data and show
$lob_data = i5_getblob($result, 'TEST_VALUE');
echo("
LOB Data = " .$lob_data ."
");
// need to roll back to allow the transaction to be closed
i5_rollback($conn);
// close the transaction
if(!i5_transaction(I5_ISOLEVEL_NONE, $conn)) {
echo("Failed to set transaction level NONE");
}
// free the resources
i5_free_query($query);
}
Here is the output of the above
array(3) { [“TITLE”]=> string(4) “boss” [“TEST_VALUE”]=> int(256) [“SYSNAME”]=> string(7) “shield3” }
Blob Data = some clob text
As you can see the dump of the $rec variable shows an int(256), if you ran the query in a STRSQL session you would see *POINTER for the data in this field, iNavigator SQL will resolve the pointer automatically and display the actual data immediately. Running the i5_getblob() function correctly returns the content of the pointer, you should note that you do not need to pass in $rec[‘TEST_VALUE’] just ‘TEST_VALUE’ for the function to work correctly.
And that was our first foray into LOB columns in DB2… Hope you found some useful information from this.
Happy PHP’ing
Chris…