Forgot to mention that char is better than varchar, because the "raw"
format for varchar will include a string length field in binary form.
Not very human-friendly...

Use char in the external table decl, and trim or substring the data in
SQL to get what you need when you select from it.

Kjell

Kjell Rilbe wrote:

> As for external table, the format is "raw". In other words, FB expects
> the data to appear in FB's native form, "side by side". You would
> probably want to use a format like this for your sets, assuming ANSI
> charset (iso-8859-1 or win-1252 for example):
>
> 01A<linebreak>
> 02A<linebreak>
> 03A<linebreak>
> 04A<linebreak>
> 05A<linebreak>
> 06A<linebreak>
> 01B<linebreak>
> 02B<linebreak>
> ...
> 06P<linebreak>
>
> This external table would be declared as:
>
> create table "MyExternalTable"
> external file 'C:\MyDataFiles\MyExternalData.txt' (
> "Data" char(3) character set WIN1252,
> "Dummy" char(2) character set WIN1252
> );
>
> If you need UTF8 or any variable length charset, then your file should
> pad to N * max_byte_per_char for that charset.
>
> The Dummy column will contain the linebreaks.
> You could also save the file as:
>
> 01A02A03A04A05A06A01B02B...06P
>
> Then you can/must skip the Dummy column.
>
> For numeric data, the best option is to write the values in text format
> in the external file and declare as varchar, then cast in SQL to the
> desired numeric format. otheriwse you'll have to store the numerics in
> binary format in the external file, which makes that file very hard to
> read for humans.
>
> You'll need to edit a firebird.conf file entry as follows:
>
> ExternalFileAccess = Restrict C:\MyDataFiles
>
> Restart the Firebird service for that change to take effect .(For
> superserver, that is. Classic will have a new process per connection so
> I suppose it will take effect for each new connection).
>
> Hope this helps.
>
> Regards,
> Kjell
>
> blizzardb2001 wrote:
>
>
>>
>>
>>I am trying to insert a list of seats (e.g. 01A, 02A .. 06P) into
>>TblSeats on the server.
>>So to insert say, 96 seats (01A to 06P) means 96 inserts and it's taking
>>a long time, especially in the field where internet access may be lousy.
>>
>> >From what I understand my options are:
>>1. Stored Procedure or
>>2. Upload a TXT file into a temporary table on the Firebird Server, and
>>get it to do the insert that way.
>>
>>Problem with 1 is that the creation of the seat name is hard to
>>translate into STORED PROCEDURE code (you can get an idea of the Delphi
>>code for this below if you like).
>>
>>problem with 2 is I don't know how. Can you provide some proper
>>instructions? I can generate the TEXT file without your help but I'll
>>need to know the format.
>>
>>Below is the Delphi code in case you can do a stored procedure:
>>============
>>//Explanation: The user gives 3 inputs: 1. The device ID, 2. The number
>>of Rows (e.g. 15 as in A to P) 3. The number of columns (e.g. 6 as in 1
>>to 6)
>>
>>k := edtRows.AsInteger;
>>l := edtColumns.AsInteger;
>>with QrySeats do
>>begin
>>for j := 1 to l do
>>for i := 1 to k do
>>begin
>>insert;
>>fieldbyname('DeviceID').asinteger := QryDevice.FieldByName('ID').AsInteger;
>>if length(inttostr(i)) > 1 then
>>fieldbyname('SeatNumber').AsString := inttostr(i) + Char(65 + j - 1)
>>else
>>fieldbyname('SeatNumber').AsString := '0' + inttostr(i) + Char(65 + j - 1);
>>post;
>>end;
>>end;
>>
>>
>
>

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64