Cannot retrieve images inserted through VB and odbc, after a table reorganization.

[Alanoly Andrews]

Hello,

We have an issue with images (TIFF files) that are loaded into a postgres database table (where the image field is defined as type "lo"). The code to load the images is written in VB and the connection to the database is through postgres odbc installed on the PC. All images inserted through the VB program are retrievable also through odbc/VB, as long as the table rows at the backend are not reorganized in any way. If the table data is unloaded/reloaded or clustered (on an index) or reloaded through a pg_dump and restore, then on trying to retrieve the images, the VB program gets the error : "Multiple-step OLE DB operation generated errors. Check each OLE DB status value......etc".

The data in the backend database is still readable through other image retrieval programs connecting directly to the server (not through ODBC or VB).

The database is on an AIX server (version 6.1). Postgres version is 9.1.4.

I'd welcome any input towards the resolution of this problem.

Thanks.

Alanoly Andrews
***@invera.com



________________________________

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:***@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.




Si vous ne d?sirez plus recevoir de nos courriels, veuillez appuyer sur D?SABONNEMENT.<mailto:***@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par retour de courriel ou par un autre moyen.

[Craig James]

*We have an issue with images (**TIFF files) that are loaded into a
postgres database table (where the image field is defined as type **“**lo*
*”**)**. The code to load the images is written in VB and the connection
to the database is through postgres odbc** installed on the PC. * *All
images inserted through the VB program are retrievable also through
odbc/VB, as long as the table **rows **at the backend **are** not re**organized
in any way. If the table dat**a** is unloaded/reloaded or clustered (on
an index) or rel**oaded through a pg_dump and restore, then **on trying
to retrieve the images, **the VB program gets the error : **“**Multiple-step
OLE DB operation generat**ed errors. Check each OLE DB status value**

*
*etc**”**.*
*The data in the backend database is still readable through other image
retrieval progr**ams connecting directly to the server (not through ODBC
or VB).*

Are you reloading into the very same database, or into a different one?
Your question doesn't make it clear. If you're loading into a new database,
then one thing that comes to mind is that the bytea_output parameter in
postgresql.conf might be different on the second database.

Craig

*The database is on an AIX server (version 6.1). **Postgres version is
9.1.4. *
*I**’**d welcome **any** input towards the resolution of this problem. *
*Thanks.*
*Alanoly Andrews*
* ________________________________ *
If you no longer wish to receive any of our emails, click on
e-mail may be privileged and/or confidential, and the sender does not waive
any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended
recipient is unauthorized. If you received this e-mail in error, please
advise me (by return e-mail or otherwise) immediately.
Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur
courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux
droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou
copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous
recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par
retour de courriel ou par un autre moyen.

[Alanoly Andrews]

Hi Craig,

I’m reloading into the very same database.
If I insert a new row into the table (through VB/ODBC), I’m able to retrieve it (again from VB). But if after inserting the new row, I do a reorg of the table (eg. with a “cluster table”), I’m no longer able to retrieve that same row. In short, as long as the newly inserted rows are not “re-written” in some way, they can be retrieved through VB/ODBC.

Alanoly.

From: Craig James [mailto:***@emolecules.com]
Sent: Wednesday, September 03, 2014 12:13 PM
To: Alanoly Andrews
Cc: pgsql-***@postgresql.org; pgsql-***@postgresql.org
Subject: Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.


On Wed, Sep 3, 2014 at 8:13 AM, Alanoly Andrews <***@invera.com<mailto:***@invera.com>> wrote:
We have an issue with images (TIFF files) that are loaded into a postgres database table (where the image field is defined as type “lo”). The code to load the images is written in VB and the connection to the database is through postgres odbc installed on the PC. All images inserted through the VB program are retrievable also through odbc/VB, as long as the table rows at the backend are not reorganized in any way. If the table data is unloaded/reloaded or clustered (on an index) or reloaded through a pg_dump and restore, then on trying to retrieve the images, the VB program gets the error : “Multiple-step OLE DB operation generated errors. Check each OLE DB status value

etc”.

