Hello everyone,

>    CREATE TABLE table EXTERNAL external_file OPTIONS In the OPTIONS,
> users can determine field separator, assume missing values as NULL, skip first
> line or not, etc. Maybe I am wrong but it's just a suggestion.

>That suggestion was logged into the project Tracker some time ago (http://tracker.firebirdsql.org/browse/CORE-674)
>The outline does not exactly match up to your suggestion (CREATE TABLE

... OPTIONS...), so you might want to add your suggestion/comments

>Sean

I read the new feature submitted by jmccrackenon on January 09, 2005 and you are wright, Sean. It is similar to my suggestion with differences. Jmccrakenon would like the external file be anything else even a RDMS. Also he wants plugin where I suggest modification at the core. It is not the way I suggest. My OPTIONS is just a way to tell that some ones can be put there like this :


(RE)CREATE TABLE table EXTERNAL [FILE] filepath
      [CHARACTER SET charset]
      [VARYING value1]      [LEADING value2 [, SKIP lines]]
      [ESCAPE separator]
      [CHARACTER quotechar]      [NO VALUE replace]


First of all, I do my best to not introduce new keywords, so using the ones above in this context will not affect their actual use. Replacing them with meaningful ones is possible, up to you.

CHARACTER SET charset : the external file character set. Default value : UTF8.


VARYING value1 : this is a boolean value : TRUE the external table can be modified by replacing the current line with new values. FALSE the file is readonly. Of course, for writable external file modifications are done without any transaction and rollback is not possible. This functionality give opportunity to produce external files taking data from a real table. Default value : FALSE.


LEADING value2 : this is a boolean value. TRUE means that the first line in the external file contains fields'names. FALSE, there is no fields'names on the first line. I agree that this name can be confused and HEADING would be better, but the purpose is not to submit another reserve word. Default value : TRUE.


SKIP lines. An integer value that determine number of rows to skip from the first row of data in the external file. Default value : 0. This possibility makes reading/writing on external file more flexible.

ESCAPE separator. This is the character used in the external file to separate fields. Default value : ',' (comma). Another possible default value : '\t' (TAB character). With this improvement, users have not to spend time for counting characters between 2 fields. For a simple table of two-three lines, there is no problem. From more than 20 lines, mistakes are possible. The purpose of this external file is to read a bulk of data so if you have to count spaces to complete a field, the chance is that you take years to do it.
A single character specifying the separation of fields can resolve the fixed-length size impose by IB6.

CHARACTER quotechar : Character used to determine a string. Default value : ' (single quote). Because we use the same reserve word twice, the latter must be placed in anyway after the first. Also the paramater of this latter is a character not a string. With this option, fields can contain string values where commas are used without confused them with a field separator.
If single quote is used to detrmine strings, any single quote in the string value must be doubled.
If double quote is used (C-like string), there is no problem with single quote in a string value.


NO VALUE replace : Here we put the value used to replace missing value in the external file. Default value : NULL. Suppose that default value of ESCAPE is used, so in a line such this : 101,Lobi,Zoe,, we have 5 fields where the 4th and 5th fields will be replaced by NULL when read from FB. When writing in the external fille, NULL is replace by none.

Sean,

I was too long because I tried to be more complete. Improvements are always possible.

Have a nice day,

Zoe

[Non-text portions of this message have been removed]