Solved

Oracle Stored Procedure problem

Posted on 2003-11-24
9
334 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
  • 5
  • 4
9 Comments
 
LVL 10

Accepted Solution

by:
NetGroove earned 50 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When you need to keep track of a simple list of numbers or strings, the Array object is your most direct tool.  As we saw in my earlier EE Article (http://www.experts-exchange.com/A_3488.html), typical array handling might look like this: (CODE) B…
In this article, we'll look how to sort an Array in JavaScript, including the more advanced techniques of sorting a collection of records either ascending or descending on two or more fields. Basic Sorting of Arrays First, let's look at the …
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now