database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
ㆍInformix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Informix Q&A 2487 게시물 읽기
No. 2487
[TIP] embedding SQL in UNIX Script
작성자
김선규
작성일
2008-03-07 11:30
조회수
8,830

우선 게시판 성격에 맞지 않는 글을 올려서 죄송합니다. devel이나 튜토리얼쪽에는 쓰기권한이 없어서 정보를 공유하고자 여기에 올립니다.


DB내의 쿼리를 쓰면서 Shell 에서도 유용하게 쓸 수 있는 Script 작성 방법을 알려드리고자 합니다. 다 아시다시피 Scipt는 컴파일 할 필요도 없고 어떤 시스템에서도 구동할 수 있다는 장점이 있어서 많은 시스템 관리자들이 유용하게 사용들을 하고 있죠. 이런 Script 내에서 SQL문을 삽입하여 사용을 할 수 있는 방법이 있습니다.


다음의 4가지 예제를 통하여 확인해보시고 각자에 맞게 확장된 script를 만들어서 유용하게 사용하시기 바랍니다.

아래의 예제는 informix의 가장 기본적인 sample인 stores_demo를 이용해서 만들어졌습니다.


1. 직접입력(Redirecting input)


#!/bin/sh

dbaccess <<SQLSTMT

    database stores_demo;

    select customer_num, fname, lname, company from customer;

SQLSTMT


----------------

sql문을 실행함으로 결과값을 얻을 수 있는 간단한 shell 입니다. 여기서 보이고 있는 SQLSTNT는 단순한 입력 한자리 단어로 마음에 드는 것으로 바꾸어 사용해도 잘 실행이 됩니다. 주의할 점은 sql문 시작시와 끝날때 꼭 같이 넣어주어야 한다는 것입니다.



2. 직접출력(redirecting output)


#!/bin/sh

{

dbaccess 2>error.log <<SQLSTMT

    database stores_demo;

    select customer_num, fname, lname, company from customer;

SQLSTMT

} | more


---------------------------

error.log 라는 필드를 넣음으로 shell 실행 시 발생되는 messages를 저장할 수 도 있고 { } 를 사용한 이후 pipe를 이용하여 more 같은 unix의 다른 프로그램 사용도 가능합니다.



3. Shell변수이용(Using Shell Variable)


#!/bin/sh

echo "Enter company name (use * for wildcard matches) to find"

echo "Company : \c"

read comp

dbaccess 2>error.log <<SQLSTMT

    database stores_demo;

    select customer_num, fname, lname, company from customer

    where company matches "$comp";

SQLSTMT


----------------------------

Shell의 변수들을 이용하여 값을 얻어낼 수 도 있습니다. shell 상에서 comp에 값을 입력해주면 where 구문에서 변수값으로 받아 찾아낸 값을 출력해주는 방식입니다. 이런 정도만 사용하는 곳이라면 복잡하게 프로그램 짜거나 일일히 db에 접속하지 않고 단순히 shell만 돌려도 되겠죠?



4. Unix 프로그램에 Shell을 이용해 얻어낸 Data 사용(Getting Data into Shell Variables)


#!/bin/sh

today=`date +%m/%d/%y` # get today's date

{

dbaccess 2>error.log <<SQLSTMT

    database stores_demo;

    output to pipe "pr -t" without headings

    select customer_num, fname,lname,company from customer;

SQLSTMT

} | while read line # pipe the output to while read

do

if [ "$line" ] # check if line is not NULL

then

    # First parse the line into words/variables using set 

    set $line # assign the line to positional variables

    name="$2 $3" # get the second and third variable for name

    # company name may include spaces, $4 is only the first word

    # so we discard the first 3 positions and assign the

    # rest of the line to the comp variable

    shift 3 # discard the first three variables

    comp="$*" # let all remaining variables = the company

    ## Start of simple form letter`

    echo "Date: $today"

    echo "To: $name"

    echo " $comp"

    echo "Thank you for your business" 

    echo " "

fi

done 

----------------------------------------

output이라는 sql내의 명령문을 이용해서 unix의 다른 프로그램으로도 연결할 수 있습니다. 이 예제는 메일머지에 사용된 예제입니다. 다음은 결과입니다.


.............

Date : 03/07/08

To : Chris Putnum

Putnum's Putters

Thank you for your business

Date : 03/07/08

To : James Henry

Total Fitness Sports

Thank you for your business

................



머.. 이 간단한 예제를 더욱 발전시켜서 여러분들의 환경에 잘 써보시기 바랍니다. 아참, 이것은 제가 만든것이 아니고 IIUG의 memeber중의 한명이 Lester Knutsen이 만든것입니다. 다음은 원문입니다.


Simple shell scripts play a role in small programs and very complex applications.

One of the advantages of Unix and Linux is the ability to use scripts for developing systems and programs. I’ll introduce you to using shell scripts with embedded SQL to access your database. Shell scripts are easy to write, they don’t need to be compiled, and are great for small batch programs.

Shell scripts do have their limits. They don’t provide a nice GUI interface and, because they aren’t compiled, everyone gets to see the source code. These limitations, though, are minor trade-offs.

In these examples, I use an Informix database and the Informix SQL command interpreter dbaccess. However, the examples will also work with Informix isql — and should work with any database that lets you redirect standard input and output. The basic items I’ll explore are: redirecting input and output, passing shell variables to SQL, and setting shell variables with the results of SQL commands.

Redirecting Input

One way to include SQL commands in a shell script is to redirect standard input to dbaccess from within the shell script. The example shell script in Listing 1 takes an SQL statement and redirects the SQL so that it is executed by dbaccess.

