Creating a temp-table outside of Progress

B

beakerman29

New Member
Okay I am very new to progress and I was wanting to use some code to create a temp-table in the progress database. Unfortuntely I have not found a command besides 4G/L and such. I would like to use an SQL like statement such as Create Table ..... I thought about using the create table itslef but receive an
OpenEdge - Failure to acquire exclusive schema lock for DDL operation (7872) error. My version of OpenEdge is 10.1B. Can someone point me in the right direction until I can get some training on Openedge in the next few days ? Thanks
 
It sounds like you are trying to use the SQL-89 syntax that is built in to the 4GL.

Don't waste your breath. You will end up frustrated, angry and in prison.

If the temp table is private to the session then you use the 4GL statements and such that you already alluded to. "define temp-table ..." etc.

If you need to share the table across sessions then there is no option other than a "real" database table. In theory you could reverse engineer the data dictionary tools to figure out how to create one with 4gl (start by creating _file, _field and _index records...) but it will only lead to an even longer prison sentence ;)

Or you can, of course, use sqlexp and the SQL-92 interface but that doesn't usually mix very well with an existing 4gl code base.
 
That's what I was thinking. So what it really boils down to is that the 4G/L interface is really the prefered method of doing this. On the second part the Create table myname.mytables(field type (length)); is this an option for creating a table and then dropping it after done. I'm sure there is a reason you shouldn't do this and think the 4G\L approach may be safer.

Matthew
 
The reason not to do it is that it uses the embedded SQL-89 and that is just going to be endless frustration.

The more detailed reason is that it isn't creating a temp-table -- it is (trying to) creating a real table. Thus it needs a schema lock. It won't be able to get one of those unless you are the sole user of that database. So, I suppose, you /could/ create a temporary single user database and put your table there. But that would be a ridiculous amount of overhead and it would introduce all sorts of pointless complexity (for instance, you will need to create that database... and delete it when you are done with it) when there are simple and well understood 4GL methods readily available.

Rule #1. Progress 4GL is not SQL. The harder you try to make it SQL the unhappier you will be. If you are programming in the 4GL DO NOT TRY TO USE SQL. If you must use SQL with a Progress database use SQL-92 via ODBC/JDBC and Crystal Reports or C# or VB or Java or some language or tool that SQL people use.
 
Okay so you made a reference to SQL-92 I do know that I am using an ODBC connection to try and create the connection but from what you said it sounds like a WINSQL or other Datbase browser tool will simply not be able to create the temp table or real table. I did run across an article from progress that references using the ADO.Net framework to create a temp table so my guess is unless you use a true programming interface that can call the 4G/L methods then you might as well spit in the wind.
 
The first problem here is to get clear what you are trying to do ... otherwise the advice will be hit and miss.

Do you want to create a new table in the DB that will last across sessions or a temporary one that will apply only to the session?

Do you want to access this table from the 4GL or only from SQL?

If a temporary table and SQL only, what are you trying to accomplish? Might it be better done with a view? Have you consulted the SQL reference to make sure that the operators you are expecting to use are available in the Progress implementation?

FWIW, my own experience is that the SQL-92 access is just fine for use with a third party reporting tool, but I wouldn't want to use it for DB updates or production activity.
 
I too am interested in creating temp tables using SQL.
I am a .net developer with Informix 4GL background (not much use here).
Before developing a .net solution I like to use an SQL editor such as WINSQL to run queries as I research the available data.
Temp tables are often handy for rolling up data during one of these discovery sessions.
I am interested in using these temp tables in one session and then dropping them.

Our Progress database is used with our Epicor Vantage ERP system. There is an account on the Progress database named SYSPROGRESS. This is the account we use to connect through our OpenEdge ODBC driver.

Since I was unable to find an SQL temp table solution, I tried to use CREATE TABLE [owner].tablename

I received errors when I did not specify an owner. I tried to specify SYSPROGRESS as the owner and received the same error.

I then specified and owner using CREATE TABLE jplahitko.tablename and the table now exists. I inserted a record and retrieved it using a SELECT statement. However, when I try to drop the table, I get the same error as beakerman29 above.
OpenEdge - Failure to acquire exclusive schema lock for DDL operation (7872)

I have tried these with the same error:
DROP TABLE tablename
DROP TABLE sysprogress.tablename
DROP TABLE jplahitko.tablename

I can create a table and insert records for a user that is not actually in the database. But I cannot delete it.

Any thoughts?
Jim
 
You must log in or register to reply here.
Back
Top