Hi all,
How do I read a field value into a & variable?
I am using the following code that I found in a previous post:
TABLE FILE CAR
PRINT MAX.CAR
ON TABLE HOLD
END
-RUN
-READ HOLD &CAR.A16
-TYPE MYVAR: &CAR
This works fine, but when I point it at my table it types out:
0 NUMBER OF RECORDS IN TABLE=3 LINES=3
MYVAR:
So it looks like it's not actually reading the data in the table. Anybody have any clues?

Thanks!

Sorry - that didn't come out right. It prints out a 'square' ASCII character.

The syntax is correct, as shown by the fact that it works with the CAR file. The one thing you have to make sure is that the datatype (e.g. A16 for the CAR file) is the right size for the data you get from your table. I tried your synatx against one of my Oracle tables, and it worked fine. A square ASCII character normally indicates an 'unprintable' data value. Try adding a -SET &ECHO=ALL; as the first line in your focexec and comment out the ON TABLE HOLD as well as the READ. Check the data you get back.

Hi Darren,

What do you really want to do? I would help if you told us a bit more.

First of all you don't need to hold the file as you don't need to create a MFD. So instead of :
ON TABLE HOLD
use
ON TABLE SAVE AS name FORMAT ALPHA

This works fine for me

-SET &ECHO = ALL;
TABLE FILE CAR
PRINT MAX.CAR
ON TABLE HOLD AS TEST FORMAT ALPHA
END

-RUN
-READ TEST &CAR.A16
-TYPE MYVAR: &CAR
-RUN

This is the result:

0 NUMBER OF RECORDS IN TABLE= 10 LINES= 10
-READ TEST &CAR.A16
-TYPE MYVAR: JAGUAR
MYVAR: JAGUAR

Depending on what you want to do, you will need to create a loop to read all the values from the save file.
Let me know if you need more help.

Regards,
Dirk JJI

I tried your code and here's what I got (I have echo turned on):

TABLE FILE CAR
PRINT MAX.CAR
ON TABLE HOLD
END
-RUN

0 NUMBER OF RECORDS IN TABLE= 10 LINES= 10

-READ HOLD &CAR.A16
-TYPE MYVAR: JAGUAR

MYVAR: JAGUAR

Is your CAR file okay? If you do a TABLE FILE on the hold, does it come out okay?

Hiya,

Jessica - Yep, my CAR file works fine, it's just the sproc results that don't seem to work too well. So I guess the code must be right, but there's a problem somewhere else?

Dirk - This is what I'm trying to do: I return a row from a table that contains 4 fields by executing a sproc that sits on a database.. These fields contain a username, a password, a sproc name and a database name. The aim is to be able to run sproc's dynamically based on what is contained in the row. So I would read field 1 into &UNAME, field2 into &PWD, field 3 into &SPROC and field 4 into &DB_NAME. I would then use these variables in my code to say something like:
EX &SPROC &UNAME,&PWD
I am monitoring a table that will contain this data, and depending on what is in the fields - the code should run the appropriate sproc. So I figured the easiest way would be to write a generic piece of code that I could assign dynamic values to. As above, the syntax seems to be okay as it works for the CAR file, just returning the sproc outputs don't want to play along

Okay - I'm getting closer.
Dirk - thanks for the code - that makes sence and kinda works. I can get the code to return 2 of the 4 values I need (not sure what's happened to the other 2 values?!) but the values that are returned are prefixed with a string of integers.
So here is the code I am using:
-SET &ECHO = ALL;
TABLE FILE tprocess
PRINT PROCESSID STOREDPROCNAME PARAMETERS MANCOCODE
ON TABLE HOLD AS TEST FORMAT ALPHA
END

-RUN
-READ TEST &PROCESSID.I4. &STOREDPROCNAME.A50V. &PARAMETERS.A200V. &MANCOCODE.A4V
-TYPE MYVAR: &PROCESSID
-TYPE MYVAR: &STOREDPROCNAME
-TYPE MYVAR: &PARAMETERS
-TYPE MYVAR: &MANCOCODE
-RUN

And the output is:
TABLE FILE tprocess
PRINT PROCESSID STOREDPROCNAME PARAMETERS MANCOCODE
ON TABLE HOLD AS TEST FORMAT ALPHA
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3
-READ TEST &PROCESSID.I4. &STOREDPROCNAME.A50V. &PARAMETERS.A200V. &MANCOCODE.A4V
-TYPE MYVAR:
MYVAR:
-TYPE MYVAR: 1000014sGetTrailerFee
MYVAR: 1000014sGetTrailerFee
-TYPE MYVAR: 000065'Server=MILANO;Database=PWCTRL;uid=rtonline;pwd=rtonl1ne;', 'COR'
MYVAR: 000065'Server=MILANO;Database=PWCTRL;uid=rtonline;pwd=rtonl1ne;', 'COR'
-TYPE MYVAR:
MYVAR:
-RUN
END
-RUN
-TYPE

The actual values in my table are: [1] [sGetTrailerFee] ['Server=MILANO;Database=PWCTRL;uid=rtonline;pwd=rtonl1ne;', 'COR'] [COR]

Anybody seen this before or have any clues?
Cheers

Prod: Service Manager 5.5 - DataMigrator 7.6 - Win2K
Test: Service Manager 5.5 - DataMigrator 7.6 - Win2K
Local: DevStudio 5.5.3 - Management Console
- Windows XP SP2

1. I always use ON TABLE SAVE for this purpose.
WebFOCUS will then show you how it is outputting each column ie the field length.

When you read them back use all A formats to match the lengths shown by the ON TABLE SAVE report as & variables do not have a datatype other than alpha.

You have tried to use .I4 where this column may not actually be 4 characters in the output file. Even if it is you should read it back as .A4.

quote:

the values that are returned are prefixed with a string of integers.

AnV fields in an extract file are prefixed with the actual length of the value. I'd suggest changing your master file descriptions to use An formats instead.

Hi Piipster & Opaltosh,
That is exactly what it is. For the benefit of other users (who may have missed the fundamentals like I have!) - this is the solution:

If I look in the master I see that the field STOREDPROCNAME is defined as: USAGE=A50V, ACTUAL=A50V This means that it is stored in the data base as variable length field with a maximum length of 50 positions. When you PRINTed that field into a HOLD file, it was written to the HOLD file as 00022sGetRebateReinvestment. 000022 means that the length of the field is 22, followed by the content of the field. The same goes for the MANCOCODE field. Probably this is not what you want. What you have to do, is to temporarily, for this fex, only work with fixed fields. To do this, code the
following:

-* beware the HOLDLIST setting that is required here
SET HOLDLIST=PRINTONLY
TABLE FILE tprocess
PRINT PROCESSID
-* observe that the two fields are dynamically defined as FIXED length STOREDPROCNAME/A50 MANCOCODE/A4
-*
ON TABLE HOLD AS SOURCE_TABLE FORMAT ALPHA
END
-RUN
-*Read the columns into variables
-*BEWARE THAT THE FIELDS ARE NO LONGER READ IN AS VARIABLE -READ SOURCE_TABLE &PROCESSID.A11. &STOREDPROCNAME.A50. &MANCOCODE.A4. -TYPE PROCESSID: &PROCESSID -TYPE SPROC: &STOREDPROCNAME -TYPE MANCO: &MANCOCODE

Alternatively, I changed the field definitions in the synonym to be fixed-length fields rather than variable length.

Thanks to all for their input!