Library Query

Started by Mike Hatfield, May 27, 2024, 05:12:28 AM

Previous topic - Next topic

Mike Hatfield

I want to create a Stanard Library Query where you pass it the file name to open for the query.
For example:
I want to create a query that will display a workfile that contains a set of transactions from the general ledger.
The workfile has a generated unique file name: EG GLWK+fid(0)+TCB(13)
I can create a File Dictionary table for a generic GLWK workfile.
My problem is how to tell the Query what file to read and how will it know to use the generic dictionary definition?

Thanks
Mike H

Jane Raymond

There are two possibilities:

       
  • In the Data Dictionary, use the expression GLWK+FID(0)+TCB(13) as your physical file name. Then, whenever a new  file is created, be sure to embed the dictionary. When the query runs, it will evaluate the file name and open it.
  • In the query definition, use the expression as the File/Table. If the dictionary is embedded in the file the expression evaluates to, it will use it.
Jane Raymond
Software Developer
PVX Plus Technologies Ltd.

Mike Hatfield

Hi Jane, I have already tried what you suggested. Please see attached screenshots of the dictionary definition and query definition.
My WindX session was on FID(0)="T001" in the Dictionary maintenance.
Notice that file ending in T0014, FID(0) is "T001", has been created. The number 4 being, I presume, the content of TCB(13).

This code creates the work file in the main program:
I am in WindX session where FID(0)="T003"
01031  KEYED "WKFL"+FID(0)+STR(TCB(13)),[1:1:2],[2:1:8],[3:1:8],[4:1:3],0,79
01040  OPEN (FILECHAN)"WKFL"+FID(0)+STR(TCB(13))
TCB(13) AT THIS POINT IS 0

See screenshot - query running.
Notice the WKFL" file is defined for "T003 - Correct, but the TCB(13) is 6.
I presume the TCB value is associated with the call level in the stack.

Doesn't matter how I name/define the workfile, by the time the Query wants to read it the name is different because of a random TCB(13) number.
There must be a way to do this. I don't want hundreds of temporary workfiles cluttering up the disk.
Mike H

Stéphane Devouard

Mike
TCB(13) is the current level in the program call stack
If you create the work file in your main level program, its value will be 0
But PROCESS a panel and it's value is set to 1 or 2 because basically PROCESS is a call to *winproc
Invoke a query from the panel (which is a call to *winproc which then performs *winqry) and now your TCB(13) is 4 or 5
Not sure why you want to include TCB(13) in the file name, but it will never work as you've experienced. If you included this for file name uniqueness, maybe TCB(89) would be a better choice as it is the same whatever level you are in the call stack
Hope this helps
Stéphane Devouard
Portfolio | Work

Mike Hatfield

Hi Stéphane
Sometimes what you think is going to be an easy exercise turns out to be impossible.
I have a generic file definition in the dictionary. I use it to create a unique named work file at runtime using *dict/dd_updt. I'm changing a text based program to modernize it with Query. In my program I want to call Nomads Query that will read/process that unique file name with the
generic dictionary definition. Seems to me there is a shortcoming in the Query options.
Being able to do this with Query would save me a massive amount of recoding time to use a regular nomads panel list boxes.
Mike H

Jane Raymond

You want to create a query which uses a file whose name changes. The Physical File used by the query can be an expression set to the value of the current file name. It looks like the issue is setting that file name to a unique value that can be evaluated by the query.

Jane Raymond
Software Developer
PVX Plus Technologies Ltd.

Stéphane Devouard

#6
Mike

The problem here is the inclusion of TCB(13) in the file name

TCB(13) is not a fixed value throughout your session. It varies depending how deep you are in the CALL stack.
In your main program it is 0.
In a subprogram called / performed from your main program it is 1.
In a sub-subprogram called / performed from your subprogram it is 2.


Doing a PROCESS of a panel or a query is like doing a CALL.
So if you created your temp file in your main program, it was named WKFLT0010.
If you PROCESS a query to read it, that's a CALL, so when the query runtime program tries to resolve the file name, it evaluates to WKFLT0011 which does not exist.

