I want to use a database lookup to populate a Pulldown module.
Is there a way to output the contents of a database lookup? With a static CSV file lookup, I can use
| inputlookup filename.csv and it'll show me all the contents of the CSV file.
I want to be able to do the same thing with a database lookup, but
| inputlookup lookupname returns an error: "The lookup table 'lookupname' is invalid."
| lookup lookupname demands field names: "Error in 'lookup' command: Must specify one or more lookup fields."
Strictly speaking, you cannot take a database lookup and use the
inputlookup command to get you the "rows" of the lookup.
However you could certainly use the
dbquery command to do so. Just use the same SQL syntax as your lookup, or do
SELECT * FROM the same table as your lookup, and you'll be good to go.
| dbquery someDatabase limit=10000 "select * from someTable"
Okay, that brings up the question: when piping a command to lookup to do a database lookup, e.g.,
| loadjob MyJob | lookup MyLookupName MyLookupField as MyField OUTPUT MyLookupField2
Is the lookup literally running out to SQL Server in real-time, or does it run it real-time the first time and then cache the results for later use?
(In this case, using dbquery to populate the pulldown is not ideal because of the customizable/fluid nature of the form. I'm trying to be as dynamic as possible and use ValueSetter modules to control the order of Pulldowns. I know, it's weird.)
I think this is actually a good question to ask, but it's a separate question and better answered by someone more involved with DBConnect's development. Can you post it separately? For the record, I doubt that the database lookup caches the lookup rows locally and I don't see any mention of such functionality in the docs.