MalformedEndpointException

[acw]

When i try to fire this sql aganst Oracle
jdbc://SELECT%*%20FROM%20CATS_OWNER.V_CATS_CPH_WWW_SUBSCRIBE%20WHERE%20(FLT_TYPE%20in%20('B','C','G','J','S')%20AND%20PUBLIC_ROLL_OFF%20=%200)%20AND%20SCHEDULE_TM%20>=%20'2007-04-13%2012:16:40'%20AND%20SCHEDULE_TM%20<=%20'2007-04-13%2014:16:40'

i get an org.mule.umo.endpoint.MalformedEndpointException.

This sql works perfectly in sql+ and TOra.

The problem only comes when i add the last two conditions, SCHEDULE_TM >= '2007-04-13 12:16:40' AND SCHEDULE_TM <= '2007-04-13 14:16:40' ...SCHEDULE_TM is a Date field.

What's wrong with inserting dates this way?

[Tim Schraepen]

My first guess would be the ":" that mark the separators between hours, minutes and seconds.
I think they're being "misinterperated" by the connector.

On 4/13/07, acw <andreas.willadsen@...> wrote:

When i try to fire this sql aganst Oracle
jdbc://SELECT%*%20FROM%20CATS_OWNER.V_CATS_CPH_WWW_SUBSCRIBE%20WHERE%20(FLT_TYPE%20in%20('B','C','G','J','S')%20AND%20PUBLIC_ROLL_OFF%20=%200)%20AND%20SCHEDULE_TM%20>=%20'2007-04-13%2012:16:40'%20AND%20SCHEDULE_TM%20<=%20'2007-04-13%2014:16:40'

i get an org.mule.umo.endpoint.MalformedEndpointException.

This sql works perfectly in sql+ and TOra.

The problem only comes when i add the last two conditions, SCHEDULE_TM >=
'2007-04-13 12:16:40' AND SCHEDULE_TM <= '2007-04-13 14:16:40'
...SCHEDULE_TM is a Date field.

What's wrong with inserting dates this way?
--
View this message in context: http://www.nabble.com/MalformedEndpointException-tf3571684.html#a9979137
Sent from the Mule - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list please visit:

    http://xircles.codehaus.org/manage_email

[kennywest]

This is how I solved this:
- configure your connector like this:
        <connector name="MyConnector"
                className="org.mule.providers.jdbc.JdbcConnector">
                <properties>
                        <container-property name="dataSource"
                                reference="MyDataSource" />
                        <map name="files">
                                <property name="MyQuery" value="MyQuery.sql" />
                        </map>
                        <factory-property name="queries"
                                factory="foo.bar.LoadFromFileFactory" />
                </properties>
        </connector>
- next create the following class:
package foo.bar;

import java.util.HashMap;
import java.util.Map;

import org.mule.config.PropertyFactory;
import org.mule.util.IOUtils;

public class LoadFromFileFactory implements PropertyFactory {

        public LoadFromFileFactory() {
        }

        public Object create(Map properties) throws Exception {
                HashMap ret = new HashMap();
                HashMap<String, String> files = (HashMap<String, String>) properties.get("files");
                String fileName = null;
                String content = null;
                for (String key : files.keySet()) {
                        fileName = files.get(key);
                        content = IOUtils.getResourceAsString(fileName, this.getClass());
                        ret.put(key, content);
                }
                return ret;
        }
}

- finally configure your endpoint:
                <endpoint name="MyEndpoint" address="jdbc://MyQuery"
                        connector="MyConnector" />

Done. The cool thing is that there's no need to escape your queries anymore. You can just create a file called MyQuery.sql containing your query, put it in your classpath and you're set.

@Mule guru's, maybe you can add this code (or a better version) to Mule and add the above sample in the documentation. Thanks!

[acw]

So what should I use instead? yyyy-MM-dd hh:mm:ss is the format in the db...

Tim Schraepen wrote:

My first guess would be the ":" that mark the separators between hours,
minutes and seconds.
I think they're being "misinterperated" by the connector.

