7 Replies Latest reply: Jun 10, 2009 10:30 AM by 807581 RSS

    How to connect musicdb to Microsoft SQL Server

    807581
      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
          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
            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
              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
                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
                  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
                    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
                      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.