aix: read comma delimited file and put into a temp sybase table

I have a test file that I need to take the contents and place into a temp Sybase Table.  
From the command line I am able to read the file and echo it to the screen; however, I can't get it loaded into the temp table.  I am doing this in a procedure

For my example, it is assumed that the is present and read.

File contents example is like this

123456789,Apr 7 2010 9:22:41:800AM
234567891,Apr 8 2010 9:22:41:800AM
etc....

Here is what I have so far:
cat > ${SQL_SCR} << END_OF_PROC
...
...
...
while read file

do
echo "field1 = " `echo $file | cut -d, -f1`
echo "field2 = " `echo $file | cut -d, -f2`
 
insert  into TEMP_TABLE values (${field1},${field2})
done  < $CLR_FILE

END_OF_PROC

I am unable to get the data into the table.  What am I doing wrong.

I am getting the following error mesage:

Incorrect syntax near the keyword 'read'.
 000009 Msg 102, Level 15, State 1:
 000010 Server 'TEST', Line 8:
 000011 Incorrect syntax near ','.
brake4blondsIT AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
wilcoxonCommented:
If you want to use shell scripting, I'd do something like this...

There's no reason to parse out a file like you have (that I can tell from the info given anyway).

I think the problem in your code is not enclosing ${field2} in quotes in the insert.
#!/bin/bash

# assuming it is a #temp table (rather than a permanent "temp" table)
echo 'create table TEMP_TABLE (field1 integer,field2 datetime) go' | isql -S $SERVER -D $DATABASE -U $USER -P $PASS
# -d might be -t - I always forget which
bcp -c -d , -S $SERVER -D $DATABASE -U $USER -P $PASS file_to_insert

Open in new window

0
 
wilcoxonCommented:
Alternately, I'd do this as a perl script...
#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect('dbi:Sybase:$DATABASE@$SERVER', $USER, $PASS, { RaiseError => 1, AutoCommit => 1 }) or die $DBI::errstr;
open IN, 'file_to_insert' or die "could not open file: $!";
# uncomment this line to create temp table if needed
# $dbh->do("create table TEMP_TABLE (field1 integer, field2 datetime)") or die $dbh->errstr;
while (<IN>) {
    chomp;
    my (@vals) = m{^(\d+),(.*)$};
    $vals[1] = $dbh->quote($vals[1]);
    $dbh->do("insert TEMP_TABLE values ($vals[0], $vals[1])") or die $dbh->errstr;
}

Open in new window

0
 
brake4blondsIT AnalystAuthor Commented:
ok, maybe I am just a little slow but here is more of the code:

cat > ${SQL_SCR} << END_OF_PROC

BEGIN
      truncate table TEMP_TABLE
      drop table TEMP_TABLE
END
go

Create table TEMP_TABLE (
REF_ID              char(09),
DATE_TIME           char(29))
go

select 'Creating Procedure TEMP_TABLE Started at ', getdate()
go

while read file

do
echo "field1 = " `echo $file | cut -d, -f1`
echo "field2 = " `echo $file | cut -d, -f2`
 
insert  into TEMP_TABLE values (${field1},${field2})

done  < $CLR_FILE

END_OF_PROC

isql -S${Server} -D${Database} -U${UserId} -P${Password} -i${SQL_SCR}A -o${SQL_LOG} 2>${SQL_ERR}


it works fine until the read statement.  I enclosed the values in the insert statement and that work either...any other suggestions>
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
wilcoxonCommented:
I took another look at the error message you're getting and it looks like the while read is being included in $SQL_SCR directly rather than executed.  I think this will fix the issue (it should at least change what the error is).
cat > ${SQL_SCR} << END_OF_PROC

BEGIN
      truncate table TEMP_TABLE
      drop table TEMP_TABLE
END
go

Create table TEMP_TABLE (
REF_ID              char(09),
DATE_TIME           char(29))
go

select 'Creating Procedure TEMP_TABLE Started at ', getdate()
go

END_OF_PROC

while read file

do
echo "field1 = " `echo $file | cut -d, -f1`
echo "field2 = " `echo $file | cut -d, -f2`
 
echo "insert  into TEMP_TABLE values (${field1},'${field2}')" >> $SQL_SCR
echo go >> $SQL_SCR

done  < $CLR_FILE

isql -S${Server} -D${Database} -U${UserId} -P${Password} -i${SQL_SCR}A -o${SQL_LOG} 2>${SQL_ERR}

Open in new window

0
 
wilcoxonCommented:
I just noticed REF_ID is also a char field so ${field1} should also be quoted in the insert.
0
 
brake4blondsIT AnalystAuthor Commented:
Niether of the suggestions worked...still has a problem with the read statement...
0
 
wilcoxonCommented:
In that case, I'd try this as an alternate version...
cat > ${SQL_SCR} << END_OF_PROC

BEGIN
      truncate table TEMP_TABLE
      drop table TEMP_TABLE
END
go

Create table TEMP_TABLE (
REF_ID              char(09),
DATE_TIME           char(29))
go

select 'Creating Procedure TEMP_TABLE Started at ', getdate()
go

END_OF_PROC

isql -S${Server} -D${Database} -U${UserId} -P${Password} -i${SQL_SCR}A -o${SQL_LOG} 2>${SQL_ERR}
bcp ${Database}..TEMP_TABLE in $CLR_FILE -S${Server} -U${UserId} -P${Password} -c -d,

Open in new window

0
 
brake4blondsIT AnalystAuthor Commented:
now I get this error message:

Copy direction must be either 'in' or 'out'.
  Syntax Error in 'TEMP_TABLE'.
  usage: bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile
       [-m maxerrors] [-f formatfile] [-e errfile]
       [-F firstrow] [-L lastrow] [-b batchsize]
       [-n] [-c] [-t field_terminator] [-r row_terminator]
       [-U username] [-P password] [-I interfaces_file] [-S server]
       [-a display_charset] [-q datafile_charset] [-z language] [-v]
       [-A packet size] [-J client character set]
       [-T text or image size] [-E] [-g id_start_value] [-N] [-X]
       [-M LabelName LabelValue] [-labeled]
       [-K keytab_file] [-R remote_server_principal]
      [-V [security_options]] [-Z security_mechanism] [-Q]
0
 
wilcoxonCommented:
That's really weird.  Direction is clearly defined in the bcp command.  Does your database name have a space in it?  I'm not sure that's even valid but it's the only thing I can see in the bcp command that would cause it to miss the direction ("in").  I do see that I did misremember that field_terminator switch.

Here's a slightly updated version that I don't think will change anything...
cat > ${SQL_SCR} << END_OF_PROC

BEGIN
      truncate table TEMP_TABLE
      drop table TEMP_TABLE
END
go

Create table TEMP_TABLE (
REF_ID              char(09),
DATE_TIME           char(29))
go

select 'Creating Procedure TEMP_TABLE Started at ', getdate()
go

END_OF_PROC

isql -S${Server} -D${Database} -U${UserId} -P${Password} -i${SQL_SCR}A -o${SQL_LOG} 2>${SQL_ERR}
bcp "${Database}..TEMP_TABLE" in "$CLR_FILE" -S ${Server} -U ${UserId} -P ${Password} -c -t ,

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
brake4blondsIT AnalystAuthor Commented:
If the bcp statement looks like this it will work

bcp ${Database}..TEMP_TABLE in $CLR_FILE -S ${Server} -U ${UserId} -P ${Password} -c -t,
 
It won't work with the quotes.  Nonetheless, you did resolve my problem.

Thanks a bunch
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.