The data in the backend database is still readable through other image retrieval programs connecting directly to the server (not through ODBC or VB).

Are you reloading into the very same database, or into a different one? Your question doesn't make it clear. If you're loading into a new database, then one thing that comes to mind is that the bytea_output parameter in postgresql.conf might be different on the second database.

Craig


The database is on an AIX server (version 6.1). Postgres version is 9.1.4.

I’d welcome any input towards the resolution of this problem.

Thanks.

Alanoly Andrews
***@invera.com<mailto:***@invera.com>


________________________________
If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:***@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.


Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur DÉSABONNEMENT.<mailto:***@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------

________________________________

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:***@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.


Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur DÉSABONNEMENT.<mailto:***@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.

[Adrian Klaver]

*Hi Craig,*
**
*I’m reloading into the very same database. *
*If I insert a new row into the table (through VB/ODBC), I’m able to
retrieve it (again from VB). But if after inserting the new row, I do a
reorg of the table (eg. with a “cluster table”), I’m no longer able to
retrieve that same row. In short, as long as the newly inserted rows are
not “re-written” in some way, they can be retrieved through VB/ODBC. *

To follow up on my previous post with regards to OIDs. That may have
been a wrong fork. A little testing:

test=# CREATE TABLE image (
name text,
raster oid
);

test=# create index img_idx on image (raster);

test=# INSERT INTO image (name, raster)
VALUES ('another beautiful image',
lo_import('/home/aklaver/Pictures/IMG_0359JPG'));

test=# INSERT INTO image (name, raster)
VALUES ('another beautiful image',
lo_import('/home/aklaver/Pictures/IMG_0360.JPG'));

test=# INSERT INTO image (name, raster)
VALUES ('another beautiful image',
lo_import('/home/aklaver/Pictures/IMG_0361.JPG'));

test=# select oid, relfilenode from pg_class where relname ='image';
oid | relfilenode
--------+-------------
532144 | 532175

test=# cluster image using img_idx;
CLUSTER
test=# select oid, relfilenode from pg_class where relname ='image';
oid | relfilenode
--------+-------------
532144 | 532182

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(unless you use
--oids with pg_dump).

So to revise my previous statement, my guess is your VB/ODBC code is
using the relfilenode value to refer to the table and when that changes
it cannot find it any more.

**
*Alanoly.*

[Alanoly Andrews]

Hi Adrian,

Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that this happens because the table rows are being physically moved from one location to another. But such changes at the backend should be transparent to the end user. The VB code at the client side runs a simple sql like "select image from image_table where image_key=somevalue". There is no reference to postgres-specific internal variables like "oid" and "relfilenode". I do not know the inner workings of the postgres odbc driver; but I would be surprised if it works at the granularity of "oid" and "relfilenode" and that it would store the actual physical values of relfilenode (which would keep changing after every table reload, reorg etc.).

Alanoly.

-----Original Message-----
From: Adrian Klaver [mailto:***@aklaver.com]
Sent: Wednesday, September 03, 2014 3:10 PM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-***@postgresql.org; pgsql-***@postgresql.org
Subject: Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

*Hi Craig,*
**
*I’m reloading into the very same database. *
*If I insert a new row into the table (through VB/ODBC), I’m able to
retrieve it (again from VB). But if after inserting the new row, I do
a reorg of the table (eg. with a “cluster table”), I’m no longer able
to retrieve that same row. In short, as long as the newly inserted
rows are not “re-written” in some way, they can be retrieved through
VB/ODBC. *

To follow up on my previous post with regards to OIDs. That may have been a wrong fork. A little testing:

test=# CREATE TABLE image (
name text,
raster oid
);

test=# create index img_idx on image (raster);

test=# INSERT INTO image (name, raster)
VALUES ('another beautiful image', lo_import('/home/aklaver/Pictures/IMG_0359JPG'));

test=# INSERT INTO image (name, raster)
VALUES ('another beautiful image', lo_import('/home/aklaver/Pictures/IMG_0360.JPG'));

