Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Problem in passing IFS array to SQL Query
Top Forums Shell Programming and Scripting Problem in passing IFS array to SQL Query Post 303025323 by yuvi on Tuesday 30th of October 2018 04:26:17 PM
Old 10-30-2018
Problem in passing IFS array to SQL Query
Hi,

I have created a shell script that reads line from text file and insert into DB table. I have used IFS to separate the line text. Looks IFS is splitting text properly but while passing one of the values that has special characters in it to query, it is giving weird issue. Below is my script looks like

Code:
IFS.OLD=$IFS
IFS='#'
while read line
do
read -r -A a <<< "$line"
echo "flag is- ${a[0]}"
if [ "${a[0]}" = "Flag" ];
then
echo "Header"
elif [ "${a[0]}" = "NU" ];
then
echo "User - Insert"
echo "company is- ${a[4]}"
sqlplus -s $DB_PATH  <<EOF
set head off feedback off
INSERT INTO USERS (EMAIL, FULL_NAME, CONTACT_PHONE_NUMBER,COMPANY, USER_ID) VALUES ( '${a[1]}', '${a[2]}','${a[3]}','${a[4]}',999527 );
commit;
exit;
EOF
fi

Below is my text file data
Code:
Flag#NEW#EMAIL#Name#Phno#Comp#***
NU#john@gmail.com#John Mathew#1122333#A&N Services, Inc.

when I run the script, output is coming as below

Code:
flag is- Flag
Header
flag is- NU
User - Insert
company is- A&N Services, Inc.
 Enter value for n: old   1: INSERT INTO USERS (EMAIL, FULL_NAME, CONTACT_PHONE_NUMBER,COMPANY, USER_ID) VALUES ( 'john@gmail.com', 'John Mathew','1122333','A&N Services, Inc.',999527 )
 new   1: INSERT INTO USERS (EMAIL, FULL_NAME, CONTACT_PHONE_NUMBER,COMPANY, USER_ID) VALUES ( 'john@gmail.com', 'John Mathew','1122333','Acommit; Services, Inc.',999527 )

I really do not understand why it is showing output like this but I investigated and found that this issue is coming due to the parameter COMPANY in the query. It has special characters. If I remove and change to simple text then it is not giving this issue and record is inserting successfully. Please suggest if any solution.
Last edited by vgersh99; 10-30-2018 at 05:29 PM.. Reason: Code tags, please!
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

problem with IFS

hi, :) I set IFS=":" But when i try to echo $IFS,i am not getting any thing on the screen escept a blank line. any help pls. cheers RRK (11 Replies)
Discussion started by: ravi raj kumar
11 Replies

2. Shell Programming and Scripting

Problem while storing sql query value in a variable

Hi, When i execute the below statement , the value is not getting stored in the variable. AnneeExercice=`sqlplus $LOGSQL/$PASSWORDSQL << FIN >> $GEMOLOG/gemo_reprev_reel_data_ventil_$filiale.trc SELECT bi09exercice FROM bi09_scenario WHERE bi09idfiliale=UPPER('de') AND ... (1 Reply)
Discussion started by: krishna_gnv
1 Replies

3. Shell Programming and Scripting

Problem in Passing sql query for a script

Unix prompt ========= echo "Enter the query" read q ========== User has entered : SELECT * FROM employee ===================== Now the problem starts.. echo $q Output: SELECT "all files names in the PWD" FROM employee ================================================ ... (5 Replies)
Discussion started by: Niroj
5 Replies

4. Shell Programming and Scripting

sql query problem

Hi, I am passing an argument for the script and that argument values should exist in database. bill_period_input="'""$1""'" bill_period=`sqlplus uname/pwd@dbname <<eof! set verify off set heading off set feedback off select bill_period from bill_period_ref where... (4 Replies)
Discussion started by: ss_ss
4 Replies

5. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

6. UNIX for Advanced & Expert Users

Passing Hash variable in to sql query in perl

