Solved

Oracle , ASP and SP with parameters....

Posted on 2004-10-03
39
801 Views
Last Modified: 2008-02-07
Dear all,

I have the following SP created in oracle db:

PROCEDURE xxx
(                                  
 p_trx_value IN NUMBER,     p_cardnumber IN VARCHAR2,     p_merchant_id  IN NUMBER, p_mweb_id IN NUMBER,   p_cart_ID  IN VARCHAR2,            
 p_trx_id  IN VARCHAR2 ,     p_desc  IN VARCHAR2 ,   p_email IN VARCHAR2 ,  p_ipaddress IN VARCHAR2 ,  o_status OUT VARCHAR2,    
 o_cart_id OUT VARCHAR2,    o_trx_id OUT VARCHAR2,      o_trx_value OUT NUMBER,    o_status_desc OUT varchar2 )    

in ASP how can call that SP and pass all parameters to it and get output parameters from it?


Thanks.
0
Comment
Question by:ethar1
  • 18
  • 13
  • 8
39 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12211632
Here is an example from http://www.freevbcode.com/:

  Set objCon = New ADODB.Connection
    Set objCom = New ADODB.Command
   
'----------------->Change this to match your environment<---------------------------------------
    objCon.ConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=;PERSIST SECURITY INFO=TRUE;USER ID=sa;INITIAL CATALOG=TestSQL;DATA SOURCE=Rockets"
   
    'Opening the connection
    objCon.Open objCon.ConnectionString
       
    'assigning the command object parameters
    With objCom
        .CommandText = "GetRecords"     'Name of the stored procedure
        .CommandType = adCmdStoredProc  'Type : stored procedure
        .ActiveConnection = objCon.ConnectionString
    End With
   
    'Create 2 output parameters
    Set objPara = objCom.CreateParameter("rows", adInteger, adParamOutput)
    Set objpara2 = objCom.CreateParameter("Status", adVarChar,
adParamOutput, 50)
   
    'Append the output parameters to command object
    objCom.Parameters.Append objPara
    objCom.Parameters.Append objpara2
   
    'Store the result in a recordset
    Set objRS = objCom.Execute
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12211636
IN order for this to work, don't forget to include the ADOVBS.inc file.

FtB
0
 

Author Comment

by:ethar1
ID: 12211771
Can you please write exact code.
I already have this:


                    Set cmd = Server.CreateObject("ADODB.Command")
                    cmd.ActiveConnection = cno
                    cmd.CommandText = "xxx"
                    cmd.CommandType = adCmdStoredProc ' 4
                   
                    cmd.Prepared = True
                    cmd.Parameters.Refresh
   
                        cmd.Parameters.Append cmd.CreateParameter ("p_trx_value",   adInteger,adParamInput,30,fldAmount )
                        cmd.Parameters.Append cmd.CreateParameter ("p_cardnumber",  adVarChar,adParamInput,33,fldCardsValue )
                        cmd.Parameters.Append cmd.CreateParameter ("p_merchant_id", adInteger,adParamInput,30,fldMerchantID )
                        cmd.Parameters.Append cmd.CreateParameter ("p_mweb_id",     adInteger,adParamInput,30,fldWebSiteID )
                        cmd.Parameters.Append cmd.CreateParameter ("p_cart_ID",     adVarChar,adParamInput,50,fldCartId)
                        cmd.Parameters.Append cmd.CreateParameter ("p_trx_id",      adVarChar,adParamInput,50,fldPreTransactionID )
                        cmd.Parameters.Append cmd.CreateParameter ("p_desc",        adVarChar,adParamInput,100,fldCartDesc)
                        cmd.Parameters.Append cmd.CreateParameter ("p_email",       adVarChar,adParamInput,50,fldCustomerEmail )
                        cmd.Parameters.Append cmd.CreateParameter ("p_ipaddress",   adVarChar,adParamInput,20,fldCustomerIP  )



                        cmd.Parameters.Append cmd.CreateParameter ("o_status",     adVarChar, adParamOutput,10)
                        cmd.Parameters.Append cmd.CreateParameter ("o_cart_id",    adVarChar, adParamOutput,50)
                        cmd.Parameters.Append cmd.CreateParameter ("o_trx_id",     adVarChar, adParamOutput,50)
                        cmd.Parameters.Append cmd.CreateParameter ("o_trx_value",  adInteger ,adParamOutput,30)
                        cmd.Parameters.Append cmd.CreateParameter ("o_status_desc",adVarChar ,adParamOutput,100)
   

                    cmd.Execute




0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12211950
So when you run this, does all of the input take place? Also, what if you change this line:

 cmd.Execute

to this:

 Set objRS = cmd.Execute

Do you get a recordset with the values that you are looking for?

FtB
0
 

Author Comment

by:ethar1
ID: 12212172
The problem that I can't get the sp run, its always give an error, sometimes the coputer hang and give no response at all...

