小弟前段试着利用Bytea类型存取大对象,但是程序编好后测试发现虽然一个butea的属性可以存储高达1GB的二进制数据,但是它需要占用大量内存来完成这个大数据,实际中远达不到1G,上传一个大于13M的文件就会出现out of memory 或者connection is broken的异常,相当郁闷。当然也有可能是我程序的原因。
现在正在初试用oid这个类型来存取,修改程序后运行,却老出现invalid large-object descriptor: 0的异常,相当的郁闷闷!希望懂这方面的好心高手帮帮小弟,先谢谢您的大恩啦!QQ一直在线:254380172建库:
CREATE DATABASE mydb
WITH OWNER = postgres
ENCODING = 'UTF8';建表:
CREATE TABLE file2
(
"name" text,
"content" oid
)
WITH (OIDS=FALSE);
ALTER TABLE file2 OWNER TO postgres;程序代码:
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
fullfilename = ofd.FileName.ToString();
filename = Path.GetFileName(fullfilename);
NpgsqlConnection conn = new NpgsqlConnection("server=127.0.0.1;database=mydb;uid=postgres;pwd=1234");
conn.Open();
NpgsqlTransaction transaction = conn.BeginTransaction();
LargeObjectManager lom = new LargeObjectManager(conn);
conn.Open();
int noid = lom.Create(LargeObjectManager.READWRITE);
LargeObject lo = lom.Open(noid, LargeObjectManager.WRITE);
FileStream fs = File.OpenRead(fullfilename);
byte[] buf = new byte[fs.Length];
fs.Read(buf, 0, (int)fs.Length);
MessageBox.Show(conn.State.ToString());
lo.Write(buf);//错误提示出:ERROR: 42704: invalid large-object descriptor: 0
fs.Close();
lo.Close();
NpgsqlCommand command = new NpgsqlCommand("insert into file2(name, content) values(@name, @content)", conn);
command.Parameters.Add(new NpgsqlParameter("@name", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new NpgsqlParameter("@content", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters["@name"].Value = filename;
command.Parameters["@content"].Value = noid;
command.ExecuteNonQuery();
transaction.Commit();
conn.Close();
MessageBox.Show("上传文件成功!");
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
if (sfd.ShowDialog() == DialogResult.OK)
fullfilename = sfd.FileName.ToString();
filename = Path.GetFileName(fullfilename);
NpgsqlConnection conn = new NpgsqlConnection("server=127.0.0.1;database=mydb;uid=postgres;pwd=1234");
conn.Open();
NpgsqlTransaction transaction = conn.BeginTransaction();
LargeObjectManager lom = new LargeObjectManager(conn);
conn.Open();
//NpgsqlDataAdapter sda = new NpgsqlDataAdapter("select * from file2 where name = '" + filename + "'", conn);
//DataSet ds = new DataSet();
//sda.Fill(ds, "file2");
//int noid = (int)ds.Tables["file2"].Rows[0][1];
NpgsqlCommand cmd = new NpgsqlCommand("select content from file2 where name = '" + filename + "'", conn);
NpgsqlDataReader sdr = cmd.ExecuteReader();
int noid = sdr.GetInt32(1);
LargeObject lo = lom.Open(noid, LargeObjectManager.READ);
FileStream fs = File.OpenWrite(fullfilename);
byte[] buf = new byte[lo.Size()];
buf = lo.Read(lo.Size());
fs.Write(buf, 0, (int)lo.Size());
fs.Flush();
fs.Close();
lo.Close();
transaction.Commit();
DeleteLargeObject(noid);
//MemoryStream ms = new MemoryStream(buf);
//pictureBox1.Image = Image.FromStream(ms);
conn.Close();
MessageBox.Show("上传文件成功!");
}
public static void DeleteLargeObject(Int32 noid)
{
NpgsqlConnection conn = new NpgsqlConnection("server=127.0.0.1;database=mydb;uid=postgres;pwd=1234");
conn.Open();
NpgsqlTransaction t = conn.BeginTransaction();
LargeObjectManager lom = new LargeObjectManager(conn);
lom.Delete(noid);
t.Commit();
conn.Close(); }
现在正在初试用oid这个类型来存取,修改程序后运行,却老出现invalid large-object descriptor: 0的异常,相当的郁闷闷!希望懂这方面的好心高手帮帮小弟,先谢谢您的大恩啦!QQ一直在线:254380172建库:
CREATE DATABASE mydb
WITH OWNER = postgres
ENCODING = 'UTF8';建表:
CREATE TABLE file2
(
"name" text,
"content" oid
)
WITH (OIDS=FALSE);
ALTER TABLE file2 OWNER TO postgres;程序代码:
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
fullfilename = ofd.FileName.ToString();
filename = Path.GetFileName(fullfilename);
NpgsqlConnection conn = new NpgsqlConnection("server=127.0.0.1;database=mydb;uid=postgres;pwd=1234");
conn.Open();
NpgsqlTransaction transaction = conn.BeginTransaction();
LargeObjectManager lom = new LargeObjectManager(conn);
conn.Open();
int noid = lom.Create(LargeObjectManager.READWRITE);
LargeObject lo = lom.Open(noid, LargeObjectManager.WRITE);
FileStream fs = File.OpenRead(fullfilename);
byte[] buf = new byte[fs.Length];
fs.Read(buf, 0, (int)fs.Length);
MessageBox.Show(conn.State.ToString());
lo.Write(buf);//错误提示出:ERROR: 42704: invalid large-object descriptor: 0
fs.Close();
lo.Close();
NpgsqlCommand command = new NpgsqlCommand("insert into file2(name, content) values(@name, @content)", conn);
command.Parameters.Add(new NpgsqlParameter("@name", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new NpgsqlParameter("@content", NpgsqlTypes.NpgsqlDbType.Integer));
command.Parameters["@name"].Value = filename;
command.Parameters["@content"].Value = noid;
command.ExecuteNonQuery();
transaction.Commit();
conn.Close();
MessageBox.Show("上传文件成功!");
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
if (sfd.ShowDialog() == DialogResult.OK)
fullfilename = sfd.FileName.ToString();
filename = Path.GetFileName(fullfilename);
NpgsqlConnection conn = new NpgsqlConnection("server=127.0.0.1;database=mydb;uid=postgres;pwd=1234");
conn.Open();
NpgsqlTransaction transaction = conn.BeginTransaction();
LargeObjectManager lom = new LargeObjectManager(conn);
conn.Open();
//NpgsqlDataAdapter sda = new NpgsqlDataAdapter("select * from file2 where name = '" + filename + "'", conn);
//DataSet ds = new DataSet();
//sda.Fill(ds, "file2");
//int noid = (int)ds.Tables["file2"].Rows[0][1];
NpgsqlCommand cmd = new NpgsqlCommand("select content from file2 where name = '" + filename + "'", conn);
NpgsqlDataReader sdr = cmd.ExecuteReader();
int noid = sdr.GetInt32(1);
LargeObject lo = lom.Open(noid, LargeObjectManager.READ);
FileStream fs = File.OpenWrite(fullfilename);
byte[] buf = new byte[lo.Size()];
buf = lo.Read(lo.Size());
fs.Write(buf, 0, (int)lo.Size());
fs.Flush();
fs.Close();
lo.Close();
transaction.Commit();
DeleteLargeObject(noid);
//MemoryStream ms = new MemoryStream(buf);
//pictureBox1.Image = Image.FromStream(ms);
conn.Close();
MessageBox.Show("上传文件成功!");
}
public static void DeleteLargeObject(Int32 noid)
{
NpgsqlConnection conn = new NpgsqlConnection("server=127.0.0.1;database=mydb;uid=postgres;pwd=1234");
conn.Open();
NpgsqlTransaction t = conn.BeginTransaction();
LargeObjectManager lom = new LargeObjectManager(conn);
lom.Delete(noid);
t.Commit();
conn.Close(); }
失望
语句后面,做个检测是成功
想再问一下,如果就我这段用OID的程序而言,能帮我运行一下并分析错误的原因吗?如果成功,分数双手奉上!不胜感激
http://bbs.pgsqldb.com/index.php?t=msg&th=238&start=0&rid=&S=05c2b41c3b804986eeb3d108434c299ehttp://blog.csdn.net/zst126/category/353187.aspx
用ado方式连接数据库的话,你可以先试验下2M或更小的,看是否能上传成功。如果成功,再考虑大的,如果不成功,应该是你代码有问题吧。
谢谢WangZi,你的建议非常好!多谢帮助!还有灵犀一点的热心帮助!
conn.setAutoCommit(false);// 获取大对象管理器以便进行操作
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();//创建一个新的大对象
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);//打开一个大对象进行写
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);// 现在打开文件
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);// 从文件拷贝数据到大对象
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0)
{
obj.write(buf, 0, s);
tl += s;
}// 关闭大对象
obj.close();//现在向 imgesLO 插入行
PreparedStatement ps = conn.prepareStatement("INSERT INTO imagesLO VALUES (?,?)");
ps.setString(1, file.getName());
ps.setInt(2, oid);
ps.executeUpdate();
ps.close();
fis.close();从大对象中检索图像∶ // 所有 LargeObject API 调用都必须在一个事务里
conn.setAutoCommit(false);// 获取大对象管理器以便进行操作
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();PreparedStatement ps = con.prepareStatement("SELECT imgOID FROM imagesLO WHERE imgname=?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
while(rs.next()) {
//打开大对象读
int oid = rs.getInt(1);
LargeObject obj = lobj.open(oid, LargeObjectManager.READ); //读取数据
byte buf[] = new byte[obj.size()];
obj.read(buf, 0, obj.size());
//在这里对读取的数据做些处理 // 关闭对象
obj.close();
}
rs.close();
}
ps.close();