[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

input from user in sybase procedure

I am working on UNIX sun solaris platform and sybase database.
If i run "select @@version" in my database session , i get following output

Adaptive Server Enterprise/12.5.0.3/EBF 11441 ESD#4/P/Sun_svr4/OS 5.8/rel12503/1939/32-bit/FBO/Sun Sep 21 03:36:06 2003

This might help you to understand, where i am working.
Now question:

I am writing a sybase procedure
For that i have 2 questions
1)
          Can i get user's input in middle of procedure i.e.
          declare @choice char
          ask user to enter his choice
          read user's choice
          if @choice = 'y'
                 statement
          else
                statement
if YES, then how i can do this thing?

2)    in procedure can i have more than 1 statement in if's or else's result i.e
           if @choice = 'y'
                  statement 1
                  statement 2
             else
                   statement 3
                   statement 4

it will be much apriciated, if you can provide me a link where i can get syntex help about various commands used in sybase procedure.

Thanks
0
pattha
Asked:
pattha
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Joe WoodhousePrincipal ConsultantCommented:
1) I don't know of any way to do this. Sybase ASE procedures execute solely on the server, and don't have any interaction inside them. Usually you'd do this with interaction in the client application and then pass details as parameters to the procedure in the server.

2) You need to wrap these in "begin" and "end":

if @choice = 'y'
    begin
         statement 1
         statement 2
    end
    else
    begin
         statement 3
         statement 4
    end

3) Not sure what you're looking for in terms of syntax, but a good place to start are the Sybase manuals, all available (for your version) at

http://sybooks.sybase.com/asg1250e.html

Try the Transact-SQL User's Guide and the Reference Manuals.

Good luck!
0
 
koppchaCommented:
1> For your first question.I used to do this.If it is a SQL script then i used to replace the variables in the script with the corresponsding input and then execute the script.See this example
In the SQL script(myscriptTemplate.sql) i have this
declare @choice char
select @choice="REPLACE_WITH_USER_CHOICE" //Here is teh user choice which will be replace accordingly.
if @choice = 'y'
statement
else

you can use this perl command to replace the string with the input like
perl -i -pe 's/REPLACE_WITH_USER_CHOICE/y/g' myscriptTemplate.sql

this command will replace the string to  y.

Ok This is the sequence i follow
1>Create a copy of the script (cp myscriptTemplate.sql myscript.sql)
2>use the perl command to replace perl -i -pe 's/REPLACE_WITH_USER_CHOICE/y/g' myscript.sql
3>Run this script
4>after that you will delete the script

      The reason why i am doing like above is to keep one script as the template so that the string will never be changed and i will do the replacement in the copied file.

Hope this helps.
Good Luck

0
 
patthaAuthor Commented:
Hi koppcha,

I am not able to take help from your comment.
I am telling what i am doing.From my question, you must be knowing where i am working.
I have a shell script file called proc.
it contains


echo "enter pos"
read pos
echo "enter new value"
read NEW
isql -Urwausrep -Ppassword <<!
begin tran
declare @msg varchar(1024)
print "Before updating"
select ICT_MSG_TXT from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA58704123180305'
select @msg = substring(ICT_MSG_TXT,patindex("%$pos%",ICT_MSG_TXT)+4,2)
from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA20765427162501'
print @msg
if @msg = "CZ"
update ICT_INCMNG_CHSS_TXT
set
ICT_MSG_TXT =
(
select substring(ICT_MSG_TXT,1,patindex("%$pos%",ICT_MSG_TXT)+3) +"$NEW"+right(ICT_MSG_TXT,patindex("%$pos%",ICT_MSG_TXT)-6)
from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA20765427162501'
)
print "After updating"
select ICT_MSG_TXT from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA58704123180305'
declare @choice char
get user's choice into @choice
if @choice = 'y'
      commit tran
else
     rollback tran
print "Finally"
select ICT_MSG_TXT from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA58704123180305'
declare @choice char
go
!

Now my question id how to get user's choice into @choice during sybase query running.

