?
Solved

Unix shell scripting(SQL problem)

Posted on 2003-02-26
11
Medium Priority
?
254 Views
Last Modified: 2013-12-26
I hae a program but now havin some problem with it.
can anyone please kindly help me.. thank alot.

question: i had a script that will retrieve data from the database but it just dont echo out the result. is there any problem with my script?
And also
what is isql use for??
what is ISQLCTO??
what is |sed -n 's/^#//P' ??
thank a lot...

my script==>

#!/bin/sh

echo $1
 
#   -S==>server
#   -U==>user id
#   -P==>password
#   -I==>interface file
#   -W==>column width

ISQLCTO = "isql"
(ISQLCTO << starthere
--Get value in dp_hlr_job_dtl
declare cn_cntr_cur cursor for
select arriive_dt
from dp_hlr_job_dtl shared
where $1
for read only
go

declare @cntr_n char(13)

open cn_cntr_cur
fetch cn_cntr_cur into arrive_dt
while (@@sqlStatus = 0)
begin
select "#", arrive_dt + "," as ##
from dp_hlr_job_dtl
where cntr_n = $1

deallocate cursor cn_cntr_cur
go
starthere
) sed -n 's/^#//P'
echo ""
echo linda




From: linda..
0
Comment
Question by:lindatan83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8033988
(1) isql is sybase program that run sql script.

do the following and let me know the output
prompt> which isql


(2) what is ISQLCTO??
Just script variable to hold the program name "isql"
ISQLCTO = "isql"

but this is missing -U user -P password -S servername

try from unix prompt
prompt> isql

and let me know what do you get


let me know so we can continue

0
 
LVL 2

Expert Comment

by:jimbb
ID: 8035370
Also don't use spaces in your shell variable assignments.
0
 

Author Comment

by:lindatan83
ID: 8038882
To HamdyHassan:

i forgot to add in this

ISQLCTO = "isql -S CTODB_DS -U zzctor -P abc123 -w 300"

so sorry.. please help again
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:tfewster
ID: 8040426
0
 
LVL 4

Expert Comment

by:Vinit Kain
ID: 8040539
Hi,

Here is your updated script

#!/bin/sh

echo $1

#   -S==>server
#   -U==>user id
#   -P==>password
#   -I==>interface file
#   -W==>column width

isql -Uusername -Ppassword -Sservername << starthere
--Get value in dp_hlr_job_dtl
declare cn_cntr_cur cursor for
select arriive_dt
from dp_hlr_job_dtl shared
where $1
for read only
go

declare @cntr_n char(13)

open cn_cntr_cur
fetch cn_cntr_cur into arrive_dt
while (@@sqlStatus = 0)
begin
select "#", arrive_dt + "," as ##
from dp_hlr_job_dtl
where cntr_n = $1

deallocate cursor cn_cntr_cur
go
starthere| sed -n 's/^#//P'
echo ""
echo linda
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8041456
that's better, please fix the following "where $1"

from dp_hlr_job_dtl shared
where $1

It should be something like
where fieldname = $1


Also when you call this script, you need to pass a paramter
so if the script name is myscript.sh, then from command prompt you should say

prompt > myscript 1000



if you fix the sql , follow these steps ....

(1) create text file mysql.sql and put the following code

declare cn_cntr_cur cursor for
select arriive_dt
from dp_hlr_job_dtl shared
where cntr_n = 1000
for read only
go

declare @cntr_n char(13)

open cn_cntr_cur
fetch cn_cntr_cur into arrive_dt
while (@@sqlStatus = 0)
begin
select "#", arrive_dt + "," as ##
from dp_hlr_job_dtl
where cntr_n = 1000

deallocate cursor cn_cntr_cur
go


(2) Now we need to test that by run the following command

isql -S CTODB_DS -U zzctor -P abc123 -w 300 -i mysql.sql


Let me know if the step number 2 is working, it should produce some
0
 

Author Comment

by:lindatan83
ID: 8076969
to HamdyHassan
 the code dont really work. error found:
ISQLCTO not found
declare not found
select not found
cant open var/amil/dp_hlr_job_dtl
where not found
syntax error at line11: only expected.

so sorry.... pls bear with me... because i just learn this unix.. pls help.

this is the code that i hav re-write in mysql.sql

declare cn_cntr_cur cursor for
select arriive_dt
from dp_hlr_job_dtl shared
where cntr_n = 1000
for read only
go

declare @cntr_n char(13)

open cn_cntr_cur
fetch cn_cntr_cur into arrive_dt
while (@@sqlStatus = 0)
begin
select "#", arrive_dt + "," as ##
from dp_hlr_job_dtl
where cntr_n = 1000

deallocate cursor cn_cntr_cur
go


 and run with the command "isql -S ctodb_ds -u username -p pwd mysql.sql"
0
 

Author Comment

by:lindatan83
ID: 8077037
to HamdyHassan
 the code dont really work. error found:
ISQLCTO not found
declare not found
select not found
cant open var/amil/dp_hlr_job_dtl
where not found
syntax error at line11: only expected.

so sorry.... pls bear with me... because i just learn this unix.. pls help.

this is the code that i hav re-write in mysql.sql

declare cn_cntr_cur cursor for
select arriive_dt
from dp_hlr_job_dtl shared
where cntr_n = 1000
for read only
go

declare @cntr_n char(13)

open cn_cntr_cur
fetch cn_cntr_cur into arrive_dt
while (@@sqlStatus = 0)
begin
select "#", arrive_dt + "," as ##
from dp_hlr_job_dtl
where cntr_n = 1000

deallocate cursor cn_cntr_cur
go


 and run with the command "isql -S ctodb_ds -u username -p pwd mysql.sql"
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8079463
ok, try this and let me know if this working


(1) create text file mysql_one.sql and put the following code

select arriive_dt
from   dp_hlr_job_dtl
where  cntr_n = 1000
go


(2) Now we need to test by run the following command

isql -S CTODB_DS -U zzctor -P abc123 -w 300 -i mysql_one.sql




Let me know ASAP if this working, so we can build on that.
Are you using sybase or oracle or what?
You can declare cursor at SQL script, you need storeproc.




0
 
LVL 18

Expert Comment

by:liddler
ID: 9827112
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ  No refund

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

liddler
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 9871239
PAQed - no points refunded (of 50)

Computer101
E-E Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

764 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