When dbaccess starts, it expects a database name and SQL script name as its arguments; if they’re not available, dbaccess will display menus and prompt you for them. The two dashes (- -) indicate to dbaccess that the database and commands will come from standard input. The << indicates to the shell that standard input is redirected and that everything between the two occurrences of SQLSTMT is to be passed to dbaccess as standard input. This is called a “here document” in Unix shell scripts.

The program dbaccess treats these lines as if you had typed them in from the keyboard. This is like typing dbaccess - - < filename, where filename is a file with the SQL commands. You don’t have to use SQLSTMT, but you do need two identical words to mark the beginning and end of input redirection. Running this script will start dbaccess and process SQL commands. The first command will open the stores_demo database and the next command will display the name and company of all the customers. You can use any valid SQL command that dbaccess will execute.

Redirecting Output

If you have a large customer table, the output will scroll off the screen. Like most Unix programs, Informix dbaccess will send output to two standard devices that are normally defined as your terminal window. Data goes to standard output, and processing messages go to standard error. In Listing 1, the names and companies are sent to standard output and the two messages (Database selected and 99 row(s) retrieved) are sent to standard error. These can be redirected to a file by changing line 2 in Listing 1 to:

dbaccess - - >cust.rpt 2>error.log <<SQLSTMT

The first > sends standard out (data) to a file cust.rpt and the 2> sends standard error (messages) to an error.log.

What’s more useful is to send data to a paging program (such as more) and messages to a log file (see Listing 2).

Notice that I’ve removed the first > and added a pair of { }. The pair of { } instruct the shell to execute the enclosed statements as a group. This instruction is useful to pipe the output to another program, such as more.

Using Shell Variables

You can use shell variables and prompts with SQL. The example in Listing 3 prompts for a company name and passes the variable to SQL for use in the SELECT statement. Entering an A* at the prompt would select all companies whose names begin with the letter A.

Getting Data Into Shell Variables

The results of an SQL command can be inserted into shell variables. The following is a simple example of a mail-merge program, selecting names and companies from a database, setting shell variables, and merging that data with some text. There are better ways to do this with the programming tools that come with a database, but this example illustrates the power of embedding SQL in shell scripts (see Listing 4).

In this example, the output is sent to a WHILE loop. The WHILE loop reads each line of output until it’s done. Each line is broken apart into words by the set command. The first word is assigned $1, the second $2, and so on. name=”$2 $3” gets the name of the person. The company name is more difficult because it may contain spaces. The name “Big Company” would be broken into to two variables. The command shift 3 discards the first three variables, and what was $4 becomes $1. All remaining variables are assigned to the company name with comp=$*.

Mastering Complexity

I’ve just scratched the surface of what you can do with embedded SQL in shell scripts. Many complex applications, such as billing and scheduling systems, are built using SQL in shell scripts. Now you have the tools to put this technique to use.


Lester Knutsen is president of Advanced DataTools Corp., an IBM Informix consulting and training partner specializing in data warehouse development, database design, performance tuning, and Informix training and support. He is president of the Washington D.C. Area Informix User Group, a founding member of the International Informix Users Group, and an IBM gold consultant.


LISTING 1. A shell script for redirecting SQL to dbaccess.


#!/bin/sh

dbaccess <<SQLSTMT

    database stores_demo;

    select customer_num, fname, lname, company from customer;

SQLSTMT

 


LISTING 2. A shell script to send data to a paging program and messages to a log file.


#!/bin/sh

{

dbaccess 2>error.log <<SQLSTMT

    database stores_demo;

    select customer_num, fname, lname, company from customer;

SQLSTMT

} | more

 


LISTING 3. Using a shell variable with SQL.


#!/bin/sh

echo "Enter company name (use * for wildcard matches) to find"

echo "Company : \c"

read comp

dbaccess 2>error.log <<SQLSTMT

    database stores_demo;

    select customer_num, fname, lname, company from customer

    where company matches "$comp";

SQLSTMT

 


LISTING 4. Setting shell variables and merging data with text.


#!/bin/sh

today=`date +%m/%d/%y` # get today's date

{

dbaccess 2>error.log <<SQLSTMT

    database stores_demo;

    output to pipe "pr t" without headings

    select customer_num, fname,lname,company from customer;

SQLSTMT

} | while read line # pipe the output to while read

do

if [ "$line" ] # check if line is not NULL

then

    # First parse the line into words/variables using set

    set $line # assign the line to positional variables

    name="$2 $3" # get the second and third variable for name

    # company name may include spaces, $4 is only the first word

    # so we discard the first 3 positions and assign the

    # rest of the line to the comp variable

    shift 3 # discard the first three variables

    comp="$*" # let all remaining variables = the company

    ## Start of simple form letter`

    echo "Date: $today"

    echo "To: $name"

    echo " $comp"

    echo "Thank you for your business"

fi

done

---------------------------------------

EXAMPLE OUTPUT:

Date: 11/15/93

To: Frank Lessor

Phoenix University

Thank you for your business

[Top]
No.
제목
작성자
작성일
조회
2490[TIP] Enhanced variable length row compression
김선규
2008-03-14
6076
2489인포믹스 버전 업그레이드할때 문제점.. [1]
김기환
2008-03-13
5888
2488lvarchar 사용시 Max설정문제 [1]
초보
2008-03-12
6722
2487[TIP] embedding SQL in UNIX Script
김선규
2008-03-07
8830
2486[급] 쿼리 성능 향상에 대한 질문 [1]
이승훈
2008-03-06
7279
2485권한에 대해... [1]
최재환
2008-03-06
6760
2484instance 띄우기.... [2]
초보
2008-03-05
5820
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2021 DSN, All rights reserved.
작업시간: 0.012초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다