CreateADOStoredProc and a adVarChar Parameter

[CDurkin]

I am trying to call a Oracle Stored proc which returns a VarChar.  But it seems to be having trouble recognising the  type "adVarChar".



I get the following error:

     "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"



My code is something like below.   If I change it to "ftFixedChar" it seems to work.



How do I get it working for Oracles' VARCHAR2.



Thanks

Chris





function CreatingStoredProcedure()

{

var SProc;

  SProc = ADO.CreateADOStoredProc();

  SProc.ConnectionString = "....

  SProc.ProcedureName = "....

    // Adding a return parameter

  SProc.Parameters.AddParameter();

  SProc.Parameters.Items(0).name = "RETURN_VALUE";

  SProc.Parameters.Items(0).DataType = adVarChar;     <<< Complains about this line

  SProc.Parameters.Items(0).Size = 255; 

  SProc.Parameters.Items(0).Direction = adParamReturnValue;

  SProc.Parameters.Items(0).Value = null;

[radov]

Hi Chris,

How do I get it working for Oracles' VARCHAR2.

According to the information from this page, you should specify the adVariant constant as the data type if your stored procedure returns a VARCHAR2 value:

SProc.Parameters.Items(0).DataType = adVariant;

It seems that the adVarChar data type actually corresponds to the VARCHAR Oracle data type, not to the VARCHAR2 type. Please try using adVariant. Does this help?

[artur0409]

I have similar problem with SQL Server 2005



I'm trying to execute following command

exec ZipStoreConversions @ProductId=N'GMVE 000057',@ParameterCollectionID=N'FH',@ConvertToProduct=N'VIT 000100',@ConvertFromProduct=N''



parameters definiod from stored procedure

@ProductID varchar(11),

@ParameterCollectionID varchar(50),

@ConvertToProduct varchar(11),

@ConvertFromProduct varchar(11)



I use following code  - it always give me error in line 

param1["DataType"] = DB["adVarChar"];

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"





Do I do something wrong?

Cmd["CommandText"]= "dbo.ZipStoreConversions"

Cmd["CommandType"] = DB["adCmdStoredProc"];

var param1 = Cmd["Parameters"]["AddParameter"]();

param1["Name"] = "ProductId";

param1["DataType"] = DB["adVarChar"];

param1["Direction"] = DB["adParamInput"];

param1["Value"] = "GMVE 000057";

var param2 = Cmd["Parameters"]["AddParameter"]();

param2["Name"] = "ParameterCollectionID";

param2["DataType"] = DB["adVarChar"];

param2["Direction"] = DB["adParamInput"];

param2["Value"] = "FH";

var param3 = Cmd["Parameters"]["AddParameter"]();

param3["Name"] = "ConvertToProduct";

param3["DataType"] = DB["adVarChar"];

param3["Direction"] = DB["adParamInput"];

param3["Value"] = "VIT 000100";

var param4 = Cmd["Parameters"]["AddParameter"]();

param4["Name"] = "ConvertFromProduct";

param4["DataType"] = DB["adVarChar"];

param4["Direction"] = DB["adParamInput"];

param4["Value"] = "";

RecSet = Cmd["Execute"]();

[AlexeyK]

Arthur,

...

I use following code  - it always give me error in line

param1["DataType"] = DB["adVarChar"];

...

I'm not a big specialist in this, but perhaps the property name is Type, not DataType. At least, MSDN and some sites I found in Google use Type.

As far as I can see, you are using C++Script or C#Script, and DataType is a property. However, the JScript engine (that is used for C++/C# scripting) treats it as a parameter/argument that is passed in brackets. That is why, the engine gives the "Arguments are of the wrong type..." error.

[artur0409]

I got answer from SmartBear on my previous case to use such syntax  - so DataType seems to be correct  -  quote:

Hello,

Here is code that should work for you

[C#Script]

  // ...   

  Cmd["CommandText"] ="dbo.Bin_DeleteItems";

  Cmd["CommandType"] = DB["adCmdStoredProc"];

  var param = Cmd["Parameters"]["AddParameter"]();

  param["Name"] = "XMLListOfIds";

  param["DataType"] = DB["adPersistXML"];

  param["Direction"] = DB["adParamInput"];

  param["Value"] = XmlString;

  RecSet = Cmd["Execute"]();

  // ...

Best regards,

Jay

[AlexanderM]

Hi Artur,




I have replied to your support case with this question, and I will post a short summary here so that others have this information as well:




The problem with the code is that you are using ADO constants for the types. However, the CreateADOStoredProc method does not return an instance of the "ADODB.Command" object - it returns a VCL wrapper object instead. That is why the "CreateADOStoredProc Method" help topic gives a reference to VCL documentation:

The IAQAADOStoredProc object is an analogue of a Borland VCL TADOStoredProc object. Methods and properties are identical.


For detailed information on the underlying TADOStoredProc object, see VCL documentation on ADODB classes.

The help topic gives this link to the VCL documentation:


http://docwiki.embarcadero.com/VCL/XE/en/ADODB






When using CreateADOStoredProc, you need to use the data types defined in VCL (Data.Win.ADODB.TDataType):


http://docwiki.embarcadero.com/Libraries/en/Data.Win.ADODB.TDataType




So, in your code, you should use the ftString type for your VarChar parameters - it works fine for me:

  Cmd["Parameters"]["Items"](0)["DataType"] = ftString;

SUMMARY:




- When using ADO.CreateADOStoredProc, use the VCL data type constants:


http://docwiki.embarcadero.com/Libraries/en/Data.Win.ADODB.TDataType




- When creating an instance of the "ADODB.Command" object to invoke a stored procedure, use the ADO data type constants:


http://www.w3schools.com/ado/ado_datatypes.asp

[artur0409]

Great answer!!

 My problem is solved now



Thanks for your time