Solved

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

Posted on 2010-09-20
10
589 Views
Last Modified: 2012-05-10
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
Comment
Question by:brake4blonds
  • 6
  • 4
10 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33721625
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33721657
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
 

Author Comment

by:brake4blonds
ID: 33721690
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33721745
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33721747
I just noticed REF_ID is also a char field so ${field1} should also be quoted in the insert.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:brake4blonds
ID: 33721774
Niether of the suggestions worked...still has a problem with the read statement...
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33721822
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
 

Author Comment

by:brake4blonds
ID: 33721955
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
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 33724109
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
 

Author Comment

by:brake4blonds
ID: 33726987
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now