Discussion:
Postgres large objects
(too old to reply)
Markus Rentsch
2019-12-19 02:09:57 UTC
Permalink
Hello

In my AL (TDI 7.1.1) i have set up a jdbc connector to the postgres db.
Regular insert or update statements work just fine.

The jdbc driver for postgres allows to store uploaded files in a large object store (outside of the table where the 'normal' columns are).

Now i try to implement uploading files into the large object store (not BYTEA column) but have no idea how to get hands on the Large Object API inside the postgres jdbc driver. The following line from the sample code (see full listing below) troubles me most - i don't know how to translate into tdi code:

LargeObjectManager lobj = conn.unwrap(org.postgresql.PGConnection.class).getLargeObjectAPI();

Is there a way to get this done?
I wasn't able to find any function in com.ibm.di.connector.JDBCConnector to kind of pass the getLargeObjectAPI to the jdbc driver...

Any ideas how i could approach this?

Thanks a lot
Markus


------------ Sample Code for jdbc handling of LOB ---------------
Quoted from https://jdbc.postgresql.org/documentation/head/binary-data.html

Alternatively you could be storing a very large file and want to use the LargeObject API to store the file:

CREATE TABLE imageslo (imgname text, imgoid oid);

To insert an image, you would use:

// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = conn.unwrap(org.postgresql.PGConnection.class).getLargeObjectAPI();

// Create a new large object
long oid = lobj.createLO(LargeObjectManager.READ | LargeObjectManager.WRITE);

// Open the large object for writing
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

// Now open the file
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);

// Copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0)
{
obj.write(buf, 0, s);
tl += s;
}

// Close the large object
obj.close();

// Now insert the row into imageslo
PreparedStatement ps = conn.prepareStatement("INSERT INTO imageslo VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setLong(2, oid);
ps.executeUpdate();
ps.close();
fis.close();

// Finally, commit the transaction.
conn.commit();
j***@gmail.com
2019-12-20 09:01:14 UTC
Permalink
If you write the code inside a hook of the JDBC Connector,
you could get the "conn" object with
var conn = thisConnector.connector.getConnection()

Otherwise, if the connector is named JDBCConnector in this AssemblyLine, try
var conn = JDBCConnector.connector.getConnection()

To get the value of org.postgresql.PGConnection.class, try this
var clazz = java.lang.Class.forName("org.postgresql.PGConnection")

And then do
var lobj = conn.unwrap(clazz).getLargeObjectAPI();

-Jens
Eddie Hartman
2019-12-20 15:18:05 UTC
Permalink
Post by j***@gmail.com
If you write the code inside a hook of the JDBC Connector,
you could get the "conn" object with
var conn = thisConnector.connector.getConnection()
Otherwise, if the connector is named JDBCConnector in this AssemblyLine, try
var conn = JDBCConnector.connector.getConnection()
To get the value of org.postgresql.PGConnection.class, try this
var clazz = java.lang.Class.forName("org.postgresql.PGConnection")
And then do
var lobj = conn.unwrap(clazz).getLargeObjectAPI();
-Jens
And I hate to be the nagging grandad in the forums, but I would recommend not creating variables that override built-in ones - like conn, work, system, task, main or current. Instead:

var ctn = JDCConnector.connector.getConnection()

Felt good getting that off my chest :)

/Eddie

Loading...