• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1380
  • Last Modified:

Using a MyBase XML DB instead of a Paradox DB

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
rwilson032697
Asked:
rwilson032697
  • 6
  • 3
1 Solution
 
rwilson032697Author Commented:
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
 
Stuart_JohnsonCommented:
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
 
rwilson032697Author Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
rwilson032697Author Commented:
No further suggestions, anyone? Even just an example of using a TQuery with a TClientDataset would be nice!

Cheers,

Raymond.
0
 
Stuart_JohnsonCommented:
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
 
rwilson032697Author Commented:
Thanks Stuart - that does look useful.

Have you tried using the TClientDataSet with a TQuery?

Cheers,

Raymond.
0
 
Stuart_JohnsonCommented:
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
 
rwilson032697Author Commented:
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
 
Russell LibbySoftware Engineer, Advisory Commented:
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
 
rwilson032697Author Commented:
Hi Stuart,

Thought you deserved the points for your help!

Cheers,

Raymond.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now