At 10:44 AM 20-02-02 +0200, you wrote:

>I'm hacking ISC4 file and run into some problem:
>
>If I have external file:
>
>CREATE TABLE log_table
> EXTERNAL FILE 'C:\Program Files\Borland\InterBase\isc4.log'
>( tstamp CHAR(20),
> spc CHAR(1) DEFAULT ' ',
> uname CHAR(31),
> crlf CHAR(2) );
>
>How can I insert the CR+LF into crlf field?
>
>I tried like this:
> INSERT INTO log_table (TSTAMP, UNAME,crlf)
> VALUES ( CURRENT_TIMESTAMP, :un,'\r\n');
>
>but the ISC4 was not accessible afterward. When I removed this 'crlf' column everything worked ok.
>
>Riho-Rene

Get hold of the FreeUDFLib from www.cvalde.com and declare this UDF to your database:

declare external function f_CRLF
returns cstring(3) /* free_it */
entry_point 'CRLF' module_name 'FreeUDFLib.dll';

Just insert straight from the function:

INSERT INTO log_table (TSTAMP, UNAME,crlf)
VALUES ( CURRENT_TIMESTAMP, :un, CRLF());

The problem with this UDF is that it's going to store a carriage return and line feed regardless of which platform the database is running on. If the db is on another platform, the line break is different, e.g. it is LFCR on Mac, and it's LF alone on the *nixes. (someone needs to put this right some time...)

It would also be a good precaution to use a different identifier than 'crlf', since it has a name clash with the UDF.

You could also use a more client-dependent approach by declaring LineBreak constants in your client app:

const
...
LineBreak_Win = #13#10;
LineBreak_Unix = #10;
LineBreak_Mac = #10#13;
...

Then you can pass the appropriate linebreak sequence to your external table by passing the constant as a parameter to the Insert statement, e.g.
MyThing.ParamByName('crlf').AsString := LineBreak_Win;

This would work too:
MyThing.ParamByName('crlf').AsString := #13#10;

This won't work:
INSERT INTO log_table (TSTAMP, UNAME,crlf)
VALUES ( CURRENT_TIMESTAMP, :un, '#13#10');

since the '#' notation for ascii characters (like your escaped characters example) is Pascal-specific and, like your example, will overflow a char(2).

cheers,
H

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________