Solved

Some string converted with ADO (MS/SQL) in Delphi

Posted on 2004-08-09
12
568 Views
Last Modified: 2013-11-23
I have started using ADO instead of the Borland BDE for application written in Delphi 7 and accessing a MS.SQL 7.0 database.
However I have a strange behavior when access some string in the database that contains text with european accentuated letter and special characters.
The behavior is the following : when I run the application from within the Delphi development environment for debugging/testing it, no problem. All characters retrieved from the DB and stored are okay.
But if I run the delphi executable from a prompt, some characters are not displayed correctly.
0
Comment
Question by:LeTay
[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
  • 5
  • 2
  • 2
  • +1
12 Comments
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 11750972
Did you write a console application???
If your DB contains unicode characters then yes, Delphi has trouble handling those correctly. However, normal special characters should be displayed correctly, depending on the font used.
0
 

Author Comment

by:LeTay
ID: 11751404
No it is a GUI application
The problem looks for me - now that I am testing a little bit further - more complicated.
The summary is as follows :
I had an application running with the standard BDE-related components to access the MS.SQL 7.0 database.
I have problem with 2 db fields :
1. one is text field, that I used to store simple text
2. the second is image field, that I used to store large amount of bytes that I did not want the DB or the application DB interface to 'interpret' or 'manipulate'
This worked fine with the BDE.
Text fields contained text with special characters but were correctly stored and retrieved
Image fields contained in fact full content of external files (kind of 'attached' files)
Now when writing the new application with ADO, I need the same kind of behavior, but
1. the text field is 'a little bit' manipulated, ONLY IF, I run the application outside the Delphi environment
2. I get error messages at run time when storing the 'image' field. For this, in the application, I use the ftBlob type when the DB data is image and ADO issues an error : wrong type used so I don't know how to handle this (I have tried text as well with ftString, but the stored byte stream is truncated)
0
 

Author Comment

by:LeTay
ID: 11751479
Addendum : I just realised that the 'image' field is correctly stored in the DB with ADO component but when I read it back, I use the fieldbyname('columnname').asstring syntax and there the result is truncated...
0
Technology Partners: 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:LeTay
ID: 11751839
I think I should restate my problem as a question based on the goal I want to achieve with ADO (this is the important thing) in Delphi.
I want to define a database field in MS/SQL that can be very large (several megabytes) and I just want to store and read data in it, without having any of the underlaying processing to change anything to the data, as my application will handle it itself with large string data types...
So :
- what data type should I use in the database (I thought first of image that was working fine with BDE) ?
- how to store the data ?
- how to read it back ?
0
 
LVL 4

Expert Comment

by:Greg Rowland
ID: 11752182
We have a very similar situation where AutoCAD files are stored in SQL 2000, there are 57k records that comprise about 3gb in the database. One suggestion I would make is to create an individual Database for the large field data, and store you more typical row data in another database. In our solution this makes backups more flexible. The Image data gets backed up 6 times daily as the source files always reside separately in there native AutoCAD format anyway, the other row or Business data gets backed up hourly.

We have developed a custom data aware TOleContainer for storing an retrieving the images. It’s really pretty neat, the Ole container will actually accept about any data you want to drop into it “Word Docs, PDFs, Cad files etc… “

Where this was originally done in InterBase we are now also using SQL 2000, ADO ClientDataSets and using TClientBlobStream, LoadFromStream, SaveToStream for reading and writing the data.

You could use one of the data components OnChangeEvents on the row data to fire the read/write of your “Image Data” also using the TOleContainer to hold the data.
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 11752603
Writing data and reading it to blobfields (and the field is a blob field, I assume) requires you to stream the binary data to or from it. I've done it several years ago and it's not that difficult. It's just that I've forgotten exactly how to do this again. (Blame it on old age.) The field type should be of type TBlobField and by using LoadFromStream() and SaveToStream() you could read and write the contents to e.g. a memory stream. The memory stream could be filled or read again by using it's read/Write methods. Just don't forget to rewind it again. (Setting the Position property to 0.)
0
 

Author Comment

by:LeTay
ID: 11752616
Interesting your idea, surferjoe.
But this does not fix my problem.
My real question is concerning the type of data I have to defined in the DB (Here, MS/SQL, I think image is okay) and HOW precisely (code samples) I have to read and write it.
I should add this information : I use stored procedures for ALL DB accesses...
0
 
LVL 4

Expert Comment

by:Greg Rowland
ID: 11753285
procedure TForm1.ReadOLE;
var
  BlobStream: TClientBlobStream;
  s: pointer;
  a: array[0..10] of char;
begin
  with OleContainer1 do begin

    try
      s := @a;

      OldStreamFormat := True;

      BlobStream := TClientBlobStream.Create(( wwClientDataSet1.FieldByName('DRAWING') as TBlobField), bmRead);
      if (BlobStream.Read( s^, 1) = 1) then begin
        BlobStream.Seek( 0, 0);
        LoadFromStream( BlobStream);
        Refresh;
      end;
    finally

      BlobStream.Free;

    end;
  end;

end;

procedure TForm1.WriteOLE;
var
  BlobStream:TClientBlobStream;
begin
  with OleContainer1, wwClientDataSet1 do begin
    Edit;
    try
      OldStreamFormat := False;

      BlobStream := TClientBlobStream.Create( (FieldByName('DRAWING') as TBlobField), bmWrite);
      SaveToStream( BlobStream);
    finally
      BlobStream.Free;
    end;
  end;

end;

0
 

Author Comment

by:LeTay
ID: 11763993
Finally I found my way by myself
I defined the column in the MS/SQL table as 'image'
To stored the long string in it with a stored procedure where @Content was the input and defined as image,
I had to code the parameter setting in 2 steps like this :
   Parameters.CreateParameter('@Content',ftBlob,pdInput,MainMaxAttSize,'');
   Parameters.ParamByName('@Content').Value := MyLongString;
because doing like this :
   Parameters.CreateParameter('@Content',ftBlob,pdInput,MainMaxAttSize,MyLongString);
resulted in the storage of a string twice as long as MyLongString, each byte being replace by itself + 1 binary zero byte, tell me why !
Now to read it, a simple ftString was okay...
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 13404412
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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