Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-6552

Provide a better integration with SQL Server by using the Microsoft JDBC Driver (4.X) instead of the jTDS driver

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.2
    • Component/s: Database
    • Labels:

      Description

      For a pure .net player, this does make sense to use the jTds driver.

      Actions required to use JDBC Driver for SQL Server 4.X (provided by Koushik Dasgupta):

      Changed Code

      • Supporting MS JDBC Driver #1

      File: ..\sonar-application\src\main\java\org\sonar\application\JdbcSettings.java
      The "enum Provider" needs to be changed to specify the MS JDBC Driver.

      Following is the changed code. "SQLSERVER" is the token that needs to be used here. The "lib/jdbc/ms" indicates where in the lib folder Sonar will find the MS JDBC driver.

      You need to copy the sqljdbc41.jar file in this location. This jar file is present in the installation folder of MS JDBC driver.

      Static enumProvider{
        //Koushik
        H2("lib/jdbc/h2"),JTDS("lib/jdbc/jtds"),MYSQL("lib/jdbc/mysql"),ORACLE("extensions/jdbc-driver/oracle"), POSTGRESQL("lib/jdbc/postgresql"),SQLSERVER("lib/jdbc/ms");
      }
      
      • Supporting MS JDBC Driver #2

      File: ..\sonar-core\src\main\java\org\sonar\core\persistence\dialect\MsSql.java

      We need to register the MS JDBC driver classname. I have hard-coded this in the following function.
      TODO: Depending upon the connection string (either JTDS or MS), we need to set the appropriate class name to register. Currently, JTDS is commented out below.

       
      //Koushik-NeedtochangethefollowingclassnametomatchMSdrivername
      publicMsSql(){
        //super(ID,"sqlserver","net.sourceforge.jtds.jdbc.Driver","1","0","SELECT1");
        super(ID,"sqlserver","com.microsoft.sqlserver.jdbc.SQLServerDriver","1","0","SELECT1");
      }
      
      • Supporting MS JDBC Driver #3

      File: ..\sonar-core\src\main\java\org\sonar\core\persistence\dialect\MsSql.java

      The connection string of MS JDBC driver does not contain "microsoft", hence the following should be changed.

      Typical connection string for MS JDBC driver:

      sonar.jdbc.url=jdbc:sqlserver://YourServer:1433;database=YourDatabaseName;user=UserName@YourServer;password=YourPassword;
      

      The following code needs to be changed

      public boolean matchesJdbcURL(StringjdbcConnectionURL){
        //Koushik
        returnStringUtils.startsWithIgnoreCase(jdbcConnectionURL,"jdbc:microsoft:sqlserver:") ||StringUtils.startsWithIgnoreCase(jdbcConnectionURL,"jdbc:jtds:sqlserver:");
      }
      

      To the following:

       
      public boolean matchesJdbcURL(StringjdbcConnectionURL){
        //Koushik
        returnStringUtils.startsWithIgnoreCase(jdbcConnectionURL,"jdbc:sqlserver:") ||StringUtils.startsWithIgnoreCase(jdbcConnectionURL,"jdbc:jtds:sqlserver:");
      }
      
      • Datatype Issue with Ruby on Rail

      File:
      ..\server\sonar-web\src\main\webapp\WEB-INF\gems\gems\activerecord-jdbc-adapter-1.1.3\lib\arjdbc\jdbc\type_converter.rb

      Issue:
      MS JDBC Driver returns 2 datatype for "Timestamp" data type, namely datetime and datetime2.

      You can find more info on MS JDBC driver datatype here

      As a result, the type_converter gets confused. To fix the problem, you have let the type converter know that both the datatype are fine.

      You will see the following error in Sonar log file:

      INFO  web[jruby.rack] An exception happened during JRuby-Rack startup
      The driver encountered an unknown error: unable to choose type for timestamp from: ["datetime2", "datetime"]
      

      Change the following code:

      :timestamp=>[lambda{|r|Jdbc::Types::TIMESTAMP==r['data_type'].to_i},
      lambda{|r|r['type_name']=~/^timestamp$/i},
      lambda{|r|r['type_name']=~/^datetime/i},
      lambda{|r|r['type_name']=~/^date/i},
      

      To the following:

      :timestamp=>[lambda{|r|Jdbc::Types::TIMESTAMP==r['data_type'].to_i},
      lambda{|r|r['type_name']=~/^timestamp$/i},
      lambda{|r|r['type_name']=~/^datetime$/i},#ForMSSQLJDBC(Koushikd)
      lambda{|r|r['type_name']=~/^date/i},
       

      Installing MS JDBC Driver

      https://msdn.microsoft.com/en-us/library/aa342329(v=sql.110).aspx
      https://msdn.microsoft.com/en-us/library/ms378526(v=sql.110).aspx

      To connect to SQL Azure

      You have to list the IP of the Sonar Server (or any client which is trying to connect to SQL Azure) in the SQL Azure IP exception list.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              simon.brandhof Simon Brandhof (Inactive)
              Reporter:
              freddy.mallet Freddy Mallet (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Due:
                Created:
                Updated:
                Resolved: