Re: can you help me to solve a query problem which 3 tables are engaged in it?

[Michael Segel]

On Tuesday 03 January 2006 6:11 pm, Legolas Woodland wrote:
> Thank you for reading my post
> I have 3 tables like the above images
> a web sites , a category and a relation between them.
> I defined a relation between web sites and categories in table
> website_categories
> so if a web site incoperate in a category then its id with category id
> will be there
>
Its one thing to help someone understand how to use Derby, 
its another to actually consult on a project

Having said that

Your diagram is missing some details.

It sounds like you're attempting to have a many to many relationship between 
the websites and the categories. That is, each category can contain many 
websites and each website may be in multiple categories.

> I want to return categories that an specefic web site in not in it.
>
> :( i tried every combination of joins and where cluases but i never
>
> recieved correct categories.
>
> can some one pleas help ?
> Thank you

Well, it seems pretty straight forward.

If you think about it, based on a website_id, you already know the categories 
that its in. So all you need is a list of categories which are not already 
associated with the table.

Based on your diagram, you need to do a join between the category table and 
your w_c table.

Something like this...

SELECT category_id, category_name 
FROM category a,  web_cat b
WHERE a.category_id = b.category_id
AND  category_id NOT IN ( SELECT category_id 
  FROM web_cat 
WHERE website_id = ?)

Should do it.

But hey, what do I know?



-- 
--
Michael Segel
Principal 
Michael Segel Consulting Corp.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]

Re: apache derby with CDC/JSR 169

[Daniel John Debrunner]

Timothy Luksha (RIT Student) wrote:

> Sorry I was so slow responding to your questions.  I didn't have access to 
> the machine running CVM over the holiday.  Thanks for taking an interest in 
> my issues. 
>  
> I haven't been able to locate the foundation profile jars on Sun's website.  
> So when I build the project, I build it without the two jar files mentioned 
> in the building.txt file.   I have only been able to find what looks to be 
> some source code that could be built and used as one of the the jars.  Do you 
> happen to have the foundation profile jars from Sun already built?  I noticed 
> a post you made quite a while back indicating that you were using IBM's WCTME 
> 5.7.  When I investigated that as an option, it looked like I would have to 
> sign up and purchase it.  So that eliminated it as an option. 

Derby requires classes from the Foundation profile to work, if you don't
have that in your environment then there's not much hope. Here's a link
I've found to Sun's foundation profile, it claims to have reference
implementations, I haven't looked further.

http://java.sun.com/products/foundation/index.jsp

The JSR 169 JDBC subset is built on top of Foundation, hence its full name:

JSR-000169 JDBC Optional Package for CDC/Foundation Profile

The sysinfo output you provided showed that Derby does determine that
you are running in an J2ME environment.

Thanks for finding the NullPointerException, I will ensure that gets fixed.

Dan.

can you help me to solve a query problem which 3 tables are engaged in it?

[Legolas Woodland]

Thank you for reading my post
I have 3 tables like the above images
a web sites , a category and a relation between them.
I defined a relation between web sites and categories in table 
website_categories
so if a web site incoperate in a category then its id with category id 
will be there


