Blog Archives

Transferring from MYSQL to Mongo DB

I recently wanted to transfer from MYSQL to MONGODB so I developed some routines to make the transfer. It’s pretty self explanatory but allows you to dump tables and delete data as required.

package mongo;
import java.math.BigDecimal;
import com.mongodb.BasicDBObject;
import com.mongodb.BasicDBObjectBuilder;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import com.mongodb.MongoClient;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
 *
 * @author Paul Clevett
 * http://www.dailyforextrader.me
 */
public class Mongo {
/**
 * @param args the command line arguments
 */
 public static void main(String[] args) {
 try {
 // To directly connect to a single MongoDB server (note that this will not auto-discover the primary even
// if it's a member of a replica set:
Connection conn;
 try {
 // create a java mysql database connection
 String myDriver = "com.mysql.jdbc.Driver";
 String myUrl = "jdbc:mysql://***SERVERIPADDRESS****/**INSERTDATABASE***";
 Class.forName(myDriver);
 conn = DriverManager.getConnection(myUrl, "***INSERTUSERNAME***", "**INSERTPASSWORD***");
// your prepstatements goes here...
 } catch (Exception e) {
 System.err.println("Got an exception! ");
 System.err.println(e.getMessage());
 return;
 }
MongoClient mongoClient = new MongoClient("***MONGODBIPADDRESS**", 27017);
PrintDatabaseNames(mongoClient);
 DB db = mongoClient.getDB("**MONGODBDATABASE****");
 char[] password = "r00t21".toCharArray();
 boolean auth = db.authenticate("root", password);
deleteRecords("alarms",db);
 deleteRecords("sites",db);
 deleteRecords("users",db);
 deleteRecords("datakeys",db);
 
 
 
 dumpTable("datakeys",conn,db);
 dumpTable("alarms", conn, db);
 dumpTable("sites", conn, db);
 dumpTable("users", conn, db);
} catch (UnknownHostException ex) {
 System.out.println("Unkoown Host " + ex);
 }
 }
private static void deleteRecords(String sTableName,DB db) {
 BasicDBObject query = new BasicDBObject();
 DBCollection col1=db.getCollection(sTableName);
 col1.remove(query);
 }
private static void dumpTable(String TableName, Connection conn, DB db) {
 CreateCollection(TableName, db);
 DBCollection coll = db.getCollection(TableName);
 Statement s = null;
 ResultSet r = null;
 try {
 // get the iVolt units table
 s = conn.createStatement();
r = s.executeQuery("select * from " + TableName);
} catch (Exception e) {
}
 try {
 while (r.next()) {
 Map fields = new HashMap();
for (int i = 1; i < r.getMetaData().getColumnCount() + 1; ++i) {
 String sColumnName = r.getMetaData().getColumnName(i);
if (r.getObject(sColumnName) != null) {
if (r.getObject(sColumnName).getClass() == BigDecimal.class) {
 fields.put(sColumnName, new BigDecimal(r.getObject(sColumnName) + "").doubleValue());
 } else {
 fields.put(sColumnName, r.getObject(sColumnName));
 }
 }
}
InsertRecord(createRecord(r.getMetaData().getColumnName(1), r.getObject(1) + "", fields), coll);
 }
 } catch (Exception e) {
 System.out.println("Fail " + e);
 }
}
private static BasicDBObject createRecord(String tablename, String DBName, Map keyvalues) {
 BasicDBObject result = new BasicDBObject(tablename, DBName);
 Iterator<Map.Entry<String, String>> i = keyvalues.entrySet().iterator();
 while (i.hasNext()) {
 String key = i.next().getKey();
 result.append(key, keyvalues.get(key));
}
return result;
 }
private static void InsertRecord(BasicDBObject doc, DBCollection coll) {
coll.insert(doc);
 }
private static void PrintDatabaseNames(MongoClient mongoClient) {
List dbnames = mongoClient.getDatabaseNames();
 System.out.println("DatabaseNames");
 for (int i = 0; i < dbnames.size(); ++i) {
 System.out.println(dbnames.get(i) + "");
}
}
private static void PrintCollectionNames(DB db) {
 System.out.println("**** Collection Names ****");
 Set<String> colls = db.getCollectionNames();
for (String s : colls) {
 System.out.println(s);
 }
}
private static void CreateCollection(String CollectionName, DB db) {
DBCollection collection;
 if (db.collectionExists(CollectionName)) {
 collection = db.getCollection(CollectionName);
 } else {
 DBObject options = BasicDBObjectBuilder.start().add("capped", false).get();
 collection = db.createCollection(CollectionName, options);
 }
}
}

