Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
|
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 5th, 2007, 02:46 AM
Authorized User
 
Join Date: Aug 2007
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to zrtv
Default ado and oracle procedure problem plz help

hi All,
Iam new in oracle stored procedure, i have one stored procedure and it has to be accessed from vb6. my stored procedure as follows

-- employee salary will be entered by the user and itshould show employees less than the the given salary

CREATE OR REPLACE PROCEDURE employee1(empcode OUT VARCHAR2,
empname OUT VARCHAR2, POSITION OUT VARCHAR2, sal IN NUMBER) IS

CURSOR c1 IS SELECT empid, empname, grade FROM empmst
WHERE basicsal < sal;

BEGIN
  OPEN c1;
  LOOP
    FETCH c1
    INTO empcode, empname, POSITION;
    EXIT
  WHEN c1 % NOTFOUND;
END LOOP;

-- my vbcode is the following
    Set db = New ADODB.Connection
    db.CursorLocation = adUseClient
    Set prempcode = New ADODB.Parameter
    Set prempname = New ADODB.Parameter
    Set prbasicsal = New ADODB.Parameter
    Set prposition = New ADODB.Parameter
    Set rsempmst = New ADODB.Recordset
    rsempmst.CursorType = adOpenStatic

DBDriver = "Provider=MSDAORA.1;Password=ttt;User ID=ttt;Persist Security Info=True"

db.Open DBDriver

Set cm = New ADODB.Command
    With cm
    .ActiveConnection = db
    .CommandType = adCmdStoredProc
    .CommandText = "employee1" 'Name of the ORACLE Function, in String
    .CommandTimeout = 10
    End With
    cm.CommandText = "employee1"

cm.Parameters.Append cm.CreateParameter("empcode", adVarChar, adParamOutput, 200)

cm.Parameters.Append cm.CreateParameter("empname", adVarChar, adParamOutput, 200)

cm.Parameters.Append cm.CreateParameter("position", adVarChar, adParamOutput, 200)


cm.Parameters.Append cm.CreateParameter("basicsal1", adNumeric, adParamInput, , Val(Text1.Text))


Set rsempmst.Source = cm.Execute
rsempmst.Open
Do Until rsempmst.EOF
    MsgBox (rsempmst("empid"))
rsempmst.MoveNext
Loop

' this code showing error that
Run time error 3001
Arguments are wrong type are acceptable range, or are inconflict with
one another

Plz helppppppppppp
Thanks




 
Old August 7th, 2007, 03:30 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Your line
Code:
    Set rsempmst.Source = cm.Execute
appears to try to retrieve an SQL string as the result of execution. But your procedure (as written) doesn’t return anything in that fashion. You have set it up to return one answer in the procedure’s arguments.

Is there some reason you don’t want to just open a recordset with the constraining SQL? That would be better...
Code:
    Dim db As New ADODB.Connection
        db.CursorLocation = adUseClient
        db.Open "Provider=MSDAORA.1;" & _
                "Password=ttt;" & _
                "User ID=ttt;" & _
                "Persist Security Info=True"

    Dim rsEmpmst As New ADODB.Recordset
        rsEmpmst.CursorType = adOpenKeySet
    Set rsEmpmst.Connection = db

    rsEmpmst.Open "SELECT empid,  empname,  grade " & _
                  "FROM   empmst                  " & _
                  "WHERE  basicsal < " & Text1.Text

    Do Until rsempmst.EOF
        MsgBox rsempmst!empid
        rsempmst.MoveNext
    Loop
    (Some of this syntax might not be right; I did not put this together in a VB IDE, but in Word.)
 
Old August 9th, 2007, 05:34 AM
Authorized User
 
Join Date: Aug 2007
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to zrtv
Default

hi dear thanx for your reply,
recordset only iam using prevsly now i would like to learn procedure and command,
if you chek my procedr there is out parameteres for returning data's.it will be greatefull
if you can tell me how the procedure should be and the vb

 
Old August 9th, 2007, 12:30 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Those out parameters can only return one EmpCode (a string), one EmpName (a string), and one number. (Please note how selective capitalization can make the intent of a variable clearer. Note the syntax I am using in answering you: there should be a space following periods at the end of a sentence, and the first letter of a sentence is capitalized.)

But your statement Set rsempmst.Source = cm.Execute indicates that you were expecting fully-formed SQL to be returned. (Plus .Source would always be a string, so the Set keyword would be a syntax error.)

I don't know if you can retrieve the return value of a stored procedure which is a Function. I've never tried.

But your stored procedure should either put the SQL into an OUT argument, or you should use the returned arguments to create the SQL in your VB routine:
Code:
    cm.Execute
    rsEmpMst.Source = "SELECT *                                 " & _
                      "FROM   EmpMst                            " & _
                      "WHERE  EMPID   = '" & cm("empcode") & "' " & _
                      "  AND  EMPNAME = '" & cm("empname") & "' "
                      The string in the command's parens is that which you named it in the VB, not the name in the stroed procedure. (They might be the same, but it is the string in VB that matters.)

In your stored procedure, the Fetch Into happens to load the output parameters. But if you are going to return fully-formed SQL, you would build that SQL in the stored procedure using values that are filled by the fetch into, then use <Out-Parameter-Name> = . . . (and put your finished string after the = ).