On 4/13/07, acw <[e-mail]> wrote:
>
>
> When i try to fire this sql aganst Oracle
>
> jdbc://SELECT%*%20FROM%20CATS_OWNER.V_CATS_CPH_WWW_SUBSCRIBE%20WHERE%20(FLT_TYPE%20in%20('B','C','G','J','S')%20AND%20PUBLIC_ROLL_OFF%20=%200)%20AND%20SCHEDULE_TM%20>=%20'2007-04-13%2012:16:40'%20AND%20SCHEDULE_TM%20<=%20'2007-04-13%2014:16:40'
>
> i get an org.mule.umo.endpoint.MalformedEndpointException.
>
> This sql works perfectly in sql+ and TOra.
>
> The problem only comes when i add the last two conditions, SCHEDULE_TM >=
> '2007-04-13 12:16:40' AND SCHEDULE_TM <= '2007-04-13 14:16:40'
> ...SCHEDULE_TM is a Date field.
>
> What's wrong with inserting dates this way?
> --
> View this message in context:
> http://www.nabble.com/MalformedEndpointException-tf3571684.html#a9979137
> Sent from the Mule - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list please visit:
>
>     http://xircles.codehaus.org/manage_email
>
>

[acw]

This is not a suitable solution for me, as the sql string is built dynamically....Unless mule can register a change in the sql file...

kennywest wrote:

This is how I solved this:
- configure your connector like this:
        <connector name="MyConnector"
                className="org.mule.providers.jdbc.JdbcConnector">
                <properties>
                        <container-property name="dataSource"
                                reference="MyDataSource" />
                        <map name="files">
                                <property name="MyQuery" value="MyQuery.sql" />
                        </map>
                        <factory-property name="queries"
                                factory="foo.bar.LoadFromFileFactory" />
                </properties>
        </connector>
- next create the following class:
package foo.bar;

import java.util.HashMap;
import java.util.Map;

import org.mule.config.PropertyFactory;
import org.mule.util.IOUtils;

public class LoadFromFileFactory implements PropertyFactory {

        public LoadFromFileFactory() {
        }

        public Object create(Map properties) throws Exception {
                HashMap ret = new HashMap();
                HashMap<String, String> files = (HashMap<String, String>) properties.get("files");
                String fileName = null;
                String content = null;
                for (String key : files.keySet()) {
                        fileName = files.get(key);
                        content = IOUtils.getResourceAsString(fileName, this.getClass());
                        ret.put(key, content);
                }
                return ret;
        }
}

- finally configure your endpoint:
                <endpoint name="MyEndpoint" address="jdbc://MyQuery"
                        connector="MyConnector" />

Done. The cool thing is that there's no need to escape your queries anymore. You can just create a file called MyQuery.sql containing your query, put it in your classpath and you're set.

@Mule guru's, maybe you can add this code (or a better version) to Mule and add the above sample in the documentation. Thanks!

[Tim Schraepen]

Check out the Mule documentation on jdbc provider:
http://mule.codehaus.org/display/MULE/Jdbc+Provider

especially the "Creating Queries" part should be of interest to you :)

On 4/13/07, acw <andreas.willadsen@...> wrote:

This is not a suitable solution for me, as the sql string is built
dynamically....Unless mule can register a change in the sql file...


kennywest wrote:

