如何将二进制文件存入Oracle数据库中

先把文件读取到内存，再以二进制格式保持到数据库中的大字段中（clob或clob）。

写大对象。

Java code

public static void main(String[] args) {

// TODO Auto-generated method stub

Connection conn = null

Statement stat = null

ResultSet rs = null

OutputStream os = null

FileInputStream fis = null

int bs = 0

try {

Class.forName("oracle.jdbc.driver.OracleDriver")

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oraDB","bigfou","---")

conn.setAutoCommit(false)

stat = conn.createStatement()

stat.executeUpdate("insert into t_video(id,video) values(1,empty_blob())")

rs = stat.executeQuery("select video from t_video where id = 1")

rs.next()

oracle.sql.BLOB blo = (oracle.sql.BLOB)rs.getBlob(1)

os = blo.getBinaryOutputStream()

bs = blo.getBufferSize()

fis = new FileInputStream("D:\\Temp\\MPlayer-CVS-20040808-K&K\\mplayer.exe")

byte[] buf = new byte[bs]

int length = 0

while(true)

{

length = fis.read(buf)

if(length == -1) break

os.write(buf,0,length)

}

os.close()

os = null

fis.close()

fis = null

conn.commit()

conn.setAutoCommit(true)

conn.close()

} catch(Exception ex) {

ex.printStackTrace()

}

}

读大对象

Java code

InputStream is = null

FileOutputStream fos = null

byte[] buf = null

int bs = 0

try {

Class.forName("oracle.jdbc.driver.OracleDriver")

conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oraDB","bigfou","-")

conn.setAutoCommit(false)

stat = conn.createStatement()

rs = stat.executeQuery("select video from t_video where id = 1")

rs.next()

oracle.sql.BLOB blo = (oracle.sql.BLOB)rs.getBlob(1)

bs = blo.getBufferSize()

buf = new byte[bs]

int length = 0

is = blo.getBinaryStream()

fos = new FileOutputStream("d:\\test.exe")

while(true) {

length = is.read(buf)

if(length == -1) break

fos.write(buf,0,length)

}

fos.close()

fos = null

is.close()

is = null

conn.commit()

conn.setAutoCommit(true)

conn.close()

...

怎样在mysql中存储比较大的图片？

如果你想把二进制的数据,比如说图片文件和HTML文件,直接保存在你的MySQL数据库，那么这篇文章就是为你而写的！我将告诉你怎样通过HTML表单来储存这些文件，怎样访问和使用这些文件。

一、本文概述

本文的主要内容如下：

＊ 在MySQL中建立一个新的数据库

＊ 一个怎样储存文件的例子程序

＊ 一个怎样访问文件的例子程序

二、在MySQL中建立一个新的database

首先，你必须在你的MySQL中建立一个新的数据库，我们将会把那些二进制文件储存在这个数据库里。在例子中我会使用下列结构，为了建立数据库，你必须做下列步骤：

1. 进入MySQL控制器

2. 输入命令"create database binary_data"

3. 输入命令"use binary_data"

输入如下命令：

"CREATE TABLE binary_data ( id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,description CHAR(50), bin_data LONGBLOB, filename CHAR(50), filesize CHAR(50), filetype CHAR(50))" （不能断行）

如果没有意外，数据库 和 表 应该建立好了。

三、一个怎样储存文件的例子程序

用这个例子你可以通过Html表单将文件传输到数据库中。

store.php3

// store.php3 - by Florian Dittmer

?>

// 如果提交了表单，代码将被执行:

if ($submit) {

// 连接到数据库

// (你可能需要调整主机名，用户名和密码)

MYSQL_CONNECT( "localhost", "root", "password")

MySQL_select_db( "binary_data")

$data = addslashes(fread(fopen($form_data, "r"), filesize($form_data)))

$result=MYSQL_QUERY( "INSERT INTO binary_data (description,bin_data,filename,filesize,filetype)VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')")

$id= MySQL_insert_id()

print "This file has the following Database ID: $id"

MYSQL_CLOSE()

} else {

// 否则显示储存新数据的表单

?>

@MySQL_select_db( "binary_data")

$query = "select bin_data,filetype from binary_data where id=$id"

$result = @MYSQL_QUERY($query)

$data = @MYSQL_RESULT($result,0, "bin_data")

$type = @MYSQL_RESULT($result,0, "filetype")

Header( "Content-type: $type")

echo $data

}

?>

程序必须知道要访问那个文件， 你必须将ID作为一个参数。

例如: 一个文件在数据库中的ID为2. 你可以这样调用它: getdata.php3?id=2

如果你将图片储存在数据库里, 你可以向调用图片一样调用它。

Example: 一个图片文件在数据库中的ID为3. 你可以这样调用它:

五、怎样储存大于1MB的文件

如果你想储存大于1MB的文件，你必须对你的程序、PHP设置、SQL设置进行许多修改。

下面几条也许可以帮助你储存小于24MB的文件：

1) 修改 store.php3，将 MAX_FILE_SIZE 的值改成 24000000。

2) 修改你的PHP设置，在一般情况下，PHP只允许小于2MB的文件，你必须将max_filesize（在php.ini中）的值改成24000000