Finally, you are using a loop in the stored procedure. But all that would happen is that the loop would run, then the last values fetched would remain in the parameters when the procedure terminates. Then your VB would read those final values. You cannot return a series of values from a stored procedure. They behave [u]exactl</u>y like modifying the ByRef arguments of a VB Sub. If there is a loop in that Sub, the arguments will get modified for each iteration, but the calling routine will only see the final value(s).
 
Old August 10th, 2007, 10:36 PM
Authorized User
 
Join Date: Aug 2007
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to zrtv
Default

Dear Mr.BrianWren,
Thank you very much for your answer, actually i am really new in this stored procedure and command(vb), previously im using recordset, but i know that for a better program and fast ,we have to use stored procedure, so i tried in my old program, my basic intention is
user should enter salary parameter, and system should retrieve all the data's which matching the criteria, so can u modify and give me the procedure how it should be and vb code to call the procedure, it would be grateful so that i can continue and learn more. Thanks once more for your time.

 
Old August 13th, 2007, 01:48 PM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Here is the format for a stored procedure that returns a value, that is, a function:
Code:
CREATE OR REPLACE FUNCTION out_func (outparm OUT VARCHAR2)
RETURN VARCHAR2 IS . . .
But note well the bold-blue parts. Thes are what are called scalar variables. That meanc that they can only hold a value.

A recordset is not a value. I am pretty sure that you cannot return a recordset from an Oracle function.

You say, “ . . . i know that for a better program and fast ,we have to use stored procedure . . . ” (you should capitalize ‘I’ when referring to yourself...) But I don’t know that that is a valid statement. If you need a collection of records in your VB, it looks to me like your only choice is a RecordSet object. And they are not all that slow.

You can speed up your application substantially with indices in Oracle. It is helpful to not add more columns to your SQL than you absolutely need. So avoiding “SELECT *” can be helpful. (The exception would be when you are going to return all columns; in that case using “*” instead of spelling out every column can save a few micro-seconds in the string that is transmitted to Oracle.)

You know, there is the additional consideration that your code will be much more maintainable and readable if the behavior isn’t spread across several different technologies, but is all present for the reading in the VB routine. Then you don’t need to have both VB and Procedure Builder open at the same time just to see what the plan is.
 
Old August 14th, 2007, 05:45 AM
Authorized User
 
Join Date: Aug 2007
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to zrtv
Default

Thanks for your time and reply,
Actually I want to learn procedures and how it can be used in vb6, how can a procedure can return morethan one value,I heard that
by using sys_cursor we can open record set in procedure and it can be
return more than one value.

 
Old August 17th, 2007, 11:49 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

I have returned more than one value by concatenating the values into a delimited string, then looking for the delimiter in VB (Using split, or looping through the string with InStr()).
Of course, you can have multiple OUT parameters. That's not returning a value as a function does with “return,” but you do get the values.

(A tip: you are using commas , where you should use periods .)
 
Old August 19th, 2007, 01:20 AM
Authorized User
 
Join Date: Aug 2007
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to zrtv
Default

Thank you very much for your reply, I trying to use those.

 
Old February 28th, 2008, 01:00 PM
Registered User
 
Join Date: Feb 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I know quite some time has passed and hopefully you've already figured things out... If not, please know that Oracle is weird. In your procedure you have to declare an IN OUT variable based on a custom type that is created from a REF CURSOR.

So... step 1 is to create an oracle package (and its body) that has the ref cursor type.

CREATE OR REPLACE PACKAGE PKG_TYPES IS
  TYPE OUT_CURSOR IS REF CURSOR;
END PKG_TYPES;
/
CREATE OR REPLACE PACKAGE BODY PKG_TYPES IS
END PKG_TYPES;
/

Step 2. is to create your procedure so that it includes an IN OUT parameter of the PKG_TYPES.OUT_CURSOR type:

CREATE OR REPLACE PROCEDURE usp_SomeProcedure(SomeDateParam IN DATE, out_cursor IN OUT rti_report.pkg_types.OUT_CURSOR) IS
BEGIN
  OPEN out_cursor FOR
  SELECT * FROM SomeTable
  WHERE SomeField = SomeDateParam;
END;

Step 3. I can't give you details as I use C# and PHP. In VB you will have to define a command object with the appropriate parameters. In this example you would define both the 'SomeDateParam' and the 'out_cursor' parameter. However, you'd only set the value for 'SomeDateParam'. Once you execute the command to fill the resultset it should behave like SQLServer.

Setting things up this way allows you to reuse PKG_TYPES.out_cursor repeatedly for each procedure you write that needs to return results.

Hope this helps someone.





Similar Threads
Thread Thread Starter Forum Replies Last Post
ADO Create Procedure problem xiangchendub Access VBA 3 January 23rd, 2008 09:37 AM
plz.....plz solve out my problem.... kethireddy435 ASP.NET 1.x and 2.0 Application Design 1 October 4th, 2007 12:56 PM
ORACLE AND VB6 CAN ANYBODY HELP ME PLZ zrtv Pro VB Databases 3 August 29th, 2007 04:08 PM
ADO.Net...Help PLZ!!!! sconineuk VB.NET 2002/2003 Basics 4 March 16th, 2006 05:35 AM
Problem In Parameterized ADO.NET Stored Procedure yoord ADO.NET 3 June 19th, 2004 05:09 AM





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