Tungsten Replicator setting up and Gotchas

Tungsten Replicator site Clicky

So we have a big MYSQL server with mission critical data on it.

I want to replicate it accross to another MYSQL database, they are both ubuntu servers with Percona MYSQL database.

1. Preparation

Master – Install RUBY, JAVA,

Slave – Install RUBY, JAVA

edit the hosts files on both machines in etc/hosts and add the other servers in each one and reference the IP address – just makes things easier.

2. SSH

Make sure you set up SSH so that you can ssh from one server to the other WITHOUT using passwords.

3. PORTS

make sure all ports required 10000,10001 are available (particularly problematical PLESK, WEBMIN a whole host of things like these ports) on all machines. One server actually had a PLESK tool on it even though plesk was not installed!!

4. MYSQL

make sure everything is set up and restore a backup of the database before you start.

5. Helper scripts

Set up your tungsten. You’ll need to edit NODES_MASTER_SLAVE.sh to make sure your nodes are correct and USER_CONFIG as per the tungsten documentation.

install.sh – put in the tungsten folder e.g. tungsten-replicator-2.1.0-343

VERBOSE=1 ./cookbook/install_master_slave ./cookbook/NODES_MASTER_SLAVE.sh

tungsten.sh (actually I renamed this to tungsten and did a chmod +x tungsten)

if [ "$1" == "help" ] || [ "$1" == "" ]; then
 echo "Commands"
 ls installs/cookbook/tungsten/tungsten-replicator/bin/
else
installs/cookbook/tungsten/tungsten-replicator/bin/$1 $2 $3 $4 $5
fi

So you can type

./tungsten replicator status

To run commands.

EJB downloading an excel file from JSP

The Calling Page

Let’s say you have a button on a JSP page that calls it. One of the problems is stopping the page opening as blank. So here’s the calling button

<form method="post" action="#">
 <input type="button" value="Download" onClick="return downloadButton();"/>
        </form>
        

The iFRame is used to trigger the excel call, and here’s the download button javascript.

function downloadButton() {
 
    document.getElementById("excel").src="exportxl.jsp?sql=select * from mytable";
    return false;
    
}

The Responding Page

Now the Calling JSP Page that goes off and gets the Excel Spreadsheet from the EJB, I have an EJB called server and the corresponding remote interface “server remote” note that these must be registered in the xml files in the web app so the web app can see the remote interface in the EJB

<%-- 
    Document   : exportxl
    Created on : Aug 1, 2011, 8:36:23 AM
    Author     : paulc
--%>
<%@ page import = "java.sql.*" %>
<%@ page import = "javax.naming.Context" %>
<%@ page import = "javax.naming.InitialContext; "%>
<%@ page import = "PHJ.*" %>
<%@page contentType="application/vnd.ms-excel" pageEncoding="UTF-8"%>
<%

    PHJ.ServerRemote jobManagerBean = null;
    try {
        Context c = new InitialContext();
        jobManagerBean = (PHJ.ServerRemote) c.lookup("java:comp/env/Server");
    } catch (Exception ne) {
        System.out.println("Could not find Job Manager Bean " + ne);
        out.println("Could not find Job Manager Bean");
        return;
    }

    response.setHeader("Content-disposition", "attachment; filename=excelexport.csv");

    String sql = "";
    if (request.getParameter("sql") != null) {
        sql = request.getParameter("sql");

        out.print(jobManagerBean.prepareCSVForExcel(sql));

    } else {
        out.println("Error, No SQL statement specified");

    }
    
    

%>

The EJB

