G
Geeta
I am trying to execute a Function in Oracle package from VB , but the app is
giving me an error "Invlaid SQL statement at rs = cm.execute"
Here is my function and VB code
====================
FUNCTION GetSetting(p_section IN VARCHAR2, p_name IN VARCHAR2) RETURN
VARCHAR2 IS
setting_value VARCHAR2(4000);
BEGIN
SELECT cs.VALUE
INTO setting_value
FROM CONFIG_SETTING cs
WHERE cs.SECTION = p_section
AND cs.NAME = p_name;
RETURN setting_value;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
=======
VB Code
=====
Sub TESTFUNCTION()
Dim rs, sq, pkey, teststr, cm, constr
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Set cm = CreateObject("adodb.command")
conn.Open "Provider=OraOLEDB.Oracle.1;Server=mdcx;Data Source=mdcx;User
Id=mdma;Password=mdma1;"
Set cm.ActiveConnection = conn
cm.CommandText = "P_CONFIG_SETTING.getsetting(P1,P2)"
cm.CommandType = 1
'Create and Append the parameters
cm.Parameters.Append cm.CreateParameter("P1", 129, 1, 20, "ROUTE_ROLLOVER")
cm.Parameters.Append cm.CreateParameter("P2", 129, 1, 20, "TO_ADDRESS")
' Execute the stored procedure
rs = cm.Execute
If Not rs.EOF Then
MsgBox "got some rows"
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
Any help is highly appreciated.
giving me an error "Invlaid SQL statement at rs = cm.execute"
Here is my function and VB code
====================
FUNCTION GetSetting(p_section IN VARCHAR2, p_name IN VARCHAR2) RETURN
VARCHAR2 IS
setting_value VARCHAR2(4000);
BEGIN
SELECT cs.VALUE
INTO setting_value
FROM CONFIG_SETTING cs
WHERE cs.SECTION = p_section
AND cs.NAME = p_name;
RETURN setting_value;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN NULL;
END;
=======
VB Code
=====
Sub TESTFUNCTION()
Dim rs, sq, pkey, teststr, cm, constr
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Set cm = CreateObject("adodb.command")
conn.Open "Provider=OraOLEDB.Oracle.1;Server=mdcx;Data Source=mdcx;User
Id=mdma;Password=mdma1;"
Set cm.ActiveConnection = conn
cm.CommandText = "P_CONFIG_SETTING.getsetting(P1,P2)"
cm.CommandType = 1
'Create and Append the parameters
cm.Parameters.Append cm.CreateParameter("P1", 129, 1, 20, "ROUTE_ROLLOVER")
cm.Parameters.Append cm.CreateParameter("P2", 129, 1, 20, "TO_ADDRESS")
' Execute the stored procedure
rs = cm.Execute
If Not rs.EOF Then
MsgBox "got some rows"
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
Any help is highly appreciated.