hi all,
can anyone please tell me how to execute an oracle function from VB6.
i have a function that takes two IN parameters a date and a varchar2 and returns a boolean. the declaration is like this....
CREATE OR REPLACE function
ValidWorkingDay(given_day date,centre varchar2)
return boolean
i want to catch this returned boolean value in a VB6 variable. how can i do that? can anybody help?



thanks and regards,
kalyan sarkar

Hi,

Can you try this query

"Select ValidWorkingDay(param1,prasm2) as IsWorking from dual "

Prashant

hi prashant,
i have tried your query and i get the following error.
ORA-06571:Function ValidWorkingDay does not guarantee not to update database
however it would be of great help if you can tell me how to call a procedure which performs the same as the function above. the procedure is like this.....
CREATE OR REPLACE procedure prc_validworkingday(pDate in date,pCentre in varchar2, pValid in out varchar2)
can you help me prashant, once again?

thanks and regards,
kalyan sarkar

hi,

You need to make it a procedure. Functions cannot do inserts, updates, and deletes. You make it a Procedure with an OUT Param.

Now you can build the ADODB.Command Object with parameters to execute the procedure. Hope are using ADODB

Prashant

hi prashant,
can you help me there with the syntax. i am using ADODB but i havent used the ADODB.Command object before.

thanks and regards,
kalyan sarkar

hi,

See the sample code(not tested or complied)


    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRs As New ADODB.Recordset

    objCmd.CommandText = "YourProcedureName"
    objCmd.CommandType = adCmdStoredProcedure
    ' Connect to the data source.
    Set objConn = GetNewConnection

    objCmd.ActiveConnection = objConn

   Set objParm1 = objCmd.CreateParameter("CustId", adChar, _
                    adParamInput, 5, "ALFKI")
   objCmd.Parameters.Append objParm1

   Set objParm2 = objCmd.CreateParameter("youroutvalue", adChar, _
                    adParamInputoutput, 5)

   objCmd.Parameters.Append objParm2


    ' Execute
    Set objRs = objCmd.Execute

    debug.print objCmd.Parameters["youroutvalue"]

    'clean up
    objRs.Close
    objConn.Close
    Set objRs = Nothing
    Set objConn = Nothing
    Set objCmd = Nothing
    Exit Sub

hi prashant,
thank you for helping me with the code. however when i try to run the code i get the following error message at the execute statement.

[Microsoft][ODBC Driver Manager] SQL data type out of range

i have mentioned the IN OUT parameter as adBSTR type.

thanks and regards,
kalyan sarkar

hi prashant,
i am posting the procedure and the VB code for your convenience

PROCEDURE........

CREATE OR REPLACE procedure prc_validworkingday(pDate in date,pCentre in varchar2, pValid in out varchar2)
as

pTmp boolean := false;
begin
    pTmp:=validworkingday(pDate,pCentre);

    if pTmp then
            pValid:='True';
    else
            pValid:='false';
    end if;
end;
/

validworkingday is the earlier function i was talking about.

VB CODE.......

Private Sub ValidWorkingDay() 'As String '(given_day As Date, centre As String)
    'Dim sSql As String
    'Dim sConn As String
    Dim ValidWorkingDay As Boolean
    Dim objConn As New ADODB.Connection
    Dim objCmd As New ADODB.Command
    Dim objRs As New ADODB.Recordset

    'Set objConn = GetNewConnection
    objConn.Open ("DSN=*****;UID=******;PWD=******;")

    objCmd.CommandText = "prc_ValidWorkingDay"
    objCmd.CommandType = adCmdStoredProc
    objCmd.ActiveConnection = objConn

    'MsgBox Date
    Set objParm1 = objCmd.CreateParameter("given_day", adDate, adParamInput, , Date)
    objCmd.Parameters.Append objParm1


    Set objParm2 = objCmd.CreateParameter("centre", adBSTR, adParamInput, , "CAL")
    objCmd.Parameters.Append objParm2

    Set objParm3 = objCmd.CreateParameter("youroutvalue", adBSTR, adParamInputOutput)
    objCmd.Parameters.Append objParm3

    'Set objRs = objCmd.Execute
    objCmd.Execute '**** i am getting the error here



    objConn.Close
    Set objConn = Nothing
    Set objCmd = Nothing

End Sub

what do i do now?


thanks and regards,
kalyan sarkar

hi,

I feel your adBSTR is not the correct datatype, Try adVarChar
also for all string type you have to specify length parameter.

cmn.Parameters.Append cmn.CreateParameter("type",adVarChar, ,255)


Prashant

hi prashant,
i have changed the code to the following...

Set objParm1 = objCmd.CreateParameter("given_day", adDate, adParamInput, , Date)
    objCmd.Parameters.Append objParm1

    Set objParm2 = objCmd.CreateParameter("centre", adVarChar, adParamInput, 3, "CAL")
    objCmd.Parameters.Append objParm2

    Set objParm3 = objCmd.CreateParameter("youroutvalue", adVarChar, adParamInputOutput, 5)
    objCmd.Parameters.Append objParm3

now i get the following error.....

[Microsoft][ODBC driver for Oracle]Invalid use of default parameter



thanks and regards,
kalyan sarkar