PRO/SQL View to ODBC

[sdutta]

Good Afternoon,

I'm trying to expose a OE created view to our reporting tool in Cognos. I would like to know if it is possible to do so. I've noticed that I can create the view through the procedure editor but I am unable to do for each queries on it. However, I can do SELECT queries on it through the procedure editor.

I also tried to expose this view to ODBC utilizing sqlexp command that comes with OE but am getting an error stating that Table/View/Synonym is not found. Any ideas? Thanks.

 

[TomBascom]

The "SQL" that you can get to from the procedure editor is SQL-89.

It sucks.

Don't use it.

Only pain and agony are found down that path.

ODBC/JDBC connections and sqlexp do not use this SQL and are unaware of its existence.

The SQL-92 engine is another beast entirely and has no relation to the procedure editor SQL. This is what sqlexp and odbc/jdbc connections use. You can not access SQL-92 from the 4GL or from the procedure editor.

 

[RealHeavyDude]

The SQL you can run from the procedure editor is not really SQL. It is somehow embedded into the 4GL and gets interpreted by 4GL compiler. Therefore whatever you do there will never be known by the real SQL engine which is the SQL92 engine of the database which was introduced with Progress V9.

But, as the Progress database is not an SQL database in the first place, it is not there out-of-the-box. You need to configure the SQL92 access. But you need to be aware, depending on the version of Progress/OpenEdge you are running - it will suck more or less. Even in the latest greatest release it's still no SQL database it just gets close as they keep adding features and stability ...

To configure the SQL92 access you need to:

1. Configure a dedicated secondary login broker for the SQL clients.
2. You need to set up the SQL security. There is a big difference in the approach to security between Progress ( revoke ) vs. SQL ( grant ).

If you search the knowledge base for the terms "secondary login broker" and "sysprogress" you will get tons of useful information that should enable you to set it up.

Heavy Regards, RealHeavyDude.

 

[sdutta]

Good Morning,

We actually already have sql brokers in place for our actual database tables. Cognos and Java is already hitting these standard database tables in progress are are able to do queries and writes. However, they could not see the views. i did grant sql access to the name of the view on our database sql brokers.

I think the problem is view related and the way I created it. Is there some other way to create the view rather than through the procedure explorer? I tried created pub.tablename but this gave me an error stating that the pub database does not exist because that is not what we name the database.

Any ideas/suggestions would be greatly appreciated.
Thanks.

 

[cj_brandt]

can you use sqlexp to create the view.

 

[sdutta]

Thank you all for your help. I tried utilizing the sqlexp tool which worked but was a real pain. I ended up utilizing winsql via an ODBC connection to the database and that worked like a charm. I have been able to create views and have our reporting tool cognos access the view. My next question is does anyone know where the views are stored? I tried to do a query on _view table but it does not reside there. I would imagine that the _view table is probably for views created via 4gl.

I am trying to see if there is some way for me to create a view that will replicate over to a target database (for ODBC) if created on the source database. I tried to create a view in the target database but unfortunately, the target database does not allow you to perform this function (which makes sense since it is suppose to be a read only database). any suggestions? Thanks.

 

[MaximMonin]

Check Contents of _SysViews table.

 

[sdutta]

Thanks for the reply. unfortunately it is not in _sysviews. I did create the views in the source database...but i don't think it copied over to the target...i'm thinking its a non-system table? Strange. I'll call progress to find out.

 

[sdutta]

Okay....it did copy over to the target (after i did a backup/restore and restarted replication)..although I still do not know where it is stored at. What I did see was that the replication had stopped because it corrupted the target recovery file...I need to do further research and find out if this is caused by just creating the view, giving myself DBA permission for ODBC, or something else. update: okay, I think it got corrupted because i tried to mess with the target database and give myself resource/dba privileges and create views. If i leave target alone and create the views in the source database and give myself access only in the source database, it looks like that information is now being transferred to the target database. Its so nice to have a sandbox environment to play with! Thanks everyone for all your help.