SEO

#1 (**permalink**) 05-02-2008, 05:05 AM

Hello Group,

I've to get data from some tables with similar structure. So I decided
to write an
Unix shellscript to mask SQL staments (like: SELECT G.$
{DealClass}_Id... which will expanded to SELECT G.CarDeals_Id )
and then pass them to isql via

isql -w5000 -s";" <<EOF | grep -iv "\-\-\-" | grep -iv "row" | grep -v
"^$" | sed 's/^;//g' >> ${RptSQLResult_Deals}

Now the problem is that fields with Comments exist. This fields can
have a ";" in the field content.

Sample outputfile generated via isql:
1234;FORD;Mustang D12A;120000;USD;Cool car; call Mr. Jon Doe;<other
fields...>

This would result into an error while importing by external app which
interpretes ";" in the comment line as delimiter.

By contrast ReportBatch would generate this above as:

Sample outputfile generated via ReportBatch:
1234;"FORD";"Mustang D12A";120000;"USD";"Cool car; call Mr. Jon
Doe";<other fields...>

which is correct / requested.

So the question is, exists command line parameters to isql to say that
String/Varchar fields have to be quoted with "" or is ReportBatch my
only possibility (which causes into fully change my application).

Thanks in Advance
Celal Dikici

#2 (**permalink**) 05-02-2008, 05:05 AM

On Apr 30, 3:44*pm, Celal Dikici <CDik...@googlemail.com> wrote:
> Hello Group,
>
> I've to get data from some tables with similar structure. So I decided
> to write an
> Unix shellscript to mask SQL staments (like: SELECT G.$
> {DealClass}_Id... which will expanded to SELECT G.CarDeals_Id )
> and then pass them to isql via
>
> isql -w5000 -s";" <<EOF | grep -iv "\-\-\-" | grep -iv "row" | grep -v
> "^$" | sed 's/^;//g' >> ${RptSQLResult_Deals}
>
> Now the problem is that fields with Comments exist. This fields can
> have a ";" in the field content.
>
> Sample outputfile generated via isql:
> 1234;FORD;Mustang D12A;120000;USD;Cool car; call Mr. Jon Doe;<other
> fields...>
>
> This would result into an error while importing by external app which
> interpretes ";" in the comment line as delimiter.
>
> By contrast ReportBatch would generate this above as:
>
> Sample outputfile generated via ReportBatch:
> 1234;"FORD";"Mustang D12A";120000;"USD";"Cool car; call Mr. Jon
> Doe";<other fields...>
>
> which is correct / requested.
>
> So the question is, exists command line parameters to isql to say that
> String/Varchar fields have to be quoted with "" or is ReportBatch my
> only possibility (which causes into fully change my application).
>

If I undestand your question correctly , what you are looking for is
quotes in the result set for all the string values.
There is no command line option to achive this, however in TSQL if you
want quotes in result set you can do few tricks like surrond your
values with single quotes outside and double quotes inside for
example

select 1234,";", '"FORD"', ";" , '"Mustang D12A" ', ";",
120000,";",'"USD"'
----------- - ------ - --------------- - ----------- - -----
1234 ; "FORD" ; "Mustang D12A" ; 120000 ; "USD"

Alternatively you can have two quotes residing together to get a quote
in result set something like ...

select 1234,";"," ""FORD"" ",";"," ""Mustang D12A"" ",";",
120000,";"," ""USD"" "
----------- - -------- - ---------------- - ----------- - -------
1234 ; "FORD" ; "Mustang D12A" ; 120000 ; "USD"

You can also have a look at how set quoted_identifier works in TSQL
and play around it according to your requirements
here is a write up for that

http://infocenter.sybase.com/help/to...ug/sqlug14.htm

-HTH
Manish Negandhi
[TeamSybase]

#3 (**permalink**) 05-02-2008, 05:05 AM

On Apr 30, 4:44 am, Celal Dikici <CDik...@googlemail.com> wrote:
> Hello Group,
>
> I've to get data from some tables with similar structure. So I decided
> to write an
> Unix shellscript to mask SQL staments (like: SELECT G.$
> {DealClass}_Id... which will expanded to SELECT G.CarDeals_Id )
> and then pass them to isql via
>
> isql -w5000 -s";" <<EOF | grep -iv "\-\-\-" | grep -iv "row" | grep -v
> "^$" | sed 's/^;//g' >> ${RptSQLResult_Deals}
>
> Now the problem is that fields with Comments exist. This fields can
> have a ";" in the field content.
>
> Sample outputfile generated via isql:
> 1234;FORD;Mustang D12A;120000;USD;Cool car; call Mr. Jon Doe;<other
> fields...>
>
> This would result into an error while importing by external app which
> interpretes ";" in the comment line as delimiter.
>
> By contrast ReportBatch would generate this above as:
>
> Sample outputfile generated via ReportBatch:
> 1234;"FORD";"Mustang D12A";120000;"USD";"Cool car; call Mr. Jon
> Doe";<other fields...>
>
> which is correct / requested.
>
> So the question is, exists command line parameters to isql to say that
> String/Varchar fields have to be quoted with "" or is ReportBatch my
> only possibility (which causes into fully change my application).
>
> Thanks in Advance
> Celal Dikici

There are no command line parameters to isql to do what you want here.
However, ReportBatch isn't your only possibility.

You can use isql by changing your query / process a little.

One way would be to change the select so that it concatenates quotes
around
the char fields:

i.e. rather than

select Comments

write

select '"' + Comments + '"' as Comments

Alternatively, since the tables have similar structure, you could
write your queries to store the results in a table rather than output
them. You would write a view against the table that did this
concatenation business, and then bcp out of the view.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Using quotes in isql for varchar fields