Solved

easy SQL/write to file

Posted on 2002-07-25
19
148 Views
Last Modified: 2010-04-04
Hi,

I am compeletly new to delphi, so need detailed instructions.

My problem:
I have a database (assume already linked - please advice which link method is best). I want the program to select (automaticlly) say 3 fields of each record and write them to a file (format: new line after each data item say). The file should be in txt format.

Again ... I stress, please explain in great detail as I have minimal pascal knowledge and NO delphi knowledge. My knowledge of SQL is enough that you dont need to explain why you are using that line of code.

Thank you to everyone in advance

P.S. I am using delphi 5
0
Comment
Question by:dech
  • 11
  • 8
19 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility


var
  fs : TFileStream;
  Buffer : String;
begin
  query1.sql.text := 'Select f1, f2, f3 from ATable';  //supply statement
  query1.Open;  //open query
  query1.First;  //ensure that you're at begin
  fs := TFileStream.Create('AFileName.txt',fmCreate); //create a file
  while not query1.eof do  //while not on end loop
  begin
    //create one line
    Buffer := query1.FieldByName('f1').AsString+','+ //commadelimited
              query1.FieldByName('f2').AsString+','+
              query1.FieldByName('f3').AsString+#13#10;
    fs.write(buffer,length(buffer));  //write line to file
  end;  //end loop
  fs.free;  //close file
  query1.close; //close query
end;  //done

just from head,
not tested, typos and
syntax-errors possible

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
oops missed one line

replace this
 
 end;  //end loop
 
with
 
   query1.next; //next record
 end;  //end loop
 
meikl ;-)
0
 
LVL 2

Author Comment

by:dech
Comment Utility
Sorry, but I need more directions than this.

Where does all this go?
What tools do I use?

Details!

Thanks
Dech
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>Where does all this go?
maybe on a button-click event

>What tools do I use?
delphi of course

well ok,
-start delphi
-file|new application
-drop a tquery->query1 on the form and a tbutton->button1
-select the query1 databasename (objectinspector)
-select the button and double-click in the objectinspector on the eventtab the onclick-entry
-paste the code above into the created method-procedure
-replace the select-statement to your needs
-run the app

i can give you a working sample this evening (in ~10 hours), currently i have no delphi available

hope this helps

meikl ;-)
0
 
LVL 2

Author Comment

by:dech
Comment Utility
What about the database? I am currently learning it through the help file using TTable, TDatasource, TDBGrid.

When/where do I link the query to the database. You may have already mentioned before, but since this is my first day ... I have a god given right to sound stupid :)

Thanks,
dech
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
:-)) well, every start from scratch is hard

the link to the database is here
-select the query1 databasename (objectinspector)

more explained
-select the query-object on your form
-go to objectinspector-properties tab
-select at the property databasename the alias, you have
  defined for your database with the bde-admin tool
   (you could also play with the dbdemos-alias)

of course instead of a tquery, you can also use a ttable,
my code above must then be slight different

hope this helps

meikl ;-)

0
 
LVL 2

Author Comment

by:dech
Comment Utility
Ok ... which is better?

Also ... a few questions on the code.
------------------------------------------

fs := TFileStream.Create('AFileName.txt',fmCreate); //create a file

Does the file get created in the same directory as my executable?

------------------------------------------

query1.sql.text := 'Select f1, f2, f3 from ATable';  //supply statement

ATable - Is this the grid name in which the data is shown (ie DBGrid1) or the table name (ie Table1).

Thanks,
Dech


0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>Ok ... which is better?
depends on your needs

>Does the file get created in the same directory as my executable?
yes, if AFileName does not contain drive and/or path-informations like 'c:\myfile.txt'


>ATable - Is this the grid name in which the data is shown
>(ie DBGrid1) or the table name (ie Table1).
no and no, ATable is the physical name of the table in the database

hope this helps

meikl ;-)


0
 
LVL 2

Author Comment

by:dech
Comment Utility
Ofcourse to the TableName answer - duh - simple sql stuff ... i forgot that part of it wasnt delphi stuff :)

I will try your code and keep you informed ... live working sample would be great.

Also, in the tutorial given within the delphi help files, they have used a rather strange method to save stuff to a txt file. Is this the kinda of thing you was thinking of when you said i could use TTable as well?

procedure TForm1.Save1Click(Sender: TObject);

var
  i: integer;
