December 13, 2006, 08:39 PM
Dave AyersInclude Auto-increment fields with Maintain ??
My customer has a number of SQL2005 tables with 'Identity' fields - those auto-incrementing keys that won't take a key value.
I have tried a number of ways to Include a new record in these tables, but none of them have worked so far.
What is the correct way for Maintain to add new records to these type of datasources ?
BTW it is WebFocus 7.1.3 TIA,
Dave Ayers
December 14, 2006, 08:59 AM
Maintain WizardDave
With Maintain you need into reference the field in the stack, but not reference in the INCLUDE statement. So, assume you Master looks like this:
FILE = MNTAUTO , SUFFIX = SQLMSS ,$
SEGNAME = MNTAUTO , SEGTYPE = S0 ,$
FIELD = CONTROL ,CONTROL ,I11 ,I4 ,MISSING = OFF, FIELDTYPE = R,$
FIELD = LASTNAME ,LASTNAME ,A12 ,A12 ,MISSING = OFF,$
FIELD = FIRSTNAME ,FIRSTNAME ,A12 ,A12 ,MISSING = OFF,$
FIELD = ITEM ,ITEM ,A20 ,A20 ,MISSING = OFF,
$FIELD = AMOUNT ,AMOUNT ,P19 ,P10 ,MISSING = OFF,$
Here. CONTROL is the Identity field. The MAINTAIN code would be:
MAINTAIN FILE MNTAUTO
INFER CONTROL LASTNAME FIRSTNAME ITEM AMOUNT INTO INSTK
PERFORM UPDAT1
CASE UPDAT1
COMPUTE INSTK(1).LASTNAME = 'PIG';
COMPUTE INSTK(1).FIRSTNAME = 'PORKY';
COMPUTE INSTK(1).ITEM = 'PORKBELLIES';
COMPUTE INSTK(1).AMOUNT = 2000.00;
FOR 1 INCLUDE LASTNAME FIRSTNAME ITEM AMOUNT FROM INSTK
ENDCASE
END
The INFER command loads all of the fields into the stack. When you perform the INCLUDE you can list all BUT the CONTROL field. This technique will take advantage of the Identity field, and still allow you to Include records. I hope this helps.
Mark Derwin
December 14, 2006, 05:46 PM
Dave AyersMark,
I didn't realize that one could specify a subset of the stacks fields in an INCLUDE.
I modified my code, but it still has a problem, which I now think may be due to a funky data adaptor setup.
Thanks,
December 15, 2006, 10:50 AM
Maintain WizardDave
The rule with INCLUDE is that you only have to list the key fields. However, in this case we get around the Identity key issue by listing other fields. Note that every field besides the identity field WILL be included.
Mark