You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Leslie Software <le...@yahoo.com> on 2005/10/31 18:15:21 UTC
Trying to use UPDATE that needs more then one table for its WHERE clause
I have an application that needs to update a list of cards from
multiple editions. The database is setup with a card table which hold
the relavent information about each card; an edition table which holds
the information about each edition and; a card_version table which
keeps track of which edition each card appears. When a new edition
comes out there could be new cards that have never been seen before (no
problem I am inserting the new ones from a temp table) and some that
are reprinted and have slight changes in their contents. That updating
of card information from the new edition for cards that already exist
is giving me grief.
Once I have loaded the new edition's cards into a temp table I want to
execute some SQL that will update the card table with the updated
information for those cards that have been reprinted.
Here is the SQL I wrote that can find those cards (the ones printed in
the new edition that are alread in the database from other editions):
-- Retrieve the cards that appear in the newly loaded edition and
already exist in the database
select c.name
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
When I try to create an UPDATE SQL statement that can update the card
information for those cards I find that Derby's UPDATE does not support
more than one table and I cannot sue JOIN ON either.
After much reading and consulting with those more knowledgable than I
with SQL I found FOR UPDATE OF / WHERE CURRENT OF which I thought I
could use so that my above query could be changed to this:
select c.name, c.rules_text
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
for update of rules_text
But it turns out that this is not possible due to the "Requirments for
updatable cursors" which says the query cannot involve more then one
query.
So my question is how do I update my card table with information from
my temporary table where the cards match a certain query? Can this be
done with SQL or do I have to create code in Java to do it?
Thanks,
Ian
--
Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)
__________________________________________________________
Find your next car at http://autos.yahoo.ca
Re: Unable to create temporary table when using a readonly database
Posted by Michael Segel <ms...@segel.com>.
On Tuesday 29 November 2005 14:13, Rick Hillegas wrote:
> Hi Ian,
>
> This sounds like the wrong behavior to me, and you are welcome to file a
> bug. I realize that is cold comfort.
>
> Regards,
> -Rick
Uhm yes, silly question.
How is it a bug?
Not to sound Clinton-esque, but what do you mean when you say "read-only"?
From Derby's Developer Guide
(My version has it on page 30):
"You can create Derby databases for use on read-only media such as CD-ROMs.
Derby databases in zip or jar files are also read-only databases. Typically,
read-only databases are deployed with an application in an embedded
environment."
[Note any spelling errors are mine. ;-]
So, by this, it is not a bug to stop the creation of temp tables by the user
when you create a read-only database.
Sometimes I wonder if people are trying to create read-only copies of a derby
database because there is no concept of PERMISSIONS? (No GRANT/REVOKE
statements....)
But hey, what do I know?
Re: Unable to create temporary table when using a readonly database
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Satheesh Bandaram wrote:
> Don't think current temporary tables is designed to work in read-only
> databases. It is not documented as a supported behavior. So don't think
> this is a bug.
>
> http://db.apache.org/derby/docs/10.1/ref/rrefdeclaretemptable.html
>
> Also look at Developer guide about read-only databases:
>
> http://db.apache.org/derby/docs/10.1/devguide/tdevdeploy26887.html
>
> However, this may be useful. An IMPROVEMENT request would be good to file.
Yep, seems like a good improvement, it allows application developers to
use temp tables in their application while not allowing modification of
the read-only data.
Dan.
Re: Unable to create temporary table when using a readonly database
Posted by Michael Segel <ms...@segel.com>.
On Tuesday 29 November 2005 16:02, Satheesh Bandaram wrote:
> Michael Segel wrote:
>
>
>
> <>Eeewwww, apply the KISS rule. Where are the temp tables actually created?
> In memory only? In /tmp? Or in some place where the JVM dictates? Or cwd?
> (Current Working Directory).
>
> If you had clicked on the second link I had, you would know the answer!
>
>
> For the read-only database, set the property derby.storage.tempDirectory to
> a writable location. Derby needs to write to temporary files for large
> sorts required by such SQL statements as ORDER BY, UNION, DISTINCT, and
> GROUP BY. For more information about this property, see Tuning Derby.
> derby.storage.tempDirectory=c:/temp/mytemp
What?
Me read the manual? ;-)
Now that would take the fun out of it.
Again, that would allow Derby to function.
Why would you want to set up a read-only database where the app/user would
also create temp tables? Kinda like letting people smoke in a non-smoking
restaurant. ;-)
I really do think the better solution would be to create permissions and then
allow the person to grant or revoke the permissions. But then again, that
would be a major change...
Re: Unable to create temporary table when using a readonly database
Posted by Michael Segel <ms...@segel.com>.
On Tuesday 29 November 2005 14:59, Satheesh Bandaram wrote:
> Don't think current temporary tables is designed to work in read-only
> databases. It is not documented as a supported behavior. So don't think
> this is a bug.
>
Right.
> However, this may be useful. An IMPROVEMENT request would be good to file.
>
> Satheesh
>
Eeewwww, apply the KISS rule. Where are the temp tables actually created?
In memory only? In /tmp? Or in some place where the JVM dictates? Or cwd?
(Current Working Directory).
I guess its a question of why?
Wouldn't it be better to add permissions to Derby instead?
But hey! What do I know? I thought that Oracle and Sybase got it right when
you stored database users and passwords within the database rather than using
Unix to authenticate... ;-)
Re: Unable to create temporary table when using a readonly database
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Don't think current temporary tables is designed to work in read-only
databases. It is not documented as a supported behavior. So don't think
this is a bug.
http://db.apache.org/derby/docs/10.1/ref/rrefdeclaretemptable.html
Also look at Developer guide about read-only databases:
http://db.apache.org/derby/docs/10.1/devguide/tdevdeploy26887.html
However, this may be useful. An IMPROVEMENT request would be good to file.
Satheesh
Rick Hillegas wrote:
> Hi Ian,
>
> This sounds like the wrong behavior to me, and you are welcome to file
> a bug. I realize that is cold comfort.
>
> Regards,
> -Rick
>
> Leslie Software wrote:
>
>> 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#Creating+and+Preparing+the+Database+for+Read-Only+Use>
>>
>> to set things up.
>>
>> Any advice would be appreciated.
>>
>> Thanks,
>>
>> Ian
>>
>>
>>
>>
>>
>>
>> __________________________________________________________ Find your
>> next car at http://autos.yahoo.ca
>>
>>
>
>
>
Re: Unable to create temporary table when using a readonly database
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Ian,
This sounds like the wrong behavior to me, and you are welcome to file a
bug. I realize that is cold comfort.
Regards,
-Rick
Leslie Software wrote:
>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#Creating+and+Preparing+the+Database+for+Read-Only+Use>
>to set things up.
>
>Any advice would be appreciated.
>
>Thanks,
>
>Ian
>
>
>
>
>
>
>__________________________________________________________
>Find your next car at http://autos.yahoo.ca
>
>
RE: Unable to create temporary table when using a readonly database
Posted by Michael Segel <ms...@segel.com>.
-----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
Unable to create temporary table when using a readonly database
Posted by Leslie Software <le...@yahoo.com>.
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#Creating+and+Preparing+the+Database+for+Read-Only+Use>
to set things up.
Any advice would be appreciated.
Thanks,
Ian
__________________________________________________________
Find your next car at http://autos.yahoo.ca
Re: Trying to use UPDATE that needs more then one table for its WHERE clause
Posted by Leslie Software <le...@yahoo.com>.
Bingo - that worked. Yippeee:-)
So for my education using "set rules_text =
session.load_card.rules_text" did not result in a syntax error. Why?
Or does not behave as I expected. The number of rows effected was
correct.
The solution you offered included two sub selects (one to provide the
list of cards to update in the update's where clause and one to provide
the values to set in the set clause); is there a section of the Derby
documentation I could read that would help me understand where I can
use sub selects. The documentation for the update statement looks like
this:
UPDATE table-Name
SET column-Name = Value
[ , column-Name = Value} ]*
[WHERE clause] |
UPDATE table-Name
SET column-Name = Value
[ , column-Name = Value ]*
WHERE CURRENT OF
I have used sub selects in where clauses before so I might have figured
that out; however my friend that is helping me with my SQL keeps
suggesting I unroll my sub selects; so in my mind I have been avoiding
them lately. I guess I have to learn when they are appropriate and
when they are not. Did I miss the place where 'Value' is defined? I
admit I did not read the whole reference document. I am quite new to
SQL as well as Derby so I am learning as I go.
Thank you very much for your help. I hope you don't mind my asking a
few more questions to solidify my understanding.
Ian
--- Suavi Ali Demir <de...@yahoo.com> wrote:
> maybe this is what you want?
>
> update card set rules_text = (select lc.rules_text from
> session.load_card as lc where card.name = lc.name)
> where card.name in (
> select c.name
> from edition as e, card_version as cv, card as c, edition as e2,
> session.load_card as lc
> where
> e.edition_id = cv.edition_id and
> c.card_id = cv.card_id and
> c.name = lc.name and
> c.card_id is not null and
> e.release_date < e2.release_date and
> e2.name = 'Test Newest'
> )
>
> Regards,
> Ali
>
<snip>
--
Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)
__________________________________________________________
Find your next car at http://autos.yahoo.ca
Re: Trying to use UPDATE that needs more then one table for its WHERE clause
Posted by Suavi Ali Demir <de...@yahoo.com>.
maybe this is what you want?
update card set rules_text = (select lc.rules_text from session.load_card as lc where card.name = lc.name)
where card.name in (
select c.name
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
)
Regards,
Ali
Leslie Software <le...@yahoo.com> wrote:
Thanks Ali that must be getting closer - that was the fist time I did
not get a syntax error attempting an update. The only trouble is it
did not do anthing. Here is what I executed:
update card set rules_text = session.load_card.rules_text
where card.name in (
select c.name
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
)
session.load_card is my temp table with the new data I am loading. The
query executes fine and says two rows were affected but when I look at
the card table the rules_text column has not changed. At fist I
thought I had auto commit turned off or something but I checked my code
and then ran a test with a constant value.
If I run this instead:
update card set rules_text = 'Test'
where ...
The data in the appropriate rows of the card table contain the constant
'Test'.
The data I want is in the load_card table. When I execute this select
statement:
select c.name, c.rules_text, lc.rules_text as new_rules_text
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
I can see the two cards that have new information along with the old
information and the new information. So the new data I want updated is
there and the update you suggested works with a constant. All I need
now is to get the update to use the data from the temp table.
Any idea what else I am missing?
Thanks,
Ian
--- Suavi Ali Demir wrote:
> update card set anythingyourwant where card.name in (
>
> select c.name
> from edition as e, card_version as cv, card as c, edition as e2,
> session.load_card as lc
> where
> e.edition_id = cv.edition_id and
> c.card_id = cv.card_id and
> c.name = lc.name and
> c.card_id is not null and
> e.release_date < e2.release_date and
> e2.name = 'Test Newest'
>
> )
>
> Regards,
> Ali
--
Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)
__________________________________________________________
Find your next car at http://autos.yahoo.ca
Re: Trying to use UPDATE that needs more then one table for its WHERE clause
Posted by Leslie Software <le...@yahoo.com>.
Thanks Ali that must be getting closer - that was the fist time I did
not get a syntax error attempting an update. The only trouble is it
did not do anthing. Here is what I executed:
update card set rules_text = session.load_card.rules_text
where card.name in (
select c.name
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
)
session.load_card is my temp table with the new data I am loading. The
query executes fine and says two rows were affected but when I look at
the card table the rules_text column has not changed. At fist I
thought I had auto commit turned off or something but I checked my code
and then ran a test with a constant value.
If I run this instead:
update card set rules_text = 'Test'
where ... <same as above>
The data in the appropriate rows of the card table contain the constant
'Test'.
The data I want is in the load_card table. When I execute this select
statement:
select c.name, c.rules_text, lc.rules_text as new_rules_text
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
I can see the two cards that have new information along with the old
information and the new information. So the new data I want updated is
there and the update you suggested works with a constant. All I need
now is to get the update to use the data from the temp table.
Any idea what else I am missing?
Thanks,
Ian
--- Suavi Ali Demir <de...@yahoo.com> wrote:
> update card set anythingyourwant where card.name in (
>
> select c.name
> from edition as e, card_version as cv, card as c, edition as e2,
> session.load_card as lc
> where
> e.edition_id = cv.edition_id and
> c.card_id = cv.card_id and
> c.name = lc.name and
> c.card_id is not null and
> e.release_date < e2.release_date and
> e2.name = 'Test Newest'
>
> )
>
> Regards,
> Ali
--
Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)
__________________________________________________________
Find your next car at http://autos.yahoo.ca
Re: Trying to use UPDATE that needs more then one table for its WHERE clause
Posted by Suavi Ali Demir <de...@yahoo.com>.
update card set anythingyourwant where card.name in (
select c.name
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
)
Regards,
Ali
Leslie Software <le...@yahoo.com> wrote:
I have an application that needs to update a list of cards from
multiple editions. The database is setup with a card table which hold
the relavent information about each card; an edition table which holds
the information about each edition and; a card_version table which
keeps track of which edition each card appears. When a new edition
comes out there could be new cards that have never been seen before (no
problem I am inserting the new ones from a temp table) and some that
are reprinted and have slight changes in their contents. That updating
of card information from the new edition for cards that already exist
is giving me grief.
Once I have loaded the new edition's cards into a temp table I want to
execute some SQL that will update the card table with the updated
information for those cards that have been reprinted.
Here is the SQL I wrote that can find those cards (the ones printed in
the new edition that are alread in the database from other editions):
-- Retrieve the cards that appear in the newly loaded edition and
already exist in the database
select c.name
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
When I try to create an UPDATE SQL statement that can update the card
information for those cards I find that Derby's UPDATE does not support
more than one table and I cannot sue JOIN ON either.
After much reading and consulting with those more knowledgable than I
with SQL I found FOR UPDATE OF / WHERE CURRENT OF which I thought I
could use so that my above query could be changed to this:
select c.name, c.rules_text
from edition as e, card_version as cv, card as c, edition as e2,
session.load_card as lc
where
e.edition_id = cv.edition_id and
c.card_id = cv.card_id and
c.name = lc.name and
c.card_id is not null and
e.release_date < e2.release_date and
e2.name = 'Test Newest'
for update of rules_text
But it turns out that this is not possible due to the "Requirments for
updatable cursors" which says the query cannot involve more then one
query.
So my question is how do I update my card table with information from
my temporary table where the cards match a certain query? Can this be
done with SQL or do I have to create code in Java to do it?
Thanks,
Ian
--
Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)
__________________________________________________________
Find your next car at http://autos.yahoo.ca