Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 10th, 2005, 12:01 AM
Registered User
 
Join Date: May 2005
Location: Kolkata, West Bengal, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kalyansarkar108
Default calling an oracle function from VB6

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
Reply With Quote
  #2 (permalink)  
Old May 10th, 2005, 02:21 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

Can you try this query

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

Prashant


Reply With Quote
  #3 (permalink)  
Old May 10th, 2005, 02:51 AM
Registered User
 
Join Date: May 2005
Location: Kolkata, West Bengal, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kalyansarkar108
Default

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
Reply With Quote
  #4 (permalink)  
Old May 10th, 2005, 03:49 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Reply With Quote
  #5 (permalink)  
Old May 10th, 2005, 04:45 AM
Registered User
 
Join Date: May 2005
Location: Kolkata, West Bengal, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kalyansarkar108
Default

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
Reply With Quote
  #6 (permalink)  
Old May 10th, 2005, 05:26 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #7 (permalink)  
Old May 10th, 2005, 06:41 AM
Registered User
 
Join Date: May 2005
Location: Kolkata, West Bengal, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kalyansarkar108
Default

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
Reply With Quote
  #8 (permalink)  
Old May 10th, 2005, 06:54 AM
Registered User
 
Join Date: May 2005
Location: Kolkata, West Bengal, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kalyansarkar108
Default

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
Reply With Quote
  #9 (permalink)  
Old May 10th, 2005, 07:14 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #10 (permalink)  
Old May 10th, 2005, 11:11 PM
Registered User
 
Join Date: May 2005
Location: Kolkata, West Bengal, India.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to kalyansarkar108
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
calling c# webservice from VB6 GS Pro VB 6 2 February 26th, 2016 09:28 AM
call oracle function using oracle link server vl SQL Server 2000 1 July 12th, 2007 08:19 AM
Calling Oracle function from Vb.net class monuindia2002 ADO.NET 1 August 4th, 2006 05:00 AM
Calling oracle packages from VB RGC Pro VB Databases 1 June 18th, 2004 02:16 AM
Calling WebService from VB6 without use SOAP toolk sanjaykabra82 .NET Web Services 1 February 25th, 2004 08:30 AM



All times are GMT -4. The time now is 03:39 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.