Solved

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

Posted on 2010-09-20
10
591 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
reading text file and converting it to csv 8 69
Linux "time" command output redirection 16 146
AWS- KeepAlived notify script not working 23 83
Help With Shell Script Centos 6 6 53
This Windows batch file is useful for organizing image files from a digital camera or other source, but can have many other uses.  It simply renames the file(s) to match their create date.  For example, if you took a picture today at 1:40pm and the …
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…
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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