test=# INSERT INTO image (name, raster)
VALUES ('another beautiful image', lo_import('/home/aklaver/Pictures/IMG_0361.JPG'));

test=# select oid, relfilenode from pg_class where relname ='image';
oid | relfilenode
--------+-------------
532144 | 532175

test=# cluster image using img_idx;
CLUSTER
test=# select oid, relfilenode from pg_class where relname ='image';
oid | relfilenode
--------+-------------
532144 | 532182

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(unless you use --oids with pg_dump).

So to revise my previous statement, my guess is your VB/ODBC code is using the relfilenode value to refer to the table and when that changes it cannot find it any more.

**
*Alanoly.*

--
Adrian Klaver
***@aklaver.com

________________________________

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:***@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur DÉSABONNEMENT.<mailto:***@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.

[David G Johnston]

You are going to have to help us by providing server (and any other) logs
with complete error messages and VB/ODBC code, with corresponding schema,
that will reliably reproduce the problem.

Note that since you are on an ancient 9.1 release it is possible that, if
this is indeed a bug, this has already been fixed in one of the 10
subsequent releases.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/Cannot-retrieve-images-inserted-through-VB-and-odbc-after-a-table-reorganization-tp5817580p5817743.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Adrian Klaver]

Post by Alanoly Andrews
Hi Adrian,
Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that this happens because the table rows are being physically moved from one location to another. But such changes at the backend should be transparent to the end user. The VB code at the client side runs a simple sql like "select image from image_table where image_key=somevalue". There is no reference to postgres-specific internal variables like "oid" and "relfilenode". I do not know the inner workings of the postgres odbc driver; but I would be surprised if it works at the granularity of "oid" and "relfilenode" and that it would store the actual physical values of relfilenode (which would keep changing after every table reload, reorg etc.).

In addition to what David mentioned, some more detail on what you are
calling a 'large object'. In Postgres there is not really a 'large
object' type(though I am guilty of saying there is), instead there is a
way of working with large objects outside of the bytea type. Now in the
ODBC FAQ there is a recipe for creating a 'lo' type:

http://psqlodbc.projects.pgfoundry.org/faq.html#4.4

Is this what you are using?

If not how are you working with the large objects?

Also given that you are working with the Postgres ODBC driver, you might
want to break the cross post rule and ask this question on the psql-odbc
list:

http://www.postgresql.org/list/pgsql-odbc/

In any case your ODBC settings would be helpful.

Post by Alanoly Andrews
Alanoly.

[David G Johnston]

Post by Adrian Klaver

Post by Alanoly Andrews
Hi Adrian,
Thanks for that explanation of how the "relfilenode" changes after a
table reorganization. It is not surprising that this happens because the
table rows are being physically moved from one location to another. But
such changes at the backend should be transparent to the end user. The VB
code at the client side runs a simple sql like "select image from
image_table where image_key=somevalue". There is no reference to
postgres-specific internal variables like "oid" and "relfilenode". I do
not know the inner workings of the postgres odbc driver; but I would be
surprised if it works at the granularity of "oid" and "relfilenode" and
that it would store the actual physical values of relfilenode (which
would keep changing after every table reload, reorg etc.).

In addition to what David mentioned, some more detail on what you are
calling a 'large object'. In Postgres there is not really a 'large
object' type(though I am guilty of saying there is), instead there is a
way of working with large objects outside of the bytea type. Now in the
http://psqlodbc.projects.pgfoundry.org/faq.html#4.4
Is this what you are using?
If not how are you working with the large objects?
Also given that you are working with the Postgres ODBC driver, you might
want to break the cross post rule and ask this question on the psql-odbc
http://www.postgresql.org/list/pgsql-odbc/
In any case your ODBC settings would be helpful.

I suspect the OP is referring to the contrib module/extension:

http://www.postgresql.org/docs/9.1/interactive/lo.html

David J.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Cannot-retrieve-images-inserted-through-VB-and-odbc-after-a-table-reorganization-tp5817580p5817761.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Adrian Klaver]

Post by David G Johnston

Post by Adrian Klaver

