Can't update blob fields

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
gb
Posts: 42
Joined: Wed 05 Mar 2008 12:32

Can't update blob fields

Post by gb » Fri 03 Apr 2009 11:56

I've an Oracle table like

CREATE TABLE SOMETABLE (
SOMEKEY NUMBER(*, 0) NOT NULL,
SOMEBINARY BLOB
)

and a
TSQLQuery *qr = ...;

qr->SQL->Text =
"UPDATE SomeTable SET SomeBinary=:SomeBinary WHERE SomeKey=123"; // Yes, that record *is* there!

const String &FileName = "c:\SomeFile.jpg";
qr->ParamByName("SomeBinary")->LoadFromFile(FileName,ftBlob);
qr->ExecSQL();
// 1. Do not change it's content even that qr->RowsAffected == 1

std::auto_ptrFile(new TFileStream(FileName,Sysutils::fmOpenRead));
qr->ParamByName("SomeBinary")->LoadFromStream(File.get(),ftBlob);
qr->ExecSQL();
// 2. Do not change it's content even that qr->RowsAffected == 1

std::auto_ptrStr(new TStringStream("")); Str->CopyFrom(File.get(),0);
qr->ParamByName("SomeBinary")->AsBlob = Str->DataString;
qr->ExecSQL();
// 3. Do not change it's content even that qr->RowsAffected == 1

std::auto_ptrMem(new TMemoryStream()); Mem->CopyFrom(File.get(),0);
qr->Params->Clear();
qr->Params->CreateParam(ftBlob,"SomeBinary",ptInput);
qr->Params->ParamByName("SomeBinary")->SetBlobData(Mem->Memory,Mem->Size);
qr->ExecSQL();
// 4. Do not change it's content even that qr->RowsAffected == 1

I've tried 4 different ways of updating that blob field and none of them worked. Any ideas?

Tried only on the latest version 4.40.15 but *should* be the same on any older ones. C++Builder: 2007 R2 Version 11.0.2987.1077.

Thanks

[Edit: For the sake of completeness I've added a fourth method).
Last edited by gb on Mon 06 Apr 2009 10:54, edited 1 time in total.

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Mon 06 Apr 2009 07:42

You should use the following SQL to update a BLOB field:

Code: Select all

UPDATE SomeTable SET SomeBinary=empty_blob() WHERE SomeKey=123
RETURNING SomeBinary INTO :SomeBinary

gb
Posts: 42
Joined: Wed 05 Mar 2008 12:32

Post by gb » Mon 06 Apr 2009 09:02

Plash wrote:You should use the following SQL to update a BLOB field:

Code: Select all

UPDATE SomeTable SET SomeBinary=empty_blob() WHERE SomeKey=123
RETURNING SomeBinary INTO :SomeBinary
I beg to differ, but it just didn't work either.

By the way, what was wrong with the first syntax, is it not standard?

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Wed 08 Apr 2009 08:52

If you cannot update BLOB field after you change the SQL property, please send to dbx*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.

Standard syntax is one that I have shown. A LOB locator is required to write LOB data. The statement with RETURNING creates an empty BLOB and returns its locator for writing data.

gb
Posts: 42
Joined: Wed 05 Mar 2008 12:32

Post by gb » Wed 08 Apr 2009 15:54

Ok, I've just sent an email with a sample. If anyone needs to follow this I'm also posting it here for convenience.

std::auto_ptrSQL(new TSQLConnection(NULL));
SQL->ConnectionName = "Oracle Direct by Core Lab";
SQL->DriverName = "Oracle Direct by Core Lab";
SQL->LoginPrompt = false;
SQL->LoadParamsOnConnect = false;
SQL->LibraryName = "dbexpoda40.dll";
SQL->GetDriverFunc = "getSQLDriverORADirect";
SQL->VendorLib = "dbexpoda40.dll";
SQL->Params->Text = "BlobSize=-1\n"; // ...
// or just set in design time the proper ConnectionName to get all this params properly defined

SQL->Params->Values["DataBase" ] = "ip:port:sid";
SQL->Params->Values["User_Name"] = edUser->Text;
SQL->Params->Values["Password" ] = edPass->Text;
SQL->Open();

std::auto_ptrqr(new TSQLQuery(NULL));
qr->SQLConnection = SQL.get();
qr->SQL->Text = "CREATE TABLE SomeTable (SomeKey NUMBER(*, 0) NOT NULL, SomeBinary BLOB)";
qr->ExecSQL();

qr->SQL->Text = "INSERT INTO SomeTable (SomeKey) VALUES (:SomeKey)";
qr->ParamByName("SomeKey")->AsInteger = 1;
qr->ExecSQL();

qr->SQL->Text = "UPDATE SomeTable SET SomeBinary=empty_blob() WHERE SomeKey=1 "
"RETURNING SomeBinary INTO :SomeBinary";

std::auto_ptrMem(new TMemoryStream());
BYTE Buff[20*1024] = { 0 }; // 20KB
for ( unsigned int i=0; iWrite(Buff,sizeof(Buff));
Mem->Position = 0;

qr->Params->Clear();
qr->Params->CreateParam(ftBlob,"SomeBinary",ptInput)->SetBlobData(Mem->Memory,Mem->Size);
qr->ExecSQL();

qr->SQL->Text = "SELECT SomeBinary FROM SomeTable WHERE SomeKey=1";
qr->Open();

{
std::auto_ptrblob(qr->CreateBlobStream(qr->FieldByName("SomeBinary"),bmRead));
if ( !blob->Size )
ShowMessage("Empty");
Mem->Clear(); // Empty previous Stream content
Mem->CopyFrom(blob.get(),0); // If Count is 0, CopyFrom sets Source position to 0
Mem->Position = 0;
}

qr->Close();

memset(Buff,0,sizeof(Buff)); //Reset internal buffer
Mem->ReadBuffer(Buff,Mem->Size); // Fill it
for ( unsigned int i=0; iSQL->Text = "DROP TABLE SomeTable";
qr->ExecSQL();


Notice that if blobs are already there (hint: done in the past through BDE), I'm able to properly pick them. However, updated them (or Insert) just won't work.

Thank you.

gb
Posts: 42
Joined: Wed 05 Mar 2008 12:32

Post by gb » Thu 09 Apr 2009 08:51

Ok, I think I solved it for now.

The first thing is that I was wrongly using "ftBlob" instead of the correct "ftOraBlob".

The second is that by some reason SetBlobData does not seem to be working properly (thought LoadFromStream will work just fine).

Edit: Ok, just got an email reply from support that comes inline with the above... "(...) change ftBlob to ftOraBlob in your example. You also cannot use the SetBlobData method because it sets DataType of the parameter back to ftBlob. Use the LoadFromStream method instead:
qr->Params->CreateParam(ftOraBlob,"SomeBinary",ptInput)->LoadFromStream(Mem, ftOraBlob);"

Thank you.

Post Reply