SQL table to delphi record using BCP

Question

I have a scenario in which I have to export data of around 500,000 records from sql table to be used in Delphi application. The data is to be loaded into a packed record. Is there a method in which i can use the BCP to write data file similar to that of writing the records to file.

As of now I am loading the data using this psudo code.

    // Assign the data file generated from BCP to the TextFile object.
    AssignFile(losDataFile, loslFileName);
    Reset(losDataFile);
    while not EOD(losDataFile) do
    begin
      // Read from the data file until we encounter the End of File
      ReadLn(losDataFile, loslDataString);

      // Use the string list comma text to strip the fields
      loclTempSlist.CommaText := loslDataString;

      // Load the record from the items of the string list.
      DummyRec.Name := loclTempSList[0];
      DummyRec.Mapped = loclTempSList[1] = 'Y';
    end;

For convenience i have listed the type of Dummy rec below

    TDummyRec = packed record
      Name : string[255];
      Mapped : Boolean;
    end;

So, my question is, instead of exporting the data to a text file, will it be possible to export the data to binary so that i can read from the file directly using the record type?

like

   loclFileStream := TFileStream.Create('xxxxxx.dat', fmOpenRead or fmShareDenyNone);
   while loclFileStream.Position < loclFileStream.Size do
   begin
     // Read from the binary file
     loclFileStream.Read(losDummyData, SizeOf(TDummyRec));
     //-  -------- Do wat ever i want.
   end;

I don't have much experience on using the BCP. Please help me with this.

Thanks Terminator...

Answer 1

In your record, a string[255] will create a fixed-size Ansi string (i.e. a so-called shortstring). This type is clearly deprecated, and should not be used in your code.

It will be an awful waste of space to save it directly, using a TFileStream (even if it will work). Each record will store 256 bytes for each Name.

And using a string[255] (i.e. a so-called shortstring) will make an hidden conversion to a string for most access to it. So it is not the best option, IMHO.

My advice is to use a dynamic array then serialize / unserialize it with our Open Source classes. For your storage, you can use a dynamic array. Works from Delphi 5 up to XE2. And you'll be able to use a string in the record:

TDummyRec = packed record
  Name : string; // native Delphi string (no shortstring)
  Mapped : Boolean;
end;

Edit after OP's comment:

BCP is just a command-line tool meant to export a lot of rows into a SQL table. So IMHO BCP is not the good candidate for your purpose.

You seems to need to import a lot of rows from a SQL table.

In this case:

• Using shortstring will be in all case a waste of memory, so you'll get faster out of memory than with using a good string;
• You can try our Open Source classes to retrieve all data rows one by one, then populate your records using this data: see SynDB classes - it is lighter than ADO; Then you'll be able to retrieve the record data one by one, then use our record serialization functions to create some binary content - or try a dedicated faster engine like our SynBigTable;
• There are some articles about using directly the OleDB feature used by BCP from Delphi code in here - it is in french, but you can use google to translate it and here for fast bulk copy; full source code included.

Answer 2

You want to read a SQL-table into a record, I have no idea why you are working with the archaic AssignFile.

You should really use a TADOQuery (or suitable variant) for you database.
Put a sensible SQL-query in it; something like:

SELECT field1, field2, field3 FROM tablename WHERE .....

When in doubt you can use:

SELECT * FROM tablename

Which will select all fields from the table.

The following code will walk through all the records and all the fields and save them in a variants and save that in a FileStream.

function NewFile(Filename: string): TFileStream;
begin
  Result:= TFileStream.Create(Filename, fmOpenWrite);
end;

function SaveQueryToFileStream(AFile: TFileStream; AQuery: TADOQuery): boolean;
const
  Success = true;
  Failure = false;
  UniqueFilePrefix = 'MyCustomFileTypeId';
  BufSize = 4096;
var
  Value: variant;
  Writer: TWriter;
  FieldCount: integer;
  c: integer;
  RowCount: integer;
begin
  Result:= Success;
  try
    if not(AQuery.Active) then AQuery.Open

    FieldCount:= AQuery.Fields.Count;
    Writer:= TWriter.Create(AFile, BufSize);
    try
      Writer.WriteString(UniqueFilePrefix)
      //Write the record info first
      Writer.WriteInteger(FieldCount);
      //Write the number of rows
      RowCount:= AQuery.RecordCount;
      WriteInteger(RowCount);
      AQuery.First;
      while not(AQuery.eof) do begin
        for c:= 0 to FieldCount -1 do begin
          Value:= AQuery.Fields[c].Value;
          Writer.WriteVariant(Value);
        end; {for c}
        AQuery.Next;
      end; {while}
    except 
      Result:= failure;
    end;
  finally
    Writer.Free;
  end;
end;