Post by Alanoly Andrews
Hi Adrian,
Thanks for that explanation of how the "relfilenode" changes after a
table reorganization. It is not surprising that this happens because the
table rows are being physically moved from one location to another. But
such changes at the backend should be transparent to the end user. The VB
code at the client side runs a simple sql like "select image from
image_table where image_key=somevalue". There is no reference to
postgres-specific internal variables like "oid" and "relfilenode". I do
not know the inner workings of the postgres odbc driver; but I would be
surprised if it works at the granularity of "oid" and "relfilenode" and
that it would store the actual physical values of relfilenode (which
would keep changing after every table reload, reorg etc.).

In addition to what David mentioned, some more detail on what you are
calling a 'large object'. In Postgres there is not really a 'large
object' type(though I am guilty of saying there is), instead there is a
way of working with large objects outside of the bytea type. Now in the
http://psqlodbc.projects.pgfoundry.org/faq.html#4.4
Is this what you are using?
If not how are you working with the large objects?
Also given that you are working with the Postgres ODBC driver, you might
want to break the cross post rule and ask this question on the psql-odbc
http://www.postgresql.org/list/pgsql-odbc/
In any case your ODBC settings would be helpful.

http://www.postgresql.org/docs/9.1/interactive/lo.html

Aaah, did not know that existed, thanks.

Post by David G Johnston
David J.

[Alanoly Andrews]

Thanks, Adrian for the response.



Yes, we are using the "large object" as per the specifications in the special "lo" module that we installed on the backend server. The table is created using the "lo" datatype for the image field and the table is being regularly used for insertions and retrievals using utilities other than ones using odbc. The odbc connection, too, works well on the table, as long as the table data is not reorganized after insertion(s).



Here are three pages of our postgres odbc driver settings. Sorry, they are screen captures, not plain text!



[cid:***@01CFC82F.D8DB69F0]



[cid:***@01CFC82F.D8DB69F0]



[cid:***@01CFC82F.D8DB69F0]





Alanoly.



-----Original Message-----
From: Adrian Klaver [mailto:***@aklaver.com]
Sent: Thursday, September 04, 2014 9:39 AM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-***@postgresql.org; pgsql-***@postgresql.org
Subject: Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

Post by Alanoly Andrews
Hi Adrian,
Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that this happens because the table rows are being physically moved from one location to another. But such changes at the backend should be transparent to the end user. The VB code at the client side runs a simple sql like "select image from image_table where image_key=somevalue". There is no reference to postgres-specific internal variables like "oid" and "relfilenode". I do not know the inner workings of the postgres odbc driver; but I would be surprised if it works at the granularity of "oid" and "relfilenode" and that it would store the actual physical values of relfilenode (which would keep changing after every table reload, reorg etc.).

In addition to what David mentioned, some more detail on what you are calling a 'large object'. In Postgres there is not really a 'large object' type(though I am guilty of saying there is), instead there is a way of working with large objects outside of the bytea type. Now in the ODBC FAQ there is a recipe for creating a 'lo' type:



http://psqlodbc.projects.pgfoundry.org/faq.html#4.4



Is this what you are using?



If not how are you working with the large objects?



Also given that you are working with the Postgres ODBC driver, you might want to break the cross post rule and ask this question on the psql-odbc

list:



http://www.postgresql.org/list/pgsql-odbc/



In any case your ODBC settings would be helpful.

Post by Alanoly Andrews
Alanoly.

--
Adrian Klaver

***@aklaver.com<mailto:***@aklaver.com>

________________________________

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:***@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.


Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur DÉSABONNEMENT.<mailto:***@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.

[Adrian Klaver]

Post by Alanoly Andrews
Thanks, Adrian for the response.
Yes, we are using the "large object" as per the specifications in the
special "lo" module that we installed on the backend server. The table
is created using the "lo" datatype for the image field and the table is
being regularly used for insertions and retrievals using utilities other
than ones using odbc. The odbc connection, too, works well on the table,
as long as the table data is not reorganized after insertion(s).
Here are three pages of our postgres odbc driver settings. Sorry, they
are screen captures, not plain text!

