Cannot Drop Table After Creating

[jplahitko]

I 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 this error:
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.
I have read in other threads that all users must be out with only one user connected. This doesn't make sense to me.

Can anyone tell me how to delete this table?

 

[vinod_home]

SYSPROGRESS is a system user and is not safe to be used for a ODBC connection. But either way.. I think there is a discussion in the "Development" forum with a subject "Creating a temp-table outside of Progress" regarding a similar situation.

Temp-tables are session tables for storing data only for the session. You do not create a table in the database or make database changes for that. So basically, you shouldn't be using "CREATE TABLE, DROP TABLE" or any schema changing commands for that.

A temp-table is like you define a variable or an array type variable within a session. Dont know if this would work, but something similar should be available for you to work with in SQL. Don't know if progress would allow it.

[COLOR=#000000][FONT=Courier New]DECLARE @ProductTotals TABLE 
 (
   ProductID int, 
   Revenue money
 )
[/FONT][/COLOR]

 

[jplahitko]

I submitted this question in the Development forum and was asked to submit it here since the underlying question is related directly to SQL.

I understand what temp tables are and why they are used. I tried to use CREATE TABLE because I was unable to find SQL support for temp tables in Progress. I am here now asking if it is possible to create temp tables using SQL?

And, now that I have the table I created, can it be deleted?

Thank you for responding.

 

[jplahitko]

Can someone tell me if there is an SQL solution for creating temp tables in progress?

 

[RealHeavyDude]

TEMP-TABLE are a functionality of the Progress 4GL since (I think) V8. They only live in the client runtime session and the database is not aware of them in any form. They're are NOT a feature of the database. The whole idea of Temp-Tables is to have an object which behaves similar to a database table which you can use to store and process temporary data which have no impact on the database.

Therefore there's no way to use Temp-Tables via the SQL92 engine. The client technology (you mention .NET, C#?) must provide such functionality. AFAIK .NET provides something like data tables maybe these will help you.

If you want to create/update/delete database objects like tables you must specify a schema. The default schema is the PUB schema which should work for you.

HTH, RealHeavyDude.

 

[jplahitko]

RealHeavyDude - First, thank you for replying.

I have some background with Informix which also has a 4GL language. Now I know this is like comparing apples to oranges, but just for the sake of discussion, Informix also allowed temp tables. Not only from within 4GL, but also through their ODBC/SQL driver. This exposed functionality was a powerful tool that I had gotten used to using and I sorely miss it since coming to Progress. I was hoping this functionality was also available in Progress with the correct tool set.

With Informix, I could use the following syntax:

SELECT column, column FROM table, table INTO TEMP tablename.

When finished using the table, I could call: DROP tablename
-or- I could leave it and it would die with the session as you stated above.

So, I take that you are not aware of any such capability with Progress?

Thank you again for your response.

 

[tamhas]

I see no reference to this kind of syntax in the SQL Reference.

You might want to try http://communities.progress.com/pcom/community/psdn/openedge for a larger audience.

 

[vinod_home]

I think its something specific to Informix.

A statement from Informix documentation.
http://publib.boulder.ibm.com/infoc....jsp?topic=/com.ibm.sqls.doc/ids_sqs_0571.htm

This chapter introduces concepts that are common to SQL programming in any language. Before you can write a successful program in a particular programming language, you must first become fluent in that language. Then, because the details of the process are different in every language, you must become familiar with the publication for the IBM® Informix SQL API specific to that language.

 

[tamhas]

Not unique to Informix. Just not a part of the Progress SQL.