what are correct steps to programmatically write/read large objects to/from a data base table?

From: "Ian Bell" <ib(at)ianbellsoftware(dot)com>
To: <pgsql-novice(at)lists(dot)postgresql(dot)org>
Subject: what are correct steps to programmatically write/read large objects to/from a data base table?
Date: 2018-04-11 15:55:47
Message-ID: !&!AAAAAAAAAAAYAAAAAAAAADldbAmb6+pIq6nH7MxZl07CgAAAEAAAAHwu5Zn/ZI5Lm1556V/JZV8BAAAAAA==@ianbellsoftware.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

I have just started working with PostgreSQL (v10.3) running under Window 10.
Also I am new to SQL in general. I am writing test code to see how to
write/read large objects and have a question about the order/number of steps
when executing code written in C/C++ and C#.

I want to write and read binary data as large objects.

1) In PSQL, I do this using the following commands:

create database mydb;

\c mydb;

create table test( name text, mylargevariable oid);

insert into test( name, mylargevariable) values( 'n1',
lo_import('f:/temp/test.txt') );

select convert_from( lo_get(test.large), 'UTF8') from test;

The point of this example is that the large object id value ('OID') is
stored in the 'mylargevariable' field of the table 'test'. My question is
about how to programmatically do this using code written in C/C++ and C#.
I address this in my next point.

2) The PostgreSQL sample code
<https://www.postgresql.org/docs/10/static/lo-examplesect.html> illustrates
how to write/read a large object in C/C++ while the NPGSQL sample code
<http://www.npgsql.org/doc/large-objects.html> illustrates how to
write/read large objects in C#. I am able to successfully compile and run
both sets of sample code. Here are my questions:

a. My understanding is these examples write the binary data as a large
object to the 'pg_largeobject' table. Would you please confirm this is
correct?

b. If I want to be able to retrieve the binary data associated with a
large object stored in the 'pg_largeobject' table at a later time (i.e.
start a new process or run a new application) then I will have to
persist/save/write the OID's referencing the large object to a table when
the large object is first created. So the pseudo code for both C/C++ and C#
programs would be as follow:

i.
Create my database and create my own table which will store large object
OIDs

ii. Create
a large object OID (e.g. call the function 'lo_creat' in the C libpg
library)

iii. Write
the binary data to this large object (e.g. call the function 'lo_write' in
the C libpg library)

iv. Write
the large object OID generated in step ii to a PostgreSQL table that I
created in step i

Would you please confirm these steps and their order are correct? The
reason for asking this question is I want to confirm that i) while non-large
objects require only one write to the data base ii) in contrast,
large-objects require two writes (i.e. the first to write the large object,
the second to insert/write the large object OID to a table field). I
actually have written test code that does just this and it seems to be
working. However, given my lack of experience with PostgreSQL and SQL in
general, I would like confirmation the writing/reading binary data large
object is this simple and that I have not missed something important.

Thank you

Ian

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Laurenz Albe 2018-04-12 06:43:09 Re: what are correct steps to programmatically write/read large objects to/from a data base table?
Previous Message Bee.Lists 2018-04-06 22:19:47 earthdistance Module & Syntax