You need to change the way you name your WKFL files if you want to make this work.
Stéphane Devouard
Portfolio | Work

Mike Hatfield




Jane,
Example:
I am currently FID T001
My Dictionary definition is "General Ledger Enquiry" and the file name is expression "./WKFL"+FID(0)
When I save the definition it creates the file WKFLT001
There is no data in the file yet.
Now comes the tricky part.....
If I now create the Query GLTR_QRY whilst still on T001....
I have the choice of using the Dictionary definition name or expression.
IF, I am on the same session, IE T001 the Query maintenance 'sees' the physical file whether I define the query as physical or expression and it will pop up the query screen with no data.
IF, I run my program whilst still on T001 the program ruins and the Query runs and all is good.
IF I now start a new session on T003 my program creates the workfile WKFLT003 and fills it with data (*dict/dd_update created the file).
At the end of the data generation the program PROCESSes the query GLTR_Qry
Unfortunately the Query fails because it thinks the source data file is WKFLT001 and not WKFLT003.
It seems to me that when Query goes to the Dictionary to get the real file name, Dictionary replies with the original physical name that was created directly by the dictionary.
In other words if the Dictionary and the Query use expression for the file name the Query always fails UNLESS the Dictionary Maintenance, Query and my program are all in the same session.

In other words using expression for the file name doesn't work when part of the expression is variable - eg FID(0)
Unfortunately, you can't define a query if it can't find the file that contains the data and its dictionary.

Mike H

Jane Raymond

#8
Mike,
To try to duplicate your situation I did the following:
   Created a file (smnfile01) and duplicated it in a second file (smnfile02), then changed some of the records in the second file so would have different data.
   Set %c$="01"
   Created a generic data dictionary entry (Generic sales person) using the expression "smnfile"+%c$ as my physical file and updated the file.
   Created a query using Fixed 'Generic sales person' as my physical file name. Added some fields and saved it.
   Ran the query and it showed the data from smnfile01. Correct.
   Set %c$="02"
   Ran the query and it showed the data from smnfile02. Correct.
   Tried changing the query so the physical file name was Expression "smnfile"+%c$
   Ran the query setting %c$="01" and %c$="02" and the both showed the correct data.

What you did and what I did look very similar, so I don't know why yours is not working unless the value of FID(0) is not what you're expecting.
Jane Raymond
Software Developer
PVX Plus Technologies Ltd.

Mike Hatfield

Hi Jane,
Is there a reason why you chose %C and %S?
As I understand it, these two global variables are reserved and inbuilt into PxPlus and are also linked with ODBC as 'Session ID' and 'Company Code'.

We use %C and %S extensively throughout the dictionary in the file name expression for most of the application files.
EG "./comp"+%S$+"/APTR"+%C$

We set these two Globals in the application session startup, Local and WindX.
If we change the value of these in this program will it 'screw up' the normal operation of file access in the Dictionary in the current session or other sessions or separately in ODBC?

I decided not to use %C$ and %S$ just in case.

In my program I have set a variable %SEQN$=str(int(tim)*1000)
In the Dictionary the file NAME is "General Ledger Enquiry" and physical file is Expression "./WKFL"+%SEQN$
01010  CLOSE (8);
       LET FILECHAN=8;
       LET %SEQN$=STR(INT(TIM*1000));
       LET WORKFILE$="WKFL"+%SEQN$
01020  ERASE WORKFILE$,ERR=1030;
       GOTO 1020
01030  CALL "*dict/dd_updt;update_physical",ERR=9700,"General Ledger Enquiry","","",ERRMSG$

The Query file name is Fixed "General Ledger Enquiry"

Now my program works.
I think including FID(0) in the workfile name was causing a mismatch between the dictionary and the query.
Once I removed FID(0) it all worked.
How nice to 'waste' 1.5 days trying to do something that intuitively you knew should work but stymied by an intenal variable.  :(
Thank you for your input.
Mike H

Jane Raymond

Mike,
Sorry I confused you by using %C$. The variable could have been anything.
I am happy that you got it all working.

Jane Raymond
Software Developer
PVX Plus Technologies Ltd.