MaxDB 7.6.00.34, Windows 2003 Server, Windows XP
clients
I am a newbie in MaxDB. I am attempting to rewrite an
existing Visual Foxpro application with native VFP
database to one with MaxDB as the backend, retaining
the builtin VFP functions where no record fetching is
required. Where it does I created dbprocs or
functions, like the following example which works,
written near enough to the way i would code it in VFP,
which I am sure there would be more elegant if done by
MaxDB pros.
CREATE TABLE "SA"."SYSMAPCHAR"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"DECCODE" Fixed (2,0),
"ASCVAL" Char (1) ASCII,
PRIMARY KEY ("ID")
)
CREATE TABLE "SA"."SYSCODEFMT"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"COMP_ID" Integer NOT NULL,
"CODENAME" Char (16) ASCII NOT NULL,
"LASTVALUE" Char (16) ASCII,
PRIMARY KEY ("ID", "COMP_ID", "CODENAME")
)
insert into sa.sysmapchar (deccode, ascval) (48, '0')
insert into sa.sysmapchar (deccode, ascval) (49, '1')
....
insert into sa.sysmapchar (deccode, ascval) (57, '9')
insert into sa.sysmapchar (deccode, ascval) (65, 'A')
insert into sa.sysmapchar (deccode, ascval) (66, 'B')
....
insert into sa.sysmapchar (deccode, ascval) (90, 'Z')
insert into sa.syscodefmt (comp_id, codename,
lastvalue) (1,'EMPCODE' ,'AAA0000')
insert into sa.syscodefmt (comp_id, codename,
lastvalue) (1,'APPCODE' ,'0000000')
insert into sa.syscodefmt (comp_id, codename,
lastvalue) (1,'STDPAY' ,'B000000')
insert into sa.syscodefmt (comp_id, codename,
lastvalue) (1,'STDDED' ,'C000000')
insert into sa.syscodefmt (comp_id, codename,
lastvalue) (1,'STDBEN' ,'D000000')
CREATE DBPROC PR_SYSTEM_CODING_FMT (IN CODEFLD
CHAR(16), IN COMPID INTEGER ) RETURNS CURSOR AS
VAR NEWCODE CHAR (16) ; LASTCODE CHAR (16) ;
CHECKKNT INTEGER ; CODESTR1 CHAR(1) ; TESTLEN INTEGER
; ACTLEN INTEGER ; NULLCOMP CHAR (1) ; NUMCOD CHAR
(16) ; ALPHACOD CHAR (16) ;
CODEFMT CHAR (16) ; CODESTR CHAR (1) ; CODENUM
SMALLINT ; CODESTR2 CHAR (1) ; CODESTR3 CHAR (1) ;
PREVSTR1 CHAR (1) ; PREVSTR2 CHAR (1) ;
TRY
SET ALPHACOD = '' ;
SET NUMCOD = '' ;
SET LASTCODE = '';
SET NEWCODE = '' ;
SET CHECKKNT = 0 ;
SET CODESTR = '' ;
SET CODESTR1 = '' ;
SET NULLCOMP = '' ;
SET ACTLEN = 0 ;
SET CODESTR2 = '' ;
SET PREVSTR1 = '' ;
SET PREVSTR2 = '' ;
DECLARE CURSORSET CURSOR FOR
SELECT CODE_FMT, LASTVALUE, LENGTH(CODE_FMT)
TESTLEN FROM SA.SYSCODEFMT WHERE CODENAME=:CODEFLD AND
COMP_ID=:COMPID WITH LOCK EXCLUSIVE ;
WHILE $RC=0 DO BEGIN
FETCH CURSORSET INTO :CODEFMT, :LASTCODE,
:ACTLEN ;
WHILE CHECKKNT < ACTLEN DO BEGIN
SET CODESTR = SUBSTR (LASTCODE, ACTLEN -
CHECKKNT , 1) ;
SET PREVSTR2 = PREVSTR1 ;
SET PREVSTR1 = CODESTR ;
SET CODESTR1 = CODESTR ;
IF CHECKKNT = 0 AND CODESTR ='9' THEN SET
CODESTR1 = '0' ;
IF CHECKKNT = 0 AND CODESTR = 'Z' THEN SET
CODESTR1 = 'A' ;
IF CHECKKNT = 0 AND CODESTR NOT IN ('9','Z')
THEN SELECT ASCVAL INTO :CODESTR1 FROM SA.SYSMAPCHAR
WHERE DECCODE = (SELECT DECCODE FROM SA.SYSMAPCHAR
WHERE ASCVAL=:CODESTR) + 1;
IF PREVSTR2<>NULLCOMP AND CODESTR2 <PREVSTR2
AND CODESTR NOT IN ('9','Z') THEN SELECT ASCVAL INTO
:CODESTR1 FROM SA.SYSMAPCHAR WHERE DECCODE = (SELECT
DECCODE FROM SA.SYSMAPCHAR WHERE ASCVAL=:CODESTR) + 1;
IF PREVSTR2<>NULLCOMP AND CODESTR2 <PREVSTR2
AND CODESTR='9' THEN SET CODESTR1= '0' ;
IF PREVSTR2<>NULLCOMP AND CODESTR2 <PREVSTR2
AND CODESTR='Z' THEN SET CODESTR1= 'A' ;
IF CODESTR1 BETWEEN '0' AND '9' THEN SET
NUMCOD = CODESTR1 & NUMCOD ;
SET CODESTR2 = CODESTR1 ;
SET NEWCODE = TRIM(CODESTR1) & NEWCODE ;
SET CHECKKNT = CHECKKNT + 1 ;
END ;
IF NUM(NUMCOD) = 0 THEN SET ALPHACOD =
SUBSTR(NEWCODE,1,ACTLEN - LENGTH(NUMCOD)) ;
IF LENGTH(NUMCOD)>0 AND NUM(NUMCOD) = 0 THEN
SET NEWCODE = ALPHACOD &
SUBSTR(NUMCOD,1,LENGTH(NUMCOD)-1) & '1' ;
UPDATE SA.SYSCODEFMT SET LASTVALUE= :NEWCODE
WHERE CODENAME=:CODEFLD AND COMP_ID=:COMPID ;
END ;
CATCH
IF $RC <> 100 THEN STOP ($RC, 'unexpected error');
$CURSOR = 'NEWCODECURSOR';
DECLARE :$CURSOR CURSOR FOR
SELECT :NEWCODE NEWCODE FROM SA.SYSCODEFMT WHERE
CODENAME=:CODEFLD AND COMP_ID=:COMPID ;
I also created a function with almost the same code as
the DBPROC as I thought calling a function from within
a procedure would produce the
required result faster.
CREATE FUNCTION PR_SYSTEM_CODINGFMT (CODEFLD CHAR(16),
COMPID INTEGER ) RETURNS CHAR(16) AS
VAR NEWCODE CHAR (16) ; LASTCODE CHAR (16) ;
CHECKKNT INTEGER ; CODESTR1 CHAR(1) ; TESTLEN INTEGER
; ACTLEN INTEGER ; NULLCOMP CHAR (1) ; NUMCOD CHAR
(16) ; ALPHACOD CHAR (16) ;
CODEFMT CHAR (16) ; CODESTR CHAR (1) ; CODENUM
SMALLINT ; CODESTR2 CHAR (1) ; CODESTR3 CHAR (1) ;
PREVSTR1 CHAR (1) ; PREVSTR2 CHAR (1) ;
TRY
SET ALPHACOD = '' ;
SET NUMCOD = '' ;
SET LASTCODE = '';
SET NEWCODE = '' ;
SET CHECKKNT = 0 ;
SET CODESTR = '' ;
SET CODESTR1 = '' ;
SET NULLCOMP = '' ;
SET ACTLEN = 0 ;
SET CODESTR2 = '' ;
SET PREVSTR1 = '' ;
SET PREVSTR2 = '' ;
DECLARE CURSORSET CURSOR FOR
SELECT CODE_FMT, LASTVALUE, LENGTH(CODE_FMT)
TESTLEN FROM SA.SYSCODEFMT WHERE CODENAME=:CODEFLD AND
COMP_ID=:COMPID WITH LOCK EXCLUSIVE ;
WHILE $RC=0 DO BEGIN
FETCH CURSORSET INTO :CODEFMT, :LASTCODE,
:ACTLEN ;
WHILE CHECKKNT < ACTLEN DO BEGIN
SET CODESTR = SUBSTR (LASTCODE, ACTLEN -
CHECKKNT , 1) ;
SET PREVSTR2 = PREVSTR1 ;
SET PREVSTR1 = CODESTR ;
SET CODESTR1 = CODESTR ;
IF CHECKKNT = 0 AND CODESTR ='9' THEN SET
CODESTR1 = '0' ;
IF CHECKKNT = 0 AND CODESTR = 'Z' THEN SET
CODESTR1 = 'A' ;
IF CHECKKNT = 0 AND CODESTR NOT IN ('9','Z')
THEN SELECT ASCVAL INTO :CODESTR1 FROM SA.SYSMAPCHAR
WHERE DECCODE = (SELECT DECCODE FROM SA.SYSMAPCHAR
WHERE ASCVAL=:CODESTR) + 1;
IF PREVSTR2<>NULLCOMP AND CODESTR2 <PREVSTR2
AND CODESTR NOT IN ('9','Z') THEN SELECT ASCVAL INTO
:CODESTR1 FROM SA.SYSMAPCHAR WHERE DECCODE = (SELECT
DECCODE FROM SA.SYSMAPCHAR WHERE ASCVAL=:CODESTR) + 1;
IF PREVSTR2<>NULLCOMP AND CODESTR2 <PREVSTR2
AND CODESTR='9' THEN SET CODESTR1= '0' ;
IF PREVSTR2<>NULLCOMP AND CODESTR2 <PREVSTR2
AND CODESTR='Z' THEN SET CODESTR1= 'A' ;
IF CODESTR1 BETWEEN '0' AND '9' THEN SET
NUMCOD = CODESTR1 & NUMCOD ;
SET CODESTR2 = CODESTR1 ;
SET NEWCODE = TRIM(CODESTR1) & NEWCODE ;
SET CHECKKNT = CHECKKNT + 1 ;
END ;
IF NUM(NUMCOD) = 0 THEN SET ALPHACOD =
SUBSTR(NEWCODE,1,ACTLEN - LENGTH(NUMCOD)) ;
IF LENGTH(NUMCOD)>0 AND NUM(NUMCOD) = 0 THEN
SET NEWCODE = ALPHACOD &
SUBSTR(NUMCOD,1,LENGTH(NUMCOD)-1) & '1' ;
UPDATE SA.SYSCODEFMT SET LASTVALUE= :NEWCODE
WHERE CODENAME=:CODEFLD AND COMP_ID=:COMPID ;
END ;
CATCH
IF $RC <> 100 THEN STOP ($RC, 'unexpected error');
CLOSE CURSORSET ;
RETURN NEWCODE ;
The system produces the expected result when called
from the procedure, i.e., if the lastvalue of row with
codename 'EMPCODE' is 'ABC1234', the next value that
would be produced is 'ABC1235'. But when the user
defined function is used, the new value produced is
'ABC1236', 'ABC1238' and so on, skipping one number
each time.
The point of this code by the way is to give the users
the option of either manually inputting the required
values in the code fields, or to let the system
generate the values in the format required.
Thank you and regards.
SERAFIN G. SEGADOR
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
