Devart Forums

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

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

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?

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.

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.

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.