3) 去掉MYSQL的数据包大小限制,在一般情况下 MYSQL 小于1 MB的数据包。

4) 你必须用以下参数重启你的MYSQL ：/usr/local/bin/safe_MySQLd -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M

5) 如果仍然出错：可能是超时错误，如果你通过一个很慢的连接来储存一个很大的文件，PHP缺省的时间限制为30秒。你可以将max_execution_time(在php.ini中)的值改为-1

下面是一个老外写的，可以读

Saving Images in MySQL

Sometimes, it's more convenient to save images in a database than as files.

MySQL and PHP make it very easy to do this. In this article, I will describe

how to save images in a MySQL database and display them later on.

Setting up the database

The difference between any regular text or integer fields and a field that

needs to save an image is the amount of data that is needed to be held in the

field. MySQL uses special fields to hold large amounts of data. These fields

are known as blobs (blob).

Here is the BLOB definition from the MySQL site :

A BLOB is a binary large object that can hold a variable amount of data. The

four BLOB types TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB differ only in the

maximum length of the values they can hold

For more information about MySQL BLOBs check out

hapter/manual_Reference.html#BLOB

Use the next syntax to create a basic table that will hold the images:

CREATE TABLE Images (

PicNum int NOT NULL AUTO_INCREMENT PRIMARY KEY,

Image BLOB

)

Setting the upload script

An example of a file upload front end can be seen at File Uploading by berber

(29/06/99). What we need now is the PHP script that will get the file and

insert it into MySQL. The next script does just that. In the script, I'm

assuming that the name of the file field is "icture".

<?

If($Picture != "none") {

$PSize = filesize($Picture)

$mysqlPicture = addslashes(fread(fopen($Picture, "r"), $PSize))

unlink($Picture)

mysql_connect($host,$username,$password)

or die("Unable to connect to SQL server")

@mysql_select_db($db)

or die("Unable to select database")

mysql_query("INSERT INTO Images (Image) VALUES '($mysqlPicture')")

or die("Can't Perform Query")

}

else {

echo"You did not upload any picture"

}

?>

This is all that is needed to enter the image into the database. Note that in

some cases you might get an error when you try to insert the image into

MySQL. In such a case you should check the maximum packet size allowed by

your MySQL ver. It might be too small and you will see an error about this in

the MySQL error log.

What we did in the above file is :

1. Check if a file was uploaded with If($Picture != "none").

2. addslashes() to the picture stream to avoide errors in MySQL.

3. Delete the temporary file.

3. Connect to MySQL, choose the database and insert the image.

Displaying the Images

Now that we know how to get the images into the database we need to figure

out how to get them out and display them. This is more complicated than

getting them in but if you follow these steps you will have this up and

running in no time.

Since showing a picture requires a header to be sent, we seem to be in an

impossible situation in which we can only show one picture and than we can't

show anymore Since once the headers are sent we can't send any more headers.

This is the tricky part. To outsmart the system we use two files. The first

file is the HTML template that knows where we want to display the image(s).

It's a regular PHP file, which builds the HTML that contains the <IMG> tags,

as we want to display them. The second file is called to provide the actual

file stream from the database directly into the SRC property of the <IMG>

tag.

This is how a simple script of the first type should look like:

<HTML>

<BODY>

<?

mysql_connect($host,$username,$password)

or die("Unable to connect to SQL server")

@mysql_select_db($db)

or die("Unable to select database")

mysql_query("SELECT * FROM Images")

or die("Can't Perform Query")

While($row=mysql_fetch_object($result)) {

echo "<IMG SRC=\"SecondType.php3?PicNum=$row->icNum\">"

}

?>

</BODY>

</HTML>

While the HTML is being displayed, the SecondType.php3 file is called for

each image we want to display. The script is called with the Picture ID

(PicNum) which allows us to fetch the image and display it.

The SecondType.php3 file looks like this :

<?

$result=mysql_query("SELECT * FROM Images WHERE PicNum=$PicNum")

or die("Can't perform Query")

$row=mysql_fetch_object($result)

Header( "Content-type: image/gif")

echo $row->Image

?>

This is the whole theory behind images and MySQL. The scripts in this example

are the basics. You can now enhance these scripts to include thumbnails, set

the images in various positions, enhance the database table to hold an ALT

field, Check the width and height of the images before you insert them into

the database and keep that data in the table too etc...

用文件流的方式，把从文件中读出的数据转换成二进制，从数据库中读出就是反方向的：** void button1_Click(object sender, EventArgs e){byte[] bufferbuffer = File.ReadAllBytes(\"readme.doc\")//读取文件内容//创建连接SqlConnection connect = new SqlConnection(@\"Integrated Security=SSPIPersist Security Info=FalseInitial Catalog=BSPlatform2008Data Source=.\\SqlExpress\")SqlCommand cmd = connect.CreateCommand()cmd.CommandText = \"INSERT INTO Tmp (FileContent) VALUES (@FileContent)\"//FileContent字段是Image类型cmd.Parameters.Add(\"@FileContent\", SqlDbType.Image)cmd.Parameters[\"@FileContent\"].Value = buffer//接受byte[]类型的值connect.Open()cmd.ExecuteNonQuery()connect.Close()} 查看更多答案>>

---