Professional Web Applications Themes

new 2 stored procedure - Coldfusion Database Access

I have knowledge of cursor,procedure but didnt have a chance to write a stored procedure...Can anyone help with this ? I want to select * from table BPAR where ID= #url.var# ; url.var is an integer...How will I implement that with CF and stored procedure? Thanks all....

Sponsored Links
  1. #1

    Default new 2 stored procedure

    I have knowledge of cursor,procedure but didnt have a chance to write a stored
    procedure...Can anyone help with this ?

    I want to select * from table BPAR where ID= #url.var# ; url.var is an
    integer...How will I implement that with CF and stored procedure? Thanks all.

    Sponsored Links
    emmim44 Guest

  2. #2

    Default Re: new 2 stored procedure

    First, you didn't tell what DB you'are using (MSSQL, MySQL, Oracle, DB2, etc
    etc etc etc)

    i.e. MSSQL :

    The stored procedure in the DB :
    CREATE PROCEDURE GetData
    paramID int
    AS
    select * from table where ID=paramID

    And the call from CF :

    <CFSTOREDPROC DATASOURCE="Your_Datasource" PROCEDURE="GetData">
    <CFPROCRESULT NAME="Result">
    <CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_INTEGER" VALUE="#url.var#"
    DBVARNAME="paramID" TYPE="IN">
    </CFSTOREDPROC>

    The recorDset will be in the "Result" variable

    Regards


    Sojovi Guest

  3. #3

    Default Re: new 2 stored procedure

    For Oracle, something like this......

    Phil


    CREATE OR REPLACE PACKAGE your_pkg IS

    -- Global declaration for ref_cursor
    TYPE ref_cur_type
    IS REF CURSOR;

    PROCEDURE your_proc(in_id ID NUMBER, out_rec OUT ref_cur_type);

    END your_pkg;
    /

    CREATE OR REPLACE PACKAGE BODY your_pkg IS

    PROCEDURE your_proc(in_id ID NUMBER, out_rec OUT ref_cur_type)

    IS

    BEGIN

    OPEN out_rec
    FOR
    SELECT *
    FROM bpar
    WHERE id = in_id;

    END your_proc;

    END your_pkg;
    /

    <CFSTOREDPROC DATASOURCE="Your_Datasource" PROCEDURE="your_pkg.your_proc">
    <CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_INTEGER" VALUE=#url.var# NULL="No">
    <CFPROCRESULT NAME="Result">
    </CFSTOREDPROC>

    Your "query" is named Result (or whatever you use as the NAME in CFPROCRESULT)

    paross1 Guest

  4. #4

    Default Re: new 2 stored procedure

    Thank you..Do you need to create a package ? Why dont we just create a procedure...
    emmim44 Guest

  5. #5

    Default Re: new 2 stored procedure

    You need to use a package because you need to use a reference cursor to return
    your result set from your procedure to ColdFusion. Your reference cursor needs
    to be "globally" available (available outside of the procedure), and the only
    way that this can be accomplished is be defining it in the package
    specification. Otherwise, how would you define your reference cursor type where
    ColdFusion would recognize it as such.

    Oh, and by the way, you can't return result sets via reference cursors if you
    use Oracle's ThinClient JDBC drivers. (see note at
    http://livedocs.adobe.com/coldfusion/7/htmldocs/00000314.htm)

    To use reference cursors in packages or stored procedures, use the
    cfprocresult tag. This causes the ColdFusion JDBC database driver to put Oracle
    reference cursors into a result set. (You cannot use this method with Oracle's
    ThinClient JDBC drivers.)


    Phil

    paross1 Guest

  6. #6

    Default Re: new 2 stored procedure

    Thanks man..
    emmim44 Guest

  7. #7

    Default Re: new 2 stored procedure

    Hey man..I am getting this error..
    Error Executing Database Query.
    [Macromedia][Oracle JDBC Driver]Internal error: Net8 protocol error.

    The error occurred in D:\CFusionMX\wwwroot\BPAR\BPARView.cfm: line 24

    22 : <CFSTOREDPROC DATASOURCE="#FormVector#" PROCEDURE="your_pkg.your_proc">
    23 : <CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_INTEGER" VALUE="#url.recordID#"
    NULL="No">
    24 : <CFPROCRESULT NAME="view">
    25 : </CFSTOREDPROC>
    26 :




    emmim44 Guest

  8. #8

    Default Re: new 2 stored procedure

    You might try changing CF_SQL_INTEGER to CF_SQL_NUMERIC.

    Also, are you using the Enterprise or Standard version of ColdFusion?

    Phil
    paross1 Guest

  9. #9

    Default Re: new 2 stored procedure

    CF 6.1 Enterprise...And tried the numeric ....still same err...
    emmim44 Guest

  10. #10

    Default Re: new 2 stored procedure

    What does your package look like?
    paross1 Guest

  11. #11

    Default Re: new 2 stored procedure

    What do you mean ? How about the CF part? I need to get the result on CF page..
    emmim44 Guest

  12. #12

    Default Re: new 2 stored procedure

    I'm asking if your stored procedure works from SQL*Plus so that you can
    eliminate any problems in ColdFusion. In other words, if it executes OK in
    SQL*Plus then you have a problem between your database and CF, otherwise your
    procedure is hosed. Get it working correctly when being called from SQL*Plus,
    then you can work on any CF problems/errors.

    Phil

    paross1 Guest

  13. #13

    Default Re: new 2 stored procedure

    Yes they do...I tested your sample code...Please let me know why it is not working...
    emmim44 Guest

  14. #14

    Default Re: new 2 stored procedure

    If it is a DB connection err, I used a reqular cfquery ...it works fine...
    emmim44 Guest

  15. #15

    Default Re: new 2 stored procedure

    Are you using Oracle's ThinClient JDBC drivers?

    Phil
    paross1 Guest

  16. #16

    Default Re: new 2 stored procedure

    What is that ? How will I fix that?
    emmim44 Guest

  17. #17

    Default Re: new 2 stored procedure

    Is that a yes? If so, you can't return results sets from Oracle stored
    procedures using reference cursors, as previously posted. Otherwise, I don't
    know what to tell you since my application that uses Oracle is 100% stored
    procedures, and I have been returning result sets since ColdFusion 4.5 using
    both "native drivers" and ODBC with Oracle 8.0.7, and now using MX 7 and JDBC
    and Oralce 9i.

    Phil

    paross1 Guest

  18. #18

    Default Re: new 2 stored procedure

    ok...Thanks..so saying that you dont know how to find a solution for that ..Is there a way to do the same query without a ref cursor ???
    emmim44 Guest

  19. #19

    Default Re: new 2 stored procedure

    Find a solution for what? You never did say whether of not you were using the
    Thin Client! If you are using a Thin Client then DON'T.

    Also, if it works with ODBC (Oracle not Microsoft), then you might try that,
    although that is not an ideal means of accessing your database. And, no, there
    is no solution to returning a result set from an Oracle stored procdeure
    without using a ref cursor.

    Phil

    paross1 Guest

  20. #20

    Default Re: new 2 stored procedure

    Thank you for info,,,tc
    emmim44 Guest

Similar Threads

  1. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  2. Using a stored procedure
    By MarkWright in forum Coldfusion Database Access
    Replies: 13
    Last Post: April 15th, 05:53 PM
  3. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  4. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM
  5. need help on a stored procedure
    By Helixpoint in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139