I change the SP to smallest one , after run it for twice or something the computer hang.

I have IIS server (on windows 2000) connected to oracle server (on windows 2000)


when I test the db connection using "oracle odbc test" every thing work,
but when run from asp it's hang...

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12212210
When it does run, does the SP execute correctly or does nothing happen? Are you sure that the IUSR_ account has the proper permissions to your database?

FtB
0
 

Author Comment

by:ethar1
ID: 12212220
IUSR_ account has no related with db permissions,  I think nothing happen.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12212237
>>IUSR_ account has no related with db permissions<<

If you try to execute a stored procedure through an .asp page, then it will be using the IUSR account. If that account does not have sufficient permissions, nothing will work.

FtB
0
 

Author Comment

by:ethar1
ID: 12212363
then wot's the appropriate permissions shoud I give to IUSR_ account ?

but I got some error mesage from server regarding something error in parameters...

I am at home now I don't remember the error....
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12212448
Okay, let's revisit this when you are back at home.

I don't know oracle well enogh to say, but I imagine that you will need enough permisssions to read add and edit data.

FtB
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 12219223
It's the ID in your connection string that needs permission.  You should be all set seeing that you have ID=sa.

I think you want to be using adNumeric instead of adInteger and adVariant instead of adVarChar...
http://www.w3schools.com/ado/ado_datatypes.asp
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12219856
>>It's the ID in your connection string that needs permission.  You should be all set seeing that you have ID=sa.<<

I am not sure that is the case--you are reading my sample code, not the questioner's, and I am not sure how his/her connection code works.

FtB
0
 

Author Comment

by:ethar1
ID: 12226169
I am trying to run this code now and it's take so much time and give nothing:

         stroConn = "gateway1"
         stroLogin = "xxx"
         stroPassword = "xxx"        
         Dim cno : Set cno = Server.CreateObject("ADODB.Connection")        
         cno.open stroConn, stroLogin, stroPassword
               If cno.state  = 1 Then
                  Response.Write "<BR>OPEND"                  
                    Set cmd = Server.CreateObject("ADODB.Command")
                    cmd.ActiveConnection = cno
                    cmd.CommandText = "x"
                    cmd.CommandType = 4                    
                    cmd.Parameters.Append cmd.CreateParameter ("y", adVariant, adParamInput,10, "10" )
                    cmd.Parameters.Append cmd.CreateParameter ("z", adVariant, adParamOutput, 10)                        
                    Set objRS = cmd.Execute             
               End If
cno.close
set cno = Nothing


============

Think I have problem with connection between web server and oracle server , both is windows2k...
should I do something with security in both computers?
also somethin i got this error message :
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00000: normal, successful completion
/web/gateway/testdb.asp, line 14


I give I_User administrator permissions.


any suggestions?
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 12226443
Can you run x when you log into sql plus on the db server with xxx?
What about from sql worksheet or something else on your web server.
Can you run a simple SQL query ("select sysdate from dual") from asp?
0
 

Author Comment

by:ethar1
ID: 12227037
Yes, I  am able to run everything from web server using sql+

But can't run anything even "select sysdate from dual" using ASP, it's act same... (hung)
0
 

Author Comment

by:ethar1
ID: 12227170
I have oracle client installed on the web server ( I tried the SP and SQL you give work great).
=========
I have very strange case here:
just reboot the web server then try to run ASP which is OPEN DB ONLY , it's work great.

when I try to run ASP to OPEN AND EXECUTE any SP or sql statment it's hung... then again try to execute OPEN DB ONLY its also hung.

When reboot the machine again and run ASP with open DB only it's work K..
==============
Also I got some error message like this :
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC driver for Oracle][Oracle]ORA-12500: TNS:listener failed to start a dedicated server process
/web/gateway/y.asp, line 14 ( cno.open stroConn, stroLogin, stroPassword)



0
 
LVL 15

Expert Comment

by:joeposter649
ID: 12227591
The above error sounds like it's defaulting to the Oracle provider.  You probably have to give iusr_machine permission to access the folder where the Oracle provider is installed (be sure to include subfolders).

What is "gateway1"?  Is that the connection you set up?  
Is that the same connection you used with sql+?
Did you set up and test a connection with easy config?

Try this for a connection string ...
"Provider=MSDAORA.1;Password=xxx;User ID=xxx;Data Source=theSourceYouSetUp"
or
"Provider=OraOLEDB.Oracle.1;Password=xxx;User ID=xxx;Data Source=theSourceYouSetUp"



0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12228713
That is sort of what I was getting at above--this is going to happen under the aegis of IUSR_.

One other thing, don't execute anything yet. Just try creating your connection and then doing this:

        stroConn = "gateway1"
         stroLogin = "xxx"
         stroPassword = "xxx"        
         Dim cno : Set cno = Server.CreateObject("ADODB.Connection")        
         cno.open stroConn, stroLogin, stroPassword
         response.write("The connection state is: " & cno.state)
         response.end