And here’s the routine in the EJB.

 
    private Connection conn;
    private String dbName = "myJNDIRESOURCE"; // make sure you have JNDI resource setup 
    /* This routine gets a connection from the above DBName, however, if it can't find it it lists the available JNDI resourcees
     * to the console or log
     */ 
    private Connection getConnection() {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    return conn;
                }
            } catch (SQLException ex) {
                // Do nothing just carry on.
            }

        }
        InitialContext ic = null;

        Query q = null;

        try {
            // initialize JNDI lookup parameters
            ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup(dbName);
            conn = ds.getConnection();
        } catch (Exception e) {
            try {
                System.out.println("JNDI Resource " + dbName + " not found " + e);
                NamingEnumeration children = ic.list("");
                while (children.hasMore()) {
                    NameClassPair ncPair = (NameClassPair) children.next();
                    System.out.print(ncPair.getName() + " (type " + ncPair.getClassName() + ")");

                }
                System.out.println("**** CANNOT FIND DATABASE ************************");
                children = ic.list("jdbc");
                while (children.hasMore()) {
                    NameClassPair ncPair = (NameClassPair) children.next();
                    System.out.print(ncPair.getName() + " (type " + ncPair.getClassName() + ")");

                }
                System.out.println("****************************************");
            } catch (Exception f) {
                System.out.println("Error 57:" + f);
            }
            System.out.println("ERROR 52: " + e);
            return null;
        }
        return conn;

    }

    public String prepareCSVForExcel(String sSQL) {

        String sResult = "";
        try {
            conn = getConnection();
            Statement s = conn.createStatement();
            ResultSet r = s.executeQuery(sSQL);
            // feldnames
            for (int i = 1; i != r.getMetaData().getColumnCount(); ++i) {
                sResult = sResult + r.getMetaData().getColumnName(i) + ",";


            }
            sResult = sResult + "\n";

            while (r.next()) {
                for (int i = 1; i != r.getMetaData().getColumnCount(); ++i) {
                    try {
                    if (r.getObject(i) != null) {
                        sResult = sResult + r.getString(i) + ",";
                    } else {
                        sResult = sResult + ",";

                    }
                    } catch (Exception e) {
                        sResult = sResult + ","; // Happens when there is a conversion error to a tiemstamp
                    }


                }
                sResult=sResult+"\n";



            }




        } catch (Exception e) {
            return e + "";
        }
        return sResult;
    }

Android Global Variables.

Those of you familiar with android programming will be used to using intents to pass data from activity to activity but there is a much easier way of storing state information. Using a global variable class.

1. Create a class

public class GlobalVariables extends Application {

    public String sResult = "";
}

This extends the application class.

Then edit your AndroidManifest.xml file

   <application android:name="GlobalVariables" android:theme="@style/CustomTheme" android:label="@string/app_name"
                 android:icon="@drawable/icon"  >

When you want access to one of those juicy globals in your application just use:-

  GlobalVariables gb = ((GlobalVariables) getApplicationContext());
  Log.i("Test Variable is "+gb.sResult);

Simples!

 

Problems with Webservices in Netbeans and how to solve them

Ok, rather than give a tutorial on web services – because there are some around, I thought I would solve one of the mysterious problems that can occur when you are creating these services.

Netbeans is AMAZING at creating web services, the tools are incredibly rich.

But this error dogged me. I had one or two web services that worked and others that didn’t. To set up my service I had set up some inner classes

public static class dtaData {
private String Name;
private String date;
private String sid;
}

then created a web services that returned a LIST of those objects:-

    @WebMethod(operationName = "Data")
    public List Data() {
        List result=new ArrayList();
       for (int i=0;i<20;++i) {
        dtaData DataObject=new dtaData ();
       DataObject.sid=""+i;
       result.add(Result);
     }

        
        return result;
    }
  

And it falls over with this error when you test the webserver. HOWEVER The problem is caused because there is no web service DIRECTLY returning the dtaData class. The solution is to create a web service that returns null but has dtaData as the returning Object.

e.g.

    @WebMethod(operationName = "testData")
    public dtaData testData() {
           return null;

    }
   

These errors are given when the problem occurs:-

