Digest for jooq-user@googlegroups.com - 4 updates in 2 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 4 updates in 2 topics

http://groups.google.com/group/jooq-user/topics mailing list
Michael Redlich <[hidden email]>: Nov 26 05:47PM -0800

Hello:
 
I'm still new to jOOQ. I'm trying to duplicate the following SQL statement:
 
String sql = "SELECT CONCAT(last_name,', ',first_name,' (home)') AS name,home_email FROM attendees WHERE list = 1 UNION SELECT CONCAT(last_name,', ',first_name,' (work)') AS name,work_email FROM attendees WHERE list = 1";
 
I've tried the *.union()* method in conjunction with *.selectFrom()* and with *.select()* methods and still getting errors relative to types, etc..
 
I would appreciate any help...thanks!
 
Mike.
Lukas Eder <[hidden email]>: Nov 27 09:18AM +0100

Hi Mike,
 
Thanks for your message. What have you tried? Would you mind posting your
jOOQ code as well?
 
Essentially (just like in SQL), your two union subqueries must have exactly
the same row type. In your case, that would correspond to Record2<String,
String>
 
Thanks,
Lukas
 
Michael Redlich <[hidden email]>: Nov 27 05:17AM -0800

Hi Lukas:
 
Thanks for getting back to me so quickly!
 
Here is one variation of what I tried based on the documentation:
 
public void testUnion(DSLContext context) {
// String sql = "SELECT CONCAT(last_name,', ',first_name,' (home)') AS name,home_email FROM attendees WHERE list = 1 UNION SELECT CONCAT(last_name,', ',first_name,' (work)') AS name,work_email FROM attendees WHERE list = 1";
 
Result<Record> context.selectFrom(ATTENDEES.HOME_EMAIL)
.union(context.selectFrom(ATTENDEES.WORK_EMAIL))
.fetch();
}
 
Passing in an instance of *DSLContext* has worked with other methods. And what I failed to do was consider using *Record2<String,String>*.
 
I will have to a look...thanks!
 
Mike.
 
 
 
On Monday, November 27, 2017 at 3:18:53 AM UTC-5, Lukas Eder wrote:
Manuel Rossetti <[hidden email]>: Nov 26 02:30PM -0800

I believe that there is a problem with how the DDL statements are being
constructed. I would like to know if anyone else sees this as a problem.
 
DSLContext create = DSL.using(getConnection(), SQLDialect.DERBY);
 
Queries queries = create.ddl(getUserSchema());
 
The "ALTER" queries do not have their constraints named. In the exception
below, the name of the constraint is expected but the keyword "foreign" was
encountered. In the original creation script for the database all
constraints were named but the name 'EdgeArc' was not captured by the
create.ddl() statement.
 
ALTER TABLE Arc ADD CONSTRAINT EdgeArc FOREIGN KEY (Edge) REFERENCES Edge
(ID);
 
SQLException when executing alter table "APP"."ARC" add constraint foreign
key ("EDGE") references "EDGE" ("ID")
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "foreign" at
line 1, column 41.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at
dbtools.dbutil.EmbeddedDerbyDatabase.executeCommand(EmbeddedDerbyDatabase.java:680)
at
dbtools.dbutil.EmbeddedDerbyDatabase.executeCommands(EmbeddedDerbyDatabase.java:710)
at
dbtools.dbutil.EmbeddedDerbyDatabase.duplicate(EmbeddedDerbyDatabase.java:210)
at
dbtools.dbutil.EmbeddedDerbyDatabase.testWrting(EmbeddedDerbyDatabase.java:910)
at
dbtools.dbutil.EmbeddedDerbyDatabase.main(EmbeddedDerbyDatabase.java:882)
Caused by: ERROR 42X01: Syntax error: Encountered "foreign" at line 1,
column 41.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ParserImpl.parseStatementOrSearchCondition(Unknown
Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].