Solved

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

Posted on 2004-08-09
12
563 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
  • 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
 

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:SurferJoe
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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:SurferJoe
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

18 Experts available now in Live!

Get 1:1 Help Now