Notes by voodoo
Displaying keyword search results 1 - 7
Created by voodoo on November 24, 2010 23:21:00 Last update: March 31, 2011 08:59:05
PostgreSQL jdbc connection string: jdbc:postgresql://<hostname>:<port>/<database_name> . Example code:
// register JDBC driver with DriverManager Clas...
Created by voodoo on November 25, 2010 00:15:37 Last update: November 25, 2010 00:15:37
PostgreSQL JDBC doc says: Specifically deleting a row that contains a Large Object reference does not delete the Large Object. Deleting the Large Object is a separate operation that needs to be performed. . In JDBC this can be done in two steps: Delete the large object (call PostgreSQL function lo_unlink )
long oid = jdbcTemplate.queryForObject("select...Delete the row in the referring table:
jdbcTemplate.update("delete from InventoryItem whe...
Created by voodoo on November 25, 2010 00:03:53 Last update: November 25, 2010 00:03:53
It seems that the JDBC standard way to create a BLOB is to call Connection.createBlob . However, this does not work for PostgreSQL (as of version 9.0-801 jdbc4):
Exception in thread "main" org.postgresql.util.PSQ...The workaround is to call a PostgreSQL function to create the Blob, then use JDBC to update it:
Connection conn = jdbcTemplate.getDataSource().get...Oracle Note: the Oracle way function to create an empty BLOB is EMPTY_BLOB() .
stmt.execute ("INSERT INTO my_blob_table VALUES ('...
Created by voodoo on November 24, 2010 23:43:29 Last update: November 24, 2010 23:43:29
Two of the three PreparedStatement.setBinaryStream methods are not implemented as of version 9.0-801 of the PostgreSQL JDBC driver. Test program:
import java.io.*; import java.sql.*; pub...Also note that setBinaryStream only works on a bytea column.
Created by voodoo on November 24, 2010 23:34:52 Last update: November 24, 2010 23:36:08
PostgreSQL provides two distinct ways to store binary data: Binary data can be stored in a table using the data type bytea . By using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table. Different methods are used to access the BLOBs depending on which storage type you choose: To use the bytea data type you should use the getBytes() , setBytes() , getBinaryStream() , or setBinaryStream() methods. To use the Large Object functionality you can use either the LargeObject class provided by the PostgreSQL JDBC driver, or by using the getBLOB() and setBLOB() methods. Using setBinaryStream on an OID column yields...
Created by voodoo on November 01, 2010 22:26:00 Last update: November 01, 2010 22:26:00
Some weirdness observed while updating BLOB with PostgreSQL JDBC driver: LOB position offset starts at 1 (not 0 as in IO input stream). When you call setBinaryStream(long pos) , pos must be greater than 0. I think this is JDBC standard behavior. When you write to a BLOB output stream, new contents overwrite existing contents. Old contents are not automatically truncated. If new content length is shorter than old contents, the old contents will remain after the position where new content ended. You can call Blob.truncate(long len) to truncate existing contents. However , there seems to be an discrepancy between the JDBC doc and PostgreSQL JDBC driver. The JDBC JavaDoc states that: Truncates the BLOB value that this Blob object represents to be len bytes...
Created by voodoo on July 09, 2010 21:02:35 Last update: July 09, 2010 21:02:35
The rpm command tells you where a package is installed:
[root@bamboo ~]# rpm -ql postgresql-jdbc /etc/m...