Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Exporting / Importing data files

Hi,

I am using Delphi 7 with MS-SQL 2000. I have an Employee table which saves employee's id, name, ..., and employee's picture in an IMAGE field.

The application is working fine, I am able to browse all employees and I can see their pictures using a DBIMAGE component.

I want to create an exported file that must contains all those employees data including their pictures. That file must be importable by my application for future use.

I used to create a text file which contains SQL commands like this:

INSERT INTO employee VALUES (.........)
INSERT INTO employee VALUES (.........)
INSERT INTO employee VALUES (.........)

Then, I just executes each and every line in the import process.

This will not work now because of the picture field.

any suggestion?
Hamid
0
HamidHossain
Asked:
HamidHossain
  • 3
  • 2
1 Solution
 
Wim ten BrinkSelf-employed developerCommented:
Do you use ADO? Then you can save and load the ADO recordset to a file. Either as an XML file or in some binary format. When you want to import them again, all you do is create a new recordset, load the file in it, then move record by record back in the database.
If you use the BDE, you could use the TClientDataset in a similar way, but it's less native.
0
 
HamidHossainAuthor Commented:
The following worked for me. Thanks Alex since you remind me that I can use SaveToFile

===================================

procedure TfrmExpImp.ButtonClick(Sender: TObject);
var
  I, X: Integer;
  FieldName: String;
  bs: TBlobStream;
begin
  // load the file which contains imported data into ADO
  if OpenDialog1.Execute then begin
    if Length(OpenDialog1.FileName) > 0 then begin
      ADOImport.LoadFromFile(OpenDialog1.FileName);
    end;
  end;

  try
    ADOImport.Open;
    ProgressBar1.Max := ADOImport.RecordCount;
    ADOStudent.TableName := 'student';
    ADOStudent.Open;

    // loop over all records found in the loaded file
    for I := 0 to ADOImport.RecordCount-1 do begin
      Application.ProcessMessages;
     
      ADOStudent.Filtered := False;
      ADOStudent.Filter := 'std_id=' + ADOImport.FieldByName('std_id').AsString;
      ADOStudent.Filtered := True;

      // if record id already exist in our database then use edit. Else use insert and store it's ID
      if ADOStudent.RecordCount = 1 then begin
        ADOStudent.Edit;
      end else begin
        ADOStudent.Insert;
        // this must be with insert only, you will get an error if you use it above with edit
        ADOStudent.FieldByName('std_id').AsString := ADOImport.FieldByName('std_id').AsString;
      end;

      // loop over all fields to transport their values
      for X := 0 to ADOStudent.Fields.Count-1 do begin
        FieldName := ADOStudent.Fields[X].FieldName; // get field name

        // don't stop for std_id since it is already set for insert and it must not be used for edit.
        if FieldName <> 'std_id' then begin
          // do special process if field is std_datastrip
          if FieldName = 'std_datastrip' then begin
            try
              bs := TBlobStream(ADOImport.CreateBlobStream(ADOImport.FieldByName(FieldName), bmRead));
              bs.Position := 0;
              TBlobField(ADOStudent.FieldByName(FieldName)).LoadFromStream(bs);
            finally
              FreeAndNil(bs);
            end;
          end else begin
            // for normal fields, use the normal assignment
            ADOStudent.FieldByName(FieldName).AsString := ADOImport.FieldByName(FieldName).AsString;
          end;
        end;
      end;

      ADOStudent.Post; // post the record into the SQL table

      ADOImport.Next;
      ProgressBar1.Position := ProgressBar1.Position + 1;
    end;

    ProgressBar1.Position := 0;
  finally
    ADOStudent.Close;
    ADOImport.Close;
  end;
end;
0
 
HamidHossainAuthor Commented:
Now, As my code above shows:

I have ADOImport which will contain the loaded file, and ADOStudent which contains the student table at my MS-SQL.

Usually, I will import a file contains about 400 - 600 students with their pictures. This will take very very long time.

I think the way I used inside the loop is not the best way (I used filter property)

Is there any faster way to import all records and make the loop faster? keep in mind that I am inserting new records and updating already existing ones.

Regards,
Hamid
0
 
Wim ten BrinkSelf-employed developerCommented:
One trick you could do is to import the data inside a separate thread. That way you don't have to call Application.ProcessMessages all the time. The main thread will stay responsive while the new thread will be processing the data in the background. Once it is done, it could notify the main thread.
And instead of using an ADOTable I would use an ADOQuery since it performs a lot better in many cases, especially with SQL Server. Unlike the TQuery component, you can even update records in a TADOQuery if you use a simple "select * from student" query.

And now you can probably guess how to improve speed a bit. :-) Don't use a table with a filter but use a query with a WHERE clause. Yes, it means opening and closing the query quite a few times but it will perform faster. Or use a locate() instead of a filter to find the student record in your table.
But okay, for the method you selected, a TADOTable should work as well as a TADOQuery with just "select * from students" as query without the WHERE clause. But don't filter the data, since filtering is way too slow. Use a locate!

      if Locate('std_id', VarArrayOf([ADOImport.FieldByName('std_id').AsString]), []) then begin
        ADOStudent.Edit;
      end else begin
        ADOStudent.Insert;
        // this must be with insert only, you will get an error if you use it above with edit
        ADOStudent.FieldByName('std_id').AsString := ADOImport.FieldByName('std_id').AsString;
      end;

That would be faster!
0
 
Wim ten BrinkSelf-employed developerCommented:
I meant:
  if ADOStudent.Locate('std_id', VarArrayOf([ADOImport.FieldByName('std_id').AsString]), []) then begin
0

Featured Post

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!

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