Thread: [INTERFACES] ODBC, Delphi and BLOBs (images)

Andrzej Szydlo wrote:

>I'm trying to access PostgreSQL database from Delphi via ODBC.

I'm trying to do so from C++ Builder 3.01 and have some questions which
bother me.
I have a Boolean field in a table. When I view the table in the Database
Explorer the field is exposed as Char2, though behaves as boolean. Database
Explorer also reveals table's creation date as "????", and time as
"0.00.00". Can anybodi tell me if I sould to seek for errors or it can be
ignored?
The system configuration is as following:
Client: Windows 95 OSR2
             Borland C++ Builder 3.01 Professional with BDE 4.5.
             ODBC driver ver. 6.30.0249
Server: Linux Slackware 96 (kernel has been upgraded to 2.0.35).
              PostgreSQL 6.3.2
And another question: where in the Internet can I get info about SQL in
addiion to the docs in C++ Builder 3.01 Professional, PostgreSQL 6.3.2, and
those available at http://www.insightdist.com (sort of SQL reference)?

Thank you, Mikhail.

ANGG wrote:

> Andrzej Szydlo wrote:
>
> >I'm trying to access PostgreSQL database from Delphi via ODBC.
>
> I'm trying to do so from C++ Builder 3.01 and have some questions which
> bother me.
> I have a Boolean field in a table. When I view the table in the Database
> Explorer the field is exposed as Char2, though behaves as boolean. Database
> Explorer also reveals table's creation date as "????", and time as
> "0.00.00". Can anybodi tell me if I sould to seek for errors or it can be
> ignored?
>

As far as the boolean being a char2, go into the driver's advanced options and
uncheck "Bools as Char".

Byron

Hello,

Thanks for your help.

I still cannot solve this problem. The real problem is that Delphi
doesn't send a BLOB to the database. In logs there is not even a
trace of any INSERT o UPDATE attempt. When images are already in the
table (inserted there in any other way) Delphi displays them
correctly.

I suspect the real cause is confused BDE or confused
Delphi. One of the BLOB field properties in the Delphi's Object
Inspector is "Size", which means the size of the BLOB portion stored
in a table directly. For the "lo" type the Size should be 0, I guess,
since it doesn't store any BLOB data directly in a table. Delphi
"sees" the lo (LongVarBinary) Size is 1 and when I change it to any
other value, it complains "Field 'img' is not of the expected type".
The Database Explorer doesn't show any "Size" property for lo fields,
the BLOB fields form other databases usually (haven't checked all
yet) have this property. Do you think it might cause the problem? If
so, how could I change it?

Any suggestions welcome.

Andrzej


On Tue, 18 Aug 1998 at 15:34:44 -0400, Byron Nikolaidis wrote:

>
> It seems that the error handling in the _SQLFetch routine was a little
> behind the times, which is confusing things even more and producing the
> "unrecognized return value from copy_and_convert...".
>
> I have fixed the error handling code so that it should hopefully return a
> better description of the problem.
>
> FYI, there are three possibilities that can cause the 0 to appear:
>
> 1. The large object couldn't be opened
> 2. The large object couldn't be read
> 3. Something is wrong/incompatible with the multiple GetData calls.
>
> I'm gonna send you the new driver with the better error handling to try in
> another mailing.
>
> Byron
>
>
> Andrzej Szydlo wrote:
>
> > Hello,
> >
> > I'm trying to access PostgreSQL database from Delphi via ODBC.
> > Everything works fast and reliably. The only problem are BLOBs,
> > images in this case.
> >
> > I created the "lo" type according to the FAQ:
> >
> >      create type lo (
> >      internallength=4,  externallength=10,
> >      input=int4in, output=int4out,
> >      default='',  passedbyvalue
> >      );
> >
> > and then created a table and an index:
> >
> > CREATE TABLE test_tab(nr int4, img lo);
> > CREATE UNIQUE INDEX test_inx ON test_tab (nr);
> >
> > I attached a DBImage control to the "img" field and DBEdit to "nr"
> >
> > When I insert "images" to the table manually, later  Delphi displays
> > it without any problem.
> >
> > When I try to insert any image from Dephi, the value "0" is inserted
> > instead of any real OID value, which causes:
> >
> >  "Unrecognized return value from copy_and_convert_field."
> >
> > on a select attempt.
> >
> > What can cause "0" to be inserted instead of any value, say 16648?
> > Have anyone ever had similar problem? How can it be solved?
> >
> > It seems that the problem is caused by the Delphi-ODBC interaction.
> > With Access everything works well.
> >
> > If the information I gave here is not precise enough or unclear,
> > please ask me for any extra details.
> >
> > I'd appreciate any suggestions pointing me to the right direction.

[...]

On Wed, 19 Aug 1998 at 15:17:36 -0400, Byron Nikolaidis wrote:

>
> But what did you get in your log file this time?  It should have a different
> message.

I'm sorry for not attaching it.

There are two cases. The first one I described in my previous
message. There's no activity logged then and Delphi displays "Invalid
BLOB length", nothing is written to tha table. It happens when I try
to insert a large image.

When I try to insert a relatively small image:
++++++++++
conn=43911292, query='declare SQL_CUR44066004 cursor for SELECT "img"
FROM "test_tab" WHERE "nr"=3' conn=43911292, query='fetch 100 in
SQL_CUR44066004'
    [ fetched 1 rows ]
    STATEMENT ERROR: func=SQLFetch, desc='', errnum=1, errmsg='Couldnt
    open large object for writing.'
++++++++++

Delphi displays:
"Read Error: Couldn't open large object for writing."

and only the "nr" field is written to the table.

( the table is:
CREATE TABLE test_tab(nr int4, img lo);
CREATE UNIQUE INDEX test_inx ON test_tab (nr); )

Andrzej