Consultor Eletrônico

Status: Verified

GOAL:

How to insert a JPEG, GIF, or BMP image into a SQL table in a LVARBINARY field

FIX:

The following example includes these files:

- rxtxschema.sql
- rxblob.java
- txblob.java
- yahoo2.gif

The example uses the assumptions:

- A user named "me" exists with password "x" in the database. The files can be modified accordingly to use another username.

- A database named "Sports2000" has started on the port 3002.

- The file "rxtxschema.sql" has been used to create a table in the database.

Use following steps to test the sample code:

1) java txblob jdbc:jdbcprogress:T:localhost:3002:sports2000 me x yahoo2.gif

2) java rxblob

The code follows:


/* rxtxschema.sql */

DROP TABLE lonvarbin;
CREATE TABLE lonvarbin(fld1 INT PRIMARY KEY,fld2 LVARBINARY);
COMMIT WORK;


/* rxblob.java */

//Blob Reader

import java.sql.*;
import java.io.*;
import java.util.*;

public class rxblob
{
static DataInputStream kbd = new DataInputStream(System.in);
static String url = "jdbc:jdbcprogress:T:localhost:3002:sports2000";
static String driver = "com.progress.sql.jdbc.JdbcProgressDriver";
static String login = "me";
static String passwd = "x";
static String filename = "./yahoocopy.gif";
static String tablename = "";
static String blobcolumnname = "";
static String selectcolumnname = "";
static String selectcolumnvalue = "";
static Connection curConn = null;

public static void main(String argv[]) throws IOException
{
String temp = "";
rxblob session = new rxblob();
}

public rxblob() throws IOException
{
try
{
Class.forName(driver);
curConn = DriverManager.getConnection(url, login, passwd);
}
catch (java.lang.Exception ex)
{
ex.printStackTrace();
return;
}
processBlob();
finalize();
}

protected void finalize()
{
try
{
curConn.close();
}
catch (SQLException ex)
{
}
}

private void processBlob() throws IOException
{
try
{
File blobFile = new File(filename);
OutputStream fblob = new FileOutputStream(blobFile);
Statement myStatement = curConn.createStatement();
ResultSet rs = myStatement.executeQuery("SELECT * from lonvarbin");
// we retrieve in 4K chunks
byte[] buffer = new byte[5103];
InputStream strim = null;
BufferedInputStream s = null;
int size = 0;
try
{
// fetch blob
if (rs.next())
strim = rs.getBinaryStream(2);
}
catch (SQLException e)
{
e.printStackTrace();
}
/
ew Line!
if (strim != null)
{
s = new BufferedInputStream(strim);
while(size != -1)
{
size = s.read(buffer);
System.out.println(size);
if (size == 0)
break;
}
try
{
System.out.println(buffer.length);
System.out.println(buffer[0]);
System.out.println(buffer[1]);
System.out.println(buffer[2]);
System.out.println.(buffer[3]);
System.out.println(buffer[buffer.length/2]);
System.out.println(buffer[buffer.length/2 - 1]);
System.out.println(buffer[buffer.length - 1]);
fblob.write(buffer);
fblob.flush();
fblob.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
else
System.out.println("Row not found.");
}
catch (Exception ex)
{
ex.printStackTrace ();
}
}
}


/* txblob.java */

//blob inserter

import java.sql.*;
import java.io.*;
import java.util.*;

public class txblob
{
static DataInputStream kbd = new DataInputStream(System.in);
static String driver = "com.progress.sql.jdbc.JdbcProgressDriver";
static String tablename = "";
static String blobcolumnname = "";
static String selectcolumnname = "";
static String selectcolumnvalue = "";
static Connection curConn = null;
static String login = null;
static String passwd = null;
static String filename = null;
static String url = null;

public static void main(String args[]) throws IOException
{
if (args.length ");
System.exit(1);
}
url = args[0];
login = args[1];
passwd = args[2];
filename = args[3];
String temp = "";
txblob session = new txblob();
}

public txblob() throws IOException
{
try
{
Class.forName(driver);
curConn = DriverManager.getConnection(url, login, passwd);
}
catch (java.lang.Exception ex)
{
ex.printStackTrace();
return;
}
processBlob();
finalize();
}

protected void finalize()
{
try
{
curConn.close();
}
catch (SQLException ex)
{
}
}

private void processBlob() throws IOException
{
try
{
File blobFile = new File(filename);
int blobFileLen = (int) blobFile.length();
InputStream fblob = new FileInputStream(blobFile);
System.out.println(filename + " = " + blobFileLen);
PreparedStatement myStmt = curConn.prepareStatement("insert into lonvarbin values (?,?)");
myStmt.setInt(1, 2);
myStmt.setBinaryStream(2, fblob, blobFileLen);
int res = myStmt.executeUpdate();
if (res > 0)
System.out.println("executeUpdate count = " + res);
myStmt.close();
}
catch (Exception ex)
{
ex.printStackTrace ();
}
}
}


/* yahoo2.gif */

Please replace any gif file with 'yahoo2.gif'.
.