The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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         

asked 28 Oct '10, 13:26

Jean-Marc%20Rouyre's gravatar image

Jean-Marc Ro...
21112
accept rate: 0%

edited 15 Mar '13, 21:43

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


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

permanent link

answered 28 Oct '10, 14:20

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

edited 28 Oct '10, 14:35

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×71
×24

question asked: 28 Oct '10, 13:26

question was seen: 1,208 times

last updated: 15 Mar '13, 21:43