Can't update blob fields

Can't update blob fields

Postby 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.
gb
 
Posts: 42
Joined: Wed 05 Mar 2008 12:32

Postby 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
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby 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?
gb
 
Posts: 42
Joined: Wed 05 Mar 2008 12:32

Postby 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.
Plash
Devart Team
 
Posts: 2844
Joined: Wed 10 May 2006 07:09

Postby 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; i Buff[i] = i%256;

Mem->Write(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; i if ( Buff[i] != i%256 ) {
ShowMessage("Fail");
break;
}
}

qr->SQL->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

Postby 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.
gb
 
Posts: 42
Joined: Wed 05 Mar 2008 12:32


Return to dbExpress driver for Oracle