I want to return categories that an specefic web site in not in it.
:( i tried every combination of joins and where cluases but i never 
recieved correct categories.


can some one pleas help ?
Thank you

<>

Re: where is the derby.log stored when using tomcat.

[Stanley Bradbury]

meenakshi selvi wrote:


hi
i am using tomcat -server,derby-database.
i have saved my database in tomcat-home dir.
while using tomcat ,the derby.log is not getting created.
but while accessing the databases via ij tools,i am getting the 
derby.log in my derby-home dir.
what are the changes i should make while using tomcat, to get create 
derby.log. or where the derby.log get stored while using tomcat.


Send instant messages to your online friends 
http://in.messenger.yahoo.com



Hi Meenakshi -

The derby.log errorlog file is always created and, by default, will be 
written to the default working directory of the JVM process that is 
running Tomcat and Derby (assuming Tomcat is loading the Derby embedded 
driver thus embedding the database engine).  You might find the 
following article helpful if you are not familiar with this configuration:

 http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html

The configuration used in the article embeds Derby within the Tomcat 
server JVM.  The article uses Tomcat 5.5 running as a Windows Service.  
In this case the default JVM directory is the same as CATALINA_HOME so 
derby.log is written to CATALINA_HOME.  Depending on how Tomcat is 
started this will not always be the case and the log will be created 
somewhere else.  To insure you always know where to look for the 
errorlog you can set the property derby.system.home when Tomcat is 
started (either specify it using the -D parameter to the JAVA command or 
place it in the catalina.properties file).  This will be the location of 
the derby.log file. 

Please let me know if this resolves your problem and - if it does not - 
please let me know what version of both Tomcat and Derby you are using, 
which Derby driver is being loaded,  and how the driver is loaded.  It 
would also be helpful to know what command you are using to start Tomcat.


HTH,  Stan

Re: Feedback for derby-tomcat 5.5 embedded demo

[Stanley Bradbury]

Winslow, Eric - BLS wrote:


Hello.

I just ran through the 'Embedding Apache Derby in Tomcat and creating an
iBATIS JPetStore Demo' at
http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html
and ran into some issues that might need to be addressed.

I did this on Windows XP with Java 1.5_06 and Tomcat 5.5.14

I ran into issues with the paths given at a couple of points.

1. When deploying the app in the manager interface I needed to use the
full path to the XML config file and the WAR; e.g.,
c:\apache-tomcat-5.5.14\work\file.xml. Otherwise the deployment failed
with a file not found exception.

2. When running the app iBatis ran into issues with finding the
database. Again, server.xml the URL to the database needed to be fully
specified: 
jdbc:derby:c:\apache-tomcat-5.5.14\Databases\JPetStoreDB


Maybe this is just a Windows issue. My catalina_home environmental var
is defined. I have not had a chance to go through in on Linux/Unix.

Thanks.

Eric M. Winslow


 


Hi Eric -
Thanks for sharing your experience - you ran afoul of the assumption I 
made that CATALINA_HOME will end up being the default working directory 
for the JVM process and thus be the default directory for derby (a.k.a. 
derby.system.home).  I have found that the location of the JVM default 
working directory depends on how you start Tomcat.  When CATALINA_HOME 
is not the JVM default directory (as in your environment) then the full 
path to the database must be specified (as you stated in item 2) or some 
other method used to set derby.system.home.  A different default working 
directory is likely (I still need to test this) the reason you needed to 
specify a full path when deploying using the manager interface.  In the 
next few weeks I will update the document to add this information. 

I found an alternate way of 'solving' the database path problem that I 
would appreciate you commenting on.  This appeals to me because I like 
to use environment variables and JVM properties where possible to 
specify installation specific information so that this information does 
not need to be hardcoded in other places.  I found that defining 
derby.system.home in the  CATLAINA_HOME\config\catalina.properties file 
insures that the derby system directory is not Dependant on how Tomcat 
is started and eliminates the need to specify a complete path in the 
server.xml file.   In my installation I set it to be the same as 
CATALINA_HOME.  The lines I added are:


##  Set Derby System HOME property
derby.system.home=C:/Program Files/Apache/Tomcat 5.5.12

Does this seem like a good practice to you?

Re: What is diffrence(es) between derby.system.home and DERBY_INSTALL ?

[Kristian Waagan]

Legolas Woodland wrote:

Bryan Pendleton wrote:


[EMAIL PROTECTED] wrote:

2 & 3. I have not used derby.properties file but I would assume this 
file would
be in the folder specified by derby.system.home. In my example it 
would the
derbydata folder. The network server does nothing other than delegate 
network
requests to the specified database located on the local file system. 
As far as

I know, the network server cannot be configured with derby.properties or
service.properties. 



derby.properties works fine with the Network Server. As you say, it 
should

be placed in the derby.system.home folder. Here's some more information:

http://db.apache.org/derby/docs/10.1/tuning/ctunsetprop13074.html

thanks,

bryan






Hi
Thank you all for you helps.
with the following variables , i made a derby.properties ,can you tell 
me whether it is ok or not.
1-derby is installed in  *F:\db-derby-10.1.2.0\*  so it is also 
DERBY_INSTALL
2-all of my databases are located in : *F:\db-derby-10.1.2.0\databases , 
##* *I move them myself from different places , does it current my date ?*
in the above folder there are several databases like DB1 , Tutorial , 
testDB ,MyDB .



now i create the following file and name it derby.properties , i put it 
into  *F:\db-derby-10.1.2.0\frameworks\NetworkServer\bin*

the derby.properties file that i made contains the following items

**
derby.system.home=F:\db-derby-10.1.2.0\databases
derby.infolog.append=true
derby.language.logStatementText=true
derby.storage.initialPages=320
derby.storage.pageSize=8192
derby.storage.initialPages=64
**


FYI: the setting derby.storage.initialPages is duplicated in your file.

You might want to allocate a larger page cache to Derby with 
derby.storage.pageCacheSize. If you do, don't forget to increase the 
heap space for the JVM.
Read more here: 
http://db.apache.org/derby/docs/10.1/tuning/rtunproper81359.html




do i put it in right place ?


If you do not specify derby.system.home on the command line (or from the 
application starting Derby), the derby.properties file must be in the 
directory you start the server from.


You can verify that Derby reads the property file by starting your 
server, connect to it with for instance ij and create a database - say 
'MySpecialDB'. The directory 'F:\db-derby-10.1.2.0\databases' should 
then contain a directory named 'MySpecialDB'. If it doesn't, Derby has 
not picked up your property file and created the database elsewhere 
(current directory)!


You might have to edit the script to make it behave the way you want 
(add 'cd F:\db-derby-10.1.2.0\frameworks\NetworkServer\bin', *or* add 
the Java argument '-Dderby.system.home=F:\db-derby-10.1.2.0\databases' 
and move derby.properties to the directory you specified)


I suppose what you want to do is to have the server always look in the 
same directory for databases, no matter where you execute the script 
from in the file system.




A different, but related matter:
Can someone confirm that it is *not possible* to first set 
derby.system.home on the command line (or equvialent), and then have 
Derby change the value of derby.system.home based on a second 
specification in the derby.properties file?

(only on startup, not dynamically)




--
Kristian

If i want to have a high load database (in peak time 30 tra / second ) 
with some tables loaded with 1,000,000 records , which other properties 
i should add to *derby.properties* ?


ps : which problem/limitation will derby bring with such high load system ?
sometimes ago i read that some one has problem with its Index creation . 
is it possible that i fail because of derby limitation ?



Thank you all.

Re: How to select top n record

[Jean T. Anderson]

Legolas Woodland wrote:

Geert Bevin wrote:


Hi Legolas,

afaik Derby has no SQL statement for this, you can use JDBC to get the
same effect though through the statement.setFetchSize(limit) and
statement.setMaxRows(max_rows) methods.
...  


Hi , Thank you for reply.
unfortunately i do not use JDBC directly and i use Rowsets and Data 
provider from Sun java studio creator.
so i should consider limiting total number of rows that a command return 
in Database itself..
another reason that I'm looking for sql syntax for *selecting an offset 
of records* , *ton n *, *top n%* record is that this application has to 
be very optimized.
Now that holiday is finished and all people engaged with Derby is here i 
reply again.

i hope some on know about this issue.


Derby SQL doesn't support "TOP N" nor "LIMIT".

As Geert mentioned, the way Derby supports limiting the number of rows 
returned is through the standard JDBC java.sql.Statement.setMaxRows() 
method.


I don't spot in Jira that anyone has formally requested this 
functionality for Derby, so feel free to file an enhancement request. 
Tips on how to do so are at 
http://db.apache.org/derby/DerbyBugGuidelines.html .


regards,

 -jean

Re: How to select a random record.

[Michael Segel]

On Tuesday 03 January 2006 10:59 am, Legolas Woodland wrote:
> Michael Segel wrote:
> > Why create a stored procedure when you are dealing with an application
> > specific problem? (Ok. He didn't say what language he was developing his
> > app in or if he was even writing an app or if he wanted to do this as an
> > extension to SQL. So I'm going to go out on a limb and assume he's
> > writing an app)
> >
> > The key is to generate a random number that will be representative of a
> > record.  That is to say, if I create a random number, will there be a
> > record that matches the number?
> >
> > Assuming that you are using java as your app development language...
> > Java has a class for generating random numbers.
> >
> > So create a list of distinct types of records, then you get a random
> > number within 1 to n where n is the number of records. Then get a list of
> > those records. Again, generate a random number to get a random record.
> > (You did say that there could be multiple records of the same type...)
> >
> > That should solve your problem, however please note that your random
> > number won't be truly random. But that's a different issue.
>
> *Thank you for the reply.
> by your reply i should consider that Derby has no built-in mechanism for
> selecting a random record in an effective way.
> now , should i create an stored procedure to select a random record ,
> should i use normal sql command , or something else is correct way of
> doing the job.
> *
>

I'm not sure where you were going with this.

No database has an effective method of pulling up a random record. RDBMS are 
not *designed* to do this .   

The reason I mention this is that I don't want anyone to consider this a 
limitation or a flaw within Derby. 

There may be a RANDOM() function within a database, however, this is only a 
piece of what is required to solve the problem.

There really hasn't been a good articulate description of the problem we are 
attempting to solve.

The use of stored procedures is not recommended. Yes you can use them, however 
there has not been enough information provided to show that a stored 
procedure is warranted.  By this I mean that you would have to show a 
constraint to justify the use of a stored procedure. SPs do have a cost 
associated with them. Depending on the RDBMS used, the cost vs performance 
will vary.

Going from memory, you're asking for a random category and then a random 
record from that category.  This is a two step problem.

If the types of categories are static, you may want to create a "look up" 
table which will make thing s a little faster. (Again this is app specific )

You would then get a count of the records and then generate a random number 
between 1 and n where n is the number of unique categories.

Then you can do one of the following:

1) Create a temp table containing the n records in that specific category and 
then fetch a random record from that list. A good idea if you're going to do 
this multiple times.

