Solved

Using a MyBase XML DB instead of a Paradox DB

Posted on 2001-08-15
10
1,198 Views
Last Modified: 2010-04-06
I have a simple paradox database with three tables in a master/detail relationship. I would like to use the MyBase XML facility in Delphi 6 instead of Paradox (which means I no longer need to install it - yay!).

The database is unlikely to ever get big enough that the in-memory nature of the XML tables will be a problem (or more to the point, the capacity of machines will grow much faster than the database ever will!).

What I am after is how to take this existing DB, which is acccessed by SQL statements to do all the necessary operations on it, and convert it to using TClientDatasets instead of the paradox tables.

In particular, I have SQL statements that creates a table, adds a record to it, deletes a record etc. I would like to be able to do all of this in a way that is similar, if not the same, as I currently do.

For instance, here is my code for creating one of the tables:

// Create the styles table
  if not FileExists(DataTableLocation+'Styles.db') then
    begin
      with TQuery.Create(Nil) do
        try
          SQL.add(format('CREATE TABLE "%sStyles.db"', [DataTableLocation]));
          SQL.add('(');
          SQL.add('CUSTOMERID integer,');
          SQL.add('STYLEDATE date,');
          SQL.add('STYLEIMAGEFILE char(50),');
          SQL.add('PRIMARY KEY (CUSTOMERID, STYLEDATE)');
          SQL.add(')');

          ExecSQL;
        finally
          Free;
        end;
    end;

I have had a look at the MyBaseExplorer demo, but it doesn't really go where I would like to go.

Cheers,

Raymond.
0
Comment
Question by:rwilson032697
  • 6
  • 3
10 Comments
 
LVL 12

Author Comment

by:rwilson032697
ID: 6391228
OK - A couple more details...

Each paradox table has a query used to acess it, and a datasource which references that query as its dataset. All the data aware fields then reference the datasource. Ideally I would like the TQuery to reference an XML file rather than the paradox DB file (through a TClientDataSet?????)

Cheers,

Raymond.
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6391253
Hi Ray,

I'm not an expert on this sort of thing yet, but I added XML support to my Database Manager recently and I just used a TClientDataSet to do the work.  Creating the XML file is a little harder than the old create table method, but I can definately show you how to do it if you like.

We use XML now so our clients can send us copies of their tables.  My app will create an XML table out of any database, and then at the other end, we just import it back into our AS/400 library.

If you use an XML database, you can use all the traditional DB aware controls the same way as you would with any other database.

Does this help, or am I just waffling??  Mmm.  Waffles :)

Cheers mate,

Stu.
0
 
LVL 12

Author Comment

by:rwilson032697
ID: 6391345
Stu,

Looking at my queries I can see how I might create the initial XML file field structure without using a TQuery. Each field in the query has a TField created for it in the DataModule, so I could just run through, or assign these fields to the TClientDataSet to set up the fields (does this sound feasible). At least I could create these files without using the SQL Create command as I have the information in the TField collection.

I have seen how the MyBaseExplorer demo uses a DataSource referencing a TClientDataset to do its thing with data aware controls.

