This discussion is archived
7 Replies Latest reply: Jun 10, 2009 8:30 AM by 807581 RSS

How to connect musicdb to Microsoft SQL Server

807581 Newbie
Currently Being Moderated
hi..
i have tried musicdb example with mysql, but i need to connect it to SQL Server.
i have created a similar DB in SQL SERVER, however i am not able to create a user, similar to musicdbuser and musicdbpass in SQL SERVER.

Also in the Administrator console i followed these steps.

1. Applications->web Applications->deployed the war file-> OK
2.Resources->JDBC->Connection Pool->New->Name ='musicdb-jdbc-pool-SQLserver'->Resource Type='javax.sql.Datasource'->Database Vendor='Microsoft SQL SERVER'-> Next

added properties Values

databaseName musicdb(this is the database i created in MS SQL SERVER)
databaseSourceName musicdb(this is the DSN name i created in control panel->ODBC connections)
networkProtocol TCP
user sa (this is the username that i use to start SQL SERVER)
password syntel123$ (this is the password that i use with the above user )
portNumber 1443
serverName localhost

then i click finish.
if i say ping to this connection , i get success!!

3. then i goto resources->jdbc->jdbc resources->new->JNDI Name="jdbc/musicdb"->pool name="musicdb-jdbc-pool-SQLserver'"->ok

4. Then i click resources->connectors->connector connection pool->new->Name="mep/musicdb"->resource adapter="ds-jcr-connector-jaxrs"->next->finish

5 Then i click resources->connector resources->new->JNDI name="mep/musicdb"->pool="mep/musicdb"->ok


however when i hit the URL http://localhost:8080/musicdb/resources/albums
i get the following error
type : Exception report

message

description: The server encountered an internal error () that prevented it from fulfilling this request.

exception: javax.servlet.ServletException: com.sun.jersey.api.container.ContainerException: java.lang.NoClassDefFoundError: Could not initialize class com.sun.mep.ws.vtms.util.ConnectionPool

root cause: com.sun.jersey.api.container.ContainerException: java.lang.NoClassDefFoundError: Could not initialize class com.sun.mep.ws.vtms.util.ConnectionPool