OR

2) Get a count of the records that are in the category, and use a CURSOR with 
HOLD to fetch the nth record where n represents the random number between 1 
and the record count.

So its really a two step process. And either of these solutions would work.

Doing this in an "extended SQL" would be a bit problematic.
(Not saying that you couldn't do this, however, you'd have some issues that 
would need to be thought out.)

So for a quick and dirty way of doing this, stick with Java and JDBC.

Note that you really haven't created any boundries/constraints so its 
difficult to determine an effective solution.

But hey, what do I know? ;-)

-G



--
Michael Segel
Principal 
Michael Segel Consulting Corp.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]

Re: How to select a random record.

[Jean T. Anderson]

Legolas Woodland wrote:

...
by your reply i should consider that Derby has no built-in mechanism for 
selecting a random record in an effective way.
now , should i create an stored procedure to select a random record , 
should i use normal sql command , or something else is correct way of 
doing the job.


If you want to test a SQL function that returns a random number, the 
embedded tutorial from ApacheCon 2004 provides simple code that creates 
a tutRand function:


   http://db.apache.org/derby/papers/fortune_tut.html

The SQL function gets created like this:

   create function tutRand(minInt int, maxInt int)
   returns int
   language java
   parameter style java
   no sql
   external name 'examples.tutorial.derby.DerbyRand.randIntJakarta';