>
> This is how I solved this:
> - configure your connector like this:
>       <connector name="MyConnector"
>               className="org.mule.providers.jdbc.JdbcConnector">
>               <properties>
>                       <container-property name="dataSource"
>                               reference="MyDataSource" />
>                       <map name="files">
>                               <property name="MyQuery" value=" MyQuery.sql" />
>                       </map>
>                       <factory-property name="queries"
>                               factory="foo.bar.LoadFromFileFactory " />
>               </properties>
>       </connector>
> - next create the following class:
> package foo.bar;
>
> import java.util.HashMap;
> import java.util.Map ;
>
> import org.mule.config.PropertyFactory;
> import org.mule.util.IOUtils;
>
> public class LoadFromFileFactory implements PropertyFactory {
>
>       public LoadFromFileFactory() {
>       }
>
>       public Object create(Map properties) throws Exception {
>               HashMap ret = new HashMap();
>               HashMap<String, String> files = (HashMap<String, String>)
> properties.get("files");
>               String fileName = null;
>               String content = null;
>               for (String key : files.keySet()) {
>                       fileName = files.get(key);
>                       content = IOUtils.getResourceAsString(fileName, this.getClass());
>                       ret.put(key, content);
>               }
>               return ret;
>       }
> }
>
> - finally configure your endpoint:
>               <endpoint name="MyEndpoint" address="jdbc://MyQuery"
>                       connector="MyConnector" />
>
> Done. The cool thing is that there's no need to escape your queries
> anymore. You can just create a file called MyQuery.sql containing your
> query, put it in your classpath and you're set.
>
> @Mule guru's, maybe you can add this code (or a better version) to Mule
> and add the above sample in the documentation. Thanks!
>
>

--
View this message in context: http://www.nabble.com/MalformedEndpointException-tf3571684.html#a9979371
Sent from the Mule - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list please visit:

    http://xircles.codehaus.org/manage_email

[kennywest]

Well, you can put place holders.
E.g.: select * from Customer where CustomerId=${customerId}

Mule will the use an XPath to find customerId in your incoming XML. E.g.:
<root>
    <customerId>1234</customerId>
</root>

Dunno if this helps.

>----- Oorspronkelijk bericht -----
>Van: acw [mailto:andreas.willadsen@...]
>Verzonden: vrijdag, april 13, 2007 03:51 PM
>Aan: user@...
>Onderwerp: Re: [mule-user] MalformedEndpointException
>
>
>This is not a suitable solution for me, as the sql string is built
>dynamically....Unless mule can register a change in the sql file...
>
>
>kennywest wrote:
>>
>> This is how I solved this:
>> - configure your connector like this:
>> <connector name="MyConnector"
>> className="org.mule.providers.jdbc.JdbcConnector">
>> <properties>
>> <container-property name="dataSource"
>> reference="MyDataSource" />
>> <map name="files">
>> <property name="MyQuery" value="MyQuery.sql" />
>> </map>
>> <factory-property name="queries"
>> factory="foo.bar.LoadFromFileFactory" />
>> </properties>
>> </connector>
>> - next create the following class:
>> package foo.bar;
>>
>> import java.util.HashMap;
>> import java.util.Map;
>>
>> import org.mule.config.PropertyFactory;
>> import org.mule.util.IOUtils;
>>
>> public class LoadFromFileFactory implements PropertyFactory {
>>
>> public LoadFromFileFactory() {
>> }
>>
>> public Object create(Map properties) throws Exception {
>> HashMap ret = new HashMap();
>> HashMap<String, String> files = (HashMap<String, String>)
>> properties.get("files");
>> String fileName = null;
>> String content = null;
>> for (String key : files.keySet()) {
>> fileName = files.get(key);
>> content = IOUtils.getResourceAsString(fileName, this.getClass());
>> ret.put(key, content);
>> }
>> return ret;
>> }
>> }
>>
>> - finally configure your endpoint:
>> <endpoint name="MyEndpoint" address="jdbc://MyQuery"
>> connector="MyConnector" />
>>
>> Done. The cool thing is that there's no need to escape your queries
>> anymore. You can just create a file called MyQuery.sql containing your
>> query, put it in your classpath and you're set.
>>
>> @Mule guru's, maybe you can add this code (or a better version) to Mule
>> and add the above sample in the documentation. Thanks!
>>
>>
>
>--
>View this message in context: http://www.nabble.com/MalformedEndpointException-tf3571684.html#a9979371
>Sent from the Mule - User mailing list archive at Nabble.com.
>
>
>---------------------------------------------------------------------
>To unsubscribe from this list please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>
>

---------------------------------------------------------------------
To unsubscribe from this list please visit:

    http://xircles.codehaus.org/manage_email

[Jan-Olav]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Have you tried using the TO_DATE Oracle function instead of using the date as string directly?  I think you may see some issues with the TNS_DATE_FORMAT value of your session. (it may not be set in your case)

 

