SEO

Hello,

I would like to create a flat file from isql, with fields at specific
positions (I concatenate them : 15 characters field 1 + 32 characters
field 2 + 5 characters field 3 + ...)
The file is 500 characters.
But as soon as I exceed 255 characters, nothing more is displayed :-(

Is it impossible to do such a simple task ?

Thanks in advance.

Make sure you submit the '-w<width>' flag to isql to make sure output lines
are <width> characters wide.

(I believe the default is 255 characters; if the result set is longer than
255 characters then the 'overflow' will be printed on a new line; this can
lead to some unwanted results if your OS commands aren't expecting the
embedded CR/LF's.)

Assuming you *are* using the '-w' flag ... you'll need to post more details
.... actual SQL query ... what you're doing out in the OS (eg, isql call,
what you're doing with the output, etc.).

Chris wrote:

> Hello,
>
> I would like to create a flat file from isql, with fields at specific
> positions (I concatenate them : 15 characters field 1 + 32 characters
> field 2 + 5 characters field 3 + ...)
> The file is 500 characters.
> But as soon as I exceed 255 characters, nothing more is displayed :-(
>
> Is it impossible to do such a simple task ?
>
> Thanks in advance.

Mark A. Parsons wrote:

> Make sure you submit the '-w<width>' flag to isql to make sure output
> lines are <width> characters wide.
>
> (I believe the default is 255 characters; if the result set is longer
> than 255 characters then the 'overflow' will be printed on a new line;
> this can lead to some unwanted results if your OS commands aren't
> expecting the embedded CR/LF's.)
>
> Assuming you *are* using the '-w' flag ... you'll need to post more
> details ... actual SQL query ... what you're doing out in the OS (eg,
> isql call, what you're doing with the output, etc.).

Yes, I use -w, but it's only for the line length, not the field length.
The SQL query has nothing special, I just concatenate fields at special
positions in order to build a 500 characters line (1 field) for the file
(Field 1 : pos 2, Field 2 : pos 16, Field 3 : pos 30 , ...) :

--Field 1-------Field 2-------Field 3------ ...


But after position 255, it doesn't work anymore :-(

Which brings me back to my last set of comments:

>> Assuming you *are* using the '-w' flag ... you'll need to post more
>> details ... actual SQL query ... what you're doing out in the OS (eg,
>> isql call, what you're doing with the output, etc.).

Chris wrote:

> Mark A. Parsons wrote:
>
>> Make sure you submit the '-w<width>' flag to isql to make sure output
>> lines are <width> characters wide.
>>
>> (I believe the default is 255 characters; if the result set is longer
>> than 255 characters then the 'overflow' will be printed on a new line;
>> this can lead to some unwanted results if your OS commands aren't
>> expecting the embedded CR/LF's.)
>>
>> Assuming you *are* using the '-w' flag ... you'll need to post more
>> details ... actual SQL query ... what you're doing out in the OS (eg,
>> isql call, what you're doing with the output, etc.).
>
>
> Yes, I use -w, but it's only for the line length, not the field length.
> The SQL query has nothing special, I just concatenate fields at special
> positions in order to build a 500 characters line (1 field) for the file
> (Field 1 : pos 2, Field 2 : pos 16, Field 3 : pos 30 , ...) :
>
> --Field 1-------Field 2-------Field 3------ ...
>
>
> But after position 255, it doesn't work anymore :-(
>

I think I see what you're talking about.

I ran the same 'select' statement (see below) on different ASE servers ...
11.9.2, 12.0.0.8, 12.5.0.3, 12.5.3.

I used the same version of isql (12.5.1) with '-w1000'.

On the 11.9.2 and 12.0.0.8 ASE's the output was cut off at 255 characters.

On the 12.5.0.3 and 12.5.3 ASE's the output was *not* cut off.

Which version of ASE are you running? (select @@version)

------------------

One possibility may be to break your select up into 2 chunks (each <= 255
characters in length) and then see if you can get your OS application to
splice rows together.

Alteranatively, consider defining a view that selects all the desired
fields (as distinct fields in the 'select' list). Then use bcp '-c' to
pull the records from the view. You would want to define the field
separator to be part of your desired string output.

For example, assume you wanted an output record to look like:

-<field1>-<field2>-<field3>-

You could define a view like such:

create view my_view as
select NULL,field1, field2,field3,NULL from my_table

Then bcp from my_view with '-c' and '-t"-"'.



--------- test 'select' ---------------
select
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'+'1234567890'+
':'
---------------------------------------

Chris wrote:

> Mark A. Parsons wrote:
>
>> Make sure you submit the '-w<width>' flag to isql to make sure output
>> lines are <width> characters wide.
>>
>> (I believe the default is 255 characters; if the result set is longer
>> than 255 characters then the 'overflow' will be printed on a new line;
>> this can lead to some unwanted results if your OS commands aren't
>> expecting the embedded CR/LF's.)
>>
>> Assuming you *are* using the '-w' flag ... you'll need to post more
>> details ... actual SQL query ... what you're doing out in the OS (eg,
>> isql call, what you're doing with the output, etc.).
>
>
> Yes, I use -w, but it's only for the line length, not the field length.
> The SQL query has nothing special, I just concatenate fields at special
> positions in order to build a 500 characters line (1 field) for the file
> (Field 1 : pos 2, Field 2 : pos 16, Field 3 : pos 30 , ...) :
>
> --Field 1-------Field 2-------Field 3------ ...
>
>
> But after position 255, it doesn't work anymore :-(
>

Mark A. Parsons wrote:
> I think I see what you're talking about.
>
> I ran the same 'select' statement (see below) on different ASE servers
> ... 11.9.2, 12.0.0.8, 12.5.0.3, 12.5.3.
>
> I used the same version of isql (12.5.1) with '-w1000'.
>
> On the 11.9.2 and 12.0.0.8 ASE's the output was cut off at 255 characters.
>
> On the 12.5.0.3 and 12.5.3 ASE's the output was *not* cut off.
>
> Which version of ASE are you running? (select @@version)

I use 12.0.0.x, then it migth be the reason.
I think the view is a good idea.
Otherwise, I could use many fields (F1, F2, F3,...) (without
concatening). But I will have to reformat the lines in shell (or awk)
Many thanks for your time...

Prior to ASE 12.5, var/char() datatypes (columns or @variables) were
limited to 255 characters.

With ASE 12.5 the limit on var/char() datatypes was increased from 255 to
16296.

My hunch is that somewhere under the hood these limitations play into what
you're seeing.

Chris wrote:

> Mark A. Parsons wrote:
>
>> I think I see what you're talking about.
>>
>> I ran the same 'select' statement (see below) on different ASE servers
>> ... 11.9.2, 12.0.0.8, 12.5.0.3, 12.5.3.
>>
>> I used the same version of isql (12.5.1) with '-w1000'.
>>
>> On the 11.9.2 and 12.0.0.8 ASE's the output was cut off at 255
>> characters.
>>
>> On the 12.5.0.3 and 12.5.3 ASE's the output was *not* cut off.
>>
>> Which version of ASE are you running? (select @@version)
>
>
> I use 12.0.0.x, then it migth be the reason.
> I think the view is a good idea.
> Otherwise, I could use many fields (F1, F2, F3,...) (without
> concatening). But I will have to reformat the lines in shell (or awk)
> Many thanks for your time...