Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Read field value into variable
Go
New
Search
Notify
Tools
Reply
  
Read field value into variable
 Login/Join
 
Member
posted
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!
 
Posts: 21 | Location: Cape Town, South Africa | Registered: January 15, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Sorry - that didn't come out right. It prints out a 'square' ASCII character.


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
 
Posts: 21 | Location: Cape Town, South Africa | Registered: January 15, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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.


Diptesh
WF 7.1.7 - AIX, MVS
 
Posts: 79 | Location: Warren, NJ, USA | Registered: October 25, 2006Reply With QuoteReport This Post
<JJI>
posted
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. Wink

Regards,
Dirk JJI
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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?


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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 Frowner


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
 
Posts: 21 | Location: Cape Town, South Africa | Registered: January 15, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 21 | Location: Cape Town, South Africa | Registered: January 15, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Reply With QuoteReport This Post
Member
posted Hide Post
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!


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
 
Posts: 21 | Location: Cape Town, South Africa | Registered: January 15, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Read field value into variable

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.