DSXchange

Hi All,
I need to call a stored procedure from a DataStage routine. I have written the following code:
[/
$include UNIVERSE.INCLUDE ODBC.H

** PREPARE for DATABASE CONNECTION**
status = SQLAllocEnv(HENV)
status = SQLAllocConnect(HENV, HDBC)
status = SQLConnect(HDBC, "RRTEDSS", "amar", "amar")

arrsize = 1
dim p1(arrsize)
dim p2(arrsize)

status = SQLAllocStmt(HDBC, HSTMT)

status = SQLBindParameter(HSTMT, 1, SQL.B.BASIC, SQL.DECIMAL, 0, 0, p1(1),SQL.PARAM.INPUT)
status = SQLBindParameter(HSTMT, 2, SQL.B.BASIC, SQL.CHAR, 255, 0, p2(1),SQL.PARAM.OUTPUT)

p1(1) = ArgInput
p2(1) = ArgOutput

CALLSTMT = "CALL SP_TEST(":ArgInput: ",'" : ArgOutput :"')"
*PRINT CALLSTMT
ST = SQLExecDirect(HSTMT, CALLSTMT)
If ST SQL.SUCCESS then
PRINT 'Status is ':ST
STATUS = SQLError(SQL.NULL.HENV, SQL.NULL.HDBC, HSTMT,SQLSTATE, NATIVE, TEXT)
PRINT 'SQLSTATE is ':SQLSTATE
PRINT 'Native error is ':NATIVE
PRINT 'Error text is: ':TEXT
End
Ans = ST
]
I am getting the following ERROR:
"Output variable is not a bind parameter"

Where am I going wrong in this code?? My stored proc "SP_TEST" runs fine when executed in the SQL prompt. (I am using Oracle 9i).

Thanks in advance,
Nilotpal.

To get access to columns in the result set, you need to bind columns to the result set using SQLBindCol(). The SQLBindParameter() function binds variables in the calling program to parameter markers in the SQL.
So I'm guessing, since the stored procedure's second argument appears to be an output argument, you need SQLBindCol() here.

Hi Ray,
I have modified the code as follows:
[/
$include UNIVERSE.INCLUDE ODBC.H

** PREPARE for DATABASE CONNECTION**
status = SQLAllocEnv(HENV)
status = SQLAllocConnect(HENV, HDBC)
status = SQLConnect(HDBC, "DSNNAME", "UID", "PWD")

CALLSTMT = "CALL SP_TEST(":ArgInput: ",'": ArgOutput :"')"

status = SQLBindCol(HSTMT, 1, SQL.B.CHAR, ArgOutput)
if status 0 then ErrorCode = -111

RESULT = SQLExecDirect(HSTMT, CALLSTMT)

If RESULT SQL.SUCCESS then
PRINT 'Status is ':RESULT
STATUS = SQLError(SQL.NULL.HENV, SQL.NULL.HDBC, HSTMT,SQLSTATE, NATIVE, TEXT)
PRINT 'SQLSTATE is ':SQLSTATE
PRINT 'Native error is ':NATIVE
PRINT 'Error text is: ':TEXT
End
status = SQLFetch(HSTMT)
if status 0 then ErrorCode = -112
PRINT status


Ans = RESULT
]
My error persists:
output parameter not a bind variable..
I am also trying to look up the sample BCI code as you suggested.

Thanks,
Nilotpal