clivejmorrison
New Member
Hi
I am using Progress 9.1E CHUI on a LINUX Platform and I have set up the Progress SQL Dataserver against my database, so that we can access the Progress Database via the Merant DataDirect 4.10 32-Bit ODBC Drivers, which are supplied with the Progress Client Networking installation disks.
Anyway, I can connect the database via a DSN in Crystal Report or Busniess Objects Data Integrator and I can see all of the PUB tables and the SYSPROGRESS views. However I want to be able to set up some views against the tables in this database, so that my ODBC Users can access the views rather then tables themselves. So I have done the following:-
In the progress procedure editor, I have entered the following (log is the name of the underlying Progress table):-
create view di_log as select logdate, logtime, logseq, transid, tablename, event, keyvalue, peerlist, replicdate, replictime, fieldgroup, loguser, logstack, result from log.
grant select on di_log to public.
And then executed these two statements, this has created the view within the Progress Data Dictionary:-
08/11/06 10:08:12 PROGRESS Report
Database: REPLOG (PROGRESS)
Update- Group Check
View Name able By? Option?
------------------------------ ------- ----- -------
DI_LOG yes no no
View Name: DI_LOG
Base Tables: LOG
View Def: VIEW DI_LOG AS SELECT LOGDATE, LOGTIME, LOGSEQ, TRANSID,
TABLENAME, EVENT, KEYVALUE, PEERLIST, REPLICDATE, REPLICTIME,
FIELDGROUP, LOGUSER, LOGSTACK, RESULT FROM LOG
Can-Read: root,*
Can-Write: *
Can-Create: root
Can-Delete: root
Order View Column Name Base Columns
----- ------------------------------ --------------------------------------
6 EVENT LOG.EVENT
11 FIELDGROUP LOG.FIELDGROUP
7 KEYVALUE LOG.KEYVALUE
1 LOGDATE LOG.LOGDATE
3 LOGSEQ LOG.LOGSEQ
13 LOGSTACK LOG.LOGSTACK
2 LOGTIME LOG.LOGTIME
12 LOGUSER LOG.LOGUSER
8 PEERLIST LOG.PEERLIST
9 REPLICDATE LOG.REPLICDATE
10 REPLICTIME LOG.REPLICTIME
14 RESULT LOG.RESULT
5 TABLENAME LOG.TABLENAME
4 TRANSID LOG.TRANSID
My problem is, that I would now expect to be able to see this view via any ODBC connection, however the view does not appear in the list of objects accessible within Crystal Reports or Data Intergrator. Which is where I would expect it to appear, next to all the existing tables and sysprogress views. Is there something which I am missing? Or is this a 'feature' of the PROGRESS/SQL/ODBC compatibility?
Also, within the Progress Procedure Editor, I don't have any problem at all in selecting data from this view with an SQL SELECT statement eg
SELECT * FROM di_log.
I have seen this done at a previous place where I worked, but being a die-hard Progress developer, I rarely get my hands dirty with SQL, so any help would be appriciated.
Thanks
Clive Morrison
Progress Team Leader
City-Link Ltd
LONDON
England
I am using Progress 9.1E CHUI on a LINUX Platform and I have set up the Progress SQL Dataserver against my database, so that we can access the Progress Database via the Merant DataDirect 4.10 32-Bit ODBC Drivers, which are supplied with the Progress Client Networking installation disks.
Anyway, I can connect the database via a DSN in Crystal Report or Busniess Objects Data Integrator and I can see all of the PUB tables and the SYSPROGRESS views. However I want to be able to set up some views against the tables in this database, so that my ODBC Users can access the views rather then tables themselves. So I have done the following:-
In the progress procedure editor, I have entered the following (log is the name of the underlying Progress table):-
create view di_log as select logdate, logtime, logseq, transid, tablename, event, keyvalue, peerlist, replicdate, replictime, fieldgroup, loguser, logstack, result from log.
grant select on di_log to public.
And then executed these two statements, this has created the view within the Progress Data Dictionary:-
08/11/06 10:08:12 PROGRESS Report
Database: REPLOG (PROGRESS)
Update- Group Check
View Name able By? Option?
------------------------------ ------- ----- -------
DI_LOG yes no no
View Name: DI_LOG
Base Tables: LOG
View Def: VIEW DI_LOG AS SELECT LOGDATE, LOGTIME, LOGSEQ, TRANSID,
TABLENAME, EVENT, KEYVALUE, PEERLIST, REPLICDATE, REPLICTIME,
FIELDGROUP, LOGUSER, LOGSTACK, RESULT FROM LOG
Can-Read: root,*
Can-Write: *
Can-Create: root
Can-Delete: root
Order View Column Name Base Columns
----- ------------------------------ --------------------------------------
6 EVENT LOG.EVENT
11 FIELDGROUP LOG.FIELDGROUP
7 KEYVALUE LOG.KEYVALUE
1 LOGDATE LOG.LOGDATE
3 LOGSEQ LOG.LOGSEQ
13 LOGSTACK LOG.LOGSTACK
2 LOGTIME LOG.LOGTIME
12 LOGUSER LOG.LOGUSER
8 PEERLIST LOG.PEERLIST
9 REPLICDATE LOG.REPLICDATE
10 REPLICTIME LOG.REPLICTIME
14 RESULT LOG.RESULT
5 TABLENAME LOG.TABLENAME
4 TRANSID LOG.TRANSID
My problem is, that I would now expect to be able to see this view via any ODBC connection, however the view does not appear in the list of objects accessible within Crystal Reports or Data Intergrator. Which is where I would expect it to appear, next to all the existing tables and sysprogress views. Is there something which I am missing? Or is this a 'feature' of the PROGRESS/SQL/ODBC compatibility?
Also, within the Progress Procedure Editor, I don't have any problem at all in selecting data from this view with an SQL SELECT statement eg
SELECT * FROM di_log.
I have seen this done at a previous place where I worked, but being a die-hard Progress developer, I rarely get my hands dirty with SQL, so any help would be appriciated.
Thanks
Clive Morrison
Progress Team Leader
City-Link Ltd
LONDON
England