Connect to database with shell output

11-26-2017

Registered User

12, 0

Join Date: Oct 2017

Last Activity: 11 May 2018, 2:34 AM EDT

Posts: 12

Thanks Given: 0

Thanked 0 Times in 0 Posts

Connect to database with shell output

Dear All,

I am trying to write a script which will generate output to a file based on input files on basis of a pattern which I am able to do so. But, I am stuck as I am trying to connect this to database server. Below is my command and output details.

Input Code

Code:

filedate=$(date +"%d%m%Y")

cat  $filedate*lpn* | grep "H|" | cut -c9-26|sort|uniq > $path/alllpnfile.dat

File output.

Code:

990099110012345678
990099110012345666
990099110012345645
990099110012345701
220022110012345678
220022110012345690

Now, I want to read this new file and write a select statement in SQL by reading the complete file. But the only condition is that this newly created file contains details as above and I need to use comma to include all the lines in the file so that I will be able to use a single select statement in sql.

Now I need to use these lines from alllpnfiles.dat into a single select statement as below.

Code:

select * from tablename where sdigits in
(990099110012345678,
990099110012345666,
990099110012345645,
990099110012345701,
220022110012345678,
220022110012345690)

Regards.
GVK

Moderator's Comments:

Note that this thread has been moved from the "How to Post in the The UNIX and Linux Forums" forum to the "Shell Programming and Scripting" forum.

Last edited by Don Cragun; 11-26-2017 at 04:53 AM.. Reason: Note forum change.

grvk101

View Public Profile for grvk101

Find all posts by grvk101

11-26-2017

Registered User

12,315, 4,560

Join Date: Jul 2012

Last Activity: 22 November 2019, 4:29 PM EST

Location: San Jose, CA, USA

Posts: 12,315

Thanks Given: 952

Thanked 4,560 Times in 3,818 Posts

Quote:

Originally Posted by grvk101

Dear All,

I am trying to write a script which will generate output to a file based on input files on basis of a pattern which I am able to do so. But, I am stuck as I am trying to connect this to database server. Below is my command and output details.

Input Code

Code:

filedate=$(date +"%d%m%Y")

cat  $filedate*lpn* | grep "H|" | cut -c9-26|sort|uniq > $path/alllpnfile.dat

File output.

Code:

990099110012345678
990099110012345666
990099110012345645
990099110012345701
220022110012345678
220022110012345690

Now, I want to read this new file and write a select statement in SQL by reading the complete file. But the only condition is that this newly created file contains details as above and I need to use comma to include all the lines in the file so that I will be able to use a single select statement in sql.

Now I need to use these lines from alllpnfiles.dat into a single select statement as below.

Code:

select * from tablename where sdigits in
(990099110012345678,
990099110012345666,
990099110012345645,
990099110012345701,
220022110012345678,
220022110012345690)

Regards.
GVK

Your pipeline includes a sort, but your output clearly is not sorted???

So, assuming that the output does not need to be sorted, we can replace the rest of the pipeline with a single invocation of awk:

Code:

filedate=$(date '+%d%m%Y')
awk '
/H\|/ {	pn[substr($0, 9, 18)]
}
END {	print "select * from tablename where sdigits in"
	sep = "("
	for(i in pn) {
		printf("%s%s", sep, i)
		sep = ",\n"
	}
	print ")"
}' $filedate*lpn*

But, of course, since no sample input was provided, the above code is completely untested. If, despite your sample output, you really do need the output to be sorted, you can modify the for loop in the END clause in the above awk code to feed the list to sort and feed the output from sort into another for loop to print the results. Or, the awk available on some systems provides a function that could be used to sort the pn[] array before printing the results.

And, you haven't told us what operating system or shell you're using. If you're using a Solaris/SunOS system, change awk in the above code to /usr/xpg4/bin/awk or nawk.

Last edited by Don Cragun; 11-26-2017 at 05:01 AM.. Reason: Fix tabs.

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

11-27-2017

Registered User

12, 0

Join Date: Oct 2017

Last Activity: 11 May 2018, 2:34 AM EDT

Posts: 12

Thanks Given: 0

Thanked 0 Times in 0 Posts

Dear Don,

Thnx a lot for your quick response.

Below is the set -x otput I am getting for the code.

Code:

filedate=$(date '+%d%m%Y')awk '/H\|/ {	pn[substr($0, 9, 18)]}END 
{	print "select * from tablename where sdigits in"
	sep = "("
	for(i in pn) {
		printf("%s%s", sep, i)
		sep = ",\n"
	}
	print ")"
}' $filedate*lpn* > $path/all_file.dat

Output

Code:

