Parameter problems when trying to call a Stored procedure using ADO

[ToeJamNEarl]

Hey Guys,

I am trying to call a stored procedure with ADO command object as well as connection.

I have the stored procedure return a reference cursor, and the way I call my stored procedure is:
set objRS = datCMD1.execute

The application gives me an error saying wrong type or number of parameters. I have the right number and type of parameters in my code.

This is an example of how I have created a parameter:

Set prmDatabase = datCmd1.CreateParameter("p_Database", adVarChar, adParamInput, 200)
datCmd1.Parameters.Append prmDatabase
prmDatabase.Value = strChosenDB

Any clues if I am doing this wrong? Please help.

The provider I am using is MSDAORA. I used to use provider OraOLEDB, but a testers computer keeps crashing when they try to run my application for reasons I don't know why.

Please help if you can.

Thank you,

-Diran

 

[blweb]

ToeJam,

It has to deal with how you are setting up your parameter.

    Set objCmd = New ADODB.Command
    With objCmd
        .ActiveConnection = PROJ_DB
        .CommandType = adCmdStoredProc
        .CommandText = "sel_from_tbl"
        Set objParm = .CreateParameter("@Parm1", adVarChar, adParamInput, 100, sTbl)
        .Parameters.Append objParm
    End With

Look at the .CreateParameter. The first string in quotations is the name of the variable in the stored proceedure. After that fill the appropriate information into the parameters, last of which being the value being passed in.

Cheers,
Blweb

 

[blweb]

Forgot. More specifically here's what's wrong.

Set prmDatabase = datCmd1.CreateParameter("p_Database", adVarChar, adParamInput, 200)

You're missing one parameter.

It should look like this.

Set prmDatabase = datCmd1.CreateParameter("p_Database", adVarChar, adParamInput, <length of string>, <your value passing in>)


Cheers,
Blweb

 

[mattKnight]

I'd check the number of parameters contained in the parameters collection of the command object.

Certain ADO & database combinations (SQL for one) will automatically populate the parameters collection of the command object. So it is possible that the you the required parameter does not require to be explicitly created.

As I say SQL supports/provides this functionality, but I don't know about oracle!

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[ToeJamNEarl]

Quick question, do input output paramters work well with the MSDAORA provider?

 

[williamu]

Hi,

(I knew I posted this in the wrong place the first time!)

Anyway,

Dim adoConn, adoRec, adoCmd, strConn, nRecs
strConn = "Insert Provider Details Here"
Set adoConn = new ADODB.Connection
Set adoCmd = new ADODB.Command
adoCmd.CommandType = adCmdStoredProc
adoCmd.CommandText = "SelectEvent"
adoConn.Open strConn
adoCmd.ActiveConnection = adoConn
adoCmd.Parameters.Append adoCmd.CreateParameter ("@pUID", adInteger, adParamInput, , YourParamData)
Set adoRec = adoCmd.Execute

As I said earlier I know this works for Access.

HIH



William
Software Engineer
ICQ No. 56047340

 

[ToeJamNEarl]

Hey Guys,

I have tried it your way for creating parameters and I get the same error message:

-2147217900 ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to '<PROCEDURE NAME>'
ORA-06550: line 1, column 7:
PL/SQL: statement ignored
Microsoft OLE DB Provider for Oracle.

Set prmDatabase = datCmd1.CreateParameter("p_Database", adVarChar, adParamInput, 200, strChosenDB)

That is how I create my parameters now, as you describe. I have tried once where I did the above line and then added
'datCmd1.Parameters.Appen prmDatabase', and there has been a time where I haven't.

My stored procedure has 10 parameters it is expecting total.
p_InputFieldsString IN VARCHAR2,
p_Database IN VARCHAR2,
p_SelectResult IN VARCHAR2,
p_NumOfClients OUT NUMBER,
p_NumOfProduct OUT NUMBER,
p_NumOfTrucks OUT NUMBER,
Cur_SpecialHandling IN OUT t_specialhandlingcur,
p_ErrorCode IN OUT NUMBER,
p_ErrorMessage IN OUT VARCHAR2,
p_ErrorLocation IN OUT VARCHAR2);