Hmm, to me at least nothing seems out of place. Though to be honest I am
not sure what 'Bytea as LO' does in this situation.

Are you actually trying to store any large objects in bytea columns?

I searched on the error message you originally posted, but is fairly
generic and covers a lot of different error sources.

So, is there any more specific error information in either the Postgres
or ODBC logs?

Post by Alanoly Andrews
Alanoly.

[Alanoly Andrews]

Hi Adrian,

1. We are storing our large objects in a table column whose datatype is "lo" (which, I understand, ultimately is in fact "bytea").
2. The error messge returned to the VB is, yes, very generic. And the backend postgres server does not have any messages relevant to this issue.
3. We set the trace on in the odbc driver and got a large log file, from which I give below the part that I think is significant. It refers to the "buffer size being too small". Where, in the odc settings, do we set the buffer size?


STELimageViewOn 834-fec EXIT SQLGetData with return code 1 (SQL_SUCCESS_WITH_INFO)
HSTMT 0x002BFC70
UWORD 2
SWORD 1 <SQL_C_CHAR>
PTR 0x0018EECF [ 0] ""
SQLLEN 0
SQLLEN * 0x0018EEA4 (6)

DIAG [01004] The buffer was too small for the GetData. (-2)

STELimageViewOn 834-fec ENTER SQLGetData
HSTMT 0x002BFC70
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 0
SQLLEN * 0x0018EE54

STELimageViewOn 834-fec EXIT SQLGetData with return code 1 (SQL_SUCCESS_WITH_INFO)
HSTMT 0x002BFC70
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 0
SQLLEN * 0x0018EE54 (46165)

DIAG [01004] The buffer was too small for the GetData. (-2)

STELimageViewOn 834-fec ENTER SQLGetData
HSTMT 0x002BFC70
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 32768
SQLLEN * 0x003CE284

STELimageViewOn 834-fec EXIT SQLGetData with return code -1 (SQL_ERROR)
HSTMT 0x002BFC70
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 32768
SQLLEN * 0x003CE284

DIAG [S1000] Error reading from large object. (1)

-----Original Message-----
From: Adrian Klaver [mailto:***@aklaver.com]
Sent: Thursday, September 04, 2014 4:43 PM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-***@postgresql.org; pgsql-***@postgresql.org
Subject: Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

Post by Alanoly Andrews
Thanks, Adrian for the response.
Yes, we are using the "large object" as per the specifications in the
special "lo" module that we installed on the backend server. The table
is created using the "lo" datatype for the image field and the table
is being regularly used for insertions and retrievals using utilities
other than ones using odbc. The odbc connection, too, works well on
the table, as long as the table data is not reorganized after insertion(s).
Here are three pages of our postgres odbc driver settings. Sorry, they
are screen captures, not plain text!

Hmm, to me at least nothing seems out of place. Though to be honest I am not sure what 'Bytea as LO' does in this situation.

Are you actually trying to store any large objects in bytea columns?

I searched on the error message you originally posted, but is fairly generic and covers a lot of different error sources.

So, is there any more specific error information in either the Postgres or ODBC logs?

Post by Alanoly Andrews
Alanoly.

--
Adrian Klaver
***@aklaver.com

________________________________

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:***@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur DÉSABONNEMENT.<mailto:***@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.

[David G Johnston]

On Fri, Sep 5, 2014 at 11:34 AM, Alanoly Andrews [via PostgreSQL] <

Post by Alanoly Andrews
Hi Adrian,
1. We are storing our large objects in a table column whose datatype is
"lo" (which, I understand, ultimately is in fact "bytea").

​
The "lo" data type is effectively a domain over the "oid"​

​data type which is effectively a lookup key onto the large objects table.
While you can ultimately retrieve bytea data if you have an "lo" value the
"lo" data is not itself your binary content.

I'm confused because you seem confused here but the code actually works...

You still haven't provided a self-contained scenario ​which demonstrates
the problem - during which you should enable logging of all statements in
order to definitively see what it is that PostgreSQL is doing during the
test run.

One theory I have is that you've added:

CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);

