Recently, we got a dataserver connection to a MSSQL database, so I thought that it would be a good idea to test my DataDigger against it. But either things have been set up wrong or I just don't get it.
A short program shows me my databases:
DEFINE VARIABLE iDatabase AS INTEGER NO-UNDO.
DEFINE VARIABLE lSchemaHolder AS LOGICAL NO-UNDO FORMAT "Schemaholder/Sub-schema".
CURRENT-WINDOW:WIDTH = 200.
DO iDatabase = 1 TO NUM-DBS:
IF SDBNAME(iDatabase) <> 'plshtest' THEN NEXT.
lSchemaHolder = (SDBNAME(iDatabase) = LDBNAME(iDatabase)).
DISPLAY
LDBNAME(iDatabase) FORMAT 'X(8)' COLUMN-LABEL 'LDBNAME'
SDBNAME(iDatabase) FORMAT 'X(8)' COLUMN-LABEL 'SDBNAME'
lSchemaHolder COLUMN-LABEL 'Holder/Sub'
DBTYPE(iDatabase) FORMAT 'X(8)' COLUMN-LABEL 'Type'
WITH WIDTH 200 DOWN.
DOWN.
END.
So plsh is my SQL database and plshtest my shemaholder.
Suppose I would like to run a query against a table. If I run this:
FIND FIRST plsh.progress_esb_message NO-ERROR. MESSAGE AVAILABLE plsh.progress_esb_message VIEW-AS ALERT-BOX INFO BUTTONS OK.
It runs fine and tells me that - yes - there is a record found. Whereas when I do this:
FIND FIRST plshtest.progress_esb_message NO-ERROR. MESSAGE AVAILABLE plshtest.progress_esb_message VIEW-AS ALERT-BOX INFO BUTTONS OK.
I get this:
Unknown or ambiguous table progress_esb_message. (725)
** Could not understand line 13. (196)
I always thought I could run it against the 'progress' database, but it turns out that I cannot. Can somebody give me some insight?
The ShemaHolder (plshtest) did not contain any progress tables, it is empty progress database which is only an "container" for sub-shema for MSS (plsh).
Okey, so basically I should ignore the schema holder? My DataDigger presents the user with a list of databases. How can I recognize a schema holder?
>>I should ignore the schema holder?
I think Yes.
>>How can I recognize a schema holder?
Something like that:
DEFINE VARIABLE iDatabase AS INTEGER NO-UNDO.
do iDatabase = 1 to num-dbs:
IF DBTYPE(iDatabase) <> "PROGRESS" then
message "Shema Holder is " SDBNAME(iDatabase) view-as alert-box.
end.
As Valeriy said, the schema-holder is a basically "empty" DB that holds only the schema, so 4GL can be compiled. The actual data, however, comes from the foreign DB.
If you want to look at the schema (like _File and _Field records, for example to build dynamic queries for any table) you need to look at the schema-holder. Be aware, though, that one schema-holder can hold the schema of multiple foreign DBs, not just one. So you need to look at the _DB record first to select then you want, then look at the related _File records...
As much as I remember you can also store connection parameters in the schema-holder, so progress can connect to the foreign DB automatically for you when you run a program accessing it and it is not yet connected...
Clarification: the schema-holder is a basically "empty" DB that holds only the schema, so 4GL can be compiled AND executed...