Inserting Multiple Records into MaxDB Database

[Former Member]

Inserting Multiple Records into MaxDB Database

Inserting multiple records into a database is a quite a bottle neck in most cases. But no one prefers manually entering a thousand records one by one. This is a quite a useful functionality that every ones prefers. I found a couple of threads in the MaxDB forum on the same topic and worked out a simple solution for them
How to insert multiple rows
😞 Mass Data Upload to table in MaxDB

Some time back I posted a weblog on Batch Mode Operation in MaxDB using MaxDB Loader and Java which uses the logic for performing multiple query execution at one shot. But inserting multiple records is a little different as we need a little preprocessing to the raw data before it can be uploaded into the database. A simple technique I followed and did really solve my problem is given below. Then I developed a simple java application to download the MaxDB data to XML format. This weblog discusses the same considering a simple table with a few records for test purpose.

Table Definition

I considered a simple table for the test which had the below definition.



Table Contents

The number of records it contained was only one i.e. for the ROOT user.



Records to insert

Now the task is to upload a set of records shown below into the database.



The JDBC Program

I used a JDBC program for performing the multiple records insert into MaxDB. The program is given below.

/** * Project      : InsertMultipleRecords * Date         : Aug 6, 2005 * Programmed By: Kathirvel Balakrishnan * Company      : Wipro Technologies * Designation  : Project Engineer */package com.MaxDB;import java.io.BufferedReader;import java.io.FileReader;import java.io.IOException;import java.sql.DriverManager;import java.sql.Connection;import java.sql.Statement;/** * InsertMultipleRecordsFromFile */public class InsertMultipleRecordsFromFile { static int queryCount = 0; static String[] myQueries = new String[1000]; /**  * The Records that are present in the file are read into an Array for processing  */ public void ReadRecordsIntoArray() throws IOException {  try {   BufferedReader myReader =    new BufferedReader(new FileReader("d:/multi.txt"));   String curRecord = null;   while ((curRecord = myReader.readLine()) != null) {    String[] splitFields = null;    // Splitting based on the delimeter    splitFields = curRecord.split("\,");    // Creating the INSERT SQL Query    String TempQuery =     "INSERT INTO ALLUSERS VALUES('"      + splitFields[0]      + "','"      + splitFields[1]      + "','"      + splitFields[2]      + "','"      + splitFields[3]      + "','"      + splitFields[4]      + "','"      + splitFields[5]      + "')";    myQueries[queryCount] = TempQuery;    queryCount = queryCount + 1;   }   System.out.println("The Data Records have been successfully read!");  } catch (IOException IOE) {   System.out.println(IOE.toString());  } } /**  * Loading the driver for the MaxDB Database server  */ public void loadDriver() {  try {   Class.forName("com.sap.dbtech.jdbc.DriverSapDB");   System.out.println("The Driver has been loaded successfully!");  } catch (Exception E1) {   System.out.println("Unable to load the Driver!");   System.out.println("Exceptions:" + E1.toString());   System.exit(1);  } } /**  * Now all the records that have been read are being inserted into MaxDB  */ public void InsertData() {  String serverName = "LOCALHOST";  String dataBase = "TEST";  String userName = "DBADMIN";  String password = "maxdb";  Connection myConnection;  Statement myStatement;  System.out.println("Establishing connection to Server");  try {   //Establishing the connection with the database   myConnection =    DriverManager.getConnection(     "jdbc:sapdb://" + serverName + "/" + dataBase,     userName,     password);   myStatement = myConnection.createStatement();   System.out.println("Connection to Server was Established");   // Executing the SQL Query   for (int i = 0; i < queryCount; i++) {    myStatement.executeUpdate(myQueries[i]);    System.out.println("Current Query is:"+myQueries[i]);   }   System.out.println("Data has successfully inserted into MaxDB<br /> ");   // Closing   myStatement.close();   myConnection.close();  } catch (Exception E2) {   // Prints if any exception is raised   System.out.println(" Exceptions : " + E2.toString());  }  } public static void main(String[] args) throws IOException {  // Creating a new instance   InsertMultipleRecordsFromFile InsertRecords =   new InsertMultipleRecordsFromFile();  // Loading all records into an array   InsertRecords.ReadRecordsIntoArray();  // Loading the MaxDB JDBC Driver  InsertRecords.loadDriver();  // Now inserting all the records into MaxDB  InsertRecords.InsertData(); }}

The Result of the Program

The program did work perfectly and the test run results were displayed.



Verification

Did it really insert the data into MaxDB?



Yes, it did and a simple way a few lines of code to do the trick.

The assumptions I used for this are,
1. Comma "," was used as the delimiter
2. No empty records or empty lines after the last record.

Hope this was a useful tip.