Mount WSO2 products to a remote WSO2 Governance Registry instance(MsSQL 2012)

In this post we are going to mount wso2 products into a remote governance registry. In the default scenario wso2 governance registry is pointed to a local h2 database.

First lets configure the governance registry instance to point to the MsSQL data source.

• Download WSO2 Governance Registry product and extract the product.
• Go to /repository/conf/datasources  and open master-datasources.xml
• Then change the following entries in the WSO2CARBON data source.

<datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:sqlserver://127.0.0.1:1433;databaseName=REMOTEDB</url>
                    <username>sa</username>
                    <password>pass#word2</password>
                    <driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
</datasource>

• Fill the url, username, password according to your MsSQL Instance.
• Log into the MSSQL 2012 Management studio and create a database called REMOTEDB
• Then move to the /repository/components/lib folder in the extracted greg product folder and copy the downloaded MsSQL JDBC Connector. You can download the sqljdbc driver from this link. Download
• Then Start the WSO2 governance product with the -Dsetup option. (-Dsetup option will populate the new schemas in the newly pointed MsSQL REMOTEDB)

D:\blog\wso2greg-4.6.0\bin>wso2server.bat -Dsetup

 Now lets mount a WSO2 product to this remote registry. 

• Let's use WSO2 API Manager for this example post. First Download the product and extract it to your hard drive.
• Go to /repository/conf/datasources folder and open the master-datasources.xml file

• Then add a new data source as follows. For the demonstration purpose lets name the new datasource as WSO2_CARBON_DB2.

<datasource>
            <name>WSO2_CARBON_DB2</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB2</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:sqlserver://127.0.0.1:1433;databaseName=REMOTEDB</url>
                    <username>sa</username>
                    <password>pass#word2</password>
                    <driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
</datasource>

• Note that this datasource is a completely new entry and let the default config reamins as it is.
• Now as we did in WSO2 governance product copy the saljdbc driver to the /repository/components/lib folder
• No open up the /repository/conf registry.xml file in the WSO2 API Manager product and do the following changes.
•  Add a new dbConfig as follows

<dbConfig name="remoteRegistry">
       <dataSource>jdbc/WSO2CarbonDB2</dataSource>
</dbConfig>

• Note that the dataSource is set to the jndiConfig name which we used in the new datasource defined in the API Manager's master-datasources.xml
• Now find the following configuration, uncomment it and change it to the following config values.

<remoteInstance url="https://localhost:9443/registry">
        <id>apimanager</id>
        <dbConfig>remoteRegistry</dbConfig>
        <readOnly>false</readOnly>
        <enableCache>true</enableCache>
        <registryRoot>/</registryRoot>
</remoteInstance>

• Note that the dbConfig name is set to the newly added dbConfig name, which is "remoteRegistry" in this example. Give a unique id value.
• Then we set the remote mounted path using the following configuration.

<mount path="/_system/config" overwrite="true">
        <instanceId>apimanager</instanceId>
        <targetPath>/_system/amnode</targetPath>
</mount>

• This configuration is comment by default and you have to uncomment and provide the relevant values. Note that the instanceID value is same as the id value in remoteInstance configuration.
• The target path is set to /_system/amnode if you are using several API Manager nodes you can set all to the same path. If it is a different product it is recommend to use a different path for example ESB node path would be /_system/esbnode
• /_system/config is the path which the product is going to be mounted.
• Now finally if you have followed the above steps correctly start the API Manager with some port Offset if the product is running in the same machine.

D:\blog\wso2am-1.6.0\bin>wso2server.bat -DportOffset=100

• Now log into the API Manager Web UI using the following URL https://192.168.1.2:9543/carbon/
• If you browse the resource can see the remotely mounted /_system/config registry.

API Manager Resource view

That's it for now. Feel free to shore your thoughts about the post. :)

MySQL C++ Connection Example in Visual Studio

Creating a MySQL database connection using C++ is  really easy. But for a beginner it can be really hard to find the correct procedure cause it will gave really troubles. At the first time when I'm doing it it took me quite some time to make it work. So I'm going to explain how to make a connection to the MySQL database using C++ code. We use the library called boost to make the connection. 

Step 1 (Download the boost library)

First step is to download the boost library. You can download it from here from the official download page Or you can download the one I've compressed and uploaded which is smaller in size from here.

Step 2 (Create the project using Visual Studio 2008)

First go  to File --> New --> Project and you'll get a screen like this. 

mysql c++ connection example

Select Visual C++ --> Win 32 --> Win 32 Console Application  and give a name to the project and press OK. 

Then set Application Type to Console Application and remember to tick the empty project. and click finish.

Then Right Click the project and add new Item a cpp source file called Connect.cpp.

// Standard includes
#include 
#include 
#include 
using namespace std;

// Connector Includes

#include "cppconnection/driver.h"
#include "cppconnection/exception.h"
#include "cppconnection/resultset.h"
#include "cppconnection/statement.h"


// Link to the Connector/C++ library
#pragma comment(lib, "mysqlcppconn.lib")

// Connection details
string server   = "localhost";
string username = "root";
string password = "123"; // 

int main(){
    sql::Driver     *driver; // MySQL Driver Object
    sql::Connection *connection; // MySQL connection Object
    sql::Statement  *statement;   // Statement which holds SQL commands
    sql::ResultSet  *resultSet;    // ResultSet to hold the results
    //Here is the connection
    try{
        driver = get_driver_instance();
        connection = driver->connect(server, username, password);
        statement = connection->createStatement();
        statement->execute("USE performance_schema");
        resultSet = statement->executeQuery("show tables");
        while (resultSet->next()){
            // Iterating the result set
            cout << resultSet->getString(1) << endl;                
        }
    }
    catch (sql::Exception e){
        cout << "Error message: " << e.what() << endl;
        system("pause");
        exit(1);
    }

    //Clear resources
    delete res;
    delete statement;
    delete connection;

    system("pause");
    return 0;
}

Copy the boost folder into the project folder. In this example the folder is Visual Studio 2008\Projects\MySQLConnection\MySQLConnection folder.

Then download this header and dll files and extract them into the same folder Visual Studio 2008\Projects\MySQLConnection\MySQLConnection folder.

Then the final step is right cilck on the Project and go to 

Properties --> Configuration Properties --> C/C++ --> General . 

On the right hand side set the "Additional Include Directories" value to ".\" value. (with out the quotes)

That's it build your project and run the project. And one more final thing when you run the project set the  "Solution Configuration" to Release mode. In the Debug Mode I had some error which I still don't no why.

So that's it now I think you got a clear idea about creating a connection for MySQL using C++ in Visual Studio 2008. See you around with another exciting post... :)