• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Unix shell scripting(SQL problem)

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
lindatan83
Asked:
lindatan83
1 Solution
 
HamdyHassanCommented:
(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
 
jimbbCommented:
Also don't use spaces in your shell variable assignments.
0
 
lindatan83Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Vinit KainCommented:
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
 
HamdyHassanCommented:
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
 
lindatan83Author Commented:
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
 
lindatan83Author Commented:
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
 
HamdyHassanCommented:
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
 
liddlerCommented:
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
 
Computer101Commented:
PAQed - no points refunded (of 50)

Computer101
E-E Admin
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now