serafin segador wrote:
>
> 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 we have to assume, that your function is used like this:
select PR_SYSTEM_CODINGFMT (..) from dual
?
Then please change to
select PR_SYSTEM_CODINGFMT (..) into :resultparameter from dual
For explanation: during select we have to check if at least one
result will fulfill the requirements. For this the first resultrow
is searched for and prepared (inclusive the usage of your function).
Then the decision can be made if error 100 row not found has to be
returned,
or if at least one, perhaps more rows will fulfill the requeirements and
the answer will be : an unknown number.
When the first resultrow is fetched, then it is prepared the second
time,
therefore resulting in the second 'used' value.
Therefore one number seems to be skipped.
this is true for resultset, which are not physically build.
If build (forced by using DECLARE ... CURSOR FOR SELECT ... FOR REUSE),
the this value-skipping would not happen, too.
But for one 'resultrecord', the select ... into is the better way.
Elke
SAP Labs Berlin
>
> __________________________________________________
> 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]
>
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
