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 Bruce Wen <cr...@gmail.com> on 2011/01/02 02:30:45 UTC
commands describe and show failed in eclipse derby plugin SQL editor
Hi Friends,
I used *eclipse-jee-ganymede-SR2-win32* and it contains Apache Derby plug-in
in default. However, when I execute *describe table_name* statement, "*syntax
error:Encountered "describe**" at line 1, column 1*" is encountered
unexpectedly. And *show schemas* statement also not workable because of the
same error.
Not sure if you anybody encountered the same problems during your daily
work. And could you please help me out of this if you have any solutions
there?
Thanks in advance!
Bruce Wen (GuangMing)
-----------------------------------------------------------------------------------------------------------------
"Undertake not what you cannot perform, but be careful to keep your promise
- George Washington"
-----------------------------------------------------------------------------------------------------------------
Internet Email: wengm@mail.com
LinkedIn: cn.linkedin.com/in/wengm
Blog: crest-boy.livejournal.com
MSN: crest-boy@hotmail.com
-----------------------------------------------------------------------------------------------------------------
RE: how to show all tables in current schema without using ij tool?
Posted by Sam Aoudi <ao...@hotmail.com>.
Thanks Matt and Emmanuel,
The Show table issue is resolved.
However still working on delete issue.
Date: Thu, 6 Jan 2011 08:29:12 -0700
Subject: FW: how to show all tables in current schema without using ij tool?
From: Matt.Pouttu-Clarke@icrossing.com
To: aoudi_s@hotmail.com
Message body
Hi Sam,
This comment also addresses your questions I hope...
Cheers,
Matt
------ Forwarded Message
From: Matt Pouttu-Clarke <Ma...@icrossing.com>
Reply-To: Derby Discussion <de...@db.apache.org>
Date: Thu, 06 Jan 2011 08:17:46 -0700
To: Derby Discussion <de...@db.apache.org>
Cc: Sam Aoudi <ao...@hotmail.com>
Subject: Re: how to show all tables in current schema without using ij tool?
Hi Emmanuel,
For number 1:
Connection conn = DriverManager.getConnection("jdbc:derby:<db name>");
ResultSet tables = conn.getMetaData().getTables(null, null, null, null);
while(tables.next()) {
System.out.println(tables.getString("TABLE_NAME"));
}
tables.close();
For number 2: I don’t know because I never use cascading DB constraints, as they don’t scale to any significant data sizes. However if you need cascading constraints with more scalability, try using a O/R mapping framework such as Hibernate...
Cheers,
Matt
On 1/6/11 5:34 AM, "Emmanuel Cecchet" <ma...@frogthinker.org> wrote:
Hi,
I am struggling with issues when it comes to Apache derby:
1- I haven't been able to get a list of Tables in a schema without using ij. Is this feasible?
SELECT * FROM SYS.SYSTABLES
2- I haven't been able to use "On Delete Cascade", any good examples are appreciated.
CREATE TABLE a (pk INT NOT NULL CONSTRAINT a_pk PRIMARY KEY)
CREATE TABLE b (x LONG VARCHAR, id INT CONSTRAINT b_fk REFERENCES a(pk) ON DELETE CASCADE)
INSERT INTO a VALUES (1)
INSERT INTO b VALUES ('hello', 1)
SELECT * FROM a,b
PK X ID
1 hello 1 DELETE FROM b
SELECT * FROM a,b
PK X ID
Hope this helps,
Emmanuel
iCrossing Privileged and Confidential Information
This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
------ End of Forwarded Message
iCrossing Privileged and Confidential Information
This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: how to show all tables in current schema without using ij tool?
Posted by Matt Pouttu-Clarke <Ma...@icrossing.com>.
Hi Emmanuel,
For number 1:
Connection conn = DriverManager.getConnection("jdbc:derby:<db
name>");
ResultSet tables = conn.getMetaData().getTables(null, null,
null, null);
while(tables.next()) {
System.out.println(tables.getString("TABLE_NAME"));
}
tables.close();
For number 2: I don¹t know because I never use cascading DB constraints, as
they don¹t scale to any significant data sizes. However if you need
cascading constraints with more scalability, try using a O/R mapping
framework such as Hibernate...
Cheers,
Matt
On 1/6/11 5:34 AM, "Emmanuel Cecchet" <ma...@frogthinker.org> wrote:
> Hi,
>
>> I am struggling with issues when it comes to Apache derby:
>> 1- I haven't been able to get a list of Tables in a schema without using
>> ij. Is this feasible?
>>
> SELECT * FROM SYS.SYSTABLES
>> 2- I haven't been able to use "On Delete Cascade", any good examples are
>> appreciated.
>>
> CREATE TABLE a (pk INT NOT NULL CONSTRAINT a_pk PRIMARY KEY)
> CREATE TABLE b (x LONG VARCHAR, id INT CONSTRAINT b_fk REFERENCES a(pk) ON
> DELETE CASCADE)
> INSERT INTO a VALUES (1)
> INSERT INTO b VALUES ('hello', 1)
> SELECT * FROM a,b
>
> PK X ID
> 1 hello 1 DELETE FROM b
> SELECT * FROM a,b
>
> PK X ID
>
> Hope this helps,
> Emmanuel
>
iCrossing Privileged and Confidential Information
This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: how to show all tables in current schema without using ij tool?
Posted by Emmanuel Cecchet <ma...@frogthinker.org>.
Hi,
> I am struggling with issues when it comes to Apache derby:
> 1- I haven't been able to get a list of Tables in a schema without
> using ij. Is this feasible?
SELECT * FROM SYS.SYSTABLES
> 2- I haven't been able to use "On Delete Cascade", any good examples
> are appreciated.
CREATE TABLE a (pk INT NOT NULL CONSTRAINT a_pk PRIMARY KEY)
CREATE TABLE b (x LONG VARCHAR, id INT CONSTRAINT b_fk REFERENCES a(pk)
ON DELETE CASCADE)
INSERT INTO a VALUES (1)
INSERT INTO b VALUES ('hello', 1)
SELECT * FROM a,b
PK X ID
1 hello 1
DELETE FROM b
SELECT * FROM a,b
PK X ID
Hope this helps,
Emmanuel
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development& Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet
how to show all tables in current schema without using ij tool?
Posted by Sam Aoudi <ao...@hotmail.com>.
Hi,
I am struggling with issues when it comes to Apache derby:
1- I haven't been able to get a list of Tables in a schema without using ij. Is this feasible?
2- I haven't been able to use "On Delete Cascade", any good examples are appreciated.
Thanks
Re: Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?
Posted by Pavel Bortnovskiy <PB...@Jefferies.com>.
Hello, Witold:
thanks for your response. It might be the same issue and I will try to
follow the same suggestions that Bryan gave you.
FYI: I am using Derby 10.6.2.1
If any of the Derby folks can shed some additional light onto this, it
would be greatly appreciated,
Pavel.
From:
Witold Szczerba <pl...@gmail.com>
To:
Derby Discussion <de...@db.apache.org>
Date:
01/05/2011 01:37 PM
Subject:
Re: Left-Join extremely slow in comparison to Inner-Join. How to tune
Derby?
Hi there,
Look at this:
https://issues.apache.org/jira/browse/DERBY-4595
Do you think it might be the same case I had some time ago?
There were some changes in the code in Jun/2010, maybe you have older
version or the change did not resolved the problem...
The funny thing is that the Apache Derby guys changed the issue type
from bug to improvement - so, for them, a query which should last 2 or
3 seconds is executing like 1 hour, fetching entire database, is just
a 'feature' :/
Regards,
Witold Szczerba
Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?
Posted by Witold Szczerba <pl...@gmail.com>.
Hi there,
Look at this:
https://issues.apache.org/jira/browse/DERBY-4595
Do you think it might be the same case I had some time ago?
There were some changes in the code in Jun/2010, maybe you have older
version or the change did not resolved the problem...
The funny thing is that the Apache Derby guys changed the issue type
from bug to improvement - so, for them, a query which should last 2 or
3 seconds is executing like 1 hour, fetching entire database, is just
a 'feature' :/
Regards,
Witold Szczerba
Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?
Posted by Pavel Bortnovskiy <PB...@Jefferies.com>.
Hello, all:
I am using Derby as an in-memory DB. While executing a complex query
(which contains a union and several left-joins), we discovered that this
query with a left join would take about 3.5 mins to execute and fetch the
data, whereas the same query with the left join replaced with the inner
join would take just 30 seconds.
What tools or API is available at our disposal to tune the in-memory
Derby, to see its execution plan, to try to figure out why it is taking
such an extraordinary time to fetch about 2,500-3000 records.
I also noticed that by running the same query over and over, the time to
execute and fetch seems to be slowly creeping up as well - it started with
2.5 mins, then grew to 3 mins and now it's almost 4 mins.
Any help would be greatly appreciated!
Regards,
Pavel.
Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.
Re: commands describe and show failed in eclipse derby plugin SQL editor
Posted by Bruce Wen <we...@mail.com>.
Hi Kristian,
I am not sure how eclipse derby plugin execute SQL commands against derby database. In addition, not sure if there are some of commands applicable for IJ tool not applicable for eclipse derby plugin. I haven't got any related information from derby docs.
Anyway, I raised one defect - DERBY-4957 for this topic. Thanks!
Bruce Wen (GuangMing)
-----------------------------------------------------------------------------------------------------------------
"Undertake not what you cannot perform, but be careful to keep your promise - George Washington"
-----------------------------------------------------------------------------------------------------------------
Internet Email: wengm@mail.com
LinkedIn: cn.linkedin.com/in/wengm
Blog: crest-boy.livejournal.com
MSN: crest-boy@hotmail.com
-----------------------------------------------------------------------------------------------------------------
-----Original Message-----
From: Kristian Waagan <kr...@apache.org>
To: derby-user@db.apache.org
Sent: Tue, Jan 4, 2011 3:35 pm
Subject: Re: commands describe and show failed in eclipse derby plugin SQL editor
On 04.01.11 07:05, Bryan Pendleton wrote:
> On 01/03/2011 09:56 PM, Bruce Wen wrote:
>> I used derby eclipse plugin from >> http://db.apache.org/derby/releases/release-10.7.1.1.cgi.
>
> I think that the best course of action here would be for you to file a > new
> issue in Jira, including the information that you've provided in this > email
> thread so far, and hopefully somebody one of the Derby developers who is
> familiar with the Eclipse plugin can help resolve this.
For clarity, can you confirm that the problem isn't that you are trying to execute ij-specific commands through the Derby JDBC-driver(s)?
Regards,
-- Kristian
>
> http://db.apache.org/derby/DerbyBugGuidelines.html
>
> thanks,
>
> bryan
Re: commands describe and show failed in eclipse derby plugin SQL
editor
Posted by Kristian Waagan <kr...@apache.org>.
On 04.01.11 07:05, Bryan Pendleton wrote:
> On 01/03/2011 09:56 PM, Bruce Wen wrote:
>> I used derby eclipse plugin from
>> http://db.apache.org/derby/releases/release-10.7.1.1.cgi.
>
> I think that the best course of action here would be for you to file a
> new
> issue in Jira, including the information that you've provided in this
> email
> thread so far, and hopefully somebody one of the Derby developers who is
> familiar with the Eclipse plugin can help resolve this.
For clarity, can you confirm that the problem isn't that you are trying
to execute ij-specific commands through the Derby JDBC-driver(s)?
Regards,
--
Kristian
>
> http://db.apache.org/derby/DerbyBugGuidelines.html
>
> thanks,
>
> bryan
Re: commands describe and show failed in eclipse derby plugin SQL
editor
Posted by Bryan Pendleton <bp...@gmail.com>.
On 01/03/2011 09:56 PM, Bruce Wen wrote:
> I used derby eclipse plugin from http://db.apache.org/derby/releases/release-10.7.1.1.cgi.
I think that the best course of action here would be for you to file a new
issue in Jira, including the information that you've provided in this email
thread so far, and hopefully somebody one of the Derby developers who is
familiar with the Eclipse plugin can help resolve this.
http://db.apache.org/derby/DerbyBugGuidelines.html
thanks,
bryan
Re: commands describe and show failed in eclipse derby plugin SQL editor
Posted by Bruce Wen <we...@mail.com>.
Hi Bryan,
I used derby eclipse plugin from http://db.apache.org/derby/releases/release-10.7.1.1.cgi. With this plugin, you can create SQL file in Eclipse and directly execute SQL statements also, once connected to derby database.
However, I can not got derby.log's content covering my operation in Eclipse derby plugin. What a pity!
Bruce Wen (GuangMing)
-----------------------------------------------------------------------------------------------------------------
"Undertake not what you cannot perform, but be careful to keep your promise - George Washington"
-----------------------------------------------------------------------------------------------------------------
Internet Email: wengm@mail.com
LinkedIn: cn.linkedin.com/in/wengm
Blog: crest-boy.livejournal.com
MSN: crest-boy@hotmail.com
-----------------------------------------------------------------------------------------------------------------
-----Original Message-----
From: Bryan Pendleton <bp...@gmail.com>
To: Derby Discussion <de...@db.apache.org>
Sent: Tue, Jan 4, 2011 12:03 pm
Subject: Re: commands describe and show failed in eclipse derby plugin SQL editor
> IJ commands work well in IJ command line, including show and describe. That means if I connect derby by IJ, there is no problem.
>
> Currently, the problem occurred in Eclipse inside. And I just blocked by *show* and *describe* commands so far. Other commands I
> ever used work well.
Hmmm. I'm afraid I'm not an Eclipse user, so I'm not sure how much
help I can give you.
Perhaps the problem is not with Derby, but with Eclipse. What part of
Eclipse are you using, that provide the interactive SQL editor functionality?
Can you locate a derby.log file which covers the period during which you
are getting the error in Eclipse? Is there any mention in the derby.log
file of an error message regarding "show tables"? Can you paste the
relevant section of the derby.log file into an email message to the list?
thanks,
bryan
Re: commands describe and show failed in eclipse derby plugin SQL
editor
Posted by Bryan Pendleton <bp...@gmail.com>.
> IJ commands work well in IJ command line, including show and describe. That means if I connect derby by IJ, there is no problem.
>
> Currently, the problem occurred in Eclipse inside. And I just blocked by *show* and *describe* commands so far. Other commands I
> ever used work well.
Hmmm. I'm afraid I'm not an Eclipse user, so I'm not sure how much
help I can give you.
Perhaps the problem is not with Derby, but with Eclipse. What part of
Eclipse are you using, that provide the interactive SQL editor functionality?
Can you locate a derby.log file which covers the period during which you
are getting the error in Eclipse? Is there any mention in the derby.log
file of an error message regarding "show tables"? Can you paste the
relevant section of the derby.log file into an email message to the list?
thanks,
bryan
Re: commands describe and show failed in eclipse derby plugin SQL editor
Posted by Bruce Wen <we...@mail.com>.
Hi Bryan,
Thanks a lot for your reply!
IJ commands work well in IJ command line, including show and describe. That means if I connect derby by IJ, there is no problem.
Currently, the problem occurred in Eclipse inside. And I just blocked by show and describe commands so far. Other commands I ever used work well. Attached screenshot for your reference.
Bruce Wen (GuangMing)
-----------------------------------------------------------------------------------------------------------------
"Undertake not what you cannot perform, but be careful to keep your promise - George Washington"
-----------------------------------------------------------------------------------------------------------------
Internet Email: wengm@mail.com
LinkedIn: cn.linkedin.com/in/wengm
Blog: crest-boy.livejournal.com
MSN: crest-boy@hotmail.com
-----------------------------------------------------------------------------------------------------------------
-----Original Message-----
From: Bryan Pendleton <bp...@gmail.com>
To: Derby Discussion <de...@db.apache.org>
Sent: Sun, Jan 2, 2011 11:48 pm
Subject: Re: commands describe and show failed in eclipse derby plugin SQL editor
> I used /eclipse-jee-ganymede-SR2-win32/ and it contains Apache Derby plug-in in default. However, when I execute /describe
> table_name/ statement, "/syntax error:Encountered "describe//" at line 1, column 1/" is encountered unexpectedly. And /show
> schemas/ statement also not workable because of the same error.
Do other IJ commands work successfully, is it only "describe" and "show" that fail?
What does your derby.log say?
Describe and Show were added in Derby 10.2, I believe, so they've been there for
a long time; is there any possibility that you are running an older release of Derby?
thanks,
bryan
Re: commands describe and show failed in eclipse derby plugin SQL
editor
Posted by Bryan Pendleton <bp...@gmail.com>.
> I used /eclipse-jee-ganymede-SR2-win32/ and it contains Apache Derby plug-in in default. However, when I execute /describe
> table_name/ statement, "/syntax error:Encountered "describe//" at line 1, column 1/" is encountered unexpectedly. And /show
> schemas/ statement also not workable because of the same error.
Do other IJ commands work successfully, is it only "describe" and "show" that fail?
What does your derby.log say?
Describe and Show were added in Derby 10.2, I believe, so they've been there for
a long time; is there any possibility that you are running an older release of Derby?
thanks,
bryan