How to list db tables as in data dictionary (v9 dev connecting to v8 db)

[pauloconnor]

Hi,

does anyone have sample code that will list database tables as listed in the data dictionary?

Have made various efforts:

_file._file-num > 0 etc.

but am getting strange listings.

One point to note is that I'm developing with V9 connecting into a V8 database - should this give problems reading the metadata?

Regards,
Paul.

 

[Norman Biggar]

I don't suppose it helps you much Paul, but I've just tried this and it ran OK.
FOR EACH _File NO-LOCK WHERE _File-Num GT 0:
DISPLAY _File._File-name.
END.

This was on a 9.1C network session connecting to a 8.3B UNIX database by TCP.

What kind of "strange results" are you getting?
What kind of set-up are you using?

 

[pauloconnor]

Ok,

My code is as follows:

DEF VAR i AS INTEGER.

REPEAT i = 1 TO num-dbs:
DELETE ALIAS dictdb.
create alias DICTDB for database value(ldbname(i)).
FOR EACH DICTDB._File NO-LOCK WHERE _File-Num GT 0:

DISPLAY ldbname("dictdb")
_file._file-name.

end. /* for each _file */
END.

I have three databases attached.
When I run this code, I seem to get certain tables from one database listed against all three and other tables are not listed at all (if that makes sense).

I'm trying to develop a simple case-tool to standardise a suite of maintenance programs and want to allow the user to pick tables etc (like Query Builder) around which to build the programs.


Regards,
Paul

 

[Norman Biggar]

Sorry Paul,
I have NEVER managed to get the CREATE ALIAS to switch to a second connected database.

The only method I can suggest is as follows:

DO iLoop = 1 TO NUM-DBS:
RUN pListTable.p LDBNAME(iLoop).
END.

Then code pListTable.p as follows:
FOR EACH {1}._File WHERE _File-Number GT 0:
DISPLAY _File._File-Name.
END.

This would only work if run-time compile was available.

Perhaps someone could suggest how to get the CREATE ALIAS to switch between connected databases.
Or perhaps there is a method using dynamic buffers?

 

[Samj]

I couldn't get

' FOR EACH {1}._File '

to compile. I had to put the '.' inside the {1}.

 

[Norman Biggar]

You won't get the '{1}' to compile.

That's why you need run-time compile to be available.

I'm currently investigating writing a 'dynamic' version.

If anyone has existing code for this I would be extremely pleased to acquire it.

 

[paulocon]

Data Dictionary does it

Wonder where I can get hold of the code for the data dictionary?
Better again, database connections dialog from AppBuilder.
Both these do what we are looking for.

Message imported from PEG email list

 

[Norman Biggar]

Here's how to do it! Split it up. Maybe it's something to do with the focus or scope of DICTDB.

DEF VAR i AS INT NO-UNDO.
REPEAT i = 1 TO num-dbs:
RUN changealias(INPUT i).
RUN listtable .
END.

/* changealias.p */
DEF INPUT PARAM i AS INT NO-UNDO.
DELETE ALIAS dictdb.
create alias DICTDB for database value(ldbname(i)).

/* listtable.p */
FOR EACH DICTDB._File NO-LOCK WHERE _File-Num GT 0 AND _File-Num LT 32000:

DISPLAY ldbname("dictdb")
_file._file-name.

end. /* for each _file */

 

[Unregistered]

Try

for each _file where _hidden = NO no-lock:

and subsequently to get the fields

for each _field of _file where _hidden = NO no-lock:

PeteB