Sitescope Database connection URL, how to eliminate space ?

Experts,

             I have been using a new JDBC on Database Connection URL and unfortunately its not working because there is a space on the new jdbc request as below:-

Standard normal sql jdbc:       jdbc:sqlserver://X.X.X.X;databaseName=ABCD - this works

New JDBC request :               jdbc:pi//mySQLDAS/Data Source=myAFServer; Integrated Security=SSPI;     - this doesnt work, the issue is as below:-

The New JDBC request  Database URL doesnt work because there is a space in between "Data Source" and "Integrated Security" , sitescope doesnt allow space on database urls settins, how can i eliminate the space in between them so that i make the queries work the Database Connection URL ... please help...

 

Best Regards

Sara

                             

  • Hi Sara,

    I might be totally wrong, but the way I interpret the error message, the issue is NOT with the space in "Data Source",
    but with the space between "Data Source=myAFServer; Integrated Security=SSPI".
    Did you try
    " jdbc:pi//mySQLDAS/Data Source=myAFServer;Integrated Security=SSPI;"
    already?

    Greetings
    Siggi

  • Hi Sara,

    I checked myself and found that

    jdbc:pi//mySQLDAS/Data Source=myAFServer;Integrated Security=SSPI;

    doesn't work either, as the SiteScope GUI shows the red cross at the start of the field with the message

    "The field Database Connection URL can only contain non space characters"

    Let me check if there is an easy solution for it ...

    Greetings
    Siggi

  • Hi Sara,

    I found one related case stating
    ..
    The URL has to be one without space, because that is how SiteScope works
    ..

    I guess all you can do is to
    either
    open a case to have an ER opened to enable SiteScope to deal with Database Connection URLs which contain a space,
    or
    to check with the JDBC provide whether or not there are other ways to express "Data Source" and "Integrated Security",
    perhaps "DataSource" and "IntegratedSecurity" or "Data_Source" and "Integrated_Security" can work as well.

    In
    https://msdn.microsoft.com/en-us/library/ms378428(v=sql.110).aspx
    for example I found
    ..
    Connect on the default port to the remote server:
    jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedSecurity=true;
    ..

    so
    "jdbc:pi//mySQLDAS/databaseName=myAFServer;IntegratedSecurity=SSPI;
    or
    jdbc:pi//mySQLDAS/DataSource=myAFServer;IntegratedSecurity=SSPI;
    might work?

    Greetings
    Siggi

  • Siggi,

            Thank you for the suggestion, either method did not work. I have logged a case with HP and see how it goes. Im also working with the product/software to see if there is any alternative for this. Will keep you posted on the outcome.

    Best Regards

    Sara

  • Hi Sara,

    you are correct, it's an issue which is around for quite some time, for whatever reason I missed that when I was looking for known problems.

    I was able to use the backdoor by modifying the Database Connection URL in the Persistency directly.
    For this I copied one working monitor to
    "Test DB Query MSSQL sov02bac16 VM402_BSM_MGMT"

    The original Database Connection URL is
    "jdbc:sqlserver://sov02bac16.eu.hpecorp.net:1433;DatabaseName=VM402_BSM_MGMT"

    - stop SiteScope
    <SiteScope>\bin\PersistencyViewer.bat
    select Persistency offered by the tool
    -> Filter by Type
    com.mercury.sitescope.monitors.facades.DatabaseMonitor
    click on "Test DB Query MSSQL sov02bac16 VM402_BSM_MGMT"

    the field _database contains the Database Connection URL
    double-click on it and modify the value to your needs, for example to
    jdbc:pi//mySQLDAS/Data Source=myAFServer; Integrated Security=SSPI;
    and click Commit

    - start SiteScope

    Of course the monitor doesn't run on my system due to the wrong JDBC library,
    but - and this was the whole puprose of the exercise - RunMonitor.log shows
    that SiteScope at least uses the full string for the Database Connection URL,
    and doesn't split the string at the space or alike:
    ..
    2017-01-11 10:40:05,149 [Test DB Query MSSQL sov02bac16 VM402_BSM_MGMT(200981347/28) ] (DatabaseMonitor.java:376) ERROR - Failed to instantiate driver class.
    java.sql.SQLException: Driver com.microsoft.sqlserver.jdbc.SQLServerDriver does NOT accept URL: jdbc:pi//mySQLDAS/Data Source=myAFServer; Integrated Security=SSPI;
    at com.mercury.sitescope.infra.sql.pool.DataSourceConfigurationImpl.<init>(DataSourceConfigurationImpl.java:88)

    You can give it a try. After you midified the string in Persistency, you cannot modify the monitor in SiteScope, because of the space in the Connection URL, so you would always need to update the string to eliminate the space, then do the changes to the monitor, clikc on Save, and then go through the Persistency modification again. 
    A limping workaround, but it should work.

    Greetings
    Siggi