BLOB Update Error

[wjm4]

Whenever I try to update a Blob field using unidac I get a blob field is null error. I have TemporaryLobUpdates = false. I am using to Oracle Provider and unidac version 2.00.0.2 and have the parameter field in the query set to OraBlob and IN.

Code:
TStream* s = new TMemoryStream();
Report->SaveToStream(s,true,true);
updReptQ->ParamByName("REPTID")->Value = Report->Name;
s->Size; //This is a value greater than 0 thus stream has something in it.
TBlob * lob = new TBlob(false);
lob->LoadFromStream(s);
lob->Size; //This is a value greater than 0 thus stream has something in it.
updReptQ->ParamByName("DATA")->AsBlobRef = lob;
//updReptQ->ParamByName("DATA")->AsBlobRef->LoadFromStream(s); //This way of doing things does not work either
try{updReptQ->Execute();} catch(...){ ses->Rollback();} //Exception thrown that says can not update field to NULL

This is a critical piece of software and needs to be fixed quickly. Any help would be greatly appreciated. It would be hard to create a sample project to send since I am using a stream.

Thanks for the help.

[Plash]

If you are using SQL statement without RETURNING, you should set the TemporaryLobUpdate option to True.

With TemporaryLobUpdate=False, you should use a statement like the following:

Code: Select all

INSERT INTO MyTable VALUES (:REPTID, empty_blob())
RETURNING DATA INTO :DATA

[wjm4]

Thanks for the reply,

I tried what you suggested below and that did not throw an exception but it set the blob field to null in the database. I know that what I am storing is not null based on the s->Size statement (in first post). Everything is set up as it was in my first post except for the sql statment has changed to the following:

Code: Select all

UPDATE WCS_REPT SET DATA = empty_blob(), DESCR = DESCR 
WHERE REPTID = :REPTID
RETURNING DATA INTO :DATA

With ODAC I could use the BLOBLocator and directly set data. This does not seem to be an option with unidac.

Thanks for the help

[Plash]

Please try to remove the following lines:

Code: Select all

TBlob * lob = new TBlob(false); 
lob->LoadFromStream(s); 
lob->Size; //This is a value greater than 0 thus stream has something in it. 
updReptQ->ParamByName("DATA")->AsBlobRef = lob; 

and use the line that you have commented:

Code: Select all

updReptQ->ParamByName("DATA")->AsBlobRef->LoadFromStream(s);

[wjm4]

Thanks for the response

by changing this I get an ora error:
ORA-03120: two-task conversion routine: integer overflow

I know there is not a problem with oracle because code that I used with ODAC Works fine. I have included the DFM of that component so you can see how is is set up

Code: Select all

  object updReptQ: TUniQuery
    Connection = ses
    SQL.Strings = (
      'UPDATE WCS_REPT SET DATA = empty_blob(), DESCR = DESCR '
      'WHERE REPTID = :REPTID'
      'RETURNING DATA INTO :DATA')
    UniDirectional = True
    SpecificOptions.Strings = (
      'Oracle.SequenceMode=smInsert')
    Left = 72
    Top = 8
    ParamData = 
  end

Code: Select all

object ses: TUniConnection
    ProviderName = 'Oracle'
    SpecificOptions.Strings = (
      'Oracle.Direct=True')
    ConnectDialog = conDlg
    LoginPrompt = False
    OnError = sesError
    Left = 40
    Top = 40
  end

Code: Select all

	TStream* s = new TMemoryStream();
	Report->SaveToStream(s,true,true);
	updReptQ->ParamByName("REPTID")->Value = Report->Name;
	s->Size;
//	TBlob * lob = new TBlob(false);
//	lob->LoadFromStream(s);
//	lob->Size;
	updReptQ->ParamByName("DATA")->AsBlobRef->LoadFromStream(s);
	try{updReptQ->Execute();}  catch(...){ ses->Rollback();}

[Plash]

I see that you have not set TemporaryLobUpdate to False in the DFM. So your code cannot work. You can try two cases:

1. Set TemporaryLobUpdate=False, and leave the rest of the code as it is now.

2. Do not set TemporaryLobUpdate=False, but change the SQL statement to a statement without RETURNING.

[vga]

learing...