Hi Everyone, Can anyone help me how do i call hash variable in to sql query in perl. Please see the script below i have defined two Hash %lc and %tab as below $lc{'REFF'}='V_RES_CLASS'; $lc{'CALE'}='V_CAP_CLASS'; $lc{'XRPD'}='V_XFMR_CLASS'; $tab{'V_RES_CLASS'}='V_MFR_SERS';... (6 Replies)
Discussion started by: jam_prasanna
6 Replies

7. Shell Programming and Scripting

problem in SQL query

I used the following code code select * from tablename where columnname Instead of printing the expected output it prints all the files in the present directory since there is a "*" in the code. Is there any way to overcome the problem? Thanks Ananth (2 Replies)
Discussion started by: Ananthdoss
2 Replies

8. Shell Programming and Scripting

Problem in formatting output of SQL query in excel sheet in shell script

Hi Guys.. Need your help to format the output of my shell script. I am using spool command to take out put in csv file. below is my code. (for example) col USERNAME for a15 col EMAIL for a30 col FULL_NAME for a20 col LAST_LOGIN for a40 col DATE_CREATED for a40 SPOOL 120.csv... (3 Replies)
Discussion started by: Agupte
3 Replies

9. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

10. Shell Programming and Scripting

Taking information from a postgres sql query and putting it into a shell script array

I have a postgres sql statement that is the following: select age from students; which gives me the entries: Age --- 10 15 13 12 9 14 10 which is about 7 rows of data. Now what I would like to do with this is use a shell script to create an array age. As a results... (3 Replies)
Discussion started by: JSNY
3 Replies

LEARN ABOUT DEBIAN

ndselect

NDSELECT(1)							   User Commands						       NDSELECT(1)

NAME

       ndselect - select lines and fields for numdiff

DESCRIPTION

       Usage: ndselect -h|--help|-v|--version	or

       ndselect [-b N][-e N][-s N][-F N][-L N][-I N][-S IFS][-x][-l PATH][-o PATH] [FILE]

       Print to standard output a subset of lines and fields from a given file.

       The  argument  after  the options is the name of the file to read from.	The complete path of the file should be given, a directory name is
       not accepted.  If no input file is specified, the program reads from the standard input.

       Exit status: 0 in case of normal termination, -1 (255) in case of error

       -b, --beginning, --start=N

	      Set to N the number of the first line to print (The default behavior is to start with line number 1)

       -e, --end=N

	      Set to N the number of the last line that can be printed (The default behavior is to arrive till to the end of the file)

       -s, --step=N

	      Set to N the increment to use when selecting the lines to print (The default value for the increment is 1)

       -F, --first-field=N

	      Set to N the number of the first field to print (The default behavior is to start with field number 1)

       -L, --last-field=N

	      Set to N the number of the last field that can be printed (The default behavior is to arrive till to the end of every line)

       -I, --increment=N

	      Set to N the increment to use when selecting the fields to print (The default value for the increment is 1)

       -S, --separator=IFS

	      Specify the set of characters to use to split the input lines into fields (The default set of characters is space, tab and newline).

       -x, --omit-empty-lines

	      Do not print empty lines

       -l, --warnings-to=PATH

	      Redirect warning and error messages from stderr to the indicated file

       -o, --output=PATH

	      Redirect output from stdout to the indicated file

       -h, --help

	      Show this help message

       -v, --version

	      Show version number, Copyright, Distribution Terms and NO-Warranty

COPYRIGHT

       Copyright (C) 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012  Ivano Primi <ivprimi@libero.it>
       License GPLv3+: GNU GPL version 3 or later, see <http://gnu.org/licenses/gpl.html>.
       This is free software: you are free to change and redistribute it.  There is NO WARRANTY, to the extent permitted by law.

SEE ALSO

       The full documentation for ndselect is maintained as a Texinfo manual.  If the info and ndselect programs are properly  installed  at  your
       site, the command

	      info numdiff

       should give you access to the complete manual.

ndselect 5.6.0							   January 2012 						       NDSELECT(1)
All times are GMT -4. The time now is 12:04 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy