::select statement return value with correct field size::

Shell Programming and Scripting
1

Hi Everyone,

I am facing a problem regarding the select from sybase, the return with the incorrect size.

For example, field is NAME(20).

After i selected from sybase, the result is nicky.
after i assign it to another declaration variable, it will be in actual name "nicky" , what i need the result is "nicky " with the space.

Anyone, who can help me? where to set it.. then i can get the return value with correct size which is including the space.

here is my sample of coding which

FIRST::::
typeSQL()
{

CONNECT="$SYBASE/XXX-22_8/bin/isql -S$HOSTNAME -U$USERNAME -P$PASSWORD -w400 -b -s|"

data_type_SQL=$($CONNECT cat <<-__EOF__
SET NOCOUNT ON
select LOC_TYPE, WAREHOUSE, LOCATION_ID from LOCATION where
LOCATION_ID="$loc_ID"
go
__EOF__)
}

size of each filed
LOC_TYPE(1)
WAREHOUSE(4)
LOCATION_ID(6)

After return value with incorrect size which are follow the size of each table name

data_type_SQL result is --> |G |NRT |NRTW |

SECOND::
loc_TYPE=$(echo $data_type_SQL | cut -d\| -f2| sed -e 's/|//g')
ware_HOUSE=$(echo $data_type_SQL | cut -d\| -f3| sed -e 's/|//g')
local_ID=$(echo $data_type_SQL | cut -d\| -f4| sed -e 's/|//g')

result after assigned with incorrect size

loc_TYPE="NRTW "
ware_HOUSE="G "
local_ID="NRT "

Can someone help me to check it? I just hope after assign to each field with correct size, including "SPACING"

thank you

2

I think you just need to put your variables in double quotes "$loc_type_SQL", etc.

3

you might want to do

str_out=$(echo $data_type_SQL | sed -e 's/^|//g' -e 's/|$//g' )
loc_TYPE=$(echo $str_out| cut -d\| -f1)
ware_HOUSE=$(echo $str_out| cut -d\| -f2)
local_ID=$(echo $str_out| cut -d\| -f3)

cheers,
Devaraj Takhellambam

4

Thank you devtakh and giannicello...

the sample you teach me.. still the same.
I think in the string for data_type_SQL after selected from database already wrong with SIZE.

The error according with the TABLE name size LOC_TYPE in 8,WAREHOUSE in 9 and LOCATION_ID in 11.

so, data_type_SQL = |G*******|NRT*****|NRTW*******| <-- it is wrong, coz it is follow with the table name of size.

suppose to be in its own size

LOC_TYPE(1)
WAREHOUSE(4)
LOCATION_ID(6)

the correct one should be
data_type_SQL = |G|NRT*|NRTW**|

Do you clear what i am explain?
SOS

Thank you...

5

ok then try this:

loc_TYPE=$(echo $data_type_SQL | awk -F "|" '{print substr($2,1,1)}')
ware_HOUSE=$(echo $data_type_SQL | awk -F "|" '{print substr($3,1,4)}')
local_ID=$(echo $data_type_SQL | awk -F "|" '{print substr($3,1,4)}')

you should also note that even if the size in the database is defined as 4 or 5 or 6, the actual data may be lesser than the defined size.

cheers,
Devaraj Takhellambam

6

Hi devtakh,

still the same... the spacing is not working...

thank you

7

what is it you are getting now:

a typo in the lasr post, it is

local_ID=$(echo $data_type_SQL | awk -F "|" '{print substr($4,1,6)}')

this variables will give you the output with the spaces...

cheers,
Devaraj Takhellambam

8

Hi ,

Still the same... here is the result after i debug

loc_TYPE='G'
ware_HOUSE='NRT*' the * is instead of spacing
local_ID='NRTW*' the * is instead of spacing

LOC_TYPE(1)
WAREHOUSE(4)
LOCATION_ID(6)

the correct one should be

loc_TYPE='G'
ware_HOUSE='NRT*' the * is instead of spacing
local_ID='NRTW**' the * is instead of spacing

9

anyone can help me? urgently..!!

10

Why don't you typeset your variables and double quote the output like I said before:

typeset -L1 loc_type
typeset -L4 ware_house
typeset -L6 local_id

while read line; do

loc_type=`echo $line|cut -d"|" -f2`
ware_house=`echo $line|cut -d"|" -f3`
local_id=`echo $line|cut -d"|" -f4`

echo Location : "$loc_type".
echo Warehouse: "$ware_house".
echo LocalID  : "$local_id".
done < myfile

Gianni

11

Hi Gianni,

Thank you for ur replym but still got the same problem...
here is the coding in my program

typeset -L1 loc_type
typeset -L4 ware_house
typeset -L6 local_id

loc_type=`echo $data_type_SQL|cut -d"|" -f2`
ware_house=`echo $data_type_SQL|cut -d"|" -f3`
local_id=`echo $data_type_SQL|cut -d"|" -f4`

echo Location : "$loc_type". >> ttt.log
echo Warehouse: "$ware_house". >> ttt.log
echo LocalID : "$local_id". >> ttt.log

result from ttt.log is :

Location : G*.
Warehouse: NRT*.
LocalID : NRTW*.

correct result should be -->

Location : G.
Warehouse: NRT*.
LocalID : NRTW**.