Thread: Please help with binary file
Hi ,
I am trying to upload binary file to the PostgreSQL 7.3.2 with JDBC3 driver
supplied by postgresql (pg73jdbc3). The table to which I am trying to save
the file has a field of OID datatype. And while doing so I get
"java.sql.SQLException: ERROR: oidin.............." error.
I tried changing OID data type to bytea. After that code worked and I tried
file sizes from 4KB to1.6 MB. If file size is larger than that ( even for a
10 MB size file ) execution just gets stuck. No error, no exception thrown.
My code goes like this...
----------------------------------------------------
//obtained an inputstream handle
InputStream fis=uni.getIstream();
//get size of avialble bytes for read
int c=fis.available();
//unit id is mumeric, content ios of type 'bytea' (postgresql 7.3)
String query = "INSERT INTO elunit (unitid, content) "+
"VALUES (nextval('elms_seq_unit'),?) ";
PreparedStatement ps = con.prepareStatement(query);
ps.setBinaryStream(1, fis, c);
ps.executeUpdate();
--------------------------------------------------------------
the execution just gets stuck at the following line..It does not throw an
exception also......
ps.setBinaryStream(1, fis, c);
I have even tried with the ps.setBytes(byte[]);
Result is just the same....
Please let meknow if I have missed out something or something is wrong with
the code.
Thanks,
Akash
On 02/07/2003 06:38 Akash Shah wrote: > Hi , > > I am trying to upload binary file to the PostgreSQL 7.3.2 with JDBC3 > driver > supplied by postgresql (pg73jdbc3). The table to which I am trying to > save > the file has a field of OID datatype. And while doing so I get > > "java.sql.SQLException: ERROR: oidin.............." error. > > I tried changing OID data type to bytea. After that code worked and I > tried > file sizes from 4KB to1.6 MB. If file size is larger than that ( even for > a > 10 MB size file ) execution just gets stuck. No error, no exception > thrown. Reaed the docs (5.6 Storing Binary Data). They give sample code of how to use Large Objects with JDBC. I think this will do what you want. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Paul Thomas wrote: > > On 02/07/2003 06:38 Akash Shah wrote: > >> Hi , >> >> I am trying to upload binary file to the PostgreSQL 7.3.2 with JDBC3 >> driver >> supplied by postgresql (pg73jdbc3). The table to which I am trying to >> save >> the file has a field of OID datatype. And while doing so I get >> >> "java.sql.SQLException: ERROR: oidin.............." error. >> >> I tried changing OID data type to bytea. After that code worked and I >> tried >> file sizes from 4KB to1.6 MB. If file size is larger than that ( even for >> a >> 10 MB size file ) execution just gets stuck. No error, no exception >> thrown. > > > Reaed the docs (5.6 Storing Binary Data). They give sample code of how > to use Large Objects with JDBC. I think this will do what you want. > And don't use bytea until 7.4, when the new V3 protocol is on unless you have real security issues with other people being able to read your LOB (PostgreSQL LO facility is not secure while bytea is). You would need a patch to use bytea with 7.3 but it is not an ideal solution so you should avoid it (it has not been incorporated to the sources so you would have to get into the business of building the driver yourself). -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
On Wed, 2 Jul 2003, Fernando Nasser wrote: > Paul Thomas wrote: > > > > On 02/07/2003 06:38 Akash Shah wrote: > > > >> Hi , > >> > >> I am trying to upload binary file to the PostgreSQL 7.3.2 with JDBC3 > >> driver > >> supplied by postgresql (pg73jdbc3). The table to which I am trying to > >> save > >> the file has a field of OID datatype. And while doing so I get > >> > >> "java.sql.SQLException: ERROR: oidin.............." error. > >> > >> I tried changing OID data type to bytea. After that code worked and I > >> tried > >> file sizes from 4KB to1.6 MB. If file size is larger than that ( even for > >> a > >> 10 MB size file ) execution just gets stuck. No error, no exception > >> thrown. > > > > > > Reaed the docs (5.6 Storing Binary Data). They give sample code of how > > to use Large Objects with JDBC. I think this will do what you want. > > > > And don't use bytea until 7.4, when the new V3 protocol is on unless you > have real security issues with other people being able to read your LOB > (PostgreSQL LO facility is not secure while bytea is). In an attempt to clarify, are you really trying to say that the performance of bytea is poor without the V3 protocol, so it is better to use large objects unless security is a requirement because other database users can potentially view your large objects? > > You would need a patch to use bytea with 7.3 but it is not an ideal > solution so you should avoid it (it has not been incorporated to the > sources so you would have to get into the business of building the > driver yourself). What patch is needed for bytea and 7.3? Kris Jurka
Kris Jurka wrote: > > In an attempt to clarify, are you really trying to say that the > performance of bytea is poor without the V3 protocol, so it is better to > use large objects unless security is a requirement because other database > users can potentially view your large objects? > Yes. > >>You would need a patch to use bytea with 7.3 but it is not an ideal >>solution so you should avoid it (it has not been incorporated to the >>sources so you would have to get into the business of building the >>driver yourself). > > > What patch is needed for bytea and 7.3? > I believe I've posted it (I can repost if necessary). -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
On Wed, 2 Jul 2003, Fernando Nasser wrote: > Kris Jurka wrote: > > > > In an attempt to clarify, are you really trying to say that the > > performance of bytea is poor without the V3 protocol, so it is better to > > use large objects unless security is a requirement because other database > > users can potentially view your large objects? > > > > Yes. > > > > > >>You would need a patch to use bytea with 7.3 but it is not an ideal > >>solution so you should avoid it (it has not been incorporated to the > >>sources so you would have to get into the business of building the > >>driver yourself). > > > > > > What patch is needed for bytea and 7.3? > > > > I believe I've posted it (I can repost if necessary). > The only thing relevent I can see is http://archives.postgresql.org/pgsql-jdbc/2003-06/msg00027.php which concerns a server bug which doesn't manifest itself if you have an ascii or unicode database. I thought you were refering to a JDBC driver patch. Kris Jurka
Oliver Jowett wrote: It is this one: http://archives.postgresql.org/pgsql-jdbc/2003-05/msg00127.php Patch summary: use a temporary LO to stream bytea values to the server without needing to transform them to a potentially large intermediate string representation (I think). Correct. You need almost no extra memory, like when dealing with the LO types. But you will have to VACUUM your pg_largeobject table periodically, which may be inconvenient. What impact does this patch have on latency? We do many small transactions each dealing with small bytea fields, so if this patch ends up doing additional round-trips per insert, it's going to hurt. You are absolutely right. Barry has suggested that I change it and only do it if the data is above a certain length. We set it at 16K. But I did not implement it because we decided not to add it to the community driver as the 7.4 backend will provide us with a mechanism for handling these cases (without a staging area). From a quick look at your patch, though, it appears to only touch setBinaryStream()? From memory, we use setBytes(), so this may not be an issue for us (although we'd like to keep the option of using setBinaryStream()). Yes, the idea was use setBinaryStream() if you want to use this method (we cannot do much for setBytes() anyway as we need the stream). Of course, Barry's suggestion makes it much better as it allows one to use setBinaryStream() for small data as well. If there is a lot of interest in this fix for 7.3 and 7.2 backends, and you can convince Barry to incorporate it (with his proposed changes) in the driver, I can ask for time to get it in shape for submission. But I won't be able to do it unless it goes into the main line of code -- I already have to work on the 7.4 version (V3 protocol). Best regards, Fernando -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
On Thu, Jul 03, 2003 at 09:50:53AM -0400, Fernando Nasser wrote: > >>From a quick look at your patch, though, it appears to only touch > >setBinaryStream()? From memory, we use setBytes(), so this may not be an > >issue for us (although we'd like to keep the option of using > >setBinaryStream()). > > > > Yes, the idea was use setBinaryStream() if you want to use this method > (we cannot do much for setBytes() anyway as we need the stream). Of > course, Barry's suggestion makes it much better as it allows one to use > setBinaryStream() for small data as well. Isn't streaming a win for setBytes() with large bytearrays? If I have an existing 1mb byte array to insert, the current driver will need 2-3mb of temporary string space for setBytes(), I think? I suppose the app can always wrap it in a ByteArrayInputStream and use setBinaryStream(), though. > If there is a lot of interest in this fix for 7.3 and 7.2 backends, and > you can convince Barry to incorporate it (with his proposed changes) in > the driver, I can ask for time to get it in shape for submission. But I > won't be able to do it unless it goes into the main line of code -- I > already have to work on the 7.4 version (V3 protocol). We don't benefit from the patch as we don't deal with large bytea fields, I just wanted to make sure our insert performance didn't suffer if the patch did get applied. It sounds like that won't be a problem, thanks for clarifying things. -O
Oliver Jowett wrote:> On Thu, Jul 03, 2003 at 09:50:53AM -0400, Fernando Nasser wrote: > > >>>From a quick look at your patch, though, it appears to only touch >>>setBinaryStream()? From memory, we use setBytes(), so this may not be an >>>issue for us (although we'd like to keep the option of using >>>setBinaryStream()). >>> >> >>Yes, the idea was use setBinaryStream() if you want to use this method >>(we cannot do much for setBytes() anyway as we need the stream). Of >>course, Barry's suggestion makes it much better as it allows one to use >>setBinaryStream() for small data as well. > > > Isn't streaming a win for setBytes() with large bytearrays? If I have an > existing 1mb byte array to insert, the current driver will need 2-3mb of > temporary string space for setBytes(), I think? > > I suppose the app can always wrap it in a ByteArrayInputStream and use > setBinaryStream(), though. > That is the idea. We could create the stream in the driver in the setBytes() method but as I mentioned, the new frontend-backend protocol has a mechanism that we can use to send the data so we did not want to get too sofisticated in the legacy fix. > >>If there is a lot of interest in this fix for 7.3 and 7.2 backends, and >>you can convince Barry to incorporate it (with his proposed changes) in >>the driver, I can ask for time to get it in shape for submission. But I >>won't be able to do it unless it goes into the main line of code -- I >>already have to work on the 7.4 version (V3 protocol). > > > We don't benefit from the patch as we don't deal with large bytea fields, I > just wanted to make sure our insert performance didn't suffer if the patch > did get applied. It sounds like that won't be a problem, thanks for > clarifying things. > You are welcome. Best regards, Fernando -- Fernando Nasser Red Hat - Toronto E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9