literal before select (i.e.XMLNAMESPACES)

[Denis Miorandi]

consider this query. 

--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! 
WITH XMLNAMESPACES (DEFAULT 'http://www.xxxx.com/api/yyyy') 

SELECT  a
	,b
	,SOME XML 's fields from RR.TT
 
from TABLE
  cross apply xml.nodes(' / MYROOT ') [RR]([TT])'

I need just to add this literal string before select, but I don't know how to achieve this.

WITH XMLNAMESPACES (DEFAULT 'http://www.xxxx.com/api/yyyy') 

I know just how to add literal with DSL.VAL('') but how about before query?

Tks

Denis

[Lukas Eder]

Hi Denis,

Interesting, I just had to look this up in the SQL Server manuals:

https://msdn.microsoft.com/en-us/library/ms177400.aspx

It appears that SQL Server has reused the common table expression syntax (WITH) for an entirely different use-case. This means, you cannot use jOOQ's with clause in this case, as jOOQ's with expects a Select as argument.

The only options that I can see right now are:

- Use an ExecuteListener to inject the additional SQL string in ExecuteListener.renderEnd()

- Prepend the additional SQL manually and execute the whole thing as "plain SQL"

For example:

DSL.using(configuration).fetch("with xmlnamespaces (...) {0}", select);

Does that help?

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Denis Miorandi]

I've tried second approach. Sql is generated as expected and query executed, but resultset are not typed as expected.

I.e. LocalDateTime types are java.sql.TimeStamp and so on....

Any ideas?

Do you think ExecuteListener approach would run into same issue? If you think not i could try it.

Tks

[Lukas Eder]

2016-06-02 15:55 GMT+02:00 Denis Miorandi <denis.m...@gmail.com>:

I've tried second approach. Sql is generated as expected and query executed, but resultset are not typed as expected.

I.e. LocalDateTime types are java.sql.TimeStamp and so on....

Oh, indeed. I'm sorry I overlooked that. There's a pending feature request to allow for passing the result record type to a plain SQL query:

https://github.com/jOOQ/jOOQ/issues/4473

Other than that, jOOQ doesn't know what kind of result it will get when you run plain SQL queries. 

Do you think ExecuteListener approach would run into same issue? If you think not i could try it.

No, that just modifies the rendered SQL without jOOQ noticing, so you will get the expected record type.

Lukas

[Denis Miorandi]

Hi Lukas,

       finally I've kept first approach (the fetch one) cause I need a query local approach, not a global one (my dsl context is global, via spring 4). 

I've just added my already existings java8 time* converters inside the RecordMapper in getValue(), and it works.

Just to understand for future tasks, the Listener approach is always at global/config level? Or it's possible to add a listener also at query level?

Tks

Denis

[Lukas Eder]

Hi Denis,

2016-06-03 8:32 GMT+02:00 Denis Miorandi <denis.m...@gmail.com>:

Hi Lukas,

       finally I've kept first approach (the fetch one) cause I need a query local approach, not a global one (my dsl context is global, via spring 4). 

I've just added my already existings java8 time* converters inside the RecordMapper in getValue(), and it works.

Sure, that's certainly doable. I suspect that this is for 1-2 queries only, anyway...

Just to understand for future tasks, the Listener approach is always at global/config level? Or it's possible to add a listener also at query level?

It's always at config level. But the config isn't always global. While you're still welcome to continue using a global Configuration / DSLContext, do note that there are various Configuration.derive() methods, which help you derive a *new* Configuration from an existing one, e.g. just for a single query. For example:

@Autowired

Configuration global;

// and then:

DSL.using(global.derive(ADD_WITH_XML_NAMESPACES_LISTENER))

   .select(...)

   .from(...);

// or even:

public static DSLContext withXmlNamespaces() {

    return global.derive(...);

}

// and then (this looks as though jOOQ supported the feature):

withXmlNamespaces()

    .select(...)

    .from(...)

Hope this helps,

Lukas

[Denis Miorandi]

O yeahh this what I want. 

Tks Lukas

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/Qn23iP3m5oA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Dott. Denis Miorandi
via dei Vignai 27
38060 - Nogaredo (TN)