Set command in script does not work on Pervasive V11

[DWest1965]

CREATE FUNCTION StatuteconcatA char(4), :B char(5), :C char(25))
RETURNS char(34)
AS
BEGIN

DECLARE :chapter char(4)
DECLARE :act char(4)
DECLARE :workspace char(4)
DECLARE :workspac2 char(4)
DECLARE :workspac3 char(5)
DECLARE :actdec char(1)
DECLARE :smallest char(34)

DECLARE :vInteger Int
DECLARE :vIntege2 Int
DECLARE :flag Int
DECLARE :fla2 Int


SET :workspace = LEFTB, 4)
SET :flag = 0;
SET :fla2 = 0;
SET :vInteger = 1;
SET :vIntege2 = 1;
SET :workspac2 = '';
SET :workspac3 = '';


TestLoop:
LOOP

IF vInteger > 4) THEN
LEAVE TestLoop;
END IF;

SET :workspace = SUBSTRINGA, :vInteger, 1);
IF workspace = 0 and :flag = 0) THEN
SET :workspac2 = :workspac2 + '';
ELSE
SET :flag = 1;
SET :workspac2 = :workspac2 + :workspace;
END IF;
SET :vInteger = :vInteger + 1;
IF vInteger = 5 and :workspac2 > '') THEN
SET :workspac2 = :workspac2 + '-';
END IF;

END LOOP;

SET :vInteger = 1;


TestLoop:
LOOP

IF vInteger > 4) THEN
LEAVE TestLoop;
END IF;

SET :workspace = SUBSTRINGB, :vInteger, 1);
IF workspace = 0 and :fla2 = 0) THEN
SET :workspac3 = :workspac3 + '';
ELSE
SET :fla2 = 1;
SET :workspac3 = :workspac3 + :workspace;
END IF;
IF vInteger = 4 and :workspac3 > '') THEN
SET :workspac3 = :workspac3 + '/';
END IF;
SET :vInteger = :vInteger + 1;

END LOOP;

SET :smallest = :workspac2 + :workspac3 + :C;
RETURN :smallest;

END;

This user defined function works fine using version 9 but will not work with Version 11. I have narrowed it down to the line that says SET :workspac2 = :workspac2 + :workspace;

 

[mirtheil]

What do you mean by "will not work with Version 11?" What is the expected result and what results are you seeing?
I ran your code on an 11.30.51 install of PSQL v11 (64 bit) and it seemed to work. Here's what I did:

select Statuteconcat('1234','56789', 'abcdefghijklmnopqrstuvwxy')

and the result:

abcdefghijklmnopqrstuvwxy

Mirtheil

http://www.mirtheil.com

 

[DWest1965]

Mirtheil,

I mean that I have two server engines Pervasive V9.5 and Pervasive V11. The script works on the V9.5 no problem.

The expected result is a string that gets concatenated together to be 625/5-11-106(B). That is what V9.5 does. Which is correct.

When I run the same script, on Pervasive V11, everything works except this one function. This user defined function gets called from another procedure that has this in a select clause. What this procedure does is it takes three fields that get read in and then calls this function to strip out any leading zeros and puts in a "/" and a "-" when needed. So the outcome shows a number like "625/5-11-106(B)". The data for :A, :B and :C that was pulled into this was "0625", "0050", and 11-106(B) respectively.
On Pervasive V11 all I get is the last section "11-106(B)" returned in the :smallest variable.

I appreciate any input.

 

[mirtheil]

Okay, I've reproduced the behavior you are seeing on my v11 server. It appears that in v9, the strings were trimmed automatically but aren't in v11. If you change your function as below, it should work and the new version should work with V9 as well. As to why it changed, I'm not sure. It was probably something to do with the SQL engine itself. Since PSQL v11 isn't supported, it won't get fixed. You might try it on PSQL v13 since that's the currently support product.

CREATE FUNCTION Statuteconcat(:A char(4), :B char(5), :C char(25)) 
RETURNS char(34)
AS 
BEGIN 

DECLARE :chapter char(4);
DECLARE :act char(4);
DECLARE :workspace char(4);
DECLARE :workspac2 char(4);
DECLARE :workspac3 char(5);
DECLARE :actdec char(1);
DECLARE :smallest char(34);

DECLARE :vInteger integer;
DECLARE :vIntege2 integer;
DECLARE :flag integer;
DECLARE :fla2 integer;


SET :workspace = LEFT(:B, 4);
SET :flag = 0;
SET :fla2 = 0;
SET :vInteger = 1;
SET :vIntege2 = 1;
SET :workspac2 = '';
SET :workspac3 = '';

TestLoop:
LOOP

IF (:vInteger > 4) THEN
LEAVE TestLoop;
END IF;
SET :workspace = SUBSTRING(:A, :vInteger, 1);
IF (:workspace = '' and :flag = 0) THEN
  SET :workspac2 = rtrim(:workspac2) + '';
ELSE
  SET :flag = 1;
  SET :workspac2 = rtrim(:workspac2) + rtrim(:workspace);
END IF;
SET :vInteger = :vInteger + 1;
IF (:vInteger = 5 and :workspac2 > '') THEN
  SET :workspac2 = rtrim(:workspac2) + '-';
END IF;
END LOOP; 

SET :vInteger = 1;

TestLoop:
LOOP

IF (:vInteger > 4) THEN
LEAVE TestLoop;
END IF;

SET :workspace = SUBSTRING(:B, :vInteger, 1);
IF (:workspace = 0 and :fla2 = 0) THEN
  SET :workspac3 = rtrim(:workspac3) + '';
ELSE
  SET :fla2 = 1;
  SET :workspac3 = rtrim(:workspac3) + rtrim(:workspace);
END IF;
IF (:vInteger = 4 and :workspac3 > '') THEN
  SET :workspac3 = rtrim(:workspac3) + '/';
END IF;
SET :vInteger = :vInteger + 1;
END LOOP; 

SET :smallest = rtrim(:workspac2) + rtrim(:workspac3) + rtrim(:C);
RETURN :smallest; 

END;

Mirtheil

http://www.mirtheil.com