The Limit of Excel

Today, i want to share my experience when i was convert data from .dbf files to excel files. I've used free tools DBF Viewer, this freeware can convert data from .dbf ( clipper ) to .xls or .html files. So with that capabilities i can convert medical record data from old program that used clipper to .xls file. With excel files i can easily modify data, before exported to Ms SQL Server database.
The process is very easy and i didn't get any problems with the data. The data succesfully exported to SQL Server 2008 database. But after that, when i tested the program a few data was missing, not a few but a lot! When i checked the old program (clipper), i founded that a lot of medical record data was not imported to new database( Ms SQL Server 2008). After i check carefully, i found that .xls files have limitiation with the rows. The limit rows of .xls files if 65536 rows, so if you have data row more than 655536 rows, your data will be lost after you exported from .dbf of other data files.

If you want to convert .dbf data to SQL Server or bigger database server, the tips is use freet tools that can convert data to .sql script files, the sql script files has no limitation. So you can run the .sql files on Query Analyzer ( Ms SQL Server ), and you get the complete data from old database. Tools that i've used to convert data from .dbf ( clipper ) to .sql script is DBF Viewer 2000 from Hi Base Group, or you can download here.



the picture take from here

How to Connect Java to MySQL

After we learn how to connecting Java Program to Microsoft SQL Server, now we learn to connecting our java program to MySQL.

So we must prepare tools for that, and the tools is:

1. NetBeans IDE 7.0, you can download here.
2. MySQL 5.0 , and you can download here.
3. JDBC Driver for MySQL, you can download on this site. Add this driver to our Java Project library that we will make it.

First, we must create project Java on NetBeans IDE, for the example ConnectMySQL.
And then create packages by right click on Source Packages and choose New -> Java Packages and create name on that packages for the example same with the Project Name Connect MySQL.

After that, create one class by right click on our new packages , and choose New -> Java Class and create our New Java Class with MySQLConnection.java and type or copy paste this code on our new Java Class.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

class MySQLConnection{
Connection Connect;
public Connection ConnectDB(){
String url = "jdbc:mysql://localhost/test";
try{

Class.forName("com.mysql.jdbc.Driver");
Connect = DriverManager.getConnection(url, "root", "root");

}catch(ClassNotFoundException e){
System.out.println("Driver Load Failed!");
}catch(SQLException e){
System.out.println("Connection Failed!");
}
return Connect;
}


public static void main(String[] abc){
Connection Con = new MySQLConnection().ConnectDB();
if(Con != null){
System.out.println("Connection Succesfull!");
}else{
System.out.println("Connection Failed!");
}
}
}

If you have finished, then we must create New Java Form by right click on MySQLServer packages and then choose New -> JFrame Form. And create one button and name on it with "Test Connection MySQL". And then copy paste this code to our new button.

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
Connection con = new MySQLConnection().ConnectDB();
try{
if(con != null){
JOptionPane.showMessageDialog(null, "Connection Succesfull!");
}else{
JOptionPane.showMessageDialog(null, "Connection Failed!");
}
}catch(Exception e){

}
}

and do the same thing like our Java to SQL Server project.

How to Connect Java to SQL Server

This time, we learn how to connecting Our Java program to Microsoft SQL Server, i hope this article will help you and everyone to code on Java, have fun!

Tools :

1. NetBeans IDE 7.0 download here.
2. SQL Server 2008 Enterprise / SQL Server Express, you can download here.
3. Microsoft JDBC Driver for SQL Server, you can download here. And dont forget to add the library on our Java project.

First, we must create project Java on NetBeans IDE, for the example ConnectSQLServer.
And then create packages by right click on Source Packages and choose New -> Java Packages and create name on that packages for the example same with the Project Name Connect SQLServer.

After that, create one class by right click on our new packages , and choose New -> Java Class and create our New Java Class with SQLServerConnection.java and type or copy paste this code on our new Java Class.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

class SQLServerConnection{
Connection Connect;
public Connection ConnectDB(){
String url = "jdbc:sqlserver://localhost:1433;databaseName=tempdb;";
try{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connect = DriverManager.getConnection(url, "sa", "p455w0rd");

}catch(ClassNotFoundException e){
System.out.println("Driver Load Failed!");
}catch(SQLException e){
System.out.println("Connection Failed!");
}
return Connect;
}


public static void main(String[] abc){
Connection Con = new SQLServerConnection().ConnectDB();
if(Con != null){
System.out.println("Connection Succesfull!");
}else{
System.out.println("Connection Failed!");
}
}
}

If you have finished, then we must create New Java Form by right click on ConnectSQLServer packages and then choose New -> JFrame Form. And create one button and name on it with "Test Connection SQL Server". Let code the button with this code:

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
Connection con = new SQLServerConnection().ConnectDB();
try{
if(con != null){
JOptionPane.showMessageDialog(null, "Connection Succesfull!");
}else{
JOptionPane.showMessageDialog(null, "Connection Failed!");
}
}catch(Exception e){

}
}


And Now we can check the code and run the program on NetBeans by press F6, test it!