On the glassfish server

Caused by: javax.xml.ws.WebServiceException: javax.xml.bind.MarshalException
 - with linked exception:
[javax.xml.bind.JAXBException: class PHJ.Agenda$ICDB_Agenda_data nor any of its super class is known to this context.]
        at com.sun.xml.ws.message.jaxb.JAXBMessage.writePayloadTo(JAXBMessage.java:322)
        at com.sun.xml.ws.message.AbstractMessageImpl.writeTo(AbstractMessageImpl.java:142)
        at com.sun.xml.ws.encoding.StreamSOAPCodec.encode(StreamSOAPCodec.java:108)
        at com.sun.xml.ws.encoding.SOAPBindingCodec.encode(SOAPBindingCodec.java:258)
        at com.sun.xml.ws.transport.http.HttpAdapter.encodePacket(HttpAdapter.java:320)
        at com.sun.xml.ws.transport.http.HttpAdapter.access$100(HttpAdapter.java:93)
        at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:454)

On the web service test return:


Exceptions details : java.lang.reflect.InvocationTargetException


javax.servlet.ServletException: java.lang.reflect.InvocationTargetException at com.sun.enterprise.webservice.monitoring.WebServiceTesterServlet.doPost(WebServiceTesterServlet.java:345) at com.sun.enterprise.webservice.monitoring.WebServiceTesterServlet.invoke(WebServiceTesterServlet.java:121) at com.sun.enterprise.webservice.JAXWSServlet.doPost(JAXWSServlet.java:165) at javax.servlet.http.HttpServlet.service(HttpServlet.java:738) at javax.servlet.http.HttpServlet.service(HttpServlet.java:831) at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:411) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:317) at org.apache.catalina.core.Ap

Configuring a Glassfish/Sunapp CLUSTER Server on Windows

Ok, I know, in an ideal world we’d do this on Solaris but I have customers with a pile of windows boxes so we have to work with what they have.

Server 1 – The “Controller” (machine 1, 192.168.100.10)

1. Install the SUN Application server make sure you select

– Do not require username/password
– Create a windows service (otherwise it wont stay running)
– Do not start the server from the INSTALLATION PROGRAM, use control panel, administration tools, services and start the service from there.

2. Login to the GUI http://localhost:4848

3. Select ADD CLUSTER SUPPORT – you will have to restart the server. Do NOT do this through the windows instead restart the service – Start, Control Panel, Admin Tools, Services, find SUN APPLICATION SERVER and RESTART

4. Login to the GUI http://localhost:4848 – Click on CREATE NEW CLUSTER

Enter the name and click OK now the cluster has been created (in this case i’ll use cluster1)

5. Create the first NODE. (this has to be done at the dos prompt)

Start, RUN, CMD, OK

cd \Sun\appserver\bin<ENTER>

asadmin<ENTER>

You will get a asadmin> prompt.

on each machine in the cluster you have to create and start a node agent

create-node-agent –host localhost –port 4848 nodeagent1

create an instance for this node agent

create-instance –host localhost –port 4848 –nodeagent nodeagent1 –cluster cluster1 instance1

now start the node agent

start-node-agent –syncinstances=true nodeagent1
(just hit enter on the master password)

at this point you have created and set up the first cluster machine which will control the cluster – so all your cluster admin will be done from this machine.

Exit asadmin

exit

then exit the command box

exit

Setting up Cluster Server clients (machine 2, 192.168.100.11)

1. Install the SUN Application server make sure you select

– Do not require username/password
– Create a windows service (otherwise it wont stay running)
– Do not start the server from the INSTALLATION PROGRAM, use control panel, administration tools, services and start the service from there.

2. Login to the GUI http://localhost:4848

3. Select ADD CLUSTER SUPPORT – you will have to restart the server. Do NOT do this through the windows instead restart the service – Start, Control Panel, Admin Tools, Services, find SUN APPLICATION SERVER and RESTART

4. Create a node agent, start, run cmd ok

Start, RUN, CMD, OK

cd \Sun\appserver\bin<ENTER>

asadmin<ENTER>

You will get a asadmin> prompt.

