Solved

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

Posted on 2003-12-07
15
2,024 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 380 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi - replicating a form 8 65
Working with hours 3 46
Multiple image collision 13 69
how to update exe applicatio from internet ? 6 67
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

24 Experts available now in Live!

Get 1:1 Help Now