--

jo

 

 

 

---

From: Tim Schraepen [mailto:sch3lp@...]
Sent: 13. april 2007 15:45
To: user@...
Subject: Re: [mule-user] MalformedEndpointException

 

My first guess would be the ":" that mark the separators between hours, minutes and seconds.
I think they're being "misinterperated" by the connector.

On 4/13/07, acw <andreas.willadsen@...> wrote:

When i try to fire this sql aganst Oracle
jdbc://SELECT%*%20FROM%20CATS_OWNER.V_CATS_CPH_WWW_SUBSCRIBE%20WHERE%20(FLT_TYPE%20in%20('B','C','G','J','S')%20AND%20PUBLIC_ROLL_OFF%20=%200)%20AND%20SCHEDULE_TM%20>=%20'2007-04-13%2012:16:40'%20AND%20SCHEDULE_TM%20<=%20'2007-04-13%2014:16:40'

i get an org.mule.umo.endpoint.MalformedEndpointException.

This sql works perfectly in sql+ and TOra.

The problem only comes when i add the last two conditions, SCHEDULE_TM >=
'2007-04-13 12:16:40' AND SCHEDULE_TM <= '2007-04-13 14:16:40'
...SCHEDULE_TM is a Date field.

What's wrong with inserting dates this way?
--
View this message in context: http://www.nabble.com/MalformedEndpointException-tf3571684.html#a9979137
Sent from the Mule - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe from this list please visit:

    http://xircles.codehaus.org/manage_email

 

[acw]

Thx for the inputs, I've solved this, and here is the solution:

Since the boolean operators '<' and '>' also gave a problem, i replace these with
%3C (<) and %3E (>).
Then i wrap my date in a to_date('2007-04-13 12:16:40','YYYY-MM-DD HH24:MI:SS').

This gives a query looking like:

SELECT * FROM CATS_OWNER.V_CATS_CPH_WWW_SUBSCRIBE WHERE (FLT_TYPE in ('B','C','G','J','S') AND PUBLIC_ROLL_OFF = 0) AND SCHEDULE_TM %3E= to_date('2007-04-13 12:16:40','YYYY-MM-DD HH24:MI:SS') AND SCHEDULE_TM %3C= to_date('2007-04-13 14:16:40','YYYY-MM-DD HH24:MI:SS')

acw wrote:

When i try to fire this sql aganst Oracle
jdbc://SELECT%*%20FROM%20CATS_OWNER.V_CATS_CPH_WWW_SUBSCRIBE%20WHERE%20(FLT_TYPE%20in%20('B','C','G','J','S')%20AND%20PUBLIC_ROLL_OFF%20=%200)%20AND%20SCHEDULE_TM%20>=%20'2007-04-13%2012:16:40'%20AND%20SCHEDULE_TM%20<=%20'2007-04-13%2014:16:40'

i get an org.mule.umo.endpoint.MalformedEndpointException.

This sql works perfectly in sql+ and TOra.

The problem only comes when i add the last two conditions, SCHEDULE_TM >= '2007-04-13 12:16:40' AND SCHEDULE_TM <= '2007-04-13 14:16:40' ...SCHEDULE_TM is a Date field.

What's wrong with inserting dates this way?

[kennywest]

Indeed, the problem is finding the correct escape characters.
When I was using URLs like this, I just copy pasted the select statement in a browser (like firefox) and let him resolve the escaping.

E.g. query: select * from a where b <= 1000
Paste this as: http://a.b.c/a?select * from a where b <= 1000
Returns as: http://a.b.c/a?select%20*%20from%20a%20where%20b%20%3C=%201000

Rather stupid, but it works ;)

[Holger Hoffstätte-2]

Guys,

could one of you please file an enhancement request in JIRA? We should be
able to properly escape any special characters for the JDBC URL without
making the user jump through .. the web browser ;)

Holger


---------------------------------------------------------------------
To unsubscribe from this list please visit:

    http://xircles.codehaus.org/manage_email

[kennywest]

Done:
http://mule.mulesource.org/jira/browse/MULE-1630