OUTPUT TO behavior problem

[Former Member]

Hello,

I have a weird problem with a select statement that outputs >data to a fixed width text file. If I run it within dbisql, it exports the data correctly, each string field left aligned. Now, if I execute the same Select statement from a windows >batch file that executes the SQL statement, all string >fields are RIGHT aligned! What is causing this?

Windows server 2008, SQL Anywhere 11.0.1.2044, Sybase Central V6.0.0.4739

Thanks for any help.

Jean-Marc

For Breck: here is the code.

Create table:

create table person_sap(
"ID_PERSON" VARCHAR(8) NOT NULL UNIQUE,
"DAT_CHANGE" DATE NOT NULL,
"COD_STATUS" VARCHAR(1) NOT NULL,
"NAME" VARCHAR(40) NOT NULL,
"FIRST_NAME" VARCHAR(40) NOT NULL,
"MIDDLE_NAME" VARCHAR(40) NULL,
"INITIALS" VARCHAR(10) NULL,
"NB_PAY_ROLL" VARCHAR(40) NULL,
"SAP_GROUP" VARCHAR(1) NULL,
"SAP_PERS_SUB" VARCHAR(4) NULL,
"SAP_PLANT" VARCHAR(4) NOT NULL,
"SAP_CC" VARCHAR(10) NULL,
"SSN" VARCHAR(20) NULL,
"DAT_EFF" DATE NULL)

SQL Statement to generate output:

select id_person, dateformat(dat_eff,'YYYYMMDD'),cod_status,
sap_group, sap_pers_sub, sap_plant, "name", middle_name, first_name, initials, nb_pay_roll, ssn
from person_sap where dat_change>='2010-10-21';

OUTPUT TO "d:\\actions.txt"
FORMAT FIXED;

Batch file that executes this statement and leads to incorrect format:

d:
cd \\apps\\sap
dbisql.com -datasource hrdesktop -q sap_export.sql

Bad file:

100058182010101831   AUS72                                Garrison                                       JPeter                                             103957                                  555-55-5555         

Good file

100058182010101831A   US72Garrison                                J                                       Peter                                             103957                                  555-55-5555         

[Breck_Carter]

Yes it's a bug, fixed in 11.0.1.2479: http://search.sybase.com/kbx/changerequests?bug_id=637203

Thanks go to Tyson Lewis of Sybase for answering on the NNTP newsgroup.

Here's my earlier attempt to answer; maybe I should upgrade too :)...

---

I think there is a bug here... the behavior should not be different.

However, I see different results in 11.0.1.2276.

First, when the SELECT and OUTPUT are run in dbisql in GUI mode, two of the columns are right-aligned, and the rest are left aligned.

Second, the batch file version left-aligns all text fields, which (I believe) is what it should do.

Please run the following test on your version to see what you get:

create table person_sap(
/* a */ "ID_PERSON" VARCHAR(8) NOT NULL UNIQUE,
/* b */ "DAT_CHANGE" DATE NOT NULL,
/* c */ "COD_STATUS" VARCHAR(1) NOT NULL,
/* d */ "NAME" VARCHAR(40) NOT NULL,
/* e */ "FIRST_NAME" VARCHAR(40) NOT NULL,
/* f */ "MIDDLE_NAME" VARCHAR(40) NULL,
/* g */ "INITIALS" VARCHAR(10) NULL,
/* h */ "NB_PAY_ROLL" VARCHAR(40) NULL,
/* i */ "SAP_GROUP" VARCHAR(1) NULL,
/* j */ "SAP_PERS_SUB" VARCHAR(4) NULL,
/* k */ "SAP_PLANT" VARCHAR(4) NOT NULL,
/* l */ "SAP_CC" VARCHAR(10) NULL,
/* m */ "SSN" VARCHAR(20) NULL,
/* n */ "DAT_EFF" DATE NULL)

INSERT person_sap VALUES ( 'a', CURRENT DATE, 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', CURRENT DATE );
COMMIT;

select id_person, dateformat(dat_eff,'YYYYMMDD'),cod_status,
sap_group, sap_pers_sub, sap_plant, "name", middle_name, first_name, initials, nb_pay_roll, ssn
from person_sap where dat_change>='2010-10-21';

id_person,dateformat(person_sap.dat_eff,'YYYYMMDD'),cod_status,sap_group,sap_pers_sub,sap_plant,name,middle_name,first_name,initials,nb_pay_roll,ssn
'a','20101028','c','i','j','k','d','f','e','g','h','m'

OUTPUT TO "C:\\temp\\actions.txt"
FORMAT FIXED;

NAME                                    MIDDLE NAME
aaaaaaaabbbbbbbbcijjjjkkkkddddddddddddddddddddddddddddddddddddddddffffffffffffffffffffffffffffffffffffffffeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeegggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhmmmmmmmmmmmmmmmmmmmm
a       20101028ci   jk                                          d                                       fe                                       g         h                                       m

"%SQLANY11%\\bin32\\dbisql.com" -c "ENG=ddd11;DBN=ddd11;UID=dba;PWD=sql;CON=ddd11-2" -q x.sql

NAME                                    MIDDLE NAME
aaaaaaaabbbbbbbbcijjjjkkkkddddddddddddddddddddddddddddddddddddddddffffffffffffffffffffffffffffffffffffffffeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeegggggggggghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhmmmmmmmmmmmmmmmmmmmm
a       20101028cij   k   d                                       f                                       e                                       g         h                                       m