Oracle , ASP and SP with parameters....

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

IN order for this to work, don't forget to include the ADOVBS.inc file.

FtB

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

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

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...

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

IUSR_ account has no related with db permissions,  I think nothing happen.

>>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

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....

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

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

>>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

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?

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?

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)

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)

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"

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

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"

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)

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

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.

I think I defined the problem , it's in ODBC , sometimes work and somethime no,

don't know wot's the problem?

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

FtB

yes I think it's a network issue  , it has any relation with DNS???

Well, if your network isn't working properly, then you may have an issue connecting, right?

FtB

network work good, but I have some troubles with DNS, is it effect any thing?
and how can turn over?

Try setting up another connection with easy config and use the db server's ip address.  

I did.

should I configure easy config, in web server, right?

should I configure easy config, in web server, right?

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.

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...

it's not contains ORACLE

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

Uhm, you're welcome?

FtB

FtB , try your answer here: https://www.experts-exchange.com/questions/21156371/execute-SP.html   ;)

@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

actully I ask 3 different questions ...