GetSPLF failing to perform the CPYSPLF function

General discussion on Zend Server for IBM System i
scottgcampbell
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: GetSPLF failing to perform the CPYSPLF function

Post by scottgcampbell » Fri May 29, 2015 6:50 pm

Yes, that is the one change I have in ReadSPLFData

Changed:

Code: Select all

$stmt = "SELECT ZSF255 FROM {$this->TmpLib}{$schemaSep}{$this->TMPFName} FOR FETCH ONLY;";
to

Code: Select all

$stmt = "SELECT CAST(ZSF255 AS CHAR(255) CCSID 37) FROM {$this->TmpLib}{$schemaSep}{$this->TMPFName} ";
But either way I get results, just with the first one it is "garbage" .

It is almost like the executeQuery isn't seeing the data because of an uncommited transaction (from the copy)..., but I don't know how you would debug that, or figure out if that is what it is. Maybe if you get it to do the copy and leave the file and then in a different script (different PC/browser/connecting user maybe) do the query of the file (not using Db2supp) to see if you can see the data.

Could also try removing DB2_SCROLLABLE from executeQuery also since it isn't doing anything but reading the entire file in order.

Scott

shelfos
Posts: 15
Joined: Fri Sep 30, 2011 8:38 pm

Re: GetSPLF failing to perform the CPYSPLF function

Post by shelfos » Fri May 29, 2015 9:43 pm

So, just a quick followup.

Casting the results to CCSID(37) had the following effects:
1.) When using db2_exec() in the Db2supp.php executeQuery() function, I still get 0 results.
2.) When I bypass the Db2supp.php executeQuery() function and use the db2_execute() function instead, I get results! Yea!

I still think there is something underlying this, like a setting in the conf file to make everything CCSID(37) - sorry, don't know much about that, but it seems like something that it could be.

But at least for now I've got my module back up and running.

Thank you for your help and input.

Sheldon

greg7308
Posts: 23
Joined: Fri Mar 28, 2014 1:28 am

Re: GetSPLF failing to perform the CPYSPLF function

Post by greg7308 » Wed Jul 22, 2015 3:20 am

Hi Sheldon, I am still having issues with this ... also recently upgraded to the lates release. same deal.

Where exactly did you change the xecuteQuery() to db2_execute() ?

Cheers
Greg

shelfos
Posts: 15
Joined: Fri Sep 30, 2011 8:38 pm

Re: GetSPLF failing to perform the CPYSPLF function

Post by shelfos » Wed Jul 22, 2015 5:28 pm

In my shop we rolled our own function called runQuery() which uses db2_execute, so the ReadSPLFData() function in iToolkitService.php now reads as follows:

Code: Select all

private function ReadSPLFData()
{
	$Txt='';
    
	// old code
	//$stmt = "SELECT ZSF255 FROM {$this->TmpLib}.{$this->TMPFName} FOR FETCH ONLY";
    
	// new code to retrieve schema separator, which will vary (. or /) according to the naming mode
	// and also to cast the field as ccsid 37
	$schemaSep = $this->ToolkitSrvObj->getOption('schemaSep');
	$stmt = "SELECT CAST(ZSF255 AS CHAR(255) CCSID 37) AS ZSF255 FROM {$this->TmpLib}{$schemaSep}{$this->TMPFName} FOR FETCH ONLY";

	try{
		//$Txt = $this->ToolkitSrvObj->executeQuery( $stmt );
		
		//use the following from mainClass instead of the Toolkit executeQuery() function (for some reason it doesn't work here anymore - always zero results)
		$results = mainClass()->runQuery(array(
			'QUERY'			=> $stmt,
			'DEBUG_QUERY'	=> false
		));

		foreach($results as $line) {
			$Txt[] = $line['ZSF255'];
		}
	}
	catch(Exception $e){
		$this->setError("ReadSPLFData() error:".   $e->getMessage());
	}

	return $Txt;
}
Our runQuery() function is in one of our main classes that we use throughout our whole project and is around 250 lines of code. We use it to streamline and standardize running all of our queries - it also handles calling stored procedures with multiple result sets, debugging, some standard data formatting, etc. At 250 lines it's not huge, but for purposes to keep it simple here I've pieced out the most basic, pertinent parts. You'll need to modify it as needed or just use the code body in place of the runQuery() function above.

Code: Select all

function runQuery($params = array(
	'QUERY'		=> '',
	'DEBUG_QUERY'	=> false
) {
	$options = array (
		'i5_naming'	=> DB2_I5_NAMING_ON,
		'autocommit'	=> DB2_AUTOCOMMIT_ON
	);

	$db2Connection = db2_connect("*LOCAL", $user, $password, $options);
	$statement = db2_prepare($db2Connection, $params['QUERY']);
	$queryStatus = db2_execute($statement);

	$results = array();
	if ($queryStatus !== false) {
		while ($row = db2_fetch_assoc($statement)) {
			array_push($results, $row);
		}
	}

	return $results;
}

greg7308
Posts: 23
Joined: Fri Mar 28, 2014 1:28 am

Re: GetSPLF failing to perform the CPYSPLF function

Post by greg7308 » Thu Jul 23, 2015 6:11 am

Thanks Sheldon - awesome info there.

I tried that but it still failed, but I did manage to finally get an error that shows a CCSID conversion issue from code page 65535.

I totally gave up on GETSPLF in the end, and wrote my own ... just using the CLCOMMAND from XTOOLKIT to copy the splf to a temp PF and then DB2 to connect and read it.

Bit frustrating but at least it's working now.

Cheers
Greg

Post Reply