db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: Unable to create temporary table when using a readonly database
Date Tue, 29 Nov 2005 18:43:40 GMT


-----Original Message-----
From: Leslie Software [mailto:lesliesoftware@yahoo.com] 
Sent: Tuesday, November 29, 2005 12:17 PM
To: Derby Discussion
Subject: Unable to create temporary table when using a readonly database

I have created a read-only database (currently a directory structure
but eventually it will be moved into a .jar).  I setup the properties
for the database log and the temp directory fine and most things seem
to work.  If I perform a complex query I can see temporary files being
created in the temp directory I specified; however when I try to create
my own temporary table - like this:

declare global temporary table test (name VARCHAR(10), value INT) 
not logged

I get this error:
SQL Execution Error:Execute error (25503):
DDL is not permitted for a read-only connection, user or database.

Should I be able to create temporary tables for a ready-only database? 
If not how will I be able to write multi stage queries  (perform step
one and place results in a temporary table then join the temp table to
the other tables to get the results needed)?  I am sure that as I
develop my application I will run into cases where I will need to use
temporary tables to perform the queries I need.  I followed the steps
in the manual
<http://db.apache.org/derby/docs/10.0/manuals/develop/develop34.html#Creatin
g+and+Preparing+the+Database+for+Read-Only+Use>
to set things up.
-=-
Its one thing for the engine to create its own temp tables. Its another for
you when you create a read only database. As it implies, the database is
only read only by the users. 

And of course you don't have any real way to manage users. No GRANT or
REVOKE statements (At least not documented in the reference manual...)

Using GRANT and REVOKE, you could make up a sysadm role user and revoke all
modifying SQL statements and only grant select.

It would be interesting to see if you create a read only database that has
stored procedures. It may be possible that you could create and use temp
tables via a stored procedure, but I'm just guessing. I don't know your app,
nor have I played with read only features of Derby.


	

	
		
__________________________________________________________ 
Find your next car at http://autos.yahoo.ca



Mime
View raw message