Query with External and Internal files

[James Zukowski]

I have an ODBC file linked as the primary source for a Query. Using a field from that file, I want to retrieve data from a PxPlus file on our server. I don't see how PxPlus will let me do this. It seems that once the primary is set as external, it expects all files to be in the same external Database; no "mix-and-match" capabilities.

Did I miss something?

[Jane Raymond]

You can use the Database Import Utility in Data Dictionary Maintenance to import the data dictionary information from your external table into the dictionary files. Once there, the table can be selected in the query from the regular list of table names, and you can mix and match external and native files.

[James Zukowski]

I tried importing the external definition for one of the tables. There were 6 fields with invalid characters (%:/), so they didn't get defined in the PxPlus DD. I manually defined them, as I only really need them as placeholders. But now, when trying to open the file with the link, I get Error #84: No DICTIONARY exists on OPENed file, and the query won't get there either. I don't want to try writing the iolist out, since it's an external DB file, and there are lots of other downstream apps and reports that I'm sure expect things the way they are.

Any other possibilities? 

[Jane Raymond]

Consider creating your own PREFIX FILE. That would give you an alias file name that you can probably use in the query.

Quick summary — what a PREFIX FILE does

Use a PREFIX FILE to translate a program's logical file name into a real pathname/options at OPEN time. Prefix Processing (User Guide)

The PREFIX FILE must be a variable-length KEYED file; PxPlus will look it up first (it replaces the pathname, then continues the normal prefix search).
doc -PREFIX directive - Using Paths in Keyed File

Record layout and behaviour
Key = the logical name your program opens (e.g., "CUSTDB"). The record's first field = the real pathname (or DB DSN/table string); second field = options to append to OPEN (OPT=); an optional third field may contain an IOList for IOL=* openings (supported in later PxPlus builds). PREFIX FILE (record layout)
If you use an absolute pathname in your OPEN, PxPlus does not consult the PREFIX FILE for that open.

How to create and use a PREFIX FILE — steps
Create a variable-length keyed file to hold mappings, write records where key = logical name and data fields = path, options, (optional iolist).
Set that keyed file as the PREFIX FILE with PREFIX FILE "your_pfxfile".
Then OPEN("logicalname") in your programs — PxPlus will substitute the pathname/options found in the prefix file and proceed with the normal prefix search rules. Prefix search order

Minimal example (maps a logical name to a pathname)
! create variable-length keyed prefix file and add a mapping
KEYED "MY_PFXFILE",127
OPEN (1) "MY_PFXFILE"
WRITE (1,KEY="CUSTDB") "/data/companyA/customers"
CLOSE (1)

! tell PxPlus to use that prefix file
PREFIX FILE "MY_PFXFILE"

! later in application, this will open /data/companyA/customers
OPEN (2) "CUSTDB"


Example mapping to an external DB (ODBC/MySQL) with OPT= and IOL=
! create prefix file and write an entry with DSN/table + OPT + IOList
KEYED "PFXDB",127
OPEN (1) "PFXDB"
WRITE (1,KEY="MY_TABLE") "[MYSQL]test_db;my_table;SERVER=192.168.1.114;PORT=3306;USER=xxxx;PSWD=xxxxxxx",
  "KEY=field1;REC=field1:10,field2:8.2,field3:40",
  "field1$,field2,field3$"
CLOSE (1)

PREFIX FILE "PFXDB"

! now OPEN("MY_TABLE") will use the DB connection/options/iolist from the prefix file
OPEN (3,iol=*) "MY_TABLE"