Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Store TMemo.Lines --> Long Oracle field, using TOracleQuery

Posted on 2003-12-07
15
Medium Priority
?
2,135 Views
Last Modified: 2011-10-03

Hi,

I am using Delphi 5 and Direct Oracle Access (DOA).

I want to store multiline text from TMemo component into a Long Oracle field, but I don't know how... something with variables, but....

The solution must be based on TOracleQuery, not TOracleDataSet.

Cheers,

Miroslav Olenjin
Perth, Western Australia
http://www.delphipages.com/resume/resume.cfm?ID=641

0
Comment
Question by:molenjin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9894909
have no doa,
but maybe this may work

var
  ms : TMemoryStream;
begin
  ms := TMemoryStream.Create;
  try
    memo1.savetostream(ms);
    ms.position := 0;
    DataSet.Edit;  //or Insert
    TBlobField(Dataset.FieldByName('LongRawFieldName')).LoadFromStream(ms);
    DataSet.Post;
  finally
    ms.free;
  end;
end;

meikl ;-)
0
 

Author Comment

by:molenjin
ID: 9895044
Sorry kretzschmar, no way....

I will give you guys an example from DOA's manual... it may help, I dunno...

~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Example - Long & Long Raw

These two Oracle data types can get really big (up to 2GB). Therefore, a few restrictions have been made regarding long values:

Unlike other query-fields, long values are not buffered. Every time the Field function of a long value is called, the value is fetched from the database. On the other hand, as long as you do not call the field function, the value is not fetched.

Whenever a query detects a long column in the select-list, only 1 record is buffered, regardless of the query's ReadBuffer property.

Output variables of a long data type are not really supported. Use a select statement whenever possible. If you must use a long output variable, the maximum size it can contain is equal to the size it has on input, so it is in fact an input/output variable. If a longer value is assigned to the variable, you will get an "ORA-06502: PL/SQL: numeric or value error", so you'll have to be sure that the allocated size is enough.

Long Raws are handled as strings or zero based variant arrays of bytes. To access the byte-array with the highest possible performance, make use of the VarArrayLock and VarArrayUnlock functions. To check for the size of the value, make use of VarArrayHighBound. The next example fetches a picture, determines the size, and saves the binary data to disk:

// SelectEmpPictureQuery.SQL =
// select picture from emp
// where empno = :empno
try
  with SelectEmpPictureQuery do
  begin
    SetVariable('EMPNO', Emp.EmpNo);
    Execute;
    Picture := Field('PICTURE');
    Size    := VarArrayHighBound(Picture, 1) + 1;
    Ptr     := VarArrayLock(Picture);
    WriteFile('Employee.gif', Ptr^, Size);
    VarArrayUnlock(Picture);
  end;
except
  on E:EOracleError do ShowMessage(E.Message);
end;


0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9895084
hmm,

i remember me, long time ago,
directly from the doa-forum
http://www.allroundautomations.com/doa.html


var
  LOB: TLOBLocator;    
  Buffer: array[0..99] of Byte;
begin  
  // insert into lobtable (id, lobcolumn) values (:id, empty_blob())  
  //   returning lobcolumn into :lobcolumn  
  with LOBQuery do  begin    SetVariable('id', 1);    // Create a new BLOB (initially Null)    
    LOB := TLOBLocator.Create(Session, otBLOB);    // Assign it to the returning variable    
    SetComplexVariable('lobcolumn', LOB);    
    Execute;    // After the insert, use the LOB Locator to write the data    
    LOB.Write(Buffer, 100);    
    LOB.Free;  
  end;
end;

meikl ;-)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:molenjin
ID: 9895193
No, no... the solution must involve TStrings, not blobs... must look "regular", not "dirty"..



0
 

Author Comment

by:molenjin
ID: 9895211
..........The regular solution should go something like this:

1. use "update" SQL command

2. use variables

3. use TStrings.Text property, I believe

4. set variable

5. Execute
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9895219
after a bit recherche in this forum:
-> my first sample should work with an oracledataset

the other samples are for OracleQuery with paramters

molenjin,

give my first sample a try

meikl ;-)



0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9895222
>No, no... the solution must involve TStrings, not blobs
a long raw field is treated as blobfield in delphi
0
 

Author Comment

by:molenjin
ID: 9895280
>>TBlobField(Dataset.FieldByName('LongRawFieldName')).LoadFromStream(ms);

There is no Dataset, kretzschmar. Sorry. You have TOracleSession and TOracleQuery, nothing else.

Yeah, I believe it's hard to make a proper code without DOA installed on the machine...

All the Best - Miroslav
0
 

Author Comment

by:molenjin
ID: 9895291
.....Now.... I put all my points in this, because it is IMPORTANT and I need it ASAP.

I am going home now and tomorrow morning, when come on work, I will decide which solution should get 380 points...

Cheers, Miroslav
Perth - Western Australia

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9895318
>There is no Dataset, kretzschmar. Sorry.

replace Dataset with OracleDataSet1 or what you name you have instead -> Dataset was meant as TDataset (or descendend) placeholder

meikl ;-)
0
 

Author Comment

by:molenjin
ID: 9900906
THERE IS NO DATASET OF ANY KIND, JUST TORACLESESSION AND TORACLEQUERY. PLEASE IF YOU HAVE NO DOA INSTALLED THEN DON'T SIMPLY GUESS LIKE THAT.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9902052
for toraclequery see my second comment,
just reverse it (because its for reading)

if you don't want any help,
then go on with your last comment

meikl ;-)
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 1140 total points
ID: 9902311
well, try this

var
  ms : TMemoryStream;
begin
  ms := TMemoryStream.Create;
  try
    memo1.lines.savetostream(ms);
    ms.position := 0;
    Query.SetLongVariable('FieldName', ms.memory, ms.size);
    Query.Execute;
  finally
    ms.free;
  end;
end;

meikl ;-)
0
 

Author Comment

by:molenjin
ID: 9903360
Ok, kretzschmar, I will accept your last post. (I can see you are a nice guy ;-)) Your solution is very close to that code we did today in the office - [ plus we put a SQL statement on the beginning, which is very important.]

Best wishes from Perth, Western Australia -
Miroslav
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9903508
well, glad you got it work ;-)
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question