Re: [GENERAL] Re: Cannot retrieve images inserted through VB and odbc, after a table reorganization.

From: Alanoly Andrews <alanolya(at)invera(dot)com>
To: 'Adrian Klaver' <adrian(dot)klaver(at)aklaver(dot)com>, 'Craig James' <cjames(at)emolecules(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] Re: Cannot retrieve images inserted through VB and odbc, after a table reorganization.
Date: 2014-09-04 15:03:52
Message-ID: af0397c0ba2f4fefb79c0ba63a5ab50d@Exch2.invera.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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:image001(dot)png(at)01CFC82F(dot)D8DB69F0]

[cid:image002(dot)png(at)01CFC82F(dot)D8DB69F0]

[cid:image003(dot)png(at)01CFC82F(dot)D8DB69F0]

Alanoly.

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

On 09/04/2014 05:56 AM, Alanoly Andrews wrote:

> 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.

>

> Alanoly.

>

--

Adrian Klaver

adrian(dot)klaver(at)aklaver(dot)com<mailto:adrian(dot)klaver(at)aklaver(dot)com>

________________________________

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:unsubscribe(at)invera(dot)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:unsubscribe(at)invera(dot)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.

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Adrian Klaver 2014-09-04 20:31:09 Re: Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.
Previous Message David G Johnston 2014-09-04 14:15:47 Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

Browse pgsql-general by date

  From Date Subject
Next Message Eric Fleming 2014-09-04 15:17:40 Re: GiST index question
Previous Message François Beausoleil 2014-09-04 14:48:00 Re: Employee modeling question