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).
Can't update blob fields
Can't update blob fields
Last edited by gb on Mon 06 Apr 2009 10:54, edited 1 time in total.
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.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
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.
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.
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.
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.