You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@empire-db.apache.org by Gunnar Gr-Hovest <gr...@atb-bremen.de> on 2009/11/26 10:40:36 UTC
How to generate sub queries
Hi,
I would like to know to which extent sub queries in the FROM part of an
SQL statement are currently supported by empire-db. I tried to create
such a sub query in the following way:
DBCommand subQuery = db.createCommand();
subQuery.select(MY_TABLE.ID.as("MY_KEY"), ...);
subQuery.join(...);
subQuery.where(...);
DBCommand mainQuery = db.createCommand();
mainQuery.select(subQuery.getCmdColumn(1), ...);
mainQuery.join(subQuery.getCmdColumn(0), MY_TABLE.ID);
The resulting SQL statement is:
SELECT ...
FROM ( SELECT ...
FROM ...
WHERE ...
)
INNER JOIN MY_TABLE t1 ON
t1.ID = MY_KEY
The execution of this statement on a MySQL database fails and I get a
"MySQLSyntaxErrorException: Every derived table must have its own
alias", i.e. the correct statement including the necessary alias would be:
SELECT ...
FROM ( SELECT ...
FROM ...
WHERE ...
) MY_ALIAS
INNER JOIN MY_TABLE t1 ON
t1.ID = MY_ALIAS.MY_KEY
Is it possible for me to somehow add this missing alias? Or is there
another way to generate such a sub query?
Or is this type of subquerying simply not supported yet by empire-db?
Thanks,
Gunnar
Re: How to generate sub queries
Posted by andrew cooke <ac...@gmail.com>.
Hi,
If I remember correctly, you need to use table.clone() to make a new
copy of the table with a different alias, then use that in the
subquery.
Andrew
2009/11/26 Gunnar Gr-Hovest <gr...@atb-bremen.de>:
> Hi,
>
> I would like to know to which extent sub queries in the FROM part of an
> SQL statement are currently supported by empire-db. I tried to create
> such a sub query in the following way:
>
> DBCommand subQuery = db.createCommand();
>
> subQuery.select(MY_TABLE.ID.as("MY_KEY"), ...);
> subQuery.join(...);
> subQuery.where(...);
>
> DBCommand mainQuery = db.createCommand();
>
> mainQuery.select(subQuery.getCmdColumn(1), ...);
> mainQuery.join(subQuery.getCmdColumn(0), MY_TABLE.ID);
>
> The resulting SQL statement is:
>
> SELECT ...
> FROM ( SELECT ...
> FROM ...
> WHERE ...
> )
> INNER JOIN MY_TABLE t1 ON
> t1.ID = MY_KEY
>
> The execution of this statement on a MySQL database fails and I get a
> "MySQLSyntaxErrorException: Every derived table must have its own
> alias", i.e. the correct statement including the necessary alias would be:
>
> SELECT ...
> FROM ( SELECT ...
> FROM ...
> WHERE ...
> ) MY_ALIAS
> INNER JOIN MY_TABLE t1 ON
> t1.ID = MY_ALIAS.MY_KEY
>
> Is it possible for me to somehow add this missing alias? Or is there
> another way to generate such a sub query?
>
> Or is this type of subquerying simply not supported yet by empire-db?
>
> Thanks,
> Gunnar
>
RE: How to define and retrieve sequences for SQL Server 2005
Posted by Victor Manuel Rojo Nava <vi...@asteriscogato.com>.
Thanks for the Response.
I noticed there is a table named as Sequences.
As far as I understand, I must insert on it a sequence name and the starting
value for using this feature.
The problem I have is how do I retrieve the values from it. I haven't found
on the examples how to do it.
Do you have some example about it?
I want to avoid stored procedures on my DB for doing it so.
Regards,
Victor
-----Mensaje original-----
De: Rainer Döbele [mailto:doebele@esteam.de]
Enviado el: jueves, 11 de febrero de 2010 02:43 a.m.
Para: empire-db-user@incubator.apache.org; victor@asteriscogato.com
Asunto: re: How to define and retrieve sequences for SQL Server 2005
Hi Victor,
SQL-Server does not support Sequences like e.g. Oracle does.
By Default the SQL-server driver uses identiy columns for the column type
AUTOINC for which the value is auto generated by the database.
In this case the function db.getNextSequenceValue() always returns NULL.
(You may detect this by calling
driver.isSupported(DBDriverFeature.SEQUENCES) )
To insert a record we recommend using a DBRecord object like this:
// Insert an Employee
DBRecord rec = new DBRecord();
rec.create(db.EMPLOYEES);
rec.setValue(db.EMPLOYEES.FIRSTNAME, firstName);
rec.setValue(db.EMPLOYEES..., ...);
rec.update(conn);
// Return Employee ID
return rec.getInt(db.EMPLOYEES.EMPLOYEE_ID);
The DBRecord object will take care of all necessary task involved with auto
generated values.
However if you really want sequences with SQL-Server there is another
option.
Empire-db allows sequences emulation by using a seqeunce table.
All you need to do is to enable the use of the seqeunce table by calling:
DBDatabaseDriverMSSQL driver = new DBDatabaseDriverMSSQL();
driver.setUseSequenceTable(true);
// Afterwards open the db
db.open(driver, conn);
The sequence table will be created when you let Empire-db create your
database.
For existing data models you may just create the sequence table like this:
// optionally create the sequence table
DBTable seqTable = db.getTable(driver.getSequenceTableName());
DBSQLScript script = new DBSQLScript();
db.getDriver().getDDLScript(DBCmdType.CREATE, seqTable, script);
script.run(driver, conn, true);
Regards
Rainer
Victor Manuel Rojo Nava wrote:
> re: How to define and retrieve sequences for SQL Server 2005
>
> Hi all,
>
> I'm figuring out how to define a sequence with this amazing framework
> and
> then retrieve its values.
> I tried inserting a sequence name in table sequences and after that
> invoke
> the getNextSequenceValue methods for DataBase or DBDatabaseDriverMSSQL
> but I
> always get a null value. Am I doing something wrong?
>
> Regards,
>
> ---
> Victor
re: How to define and retrieve sequences for SQL Server 2005
Posted by Rainer Döbele <do...@esteam.de>.
Hi Victor,
SQL-Server does not support Sequences like e.g. Oracle does.
By Default the SQL-server driver uses identiy columns for the column type AUTOINC for which the value is auto generated by the database.
In this case the function db.getNextSequenceValue() always returns NULL.
(You may detect this by calling driver.isSupported(DBDriverFeature.SEQUENCES) )
To insert a record we recommend using a DBRecord object like this:
// Insert an Employee
DBRecord rec = new DBRecord();
rec.create(db.EMPLOYEES);
rec.setValue(db.EMPLOYEES.FIRSTNAME, firstName);
rec.setValue(db.EMPLOYEES..., ...);
rec.update(conn);
// Return Employee ID
return rec.getInt(db.EMPLOYEES.EMPLOYEE_ID);
The DBRecord object will take care of all necessary task involved with auto generated values.
However if you really want sequences with SQL-Server there is another option.
Empire-db allows sequences emulation by using a seqeunce table.
All you need to do is to enable the use of the seqeunce table by calling:
DBDatabaseDriverMSSQL driver = new DBDatabaseDriverMSSQL();
driver.setUseSequenceTable(true);
// Afterwards open the db
db.open(driver, conn);
The sequence table will be created when you let Empire-db create your database.
For existing data models you may just create the sequence table like this:
// optionally create the sequence table
DBTable seqTable = db.getTable(driver.getSequenceTableName());
DBSQLScript script = new DBSQLScript();
db.getDriver().getDDLScript(DBCmdType.CREATE, seqTable, script);
script.run(driver, conn, true);
Regards
Rainer
Victor Manuel Rojo Nava wrote:
> re: How to define and retrieve sequences for SQL Server 2005
>
> Hi all,
>
> I'm figuring out how to define a sequence with this amazing framework
> and
> then retrieve its values.
> I tried inserting a sequence name in table sequences and after that
> invoke
> the getNextSequenceValue methods for DataBase or DBDatabaseDriverMSSQL
> but I
> always get a null value. Am I doing something wrong?
>
> Regards,
>
> ---
> Victor
How to define and retrieve sequences for SQL Server 2005
Posted by Victor Manuel Rojo Nava <vi...@asteriscogato.com>.
Hi all,
I'm figuring out how to define a sequence with this amazing framework and
then retrieve its values.
I tried inserting a sequence name in table sequences and after that invoke
the getNextSequenceValue methods for DataBase or DBDatabaseDriverMSSQL but I
always get a null value. Am I doing something wrong?
Regards,
---
Victor
Re: How to generate sub queries
Posted by andrew cooke <ac...@gmail.com>.
2009/11/26 Rainer Döbele <do...@esteam.de>:
>
> P.S. What Andrew suggested can be useful for self-joins.
oops. sorry.... :(
re: How to generate sub queries
Posted by Rainer Döbele <do...@esteam.de>.
Hi Gunnar,
for subqueries you have to use a DBQuery object to wrap your subquery command.
// Define the sub query
DBCommand subCmd = db.createCommand();
subCmd.select(...);
// Wrap command for subquery
DBQuery SQ = new DBQuery(subCmd);
// Define the main query
DBCommand cmd = db.createCommand();
cmd.select(..);
cmd.select(SQ.findQueryColumn(...));
cmd.join(..., SQ.findQueryColumn(...));
The DBQuery class does the aliasing part.
There is an example for subqueries in the advanced sample application provided with the distribution (empire-db-example-advanced). See function querySample(...) in SampleAdvApp.java.
Hope this helps.
Regards
Rainer
P.S. What Andrew suggested can be useful for self-joins.
Gunnar Gr-Hovest wrote:
> re: How to generate sub queries
>
> Hi,
>
> I would like to know to which extent sub queries in the FROM part of an
> SQL statement are currently supported by empire-db. I tried to create
> such a sub query in the following way:
>
> DBCommand subQuery = db.createCommand();
>
> subQuery.select(MY_TABLE.ID.as("MY_KEY"), ...);
> subQuery.join(...);
> subQuery.where(...);
>
> DBCommand mainQuery = db.createCommand();
>
> mainQuery.select(subQuery.getCmdColumn(1), ...);
> mainQuery.join(subQuery.getCmdColumn(0), MY_TABLE.ID);
>
> The resulting SQL statement is:
>
> SELECT ...
> FROM ( SELECT ...
> FROM ...
> WHERE ...
> )
> INNER JOIN MY_TABLE t1 ON
> t1.ID = MY_KEY
>
> The execution of this statement on a MySQL database fails and I get a
> "MySQLSyntaxErrorException: Every derived table must have its own
> alias", i.e. the correct statement including the necessary alias would
> be:
>
> SELECT ...
> FROM ( SELECT ...
> FROM ...
> WHERE ...
> ) MY_ALIAS
> INNER JOIN MY_TABLE t1 ON
> t1.ID = MY_ALIAS.MY_KEY
>
> Is it possible for me to somehow add this missing alias? Or is there
> another way to generate such a sub query?
>
> Or is this type of subquerying simply not supported yet by empire-db?
>
> Thanks,
> Gunnar