to the relevant table and, arguably incorrectly, when the table rewrite
occurs the original row pointing to the large object goes away and so the
trigger removes the large object as it is designed to do. This really
doesn't make sense but the general mechanics of what this function does and
what you describe are too similar to ignore.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Cannot-retrieve-images-inserted-through-VB-and-odbc-after-a-table-reorganization-tp5817580p5817953.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Adrian Klaver]

Post by Alanoly Andrews
Hi Adrian,
1. We are storing our large objects in a table column whose datatype is "lo" (which, I understand, ultimately is in fact "bytea").

As way of testing where the issue is, you might explore saving some
images in a test table directly to a bytea column. This is presumably
what the Bytea as LO option is for. Then run CLUSTER on the test table
and see what happens.

Post by Alanoly Andrews
2. The error messge returned to the VB is, yes, very generic. And the backend postgres server does not have any messages relevant to this issue.
3. We set the trace on in the odbc driver and got a large log file, from which I give below the part that I think is significant. It refers to the "buffer size being too small". Where, in the odc settings, do we set the buffer size?

I would recommend posting the below(with some background information) on
the -odbc list where there are folks that might better understand it.

Post by Alanoly Andrews
STELimageViewOn 834-fec EXIT SQLGetData with return code 1 (SQL_SUCCESS_WITH_INFO)
HSTMT 0x002BFC70
UWORD 2
SWORD 1 <SQL_C_CHAR>
PTR 0x0018EECF [ 0] ""
SQLLEN 0
SQLLEN * 0x0018EEA4 (6)
DIAG [01004] The buffer was too small for the GetData. (-2)
STELimageViewOn 834-fec ENTER SQLGetData
HSTMT 0x002BFC70
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 0
SQLLEN * 0x0018EE54
STELimageViewOn 834-fec EXIT SQLGetData with return code 1 (SQL_SUCCESS_WITH_INFO)
HSTMT 0x002BFC70
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 0
SQLLEN * 0x0018EE54 (46165)
DIAG [01004] The buffer was too small for the GetData. (-2)
STELimageViewOn 834-fec ENTER SQLGetData
HSTMT 0x002BFC70
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 32768
SQLLEN * 0x003CE284
STELimageViewOn 834-fec EXIT SQLGetData with return code -1 (SQL_ERROR)
HSTMT 0x002BFC70
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 32768
SQLLEN * 0x003CE284
DIAG [S1000] Error reading from large object. (1)

[Adrian Klaver]

*Hi Craig,*
**
*I’m reloading into the very same database. *
*If I insert a new row into the table (through VB/ODBC), I’m able to
retrieve it (again from VB). But if after inserting the new row, I do a
reorg of the table (eg. with a “cluster table”), I’m no longer able to
retrieve that same row. In short, as long as the newly inserted rows are
not “re-written” in some way, they can be retrieved through VB/ODBC. *

This:

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(unless you use
--oids with pg_dump).

should be:

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(if you use
--oids with pg_dump).

**
*Alanoly.*

[Adrian Klaver]

*We have an issue with images (**TIFF files) that are loaded into a
postgres database table (where the image field is defined as type
**“**lo**”**)**. The code to load the images is written in VB and
the connection to the database is through postgres odbc**installed
on the PC. ****All images inserted through the VB program are
retrievable also through odbc/VB, as long as the table **rows **at
the backend **are**not re**organized in any way. If the table
dat**a**is unloaded/reloaded or clustered (on an index) or
rel**oaded through a pg_dump and restore, then **on trying to
**“**Multiple-step OLE DB operation generat**ed errors. Check each
OLE DB status value**……**etc**”**.*
*The data in the backend database is still readable through other
image retrieval progr**ams connecting directly to the server (not
through ODBC or VB).*
Are you reloading into the very same database, or into a different one?
Your question doesn't make it clear. If you're loading into a new
database, then one thing that comes to mind is that the bytea_output
parameter in postgresql.conf might be different on the second database.

The OP is using the lo type, so I suspect it is something to do with the
OIDs changing. I do not use lo so I cannot really be more helpful.

Craig