ADO.CreateADOStoredProc(): cant return not numeric value
Hi!
Try to run Oracle stored procedure in TestComplete code:
PROCEDURE TEST_PROCEDURE(IN_ID IN INT,
OUT_ID OUT INT,
OUT_ID1 OUT VARCHAR2)
IS
BEGIN
SELECT 1,
'2'
INTO OUT_ID,
OUT_ID1
FROM DUAL;
END TEST_PROCEDURE;
JS code:
var SProc;
SProc = ADO.CreateADOStoredProc();
SProc.ConnectionString="DRIVER=...bla-bla-bla";
SProc.ProcedureName = "SHS_TEST_PKG.TEST_PROCEDURE";
// IN parameters
SProc.Parameters.CreateParameter("IN_ID", adInteger, adParamInput, 0, 21);
// OUT parameters
SProc.Parameters.CreateParameter("OUT_ID", DB.adInteger, adParamOutput, 0, 0);
SProc.Parameters.CreateParameter("OUT_ID1", DB.adInteger, adParamOutput, 0, 0);
SProc.ExecProc();
Log.Message("out1="+ SProc.Parameters.ParamByName("OUT_ID").Value+" out2=" + SProc.Parameters.ParamByName("OUT_ID1").Value);
////result
out1=1 out2=2
Result OK, while out value is numeric or may be converted to numeric!
But, when type of out value = varchar, "test 2" for example, sql -
SELECT 1,
'test 2'
INTO OUT_ID,
OUT_ID1
FROM DUAL;
and JS code for this -
SProc.Parameters.CreateParameter("OUT_ID", DB.adInteger, adParamOutput, 0, 0);
SProc.Parameters.CreateParameter("OUT_ID1", DB.adChar, adParamOutput, 0, 0);
no result with error -
"Неправильно определен объект Parameter. Предоставлены несовместимые или неполные сведения"
(!) Find solution in old topic - https://community.smartbear.com/t5/TestComplete-General-Discussions/CreateADOStoredProc-and-a-adVarChar-Parameter/td-p/52777
- When using ADO.CreateADOStoredProc, use the VCL data type constants:
http://docwiki.embarcadero.com/Libraries/en/Data.Win.ADODB.TDataTypeSo,
SProc = ADO.CreateADOStoredProc();
//worked!
SProc.Parameters.CreateParameter("OUT_ID1", ftFixedChar, adParamOutput, 1000, null);
//NOT worked!
//SProc.Parameters.CreateParameter("OUT_ID1", adVarChar, adParamOutput, 1000, null);