how to specify datasource for mysql.

[sweta]

hi,

i was trying to invoke the mysql database as done in the sql.emml

the script is as follows.

<mashup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.openemml.org/2009-04-15/EMMLSchema ../
schemas/EMMLSpec.xsd"
xmlns="http://www.openemml.org/2009-04-15/EMMLSchema"
name="MysqlDatabase">
<output name="result" type="document"/>
<datasource url="jdbc:mysql://localhost:3306/movielib"
username="root" password="mysql" />
<sql query="select * from users" outputvariable="result"/>
<display message="result = " expr="$result//CREATED/string()"/>
</mashup>


This gave me the output as..

<error>
<errorMessage>1002</errorMessage>
<errorMessage><![CDATA[Could not get JDBC Connection; nested exception
is org.ap
ache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of
class 'org.hs
qldb.jdbcDriver' for connect URL 'jdbc:mysql://localhost:3306/
movielib']]> </err
orMessage>
</error>

So is it like i can only specify datasource of hsqldb.
or do we have another way of specifying mysql database in the
datasource.

Thanks in advance.
Sweta :-)

[sweta]

Hi again,

One more thing is when i tried to run the script sql.emml as it is..
neither that ran.

It gave an error as follows..

<error>
<errorMessage>1002</errorMessage>
<errorMessage><![CDATA[Could not get JDBC Connection; nested exception
is org.apache.commons.dbcp.SQLNestedException: Cannot create

PoolableConnectionFactory (socket creation error)]]> </errorMessage>
</error>

[Anupam]

Hi Sweta ,

Please try with IP address instead of localhost .

Regards
Anupam

[Sara Mitchell]

It isn't just an IP address issue. Databases from different vendors may require slightly different forms of URLs for a connection. You can find a list of URL forms for some of the most common types of databases in the documentation at:

http://www.openmashup.org/omadocs/v1.0/emml/declareDatasource.html

You may also, however, need to look at docs for your particular database.

--- On Thu, 11/5/09, Anupam <anupa...@wipro.com> wrote:

[sweta]

Hi,

I have checked the url for mysql it has to be written the way i wrote
it.. IP address is not working too.. Sara what docs i have to refer
for mysql?

Thanks,
Sweta

On Nov 5, 11:00 pm, Sara Mitchell <samitche...@yahoo.com> wrote:
> It isn't just an IP address issue. Databases from different vendors may require slightly different forms of URLs for a connection. You can find a list of URL forms for some of the most common types of databases in the documentation at:
>
> http://www.openmashup.org/omadocs/v1.0/emml/declareDatasource.html
>
> You may also, however, need to look at docs for your particular database.
>

> --- On Thu, 11/5/09, Anupam <anupam....@wipro.com> wrote:

[sweta]

Hi again,

Now i added the driverclassname in datasource tag.

the script now is..

<mashup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.openemml.org/2009-04-15/EMMLSchema ../
schemas/EMMLSpec.xsd"
xmlns="http://www.openemml.org/2009-04-15/EMMLSchema"

name="DatabaseSample">

<output name="result" type="document"/>

<!-- datasource on this machine -->
<datasource url="jdbc:mysql://pc-p41049:3306/movielib"
driverclassname="com.mysql.jdbc.Driver" username="root"
password="mysql" />

<!-- execute select statement. Result is converted into XML
document -->

<sql query="select * from users" outputvariable="result"/>

<display message="result = " expr="$result//CREATED/string()"/
>
</mashup>

now it is giving me the same error as the one i m getting while
running sql.emml that comes with the sample scripts.

the error is as follows.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

<error>
<errorMessage>1002</errorMessage>
<errorMessage><![CDATA[Could not get JDBC Connection; nested exception
is org.apache.commons.dbcp.SQLNestedException: Cannot create

PoolableConnectionFactory (Access denied for user 'root'@'pc-
p41049.patni.com' (using password: YES))]]> </errorMessage>
</error>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks,
Sweta

[Anupam]

Hi sweta ,

For me it worked after giving the IP address & driverclassname .

are the credentials you are giving are working in other projects :
jdbc:mysql://pc-p41049:3306/movielib
user: root
pass: mysql

Regards
Anupam

[Sara Mitchell]

Can you create a connection to this MySql database outside of EMML?

The error does seem to imply that the user credentials you are providing are not correct. If you can, however, connect in another application using this URL and credentials, then there is another problem -- not sure at the moment what.

--- On Fri, 11/6/09, sweta <desai...@gmail.com> wrote:

[sweta]

hi sara,

I does connection with this mysql database in an application that is
created.. its working fine with java..

Thanks,
Sweta

On Nov 7, 12:00 am, Sara Mitchell <samitche...@yahoo.com> wrote:
> Can you create a connection to this MySql database outside of EMML?
>
> The error does seem to imply that the user credentials you are providing are not correct. If you can, however, connect in another application using this URL and credentials, then there is another problem -- not sure at the moment what.
>

> --- On Fri, 11/6/09, sweta <desai3sw...@gmail.com> wrote:

> > ---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------

> > <error>
> > <errorMessage>1002</errorMessage>
> > <errorMessage><![CDATA[Could not get JDBC
> > Connection; nested exception
> > is org.apache.commons.dbcp.SQLNestedException: Cannot
> > create
> > PoolableConnectionFactory (Access denied for user
> > 'root'@'pc-
> > p41049.patni.com' (using password: YES))]]>
> > </errorMessage>
> > </error>

> > ---------------------------------------------------------------------------­---------------------------------------------------------------------------­-----------------

> > > > > Anupam- Hide quoted text -
>
> - Show quoted text -

[sweta]

hi Sara,

It worked now.. thanks a lot.. it was just that i didnt try it with
localhost.. instead of that i was giving my pc id.. as soon as i wrote
localhost it started working..

Thanks a lot,
Sweta :-)

> > > ---------------------------------------------------------------------------­­--------------------------------------------------------------------------­-­-----------------

> > > <error>
> > > <errorMessage>1002</errorMessage>
> > > <errorMessage><![CDATA[Could not get JDBC
> > > Connection; nested exception
> > > is org.apache.commons.dbcp.SQLNestedException: Cannot
> > > create
> > > PoolableConnectionFactory (Access denied for user
> > > 'root'@'pc-
> > > p41049.patni.com' (using password: YES))]]>
> > > </errorMessage>
> > > </error>

> > > ---------------------------------------------------------------------------­­--------------------------------------------------------------------------­-­-----------------

> > - Show quoted text -- Hide quoted text -

[sweta]

hi anupan,

Thanks for the help. it really helped when you asked for the
correction of the credentials i was giving for the datasource.
Another mistake i made was i configured my MySql with localhost as my
host name so i will have to reconfigure it giving my IP address. So
probably then it will allow access with my pc identity too..

Thanks Sara and anupam,
Sweta

> > > > ---------------------------------------------------------------------------­­­-------------------------------------------------------------------------­-­-­-----------------

> > > > <error>
> > > > <errorMessage>1002</errorMessage>
> > > > <errorMessage><![CDATA[Could not get JDBC
> > > > Connection; nested exception
> > > > is org.apache.commons.dbcp.SQLNestedException: Cannot
> > > > create
> > > > PoolableConnectionFactory (Access denied for user
> > > > 'root'@'pc-
> > > > p41049.patni.com' (using password: YES))]]>
> > > > </errorMessage>
> > > > </error>

> > > > ---------------------------------------------------------------------------­­­-------------------------------------------------------------------------­-­-­-----------------