Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle Stored Procedure problem

Posted on 2003-11-24
9
Medium Priority
?
357 Views
Last Modified: 2008-03-04
I have an oracle stored procedure that looks something like this...

grant_role(
inEmployee IN NUMBER,
inRole IN VARCHAR2,
inGrant IN BOOLEAN,
status OUT NUMBER)

I want to run this in an ASP page using JScript...this is what I have so far...

var conn = Server.CreateObject("ADODB.Connection");
var comm = Server.CreateObject("ADODB.Command");
conn.Open(myConnection);  // This is setup in an include file

comm.CommandText = "GRANT_ROLE";
comm.CommandType = 4;
comm.Name="GRANT_ROLE";

comm.Parameters.Append(comm.CreateParameter("return", 3, 4));
comm.Parameters.Append(comm.CreateParameter("empl", 3, 1, 12306));
comm.Parameters.Append(comm.CreateParameter("role", 200, 1, "admin");
comm.Parameters.Append(comm.CreateParameter("grant", 11, 1, true));
comm.Parameters.Append(comm.CreateParameter("status", 3, 2));
comm.ActiveConnection = conn;
comm.Execute();

I keep getting a Type Mismatch error on the line that has 'role' being appended.  Not sure if that's because 'role' is problematic, or boolean though, as I've done a bunch of reading on forums lately that said there were problems with using / passing boolean to oracle from JScript :(

Any suggestions would be GREATLY appreciated.  Thanks in advance :)
0
Comment
Question by:ShaymusBane2
[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
  • 5
  • 4
9 Comments
 
LVL 10

Accepted Solution

by:
NetGroove earned 150 total points
ID: 9813900
If you know that, why then do you not change your stored procedure to take a number for grant?
Lats say, zero is false and one is true.

0
 

Author Comment

by:ShaymusBane2
ID: 9814132
The application that I'm creating has to run in conjunction with an existing application, and if I make changes to the database the existing application will no longer function.  

Soooo...my hands are somewhat tied.  I wish I could just change the database though....but the easy way is seldom available ;)
0
 
LVL 10

Expert Comment

by:NetGroove
ID: 9814212
Did you try to pass 0 for fals and 1 for treu without changing the procedure?
Try also -1 for true.
0
Technology Partners: 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!

 

Author Comment

by:ShaymusBane2
ID: 9814369
I had tried 1 and 0 for true / false values (hadn't thought to try -1) buy they didn't work (neither did the -1).

As I said though, the error keeps saying the mismatch is occurring on the line where 'role' is declared though.  I have used the same (similar) syntax as I posted on other stored procedures, EXCEPT, none of them have had a return value, a varchar2 or a boolean in them before...all just numbers.  So not sure if maybe that is where the problem lies.  

Thanks for the suggestions though NetGroove...as I said, I hadn't thought of trying -1...shame it didn't work out though :(
0
 
LVL 10

Expert Comment

by:NetGroove
ID: 9814446
Uhps, the position where you specify the value is the optional position for SIZE.

Look here:
http://www.w3schools.com/ado/met_comm_createparameter.asp

That say:
comm.Parameters.Append(comm.CreateParameter("return", 3, null, 4));
comm.Parameters.Append(comm.CreateParameter("empl", 3, 1, null, 12306));
comm.Parameters.Append(comm.CreateParameter("role", 200, 1, null, "admin");
comm.Parameters.Append(comm.CreateParameter("grant", 11, 1, null, true));
comm.Parameters.Append(comm.CreateParameter("status", 3, null, 2));

0
 
LVL 10

Expert Comment

by:NetGroove
ID: 9814498
Or you provide the length.

comm.Parameters.Append(comm.CreateParameter("return", 3, 4));
comm.Parameters.Append(comm.CreateParameter("empl", 3, 1, 0, 12306));
comm.Parameters.Append(comm.CreateParameter("role", 200, 1, 32, "admin");
comm.Parameters.Append(comm.CreateParameter("grant", 11, 1, 0, true));
comm.Parameters.Append(comm.CreateParameter("status", 3, 2));

0
 
LVL 10

Expert Comment

by:NetGroove
ID: 9814521
Perhaps this:

userName = "admin";
comm.Parameters.Append(comm.CreateParameter("return", 3, 4));
comm.Parameters.Append(comm.CreateParameter("empl", 3, 1, 0, 12306));
comm.Parameters.Append(comm.CreateParameter("role", 200, 1, userName.length+1, userName);
comm.Parameters.Append(comm.CreateParameter("grant", 11, 1, 0, true));
comm.Parameters.Append(comm.CreateParameter("status", 3, 2));

0
 

Author Comment

by:ShaymusBane2
ID: 9868680
None of those seemed to work :(   One thing I did, was in PL/SQL, I retested to make sure that the actual stored procedure was working...it ONLY works when I place single quotes '  ' around the role....as a value that is.  If I do double quotes in Oracle it fails...wondering if this may be part of the reason why?  

Sorry for the delay on any responses, but I have been ill the past week+ and havn't had a chance to come in and try all the suggestions....thank you VERY much for them all, by the way
0
 

Author Comment

by:ShaymusBane2
ID: 9897902
After sitting down and talking (read 'bribing') my DBA, I was able to get a second stored procedure written that took out the boolean!  

SEEMS to be working now (no errors are occurring), but the procedure hangs.  It looks like the script isn't running, but as far as I know, the web server has scripts enabled...anything further would be appreciated...once again, sorry for the long response delay, but working around here can take a LONG time to get things done... ;)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

I've been trying to accomplish this for a while and it just struck me yesterday how to accomplish this task. I have done searches all over the internet looking for ways to email pages from my applications and finally I have done it!!! Every single s…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

715 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