Regards
pattha
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
Jan FranekCommented:
Ad 1
I can imagine quite complicated scenario how to accomplish what you need using some extended procedure and probably another program to get user's choice. But it's really complicated and I have never done anything like this, so I'm not sure, if it's really possible:

1. You have to create your own extended procedure (see Transact-SQL User Guide - Chapter 16 Using Extended Stored Procedures) - you will send user's IP address as parameter (and may be some text) and it will return user's choice. This procedure (probably written in C) will connect (using some network protocol) with program running on user's workstation and this way it will get that user's choice

2. You have to create that program, that will run on user's workstation - it will listen for requests from server (sent by your extended procedure), then it will pop-up, ask user for input and return this input back to that extended procedure. You have to ensure that this program always runs on user's worstation (you may include it in your application)


It's quite a lot of work and I think, that you should rather redesign your approach. I think that it's really bad idea to start transaction and then wait for user's input before commiting. You will probably run into serious locking problems.
0
 
Joe WoodhousePrincipal ConsultantCommented:
(Sorry for not responding sooner, I stopped receiving EE notification emails for some reason.)

Thank you for more details, it's now more clear what you're trying to do.

As I said before, there is no way to interact with the client during normal stored procedure execution.

Jan_Franek is correct that you could do this with an extended stored procedure - but extended stored procedures can't be rolled back since they're outside Sybase's control. I'm not sure how that would work for you, since if you used it you wouldn't be trying to roll it back. It might be that Sybase says they can't appear in a transaction.

From your example it seems that you want to get input from the user as to whether to commit or rollback the transaction. This is a very bad design decision! Good programming practice says there should be no user interaction within a transaction - what happens if the user goes to lunch, or goes home? The transaction remains open for maybe a very long time, which causes your transaction log to grow and grow, and also holds all locks... Something to think about.
0
 
koppchaCommented:
Here is what i would suggest you to do.As suggested by Joe it is not at all a good idea to look for user input inside the transaction because it locks all the tables or whatever you are using.
     I can see you were using here documents in shell scripting to run ISQL and trying to get the input from the user ...You can change you logic like this so that you can achieve what you are looking by being in a transaction.

Step 1:

Create seperate ISQL and place all you SQL code in that file.At the place where you are looking for the user input keep a string something like(REPLACE_WITH _USER_INPUT).So you ISQL file will be something like this.

use database//Usually it starts with using the database.
go
begin tran
declare @msg varchar(1024)
print "Before updating"
select ICT_MSG_TXT from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA58704123180305'
select @msg = substring(ICT_MSG_TXT,patindex("%$pos%",ICT_MSG_TXT)+4,2)
from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA20765427162501'
print @msg
if @msg = "CZ"
update ICT_INCMNG_CHSS_TXT
set
ICT_MSG_TXT =
(
select substring(ICT_MSG_TXT,1,patindex("%$pos%",ICT_MSG_TXT)+3) +"$NEW"+right(ICT_MSG_TXT,patindex("%$pos%",ICT_MSG_TXT)-6)
from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA20765427162501'
)
print "After updating"
select ICT_MSG_TXT from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA58704123180305'
declare @choice char
select @choice="REPLACE_WITH_USER_INPUT"//here is where you keep the user input later.
if @choice = 'y'
      commit tran
else
     rollback tran
print "Finally"
select ICT_MSG_TXT from ICT_INCMNG_CHSS_TXT where ICT_TXN_REF_NUM = 'NA58704123180305'
declare @choice char
go

Step2:
  In the shell script you will get the user input first in to a string.So in the schell you will ask the user to enter the input for ISQL and then you will collect that in to a variable(At this point ISQL is not yet started).

Step3:
  Now take the user input and replace the string in the ISQL file with that string using the perl -i -pe command i have given in my earlier comment.

Step4:
  Now you run the ISQL with -i option giving the changed ISQL as the input file.

Hope This Helps
Good Luck :)
0
 
Joe WoodhousePrincipal ConsultantCommented:
I think koppcha did most of the work on this one - maybe 50 me, 50 Jan_Franek, 150 koppcha?
0
 
Jan FranekCommented:
I agree with Joe's proposal
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now