begin
  SaveDialog1.Title := Format('Save info for %s', [DBText1.Field.AsString]);
  if SaveDialog1.Execute then
  begin
    with TStringList.Create do
    try
      Add(Format('Facts on the %s', [DBText1.Field.AsString]));
      Add(#13#10);
      for i := 1 to DBGrid1.FieldCount-3 do
      Add(Format('%s : %s',
        [DBGrid1.Fields[i].FieldName,
        DBGrid1.Fields[i].AsString]));

      Add(Format(#13#10+'%s'+#13#10,[DBMemo1.Text]));
      SaveToFile(SaveDialog1.FileName);
    finally
      Free;
    end;
  end;
end;

Thanks,
Dech
0
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

 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
no,
the sample above saves only
one record (the current) and
this in multiline.

for a table i would use something like this

var
 fs : TFileStream;
 Buffer : String;
 bm : TBookMark;
begin
 bm := table1.getBookmark; //remember current record
 table1.disablecontrols; //disable visual controls, save time
 table1.First;  //ensure that you're at begin
 fs := TFileStream.Create('AFileName.txt',fmCreate); //create a file
 while not table1.eof do  //while not on end loop
 begin
   //create one line
   Buffer := table1.FieldByName('f1').AsString+','+ //commadelimited
             table1.FieldByName('f2').AsString+','+
             table1.FieldByName('f3').AsString+#13#10;
   fs.write(buffer,length(buffer));  //write line to file
   table1.next; //next record
 end;  //end loop
 fs.free;  //close file
 table1.gotobookmark(bm); //go back to currentrecord
 table1.freebookmark(bm); //release Bookmark
 table1.enablecontrols; //enable visual controls
end;  //done

again, just from head

hope this helps

meikl ;-)
0
 
LVL 2

Author Comment

by:dech
Comment Utility
I used the above code.

I cant tell whether it is working or not. Strange characters are appearing ... Not sure if it is due to me language translator i have now in the background. But it Shouldnt translate normal english words.

P.S. I just noticed the exe file in the same directory ... didnt even know it had been created. Coming from VB, thats impressly fast.

P.S.S. What support files does the exe file need, if any?

Thanks,
dech
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>Strange characters are appearing ...

this may caused by the line
fs.write(buffer,length(buffer));  //write line to file

  because it was just from head,
  there may i have a mistake done,
  but can't check it now

try following replacements

fs.writebuffer(buffer,length(buffer));
or
fs.write(PChar(buffer),length(buffer));
or
fs.writebuffer(PChar(buffer),length(buffer));

maybe you take a look into the delphihelp about
tstream.write and tstream.writebuffer

your translator can not affect the result

>I just noticed the exe file in the same directory ...
>didnt even know it had been created. Coming from VB,
>thats impressly fast.

if you run your app within the ide,
the exe is compiled by the ide and then executed,
delphi didn't interpreting as vb

>What support files does the exe file need, if any?

for a simple exe, without databaseaccess,
nothing else as the exe-file is needed.

with database accessing via bde,
you must deploy the whole bde,
your database and the exe.

with database accessing via ado
you must ensure that
the mdac-objects from microsoft
are installed or deploy it with
your database and the exe.


with database accessing with
native third-party components
nothing else than the exe and
your database must deployed

with database in general, depending which database is used,
there must be the database-clientsoftware installed first,
mostly for server-based databases

well, i will provide this evening,
a checked working sample based
on the code above

additional i have a simple interactive
sql-export-utility ready at home,
i will proide the source here then

meikl ;-)
0
 
LVL 2

Author Comment

by:dech
Comment Utility
Could it be because I am using the wrong field names?

The database in the tutorial is in a format i am not familiar with and i presummed that it uses the same name as the caption (But some captions has space).

fs.writebuffer(buffer,length(buffer));
or
fs.write(PChar(buffer),length(buffer));
or
fs.writebuffer(PChar(buffer),length(buffer));

first one worked the same as the original code. The other two failed to compile. The help file wasnt much use, it only gave an example of use.

P.S. Would it be possible for you to use both the SQL and table method within your sample. This way I can compare and understand their difference much better.

Thank you so much for your help tonight (its night here - only 2pm in Germany?), hope we can wrap this up tomorrow.

Dech.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
from where are you from?
well here is now early afternoon

>Would it be possible for you to
>use both the SQL and table method within your sample.
sure, no problem

very sorry,
that i can't validate myself
before this evening here

go asleep,
i'm sure if you wake up tomorrow,
i've posted tested samples here.

if you leave your email-address,
then i can send you additional
the project files, for the samples

waiting for coming evening here ...

meikl ;-)
0
 
LVL 2

Author Comment