root cause: java.lang.NoClassDefFoundError: Could not initialize class com.sun.mep.ws.vtms.util.ConnectionPool
  • 1. Re: How to connect musicdb to Microsoft SQL Server
    807581 Newbie
    Currently Being Moderated
    Hi Anuminu,

    You are on the right track. One thing I see is that the URL should be

    http://localhost:8080/musicdb-ws/resources/albums

    Notice the "-ws" added.

    Another thing, you may not have done is to configure the JAX-RS-based MusicDB Connector in the SGMP Admin Console with the local enterprise connector property "uri" whose value equals the URL above.

    See the section "Building, Deploying, and Configuring a JAX-RS Enterprise Connector" in the developers connector guide

    http://docs.sun.com/app/docs/doc/820-7207

    I'm not sure this will completely solve your problem but give it a try and see how much further you get.
    Good Luck!
  • 2. Re: How to connect musicdb to Microsoft SQL Server
    807581 Newbie
    Currently Being Moderated
    Hi...
    i have tried as you said. but it makes no diffence....i get the same error.
    Also i would like to share that , In the administration console when i clicked
    JDBC->Resource->connection pool->name="mep/musicdb"->resource type="javax.sql.Data Source"->vendor="MS SQL Server"->Next.
    I get the DataSource Classname as com.ddtek.jdbcx.sqlserver.SQLServerDataSource, then i add the properties

    added properties Values

    databaseName musicdb(this is the database i created in MS SQL SERVER)
    databaseSourceName musicdb(this is the DSN name i created in control panel->ODBC connections)
    networkProtocol TCP
    user sa (this is the username that i use to start SQL SERVER)
    password syntel123$ (this is the password that i use with the above user )
    portNumber 1443
    serverName 172.29.120.128(this is the IP address of the machine where the SQL Server is present )

    Now i click finish.
    Now when i say ping, it gives me an error

    Class name is wrong or classpath is not set for : com.ddtek.jdbcx.sqlserver.SQLServerDataSource

    so i changed the DataSource Classname 2 sun.jdbc.odbc.ee.DataSource.
    now when i click ping. I get a success!!


    However i am not able to hit the url : http://localhost:8080/musicdb-ws/resources/albums
    Also i have added the above url to the uri of musicdb connector in administrator console.and i am not able to add users to the musicdb connector.
    i get this error
    javax.jcr.LoginException: Login failed:javax.jcr.SimpleCredentials@d4c984: java.lang.NoClassDefFoundError: Could not initialize class com.sun.mep.ws.musicdb.util.ConnectionPool: java.lang.NoClassDefFoundError: Could not initialize class com.sun.mep.ws.musicdb.util.ConnectionPool

    please help......
  • 3. Re: How to connect musicdb to Microsoft SQL Server
    807581 Newbie
    Currently Being Moderated
    Hi,

    I was able to create a JDBC connection pool for SQL Server successfully and will share the key steps, and was able to create a MEP user with an ECBO connection configured for the SQL Server; However, it looks like there is a "false positive" problem. Even though the user was created with the SQL Server connector whose associated JDBC connection pool passed the Ping test, doing a sync as that user changed the MySQL database album table and not the SQL Sever album table. MEP didn't give any error at all. I will bring this up with our engineers.

    Here are the steps for at least getting GF JDBC connections with SQL Server working.

    1. With MS SQL Sever 2005 Server Management Studio,
    created a 'musicdb' database, a 'musicdbuser' user with password 'musicdbpass',
    and added the 'album' table with

    name (varchar(30) primary key, not null)
    artist (varchar(30), not null)
    date_published (datetime, not null)
    rating (int, not null)
    username (varchar(30), not null)
    2. With MS SQLServer Configuration Manager, enable TCP/IP on static IP address
    and 127.0.0.1 on port 1433 and restarted the SQL Server service
    3. Downloaded Microsoft's 2.0 Driver for SQL Server 2005
    4. Copied the driver sqljdbc4.jar to GF_HOME/domains/mep/lib/ext
    5. Restarted GF
    6. Login to GF Admin Console
    7. Create Resources -> JDBC -> Connection Pool
    name: musicdb_pool_sqlserver
    resource type: javax.sql.DataSource
    datasource class: com.microsoft.sqlserver.jdbc.SQLServerDataSource
    8. Create Resources -> JDBC -> Resource
    name jdbc/musicdb_sqlserver
    9. Go back to musicdb_pool_sqlserver and click Additional Properties then add
    user: musicdbuser
    password: musicdbpass
    serverName=localhost
    portNumber=1433
    databaseName=musicdb
    10. Click General tab and click Ping. Get Success!
  • 4. Re: How to connect musicdb to Microsoft SQL Server
    807581 Newbie
    Currently Being Moderated
    Turns out that the MusicAlbumProvider class has

    >public static final String MUSICDB_JNDI_DATASOURCE = "jdbc/musicdb";

    I had created a new JDBC and a JDBC resource named jdbc/musicdb_sqlserver, and left the original JDBC connection pool and resource. So I removed the "jdbc/musicdb_sqlserver" resource and changed the "jdbc/musicdb" JDBC resource to point to the connection pool "musicdb_jdbc_pool_sqlserver".

    Afterward I was still able to create a musicdb user with the SQL Server connector and on doing syncs the MySQL database was no longer being accessed. The secure musicdb client reported success, but the SQL Server database table dbo.album was not changed. The GF server log shows a javax.jcr.RepositoryException: DATE is not permitted in this context...

    It appears that the ECBO connector is now accessing the SQL Server as the backend. I tried to set up the musicdb database and album table in SQL Server as it is on MySQL as much as possible, but the date_published column doesn't seem compatible. Also, not being familiar with SQL Server, I'm not sure about the 'album' table name. When I created an album table with SQL Server Management Studio it got named "dbo.album". Not sure if that prefix "dbo" has to specified by the MEP connector, or not.

    The MEP developer who knows the musicdb sample and connectors is on vacation until next week. He should be able to help when he gets back.
  • 5. Re: How to connect musicdb to Microsoft SQL Server
    807581 Newbie
    Currently Being Moderated
    hi...
    Thanks for your reply, Now i can successfully work with SQL SERVER.I tried it with the JAX-RS connector.
    While Doing syncs the MySQL database is no longer being accessed.
    Also the album table dbo.album, does not crate any problem, it is a standard way of naming databases in SQL SERVER.
    Thanks
  • 6. Re: How to connect musicdb to Microsoft SQL Server
    807581 Newbie
    Currently Being Moderated
    Hi Anuminu,

    Glad to hear you have made such progress. I have tried both ECBO and JAX-RS connectors. They both work with the SQL Server dbo.album -- going from server to client -- but I get errors on trying to sync from client to server on the date field. Did you get past that?

    Here is the error in the GF server.log on doing a two-way sync after changing the albums on the client. Note: those albums were initially pulled onto the client from the SQL Server by doing a "Refresh from Server" sync. The client indicated the sync was successful, but using 'sqlcmd' on the server and doing 'select * from album' showed the albums were not updated on the server.

    [#|2009-06-09T09:28:18.093-0400|SEVERE|sun-appserver2.1|javax.enterprise.system.container.web|_ThreadID=22;_ThreadName=5344f1a28f6030eb52b829084c84;_RequestID=9d09ae62-8cb2-48d5-b2c6-80e3608218cc;|StandardWrapperValve[ServletAdaptor]: PWC1406: Servlet.service() for servlet ServletAdaptor threw exception
    java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '2009-6-8'.
         at com.sun.mep.ws.musicdb.sync.MusicAlbumResource.putBusinessObject(MusicAlbumResource.java:135)
         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
         at java.lang.reflect.Method.invoke(Method.java:597)
  • 7. Re: How to connect musicdb to Microsoft SQL Server
    807581 Newbie
    Currently Being Moderated
    SQL Server 2005 does not support the DATE data type. So I created the 'date_published' column in the album table with a DATETIME field. Consequently, the following SQL statement fails because it is using DATE rather than DATETIME.

    stringBuilder.append("UPDATE album SET artist='")
    .append(artist).append("', date_published=DATE '")
    .append(datePublished.get(Calendar.YEAR)).append("-")
    .append(datePublished.get(Calendar.MONTH) + 1).append("-")
    .append(datePublished.get(Calendar.DAY_OF_MONTH)).append("', rating = ")
    .append(Integer.toString(rating));

    Hopefully, you are using a version of SQL Server, such as SQL Server 2008, that has the DATE data type.