• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

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 ','.
0
brake4blonds
Asked:
brake4blonds
  • 6
  • 4
1 Solution
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now