on each machine in the cluster you have to create and start a node agent

create-node-agent –host 192.168.100.10 –port 4848 nodeagent2

create an instance for this node agent

create-instance –host 192.168.100.10 –port 4848 –nodeagent nodeagent2 –cluster cluster1 instance2

now start the node agent

start-node-agent –syncinstances=true nodeagent2
(just hit enter on the master password)

at this point you have created and set up a cluster “client”

Exit asadmin

exit

then exit the command box

exit

Now you have your two cluster machines working.

Deploying a Netbeans Project to the Cluster

1. Make sure it’s running locally
2. Change the database settings in netbeans to point to the LIVE database if it is different and make sure it runs still
3. Deploy the app to the main server in the cluster
4. Login to the GUI and go through the JDBC connectors and enable them for the cluster
5. Test the application in SINGLE USER mode (normally port 8080) and make sure it all works.
6. Go into APPLICATIONS, MANAGE TARGETS for your app and deploy it to the cluster

I will be putting up a tutorial on load balancing as well shortly.

I think I’m in love….and not just with my wife

We’ve got a new member of our programming team, Uthay, I can’t even say or spell his full name, but anyway, he came along and showed me this tool called SQLyog.

Although we only had the community edition after some coaxing we managed to get the boss to shell out for the full version, enterprise edition.

This is absolutely superb, it is WELL WORTH upgrading the full enterprise version has some amazing tools that make database development MUCH easier. Especially when like us we have to modify and change databases then roll out the changes to another server. For example when we have 5 or 6 versions of our application out there and want to roll out the new application, that’s easy enough, but remembering the differences between the two versions is a bit of a pain, with this tool we can just syncronize them and it will modify the destination tables (and data if we want) to match the source.

I particularly like the Query development tool, one of the good things about good old Microsoft Access was how easy it was to build complex queries, in this software it is just as easy, particularly useful when building large scale data structures.

It’s such a shame it doesn’t support anything else apart from MYSQL. However I will be transmographying a database from a SQL server over to MYSQL JUST TO USE IT!

http://www.webyog.com/en/

There is a community version but the full version is only like £50 so it’s well worth purchasing.

Speeding up glassfish on Windows

Okay okay, WHY? would one want to run Glassfish on Windows????

Well sometimes we have to, I do a lot of building applications that need to run on existing servers because the customer doesn’t want to buy a new server. However given the choice I would always run Glassfish on Solaris on a Sun box (one of the small ones is fine).

So being tied down to a Windows server there are two things I do to make the server run faster that are basic but useful to know.

1. Control Panel, System, Performance, Give Priority to Background Applications

2. Make sure the Glassfish server is set up as a SERVICE (otherwise if you are remote desktoping in to start the server when you quit the session your server will stop).

3. Set the application server to PRECOMPILE JSPs, check out the Application Server tab in the Glassfish admin console.

That should put the wind up your server quite nicely.

Sun application server crashes / Glassfish server crashes

Ok this is quite obscure so I’m sure that it will help someone.

We had a couple of Sun Application servers that we would start by logging into terminal services and starting the server manually (it was just the way they were set up).

Upon logging off of terminal services, the glassfish server would crash (obviously if you just close the terminal server session you are still running it and that was fine).

Solution:

Uninstall the glassfish/sunapp server and reinstall, make sure you check the box that says “Run as windows service.” and then the system won’t have a problem, however DO NOT use the start menu to stop and start the app server, use the administrative tools/services programme.

 

CVS with netbeans and Tomorrow…

I decided it was time to start using CVS with Netbeans to manage our projects, since I had an old LINUX box set up I went ahead and installed a CVS server (the box is running UBUNTU 7.10) the setup on the server was very simple and Netbeans just connected to it and came ALIVE with code management.

Just brilliant, even if you set it up your local machine its worth it. The menu additions in Netbeans mean its VERY easy to check in and out and compare code. Good use of a spare PC.

Good Job! (ofcourse you could use Mecurial or the other one that escapes me).

Tommorrow I am giving a short seminar on Netbeans and Glassfish at work in the evening so that should be fun.