It would be nice to leave the current SQL infrastructure in place (ie: I'd like to treat the Paradox --> XML storage chnge as a change in the storage layer rather than having this affect higher layers of the software).

Cheers,

Raymond.
0
 
LVL 12

Author Comment

by:rwilson032697
ID: 6430419
No further suggestions, anyone? Even just an example of using a TQuery with a TClientDataset would be nice!

Cheers,

Raymond.
0
 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 6430484
Hi Ray,

I'm sorry!  I plain forgot about this question.  I've been as busy as a one legged man in an arse kicking contest!!

This is the code I use to export a database to an XML file.  It first creates the structure, then exports the data.

I dont know if this is what you are looking for or not, but here goes anyway:

var
  NewFilename: String;
  Count, Row: Integer;
begin
  NewFilename := '';
  CancelOperation := false;
  with SaveDialog do
    begin
      Title := 'Specify A Filename';
      case TMenuItem(Sender).Tag of
        0: begin
             Filter := 'XML Files|*.XML';
             DefaultExt := 'XML';
           end;
        1: begin
             Filter := 'Client Dataset Files|*.CDS';
             DefaultExt := 'CDS';
           end;
      end; {case}
    if Execute then
      NewFilename := Filename;
  end; {SaveDialog}

  if NewFilename = '' then
     Exit;
  if Pos('.', NewFilename) = 0 then
    case TMenuItem(Sender).Tag of
      0: NewFilename := ChangeFileExt(NewFileName, '.XML');
      1: NewFilename := ChangeFileExt(NewFileName, '.CDS');
    end; {case}

  ClientDataSet.FieldDefs.Clear;
  for Count := 0 to DataSource.DataSet.FieldCount -1 do
    begin
      with ClientDataSet.FieldDefs.AddFieldDef do
        begin
          Name := DataSource.DataSet.Fields[Count].DisplayName;
          DataType := DataSource.DataSet.Fields[Count].DataType;
          Size := DataSource.DataSet.Fields[Count].Size;
        end;
    end;

  ClientDataSet.CreateDataSet;
  case TMenuItem(Sender).Tag of
    0: ClientDataset.SaveToFile(NewFileName, dfXML);
    1: ClientDataset.SaveToFile(NewFileName, dfBinary);
  end; {case}
  ClientDataSet.Active := false;
  ClientDataSet.FileName := NewFilename;

  DataGrid.DataSource := nil;
  Row := 0;
  try
    ClientDataset.Active := true;
    with Datasource.DataSet do
      begin
        First;
        while (not EOF) and (not FCancelOperation) do
          begin
            ClientDataSet.Append;
            for Count := 0 to FieldCount -1 do
              ClientDataSet.Fields[Count].Value := Fields[Count].Value;
            Inc(Row);
            MainForm.Status.Panels[1].Text :=
              Format('Exporting to XML Document.  %3.1f%s complete '+
                '[record %d of %d]', [Row/RecordCount*100,
                '%', Row, RecordCount]);
            Application.ProcessMessages;
            ClientDataSet.Post;
            Next;
          end;
      end;

    if not FCancelOperation then
      begin
        ResultsREdit.Lines.Add('');
        ResultsREdit.Lines.Add('Finished exporting to ');
        case TMenuItem(Sender).Tag of
          0: ResultsREdit.Lines.Add('XML Document');
          1: ResultsREdit.Lines.Add('Client Dataset File');
        end; {case}
      end
    else
      begin
        ResultsREdit.Lines.Add('');
        ResultsREdit.Lines.Add('Cancelled Export');
      end;

    CancelOperation := false;
    MainForm.Status.Panels[1].Text := '';
    MainForm.Status.Panels[2].Text := '';
  finally
    ClientDataset.Active := false;
    DataGrid.DataSource := DataSource;
  end; {try..finally}


I hope this helps.  It works a treat here.


All the best,


Stu.
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 12

Author Comment

by:rwilson032697
ID: 6430513
Thanks Stuart - that does look useful.

Have you tried using the TClientDataSet with a TQuery?

Cheers,

Raymond.
0
 
LVL 6

Accepted Solution

by:
Stuart_Johnson earned 300 total points
ID: 6430651
Hi Ray,

No I haven't.  One of my on going projects is a replacement for SQL Explorer and the BDE Admin.  One of the requirements was the ability to be able to export, import and browse XML files.  I would be interested in knowing how you do it thought, because it would be nice to run a query against an XML db.

Stu.
0
 
LVL 12

Author Comment

by:rwilson032697
ID: 6492488
This Q has been superceded by http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=delphi&qid=20176051

I may get around to deleting it in a few days...

Cheers,

Raymond.
0
 
LVL 26

Expert Comment

by:Russell Libby
ID: 8741980
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

To be PAQ/Refund

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Thank you,
Russell

EE Cleanup Volunteer
0
 
LVL 12

Author Comment

by:rwilson032697
ID: 8744455
Hi Stuart,

Thought you deserved the points for your help!

Cheers,

Raymond.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

9 Experts available now in Live!

Get 1:1 Help Now