I put the java source for the function at the end of this email, and it 
also includes an option to use the built-in Random instead of the 
Jakarta commons math library.


The function returns a random integer between lower and upper integer 
bounds. For example, the query below returns a random integer between 1 
and 5000:


   ij> values tutRand(1,5000);
   1
   ---
   1530

   ij> values tutRand(1,5000);
   1
   ---
   2589

It gets used to return a random row from the fortunes table, which has 
an integer id key:


   select id, fortune
   from fortunes
   where id=tutRand(1,5000);

There are two problems with this approach.

The first problem is the values in the id column must be sequential, 
can't have any gaps at all.


The second problem is other where clauses in the query can eliminate the 
id returned by tutRand from the result set. For example, the fortunes 
table sets the level of offensiveness for a particular fortune. It would 
be possible for tutRand to return the id for an offensive fortune, so 
this query could return no results:


   select id, fortune
   from fortunes
   where id=tutRand(1,5000)
   and offense=0;

regards,

 -jean

Below is the source code (which also provides an option to use the 
built-in Random).



package examples.tutorial.derby;
import java.util.*;
import org.apache.commons.math.random.*;

public class DerbyRand
{

   /**
* Entrypoint for SQL function that obtains a randomly generated 
integer by

* calling java.util.Random.nextInt(), which sets the lower bound to 0.
*
* @param maxInt Upper bound on the number to be returned.
* @return Randomly generated integer.
*/
   public static int randIntSun(int maxInt)
   {
   int return_int=0;
   Random rand = new Random();
   return_int = rand.nextInt(maxInt);
   return return_int;
   }

