Data select problem using ADO.

[mikeporter]

The problem I'm having is inserting (and then getting back) blobs from an Oracle database. The communications medium between LV and Oracle is ADO.

Due security constraints on the main datatables (the default operator can insert data but can't update or delete data) the blob data has to take a somewhat circuitous route. The blob data is first written into a temporary table that the default operator does have update permissions on.

This is needed because the AddNew ADO method (which by the way is fixed with V7 (yea!)) doesn't insert blobs all at once. It breaks them up into chunks--which from the DBMS' point of view makes it look like the operator is trying to update the table, which he isn't allowed to do.

Once the blob is in the
temporary table, it get's inserted into the main datatable using a subquery--which does insert it all in one go. This part of the procedure works well.

The problem is getting the data back out of the DBMS. If on the insert side I write the blob data to a file, read the file into an ADO stream and (finally) insert the stream into the database, everything works fine and I can fetch the blob data in any way I might care to. If however, I get the blob data into the stream using the stream Write method (thus bypassing the file write) the data goes in fine but if I try to read it back using a select statement incorporating a "where" clause, nothing comes back. The only way to get the data out is to read back the entire table.

Any ideas would be much appreciated...

Mike...

[Cult of Nurse's]

LoadFromFile sets the current position to zero after writing the stream.
WriteStream leaves the current posistion at EOS. Maybe you have to set
the position back to zero after WriteStream. Just a guess...




mikeporter wrote:
> The problem I'm having is inserting (and then getting back) blobs from
> an Oracle database. The communications medium between LV and Oracle is
> ADO.
>
> Due security constraints on the main datatables (the default operator
> can insert data but can't update or delete data) the blob data has to
> take a somewhat circuitous route. The blob data is first written into
> a temporary table that the default operator does have update
> permissions on.
>
> This is needed because the AddNew ADO method (which by the way is
> fixed with
V7 (yea!)) doesn't insert blobs all at once. It breaks them
> up into chunks--which from the DBMS' point of view makes it look like
> the operator is trying to update the table, which he isn't allowed to
> do.
>
> Once the blob is in the temporary table, it get's inserted into the
> main datatable using a subquery--which does insert it all in one go.
> This part of the procedure works well.
>
> The problem is getting the data back out of the DBMS. If on the insert
> side I write the blob data to a file, read the file into an ADO stream
> and (finally) insert the stream into the database, everything works
> fine and I can fetch the blob data in any way I might care to. If
> however, I get the blob data into the stream using the stream Write
> method (thus bypassing the file write) the data goes in fine but if I
> try to read it back using a select statement incorporating a "where"
> clause, nothing comes back. The only way to get the data out is to
> read back the entire table.
>
> A
ny ideas would be much appreciated...
>
> Mike...

[mikeporter]

Thanks! That did the trick. Apparently data stored in this way incorporates the position value ar part of the data in some way. It seems strange though that this problem would only show up when doing queries with a WHERE clause.

To quote Alice: "curiouser and curiouser..."

Mike...