Cant access table created by SQL in 4GL

[ashok]

Hi,

I am working on a customized system where I created a Progress (ver 9.1E) empty database thru the 4GL Progress Editor --> Data dictionary, had the database server up in multiuser mode with services/port parameters for SQL connectivity ( %DLC%\bin\_mprosrv c:\dir\north.db -S testing_sql -N TCP -L 8000 -c 350 -B 1000 -n 200 )

I can access this new database thru SQL explorer and also SQL programmer could run the SQL 'CREATE Table' statements/scripts and created blank tables in the progress database. But I cannot acesss/see these tables thru the progress database dctionary where my part of programming comes.

It is vice-versa as I cannot see/access the tables created thru 4gl database dictionary from the SQL explorer

So can anyone please suggest/advise on what are the things I am missings/need to setup/understand in this situation.

Thanks in Advance,
Ashok

 

[Lee Curzon]

Ashok,

There are some notable differences between SQL and 4GL tables.

Hopefully these Knowledgebase entries will explain (I can't link to them online):


KB P19620

What are the difference between SQL-92table and tables created by the
4GL?

FIX:

By the term "SQL-92 tables” we assume database tables created by the
SQL-92 "create table" statement



There is a huge difference in these, much more than can be covered in
one
solution.. I am not sure the SQL-92 doc covers it completely.

Essentially, the Progress 4GL database is a subset of the SQL/92
schema.
The Progress 4GL database contains a limited number of datatypes
(there are certain SQL-92 datatypes, like Float or Timestamp, which
the 4GL does not currently understand.), has smaller maximum record
sizes, and contains some concepts (such as arrays) that don't fit the
SQL standard. But it was the original Progress database, the only one
what works directly with the 4GL

Tables created in either 4GL or SQL-92 are inter-operable. That is,
the 4GL can use tables created by SQL-92, and vice versa. There are
limits on the interoperability. SQL-92 tables not in the PUB schema
cannot be seen by the 4GL.


------

KB 16026

Why a table created with the Data Dictionary cannot be maintained by
the SQL language and the other way around

FACT(s) (Environment):

Progress 9.x
Progress SQL92

FIX:

The reason is that the SQL is not fully compatible with the Progress
Data Dictionary. Some important restrictions to keep in mind:

1) In SQL you cannot create a field with extents. (ANSI 89)

2) In SQL you cannot manipulate the tables with a blank user-id
because this is not known in the SQL standard.

3) Because of this user-id only the user who created the table can
alter it.

4) The tables can be altered by other users if the creator of the
table grants some privileges to these other users. This mechanism
is not known to the 4GL except implementing security thru
Data-Administration, but this is not based on the same principle.

To keep track of what tables are SQL-created or created through the
Data Dictionary, Progress sets a flag in the hidden table, _file. The
field is called _DB-LANG and has value 0 for Data Dictionary creation
and value 1 for SQL creation.

Finally, tables created in SQL can be manipulated through the Data
Dictionary on fields that are not SQL-maintained (For example,
help-fields, description, etc.).


----


KB 21626

Why is a SQL table not viewable from the Data Dictionary?

FACT(s) (Environment):

Progress 9.x

FIX:

The Progress 4GL can only access the tables that are defined in the
PUB schema. Therefore, if the SQL table that is being created is part
of a schema other than PUB, then the Data Dictionary Tool will not be
able to display that table.

In order to have the SQL table that's being created displayed on the
Progress Data Dictionary screen, explicitly specify PUB as the owner
name when issuing the CREATE TABLE SQL Statement. For example:
CREATE TABLE pub.contact (name, phone, company)
AS SELECT contact, phone, name FROM pub.customer;

=====


HTH

Lee

 

[ashok]

Thanks!!

Lee,

The KB entries are very useful and I could understand the problem here in my case.

I have the KB installed but I did not get these entries in my search as my query was not framed correctly maybe.

I will try the last solution mentioned (Create Table with 'PUB'.<table-name>, and hope that solves my problem esle one more thread.

Best Regards,
Ashok