Program in Java to convert physical files in jpg format to Binary files in Oracle Database

Connection to Oracle database

import java.sql.*;
import java.sql.Connection;

public class Conn {
static String url = "jdbc:oracle:thin:@ipaddress:1521:dbname";

static String user = "username";

static String pass = "password";

public static Connection getConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, user, pass);
return conn;

} catch (Exception ms) {
ms.printStackTrace();
return null;
}
}

public static void main(String ss[]) {
Connection conn = Conn.getConnection();
}

}

Create binary files


import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

class MySignFilter implements FileFilter {
public boolean accept(File pathname) {
return (pathname.getName().toLowerCase().endsWith("jpg"));
}
}

public class Transfer {

Connection conn = Conn.getConnection();

public void ReadFile() {

try {
File chkerror = new File("C:/pic/errfile.txt");
chkerror.setWritable(true);
FileWriter writer = new FileWriter(chkerror);
PrintWriter printwriter = new PrintWriter(writer);
File file = new File("C:/pic");
MySignFilter mySign = new MySignFilter();
File list[] = file.listFiles(mySign);

for (int i = 0; i < list.length; i++) {

FileInputStream ins = new FileInputStream(list[i]);
byte b[] = new byte[ins.available()];
System.out.println(i+ ":"+ list[i].getName().trim().toLowerCase().replace(".jpg", ""));
if (b.length < 65536) {

PreparedStatement ps = conn
.prepareStatement("insert into binfiles "
+ " (fname,fvalue) "
+ " values (?,?)");
ps.setString(1, list[i].getName().trim().toLowerCase().replace(".jpg", ""));
ps.setBinaryStream(2, ins, (int) list[i].length());
ps.executeUpdate();
ps.close();
ins.close();
conn.commit();

} else {
printwriter.append(list[i].getName().trim() + "\n");
}

}
printwriter.flush();
printwriter.close();
writer.close();

} catch (Exception ms) {
ms.printStackTrace();
}
}

public static void main(String args[]) {
Transfer trn = new Transfer();
trn.ReadFile();
}

}

Program to create jpg files from the binary data stored in SqlServer

It is sometimes necessary to create the physical files from the binary data stored in the database. Given below is the code in Visual Basice that would create the files in jpg format accessing the binary data stored in SqlServer database. It is required to make ODBC Connection named "sqlserver" before the program can be executed.


Dim cAcc As New ADODB.Connection
Dim rAcc As New ADODB.Recordset

Private Sub Form_Load()
cAcc.Open "Provider=MSDASQL.1;Password=password;Persist Security Info=True;User ID=username;Data Source=sqlserver"
End Sub

Private Sub Command1_Click()
rAcc.Open "select mname,mblob from mFiles", cAcc '

Dim strStream As New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
Dim acctno As String
Do While Not rAcc.EOF
strStream.Write rAcc.Fields("mblob").Value
picname = rAcc.Fields("mname").Value
strStream.SaveToFile "C:\pic\" + picname + ".jpg", adSaveCreateOverWrite
rAcc.MoveNext
Loop

End Sub

Performance Tuning in Oracle Database

Tuning performance is a difficult task to perform. One should know the area on which the tuning is required before performing the real tuning. This column is added to know the database parameters to tune to meet the usage of the hardware requirement during the installation of oracle database.

Parameters affecting the real time transaction processing system in oracle database are as below:

For instance the server is of 1GB memory. The size of the parameters is specified in bytes.

sga_max_size=536870912
reset this parameter upto 50% of the total memrory (in bytes) -512m
shared_pool_size=107374182
reset this parameter upto 10% of the total memrory (in bytes) -102m
large_pool_size=53687091
reset this parameter upto 5% of the total memrory (in bytes) -51m
db_cache_size=107374182
reset this parameter upto 10% of the total memrory (in bytes) -102m
pga_aggregate_target=214748364
reset this parameter upto 20% of the total memrory (in bytes) -204m
sort_area_size=53687091
reset this parameter upto 5% of the total memrory (in bytes) -51m


Create the pfile from the current spfile and change or add the parameters neccessary parameters in the parameter file. Rename
the spfile to take the effect of the pfile in the next startup of the database or create spfile from the changed pfile.

These parameters helps in processing the DML statements during Online Transaction Processing System.