by:dech
Comment Utility
georgeuk@hotmail.com

Good night :)
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 300 total points
Comment Utility
well ok,

----here the tested sample

unit export_file_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, Grids, DBGrids, Db, DBTables, DBCtrls;

type
  TForm1 = class(TForm)
    Table1: TTable;
    Query1: TQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Panel1: TPanel;
    Button1: TButton;
    Edit1: TEdit;
    Label1: TLabel;
    SaveDialog1: TSaveDialog;
    CheckBox1: TCheckBox;
    DBNavigator1: TDBNavigator;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    procedure do_exportfile(ADataset : TDataset);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.FormCreate(Sender: TObject);
begin
  //Initialize Databasename->simple here, we are working with paradox
  table1.DatabaseName := ExtractFilepath(Application.Exename);
  query1.DatabaseName := ExtractFilepath(Application.Exename);
  //Supply the tablename for the table
  table1.TableName := 'NickNames.DB';
  table1.Open;
  //adjust the initialdirectory of the savdialog
  savedialog1.InitialDir := ExtractFilepath(Application.Exename);
end;

//the exportmethod
procedure TForm1.do_exportfile(ADataset : TDataset);
var
  fs : TFileStream;
  bm : TBookmark;
  Buffer : String;
  i : Integer;
begin
  If assigned(ADataset) then //do we have a valid Dataset
  begin
    If Not (ADataset.Active) then  //is it open
      ADataset.Open;
    ADataset.DisableControls;
    bm := ADataset.GetBookMark;  //remember position
    ADataSet.First; //Ensure, that you are on the first record
    try
      If savedialog1.execute then  //let input the FileName
      begin
        fs := TFileStream.Create(savedialog1.FileName,fmCreate);  //create file
        try
          while not ADataset.Eof do  //loop through all records
          begin
            Buffer := '';  //Clear Buffer
            for i := 0 to ADataset.FieldCount -1 do  //we don't know the fieldamount at designtime
              buffer := buffer + ADataset.Fields[i].AsString + ';';
            delete(buffer,length(buffer),1); //delete the last ;-sign
            buffer := buffer + #13#10; //add return and linefeed
            fs.WriteBuffer(Buffer[1],length(buffer));  //write to file, starting on char 1
            ADataset.Next; //next record;
          end;
        finally
          fs.free;
        end;
      end;
    finally
      ADataset.GotoBookmark(bm);  //go back to current record
      ADataset.EnableControls;
    end;
  end;
end;

//our click event
procedure TForm1.Button1Click(Sender: TObject);
begin
  if checkbox1.checked then
  begin
    //export query
    query1.close; //close
    query1.SQL.text := edit1.text; //supply maybe changed sql-statement
    do_exportfile(query1);  //do the export
  end
  else
    //export table
    do_exportfile(table1);
end;

end.

---end tested sample

i send you now the project files
and the exe of the simple sql-exporter,
(the source is already archived somewhere,
must first search for it, was three years ago)

hope this helps

meikl ;-)
0
 
LVL 2

Author Comment

by:dech
Comment Utility
Thanks,

Your code is working great.

I have a few last questions to ask.

Is delphi case sensitive? You have sometimes used Buffer and sometimes just buffer. Does it matter?

Using only sql, I am guessing i still need the TTable component ontop of the Query component ... is that right?

"query1.close; //close" Why do we need to close the query before it is used?

One last question - what are the naming conventions in delphi? Like say, do I use edSQL for edit1 where ed stands for edit component. Or say frmExport with frm standing for form?

Thank you very much for all your help ... I have a feeling we will be speaking again after this question. This is just the start of the project.

Dech

0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>Is delphi case sensitive? You have sometimes used Buffer
>and sometimes just buffer. Does it matter?
no, buffer<>Buffer<>BUFFER means all the same variable

>Using only sql, I am guessing i still need the TTable
>component ontop of the Query component ... is that right?
you can also work only with querys, a ttable is not required

>query1.close; //close" Why do we need to close the query
>before it is used?
the sql.text property can only be set on a closed query at runtime, thats why i close the query first

>One last question - what are the naming conventions in
>delphi? Like say, do I use edSQL for edit1 where ed
>stands for edit component. Or say frmExport with frm
>standing for form?
there are no explicit conventions, but you can use conventions for yourself. conventions make your code more readable

>I have a feeling we will be speaking again after this
>question. This is just the start of the project.
no problem :-)

meikl ;-)
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
ps. don't forget the grading ;-)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

12 Experts available now in Live!

Get 1:1 Help Now