The way I am handling that reference cursor parameter is by my
set ObjRS = datCmd1.execute.

This is aggrivating because when I had the OraoleDB.Oracle provider, my code worked on 1 test machine, but not the other. I couldn't figure out why, so I decided to try the Microsoft Oracle provider, because I saw somethign in the Microsoft Knowledge Base (229757)

Any insight as to what I may be doing wrong?

Thanks,

-Diran

 

[williamu]

If you look at the line from my example:

adoCmd.Parameters.Append adoCmd.CreateParameter ("@pUID", adInteger, adParamInput, , YourParamData)

You will see that I'm Appending the new Parameter to the Command object from the Command object.

In your code:

Set prmDatabase = datCmd1.CreateParameter("p_Database", adVarChar, adParamInput, 200, strChosenDB)
'datCmd1.Parameters.Appen prmDatabase', and there has been a time where I haven't.

prmDatabase is What? A parameter object?



William
Software Engineer
ICQ No. 56047340

 

[ToeJamNEarl]

Yes, I used parameters objects.

I have simulated your adoCMD.parameters.append line
by adding:
datCmd1.parameters.append prmX
Where X is the parameter object created.

 

[williamu]

Fair enough. How are you declaring your Stored Proc?

E.G.

adoCmd.CommandType = adCmdStoredProc
adoCmd.CommandText = "SelectEvent"
or
adoCmd.CommandText = "{? = CALL SelectEvent(...)}"

Also you are returning 7 items back in the result set?

Is is possible to see the Procedure?


William
Software Engineer
ICQ No. 56047340

 

[ToeJamNEarl]

datCmd1.CommandType = adCmdStoredProc
datCmd1.CommandTimeout = 16000 'Long timeout
datCmd1.CommandText = "app_stat.Shipping_Tracker.Get_Statistics"

The result set contains 2 fields a description and a count. the other parameters that are either IN OUT or just OUT get filled with the correct data (when using OraOLEDB, but only on my dev machine and one test machine).

 

[williamu]

Hi,

Not according to what your Stored Procedure says:

p_NumOfClients OUT NUMBER,
p_NumOfProduct OUT NUMBER,
p_NumOfTrucks OUT NUMBER,

It should have 3 vaules in the result set not 2. So since these are declared as such you must return at minimum 3 items in the result set.

HTH





William
Software Engineer
ICQ No. 56047340

 

[ToeJamNEarl]

the set objRS = datcmd1.execute is used to have the stored procedure pass down a table of information from teh stored procedure to populate the recordset used.

with the OraOLEDB provider and one other test machine,
with prmNumofClients I can see it's value by doing

prmNumOfclients.Value.

That has worked for me in the OraOLEDB provider, maybe MSDAORA provider works differently, that I do not know.

 

[williamu]

Try changing all of the OUT params to INOUT and see what results you get. Also, the Oracle OLEDB driver is far superior to the driver shipped by MS.



William
Software Engineer
ICQ No. 56047340

 

[ToeJamNEarl]

I find that last statement funny, because MS states their driver is superior.

http://support.microsoft.com/default.aspx?scid=kb;en-us;229757

 

[williamu]

Hee hee, that's funny coming from the guys that wrote windows!



William
Software Engineer
ICQ No. 56047340

 

[ToeJamNEarl]

I just tested my software (old working version with ORAOLEDB) on another test machine, and it ran fine.

I think there is something wrong with the computer itself that has been giving me problems. Is there anyway to check it a component is corrupted? Maybe the ADO component in charge of handling the EXECUTE function of an ADODB command object isn't being handled properly?

Any insight as to this problem would be grateful.

-Diran

 

[williamu]

There may well be but if you are in any doubt you should at least re-install the drivers.

Not much use stating the obvious but it's all I can offer at the moment.


William
Software Engineer
ICQ No. 56047340