?
Solved

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

Posted on 2003-12-07
15
Medium Priority
?
2,092 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

762 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