   /**
* Entrypoint for SQL function that obtains a randomly generated 
integer by

* calling org.apache.commons.math.random.nextInt().
*
* @param minInt Lower bound on the number to be returned.
* @param maxInt Upper bound on the number to be returned.
* @return Randomly generated integer.
*/
   public static int randIntJakarta(int minInt, int maxInt)
   {
   int return_int=0;
   RandomDataImpl randomData = new RandomDataImpl();
   return_int = randomData.nextInt(minInt,maxInt);
   return return_int;
   }
}

Re: How to select a random record.

[Legolas Woodland]

Michael Segel wrote:

  Why create a stored procedure when you are dealing with an application specific problem?
(Ok. He didn't say what language he was developing his app in or if he was even writing an app or if he wanted to do this as an extension to SQL. So I'm going to go out on a limb and assume he's writing an app)

The key is to generate a random number that will be representative of a record.  That is to say, if I create a random number, will there be a record that matches the number?

Assuming that you are using java as your app development language...
Java has a class for generating random numbers.

So create a list of distinct types of records, then you get a random number within 1 to n where n is the number of records. Then get a list of those records. Again, generate a random number to get a random record. (You did say that there could be multiple records of the same type...) 

That should solve your problem, however please note that your random number won't be truly random. But that's a different issue.
  

Thank you for the reply.
by your reply i should consider that Derby has no built-in mechanism
for selecting a random record in an effective way.
now , should i create an stored procedure to select a random record ,
should i use normal sql command , or something else is correct way of
doing the job.


  
-Original Message-
From: Rajesh Kartha <[EMAIL PROTECTED]>
Date: Sun, 25 Dec 2005 19:07:38 
To:Derby Discussion 
Subject: Re: How to select a random record.

Maybe there are other ways to perform this, but the first thing that came to my mind was to create a 
 Java stored procedure.  This can use simple Java code to create the logic needed and return 
 a result set from the products table based on the categories and a generated random number.
 
 Syntax for creating procedures:
 http://db.apache.org/derby/docs/10.1/ref/rrefcreateprocedurestatement.html
 
 Example:
 http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0408anderson/#cs_sp
 
 Hope the above helps.
 
 -Rajesh
 
 


On 12/25/05, Legolas Woodland <[EMAIL PROTECTED]> wrote: Hi
Thank you for reading my post.
I have a Product table and a category table.
some of products exists in more than one category.
I should select a random product from product table (a record)  and it
should be from some certain categories. 
Now the question is :
What is best solution to select a random product which is in one or more
categories ( categories are  determined by users ).


Thank you


 
Sent via BlackBerry.

-Mike Segel
Principal
MSCC
312 952 8175

  

Re: How to select top n record

[Legolas Woodland]

Geert Bevin wrote:

  Hi Legolas,

afaik Derby has no SQL statement for this, you can use JDBC to get the
same effect though through the statement.setFetchSize(limit) and
statement.setMaxRows(max_rows) methods.

However, since you're a RIFE user, you can just use our Select query
builder and use the limit and offset methods there. RIFE will handle
all this for you in the background transparently:
http://rifers.org/docs/api/com/uwyn/rife/database/queries/Select.html#limit(int)

Best regards,

Geert
  


Hi , Thank you for reply.
unfortunately i do not use JDBC directly and i use Rowsets and Data
provider from Sun java studio creator.
so i should consider limiting total number of rows that a command
return in Database itself..
another reason that I'm looking for sql syntax for selecting an
offset of records , ton n , top n% record is that
this application has to be very optimized.
Now that holiday is finished and all people engaged with Derby is here
i reply again.
i hope some on know about this issue.

Thank you


  
On 12/26/05, Legolas Woodland <[EMAIL PROTECTED]> wrote:
  
  
Hi
Thank you for reading my post.
how i can select top n record of a table ?
i tried
Select Top 10 from Leg //not works
select *from leg limit 10 //not works



is there any syntax in derby sql for selecting top n , or an offset of a
table ?


  
  

--
Geert Bevin   Uwyn bvba
"Use what you need"   Avenue de Scailmont 34
http://www.uwyn.com   7170 Manage, Belgium
gbevin[remove] at uwyn dot comTel +32 64 84 80 03

PGP Fingerprint : 4E21 6399 CD9E A384 6619  719A C8F4 D40D 309F D6A9
Public PGP key  : available at servers pgp.mit.edu, wwwkeys.pgp.net

  

Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?

[Thomas Dudziak]

On 1/2/06, Bernt M. Johnsen <[EMAIL PROTECTED]> wrote:
>  Thomas Dudziak wrote (2005-12-25 16:18:14):
> > When executing this code snippet:
> >
> >
> > Statement  stmt = conn.createStatement();
> >
> > stmt.executeUpdate("CREATE TABLE test (\n"+
> >"pk INTEGER NOT NULL,\n"+
> >"value NUMERIC(15,7) NOT NULL,\n"+
> >"PRIMARY KEY (pk)\n"+
> >")");
> > stmt.close();
> >
> > PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test (pk,
> > value) VALUEs (?, ?)");
> >
> > pstmt.setInt(1, 1);>
> > pstmt.setObject(2, new BigDecimal("0.01"), Types.NUMERIC);
>
> > pstmt.execute();
> > pstmt.close();
> >
> > conn.close();
> >
> >
> > the resulting value in the database is 0e-7, not 1e-2 as I would expect.
> > When changing that to
> >
> >
> > pstmt.setBigDecimal(2, new BigDecimal("0.01"));
> >
> >
> > it works though.
> > Did I make a mistake or is this a bug ?
>
> I think you have found a bug. I experimented a bit and found that it
> works for values >= 1.0 but the result will be 0 for values < 1.0.
>
>   pstmt.setObject(2, new BigDecimal("1.0"), Types.NUMERIC);
>
> works perefectly ok while
>
>   pstmt.setObject(2, new BigDecimal("0.9"), Types.NUMERIC);
>
> is errouneous.

Shall I file a JIRA issue then ?

Tom

Re: What is wrong with this sql script , im sure that it is ok , but derby return error.

[Thomas Dudziak]

On 1/3/06, Legolas Woodland <[EMAIL PROTECTED]> wrote:

>  I think you missed a post about this error.
>  One of users answered the question , as he said , I should remove unique
> from field that are in PK.
>  by the way if you think i should supply more about this error then , please
> let me know the path to submit more details.

I think he was refering to the not-so-helpful error message that you
received. If the error message would say something: "primary key
column is already unique" the problem would have been apparent to you.

Tom

Re: Derby-SQL: Syntax Tree generator?

[Rick Hillegas]

Hi Ashwin,

Some more responses follow.

Regards,
-Rick

Ashwin Jayaprakash wrote:


Rick, thanks for replying.

Perhaps you meant "-Dderby.debug.DumpParseTree=true" instead of  
"-Dderby.debug.true=DumpParseTree"? But I tried that too and it didn't 
work. But anyway, I looked into the GenericStatement code where the 
Tree was being printed. I realised that was what I wanted to avoid - 
using the Compiler Context, connecting to a Database instance etc.


I apologize. I forgot to mention that you have to run with the debug 
version of Derby. You need to build Derby with the following flags set 
in the ant.properties in your home directory:


sanity=true
debug=true

Detailed instructions for building Derby can be found at 
http://svn.apache.org/repos/asf/db/derby/code/trunk/BUILDING.txt.




I just wanted to parse the SQL offline.

Also , can Derby be run in a completely non-persistent mode like HSQL? 
I do not want persistence, just SQL operations on a few thousand rows 
in-memory (I know this should've been posted as a separate question, 
well..)



It looks like Dag has sent you advice on this topic.



Thanks,
Ashwin.




On Tue, 03 Jan 2006 Rick Hillegas wrote :
>Hi Ashwin,
>
>I don't know whether anyone got back to you on this topic. I hope 
that this addresses your question.

>
>The DumpParseTree tracepoint causes Derby to print the ASTs to 
derby.log. You can set this tracepoint when Derby starts up. Here for 
instance is how you would do it if you were running Derby embedded 
under the ij tool:

>
>java -cp $CLASSPATH -Dderby.debug.true=DumpParseTree 
-Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij  z.sql

>
>Regards,
>-Rick
>
>Ashwin Jayaprakash wrote:
>
>>Hi,
>>Does anybody know if there is a way to parse and view the AST for an 
SQL Query in Derby? I looked at the sqlparser.jj in the source code, 
but couldn't find a way to start it and parse a query without hacking 
into all the code.

>>
>>Some thing like this one: http://www.experlog.com/gibello/zql/ ?
>>
>>I am especially interested in extending the SQL grammar a little bit 
by adding a few keywords and re-writing some queries issued by the 
user. So,  some kind of a Visitor-pattern would be nice.

>>
>>Thanks,
>>Ashwin (www.JavaForU.com).
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>












 

Re: Re: Derby-SQL: Syntax Tree generator?

[Dag H. Wanvik]

Hi Aswin,

> "Ashwin" == Ashwin Jayaprakash <[EMAIL PROTECTED]> wrote:

Ashwin> Also , can Derby be run in a completely non-persistent mode
Ashwin> like HSQL? I do not want persistence, just SQL operations on a
Ashwin> few thousand rows in-memory (I know this should've been posted
Ashwin> as a separate question, well..)

Have a look at the Derby property `derby.system.durability', cf. this
section in the Tuning manual:

http://db.apache.org/derby/docs/10.1/tuning/rtunproperdurability.html

You can set this to "test" to improve performance at the expense of
durability. 

Dag

Re: What is wrong with this sql script , im sure that it is ok , but derby return error.

[Legolas Woodland]

Øystein Grøvlen wrote:

  

  

  
"LW" == Legolas Woodland <[EMAIL PROTECTED]> writes:

  

  

  
  
LW> Hi
LW> Thank you for reading my post.
LW> can some one please check and see what is wrong with this scripts ?
LW> im sure that they should execute but derby return errors like :
LW> org.apache.derby.client.am.SqlException:Constraints
LW> 'SQL060103004635123'  and 'SQL060103004635121'  have the  same  set of
LW> columns, which is not allowed.

I think this is a usability issues since the error message is not very
helpful in order to figure out what is the cause of this error.  I
have filed a jira issue, DERBY-789, for this.

  

Hi
I think you missed a post about this error.
One of users answered the question , as he said , I should remove
unique from field that are in PK.
by the way if you think i should supply more about this error then ,
please let me know the path to submit more details.

Re: What is wrong with this sql script , im sure that it is ok , but derby return error.

[Øystein Grøvlen]

> "LW" == Legolas Woodland <[EMAIL PROTECTED]> writes:

LW> Hi
LW> Thank you for reading my post.
LW> can some one please check and see what is wrong with this scripts ?
LW> im sure that they should execute but derby return errors like :
LW> org.apache.derby.client.am.SqlException:Constraints
LW> 'SQL060103004635123'  and 'SQL060103004635121'  have the  same  set of
LW> columns, which is not allowed.

I think this is a usability issues since the error message is not very
helpful in order to figure out what is the cause of this error.  I
have filed a jira issue, DERBY-789, for this.

-- 
Øystein

RE: apache derby with CDC/JSR 169

[Timothy Luksha \(RIT Student\)]

Sorry I was so slow responding to your questions.  I didn't have access to the 
machine running CVM over the holiday.  Thanks for taking an interest in my 
issues. 
 
I haven't been able to locate the foundation profile jars on Sun's website.  So 
when I build the project, I build it without the two jar files mentioned in the 
building.txt file.   I have only been able to find what looks to be some source 
code that could be built and used as one of the the jars.  Do you happen to 
have the foundation profile jars from Sun already built?  I noticed a post you 
made quite a while back indicating that you were using IBM's WCTME 5.7.  When I 
investigated that as an option, it looked like I would have to sign up and 
purchase it.  So that eliminated it as an option. 
 
Anyhow, that's why I asked if the prebuilt version available for download 
should be able to run on my embedded environment.  I was really hoping that the 
problem would have been with my CVM's configuration, and that I wouldn't have 
to find these jars and build the project from scratch.  Since I am changing the 
source code around now, I should get my hands on those jars.
 
I ran sysinfo with the binary version of derby that I downloaded off the 
website. (The version that gives a null pointer exception at 
BaseMonitor.getDefaultModuleProperties() when I try to run simpleAppJSR169)  
This is what I got:
 
 /mnt/newjava/bin/cvm 
-Xbootclasspath:/mnt/newjava/lib/cdc_jdbc_classes:/usr/local/java/lib/cdc.jar:/mnt/Luksha/db-derby-10.1.2.1-binold/lib/derby.jar
 org.apache.derby.tools.sysinfo
-- Java Information --
Java Version:J2ME CDC_jdbc 1.0.1
Java Vendor: Sun Microsystems Inc.
Java home:   /mnt/newjava
Java classpath:  .
OS name: Linux
OS architecture: x86
OS version:  2.4.20
Java user name:  root
Java user home:  /
Java user dir:   /mnt/Luksha/db-derby-10.1.2.1-binold/demo/simple
java.specification.name: J2ME CDC_jdbc API Specification
java.specification.version: 1.0a
- Derby Information 
JRE - JDBC: J2ME - JDBC for CDC/FP 1.0
[/org/apache/derby/info/DBMS.properties] 10.1.2.1 - (330608)
--
- Locale Information -
--

 
Thanks,
 
Tim



From: Daniel John Debrunner [mailto:[EMAIL PROTECTED]
Sent: Tue 12/27/2005 1:15 PM
To: Derby Discussion
Subject: Re: apache derby with CDC/JSR 169



Timothy Luksha (RIT Student) wrote:

> Hi,
> I have been working on setting up a derby database in an embedded
> environment. We have CVM as our java virtual machine and have the
> optional JDBC package installed. I created a little program that uses
> EmbeddedSimpleDataSources to create a database and insert a few entries.
> I am able to run this on my pc with the 1.3 jvm with the JDBC package.
> When I attempt to run the same program on the CVM, I get a null pointer
> error. Is the 10.1.2.1 binary that is available for download built to be
> used with the cdc and jsr 169? The error I receive is below. Any
> insights would be helpful.


Dumb question, do you have the foundation profile included?

And if you could run sysinfo that would be great.

java -classpath derby.jar org.apache.derby.tools.sysinfo

This command not only shows the version of Derby, but also shows what
Derby thinks the VM is, (e.g. jdk13,14,142,15 or J2ME/CDC/Foundation).

Dan.





<>

Re: Re: Derby-SQL: Syntax Tree generator?

[Ashwin Jayaprakash]

Rick, thanks for replying.

Perhaps you meant "-Dderby.debug.DumpParseTree=true" instead of  "-Dderby.debug.true=DumpParseTree"? But I tried that too and it didn't work. But anyway, I looked into the GenericStatement code where the Tree was being printed. I realised that was what I wanted to avoid - using the Compiler Context, connecting to a Database instance etc.

I just wanted to parse the SQL offline.

Also , can Derby be run in a completely non-persistent mode like HSQL? I do not want persistence, just SQL operations on a few thousand rows in-memory (I know this should've been posted as a separate question, well..)

Thanks,
Ashwin.




On Tue, 03 Jan 2006 Rick Hillegas wrote :
>Hi Ashwin,
>
>I don't know whether anyone got back to you on this topic. I hope that this addresses your question.
>
>The DumpParseTree tracepoint causes Derby to print the ASTs to derby.log. You can set this tracepoint when Derby starts up. Here for instance is how you would do it if you were running Derby embedded under the ij tool:
>
>java -cp $CLASSPATH -Dderby.debug.true=DumpParseTree -Dderby.stream.error.logSeverityLevel=0 org.apache.derby.tools.ij  z.sql
>
>Regards,
>-Rick
>
>Ashwin Jayaprakash wrote:
>
>>Hi,
>>Does anybody know if there is a way to parse and view the AST for an SQL Query in Derby? I looked at the sqlparser.jj in the source code, but couldn't find a way to start it and parse a query without hacking into all the code.
>>
>>Some thing like this one: http://www.experlog.com/gibello/zql/ ?
>>
>>I am especially interested in extending the SQL grammar a little bit by adding a few keywords and re-writing some queries issued by the user. So,  some kind of a Visitor-pattern would be nice.
>>
>>Thanks,
>>Ashwin (www.JavaForU.com).
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>