Devart Forums

Posted: **Fri 29 Sep 2006 10:41**

Hi.

There's a couple of things I'd like to clarify using BLOBs.

The native C API has a

Oid lo_creat(PGconn* conn, int mode)

function available. This creates a new LO and returns its OID, which is then used in subsequent calls to the lo_(...) functions.

I have a table defined as:

Code: Select all

create table tb_variable_map ( 
    id serial primary key, 
    varname text, 
    varclass text,
    last_modification timestamp,
    external_data oid
);

The process I'm use to working with is create a new BLOB, add its OID to the tb_Variable_Map table, and then when required, pull its OID from there to manipulate the BLOB.

The following is an example from the documentation.

Code: Select all

public void UploadBlob(PgSqlConnection pgConnection) 
{ 
  FileStream fs = new FileStream("D:\Tmp\_Water.bmp", FileMode.Open, FileAccess.Read); 
  BinaryReader r = new BinaryReader(fs); 
  PgSqlBlob myBlob = new PgSqlBlob(r.ReadBytes((int)fs.Length)); 
  PgSqlCommand pgCommand = new PgSqlCommand("INSERT INTO Test.Pictures (ID, PicName, Picture) VALUES(1,'Water',:Pictures)", pgConnection); 
  pgCommand.Parameters.Add("Pictures",myBlob); 
  pgConnection.Open(); 
  try 
  { 
    Console.WriteLine(pgCommand.ExecuteNonQuery()+" rows affected."); 
  } 
  finally 
  { 
    pgConnection.Close(); 
    r.Close(); 
  } 
}

I've tried this and get

Unable to cast object of type 'CoreLab.PostgreSql.PgSqlBlob' to type 'System.IConvertible'

.

I get a feeling I may need to re-think my approach to this using the Framework. Is there a tutorial/sample code available which has the complete stages of take empty database, create table to store blob data, insert some rows and blob data, select some rows and write some data?

Cheers,

Jon.

Posted: **Fri 29 Sep 2006 11:13**

Right, kind of solved it; was going to delete my post, but figured it might be useful in case someone else makes a false association.

PgSqlBlob wraps the 'bytea' column type in PostgreSQL, not as I mistakenly thought, the actual PostgreSQL Large Objects.

Having said that though, is it possible to use LOs via PostgreSQLDirect.NET?

Cheers,

Jon.

Posted: **Fri 29 Sep 2006 12:03**

Could you please describe in detail what exactly you need or what is the problem. Because the code you mentioned works perfectly without any error.

Posted: **Fri 29 Sep 2006 13:40**

I'll give it a go - please let me know if you require clarification.

PostgreSQL has two forms of storing 'large objects'. One is via the bytea type which is effectively a column which may be 1gb in size; the other is via a 'Large Object' interface, supporting up to 2gb.

If I have a table such as:

Code: Select all

create table tb_variable_map ( 
    id serial primary key, 
    varname text, 
    varclass text, 
    last_modification timestamp, 
    external_data bytea
);

Then yes, the code given (modifying the relevant names/parameters etc..), works without problem. However, the case is I do not have a bytea column, bur rather I have an OID which references a large object (see original table def.).

The PgSqlBlob class appears to handle bytea columns. My question is, if I have a Large Object referenced by OID, how can I access this via CoreLabs.PostgreSql?

Cheers,

Jon.

Useful resources:
http://www.postgresql.org/docs/7.4/inte ... jects.html
http://jdbc.postgresql.org/documentatio ... -data.html
(I am using PostgreSQL 8.1.4, but these were the latest docs I could find when searching...)

Posted: **Mon 02 Oct 2006 09:05**

We are considering the possibility of creating such class. Probably it will
be added in the future builds.