+ /H\|/ {       pn[substr($0, 9, 18)]}END
{       print "select * from tablename where sdigits in"
        sep = "("
        for(i in pn) {
                printf("%s%s", sep, i)
                sep = ",\n"
        }
        print ")"
} 27112017181509.271117181509_dscinlpnptl.dat.gz
+ + date +%d%m%Y
filedate=27112017awk
./ptl_lpn_new.sh[24]: /H\|/ {^Ipn[substr($0, 9, 18)]}END ^J{^Iprint "select * from tablename where sdigits in"^J^Isep = "("^J^Ifor(i in pn) {^J^I^Iprintf("%s%s", sep, i)^J^I^Isep = ",\n"^J^I}^J^Iprint ")"^J}:  not found

The command is able to fetch that file but everything after that has failed.

Regards.
GRVK

grvk101

View Public Profile for grvk101

Find all posts by grvk101

11-27-2017

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

You seem to
- be missing a line terminator between the filedate assignment and the awk command.
- operate on a gzipped file, which is not *nix text - awk sensibly works on text only. gunzip first.

Last edited by RudiC; 11-27-2017 at 11:39 AM.. Reason: typo...

RudiC

View Public Profile for RudiC

Find all posts by RudiC

11-27-2017

Registered User

12, 0

Join Date: Oct 2017

Last Activity: 11 May 2018, 2:34 AM EDT

Posts: 12

Thanks Given: 0

Thanked 0 Times in 0 Posts

Dear RUDIC,

Thnx for the edit.

@DON,

I made changes and the script is executing. But the output which I am getting is not a single in but in iterations.
The input file has 3 lines and output generated is as follows.

OUTPUT

Code:

select * from tablename where sdigits in
(990099110300112517)
select * from tablename where sdigits in
(990099110300116099,
990099110300112517)
select * from tablename where sdigits in
(990099110300116094,
990099110300116099,
990099110300112517)

The above output is from a single file only.

Regards.
GVK

grvk101

View Public Profile for grvk101

Find all posts by grvk101

11-27-2017

Moderator

3,689, 1,352

Join Date: Jan 2012

Last Activity: 22 August 2020, 11:29 PM EDT

Location: Galactic Empire

Posts: 3,689

Thanks Given: 268

Thanked 1,352 Times in 1,258 Posts

I would suggest to use SQL*Loader to load data into a temp table and query table.

Or if you have privileges to use UTL_FILE package, then you can write a PL/SQL program to read text files and query table.

Yoda

View Public Profile for Yoda

Visit Yoda's homepage!

Find all posts by Yoda

11-27-2017

Registered User

12,315, 4,560

Join Date: Jul 2012

Last Activity: 22 November 2019, 4:29 PM EST

Location: San Jose, CA, USA

Posts: 12,315

Thanks Given: 952

Thanked 4,560 Times in 3,818 Posts

Quote:

Originally Posted by grvk101

Dear RUDIC,

Thnx for the edit.

@DON,

I made changes and the script is executing. But the output which I am getting is not a single in but in iterations.
The input file has 3 lines and output generated is as follows.

OUTPUT

Code:

select * from tablename where sdigits in
(990099110300112517)
select * from tablename where sdigits in
(990099110300116099,
990099110300112517)
select * from tablename where sdigits in
(990099110300116094,
990099110300116099,
990099110300112517)

The above output is from a single file only.

Regards.
GVK

Please try using the code I suggested in post #2:

Code:

filedate=$(date '+%d%m%Y')
awk '
/H\|/ {	pn[substr($0, 9, 18)]
}
END {	print "select * from tablename where sdigits in"
	sep = "("
	for(i in pn) {
		printf("%s%s", sep, i)
		sep = ",\n"
	}
	print ")"
}' $filedate*lpn*

exactly as shown above without removing line breaks and without adding line breaks that are in the above suggestion. There is a HUGE difference in the behavior of:

Code:

END { ... }

and:

Code:

END
{ ... }

This User Gave Thanks to Don Cragun For This Post:

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

Shell Programming and Scripting

Connect to database with shell output

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to connect to Oracle database using shell script?

Discussion started by: pmotewar

2. Shell Programming and Scripting

Shell Script to connect to the oracle database

Discussion started by: bshivali

3. Shell Programming and Scripting

Shell Linux to connect to a database and execute store procedure

Discussion started by: manichino74

4. UNIX for Dummies Questions & Answers

Shell scripting+connect to oracle database+spooling

Discussion started by: Mr Mo

5. Shell Programming and Scripting

how will i connect postgres database from unix shell script

Discussion started by: jambesh

6. Shell Programming and Scripting

How to connect to database db2 through Unix Shell Script

Discussion started by: ankitgupta

7. IP Networking

how you connect to a database

Discussion started by: ramneek

8. Shell Programming and Scripting

Connect to sybase database using Korn shell script

Discussion started by: bhgopi

9. Programming

How to connect database in TC

Discussion started by: Senthil

10. Shell Programming and Scripting

Can i connect from a .sh or .sc to a database?

Discussion started by: DebianJ