Solved

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

Posted on 2003-12-07
15
2,015 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
  • 8
  • 7
15 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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



0
 

Author Comment

by:molenjin
Comment Utility
..........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
Comment Utility
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
Comment Utility
>No, no... the solution must involve TStrings, not blobs
a long raw field is treated as blobfield in delphi
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:molenjin
Comment Utility
>>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
Comment Utility
.....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
Comment Utility
>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
Comment Utility
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
Comment Utility
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 380 total points
Comment Utility
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
Comment Utility
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
Comment Utility
well, glad you got it work ;-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now