That way, we can determine where things are hanging.

FtB
0
 

Author Comment

by:ethar1
ID: 12228766
The above error comes after a while of restarting the web server only.

>>What is "gateway1"?  Is that the connection you set up?  
>>Is that the same connection you used with sql+?
>>Did you set up and test a connection with easy config?
YES
And I test it in easy config...


I already try both for the connection
Try this for a connection string ...
"Provider=MSDAORA.1;Password=xxx;User ID=xxx;Data Source=theSourceYouSetUp"
or
"Provider=OraOLEDB.Oracle.1;Password=xxx;User ID=xxx;Data Source=theSourceYouSetUp"
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 15

Expert Comment

by:joeposter649
ID: 12229367
I've seen lots of different connection strings but I've never seen one set to only the data source name.
Shouldn't it be stroConn = "Data Source=gateway1;" at a minimum?

Also, I've found it handy to debug things like this by creating vbs scripts that are run at the command prompt with the current userid.

For instance if this VBS runs from the command prompt but a similar asp page doesn't it's probably an iusr permissions problem.
test.vbs...
Set cn = CreateObject("ADODB.Connection")
WScript.Echo "ADO Version=" + cn.Version
WScript.Echo "Connection State=" + cstr(cn.State)
cn.open "Provider=MSDAORA.1;Password=xxx;User ID=xxx;Data Source=gateway1"
WScript.Echo "Connection State=" + cstr(cn.State)
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12229399
I don't know how persmissions are set in Oracle, but in SQL server, if you are going to use Windows Authentication rather than a username password in the connection string, you would need to provide the security rules for the IUSR account in SQL server proper. This is what I was getting at way up near the top of the thread.

If you can configure security to use credentials passed in the connection string instead, then you wouldn't need to worry about configuring permissions for the IUSR account.

FtB
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 12229447
Something else that might help...  
When you use easy config to set up a connection it really just updates a tnsnames.ora file.  Open the file in notepad and see if it looks reasononable.  Also, make sure iusr has permissions to this file.
0
 

Author Comment

by:ethar1
ID: 12235417
I think I defined the problem , it's in ODBC , sometimes work and somethime no,

don't know wot's the problem?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12237171
You may have to look through the Oracle knowledgebase for that? Also, could it be a network issue?

FtB
0
 

Author Comment

by:ethar1
ID: 12237205
yes I think it's a network issue  , it has any relation with DNS???
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12237218
Well, if your network isn't working properly, then you may have an issue connecting, right?

FtB
0
 

Author Comment

by:ethar1
ID: 12237314
network work good, but I have some troubles with DNS, is it effect any thing?
and how can turn over?
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 12237344
Try setting up another connection with easy config and use the db server's ip address.  
0
 

Author Comment

by:ethar1
ID: 12237354
I did.
0
 

Author Comment

by:ethar1
ID: 12237362
should I configure easy config, in web server, right?
0
 

Author Comment

by:ethar1
ID: 12237366
should I configure easy config, in web server, right?
0
 
LVL 15

Expert Comment

by:joeposter649
ID: 12237618
Yes, on the web server you should have installed the Oracle client and used easy config to set up a connection.  
The db server should have a listener running that is compatible with the Oracle client used on the web server.
If you used the ip address to set up the connection it wouldn't be a problem with DNS unless the URL of the web page can't be resolved.
The ODBC layer is bypassed if you use the oledb connection strings so that shouldn't be an issue.
http://www.devx.com/asp/Article/16802/0/page/3

What did you get when your tried FtB's code to print out connection state?
Did you try the vbs I gave?
I would start with FtB's code then the vbs to systematically troubleshoot this.
0
 

Author Comment

by:ethar1
ID: 12237714
the problem that I can't get ODBC connection stable,
now let's forget about the code, I have problem in ODBC it self, sometime work and sometime not.
I tried every possible solution, I change the web computer ...

I will read the page you sent...
0
 

Author Comment

by:ethar1
ID: 12237874
it's not contains ORACLE

StrConnString = "Provider=SQLOLEDB;" & _
      "Password=YourPassword;" & _
      "User ID=YourUserID;" & _
      "Initial Catalog=YourDatabaseName;" & _
      "Data Source=YourServerNameOrIPAddress;"
0
 
LVL 15

Accepted Solution

by:
joeposter649 earned 500 total points
ID: 12238254
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12248199
Uhm, you're welcome?

FtB
0
 

Author Comment

by:ethar1
ID: 12250747
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12250855
@ethar1--

Thanks for the gesture, but EE allows a maximum of 500 points for any one question. You should ask community support to delete the one at the link that you just posted.

FtB
0
 

Author Comment

by:ethar1
ID: 12250907
actully I ask 3 different questions ...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

11 Experts available now in Live!

Get 1:1 Help Now