Professional Web Applications Themes

Using a stored procedure - Coldfusion Database Access

I am trying to pass a ProdID to a stored procedure, but I get an error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Procedure 'PriceBreak' expects parameter 'ProdID', which was not supplied. The error occurred in D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 330 Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 317 Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 1 Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 297 Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 292 Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 1 Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 330 Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 317 Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 1 Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 297 Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 292 Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 1 328 ...

  1. #1

    Default Using a stored procedure

    I am trying to pass a ProdID to a stored procedure, but I get an error:

    Error Executing Database Query.
    [Macromedia][SQLServer JDBC Driver][SQLServer]Procedure 'PriceBreak'
    expects parameter 'ProdID', which was not supplied.

    The error occurred in D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line
    330
    Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 317
    Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 1
    Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 297
    Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 292
    Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 1
    Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 330
    Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 317
    Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 1
    Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 297
    Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 292
    Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 1

    328 : </cfquery>
    329 :
    330 : <cfstoredproc procedure="PriceBreak" datasource="store2">
    331 :
    332 : <cfprocresult name="PricingSQL" resultset ="1">

    --------------------------------------------------------------------------------
    ----------------------------
    Here's my code in ColdFusion:

    <cfstoredproc procedure="PriceBreak" datasource="store2">

    <cfprocresult name="PricingSQL" resultset ="1">

    <cfprocparam type = "In"
    cfsqltype = "CF_SQL_INTEGER"
    dbvarname = "ProdID"
    value = "#Val(URL.ProductID)#"
    null = "No">

    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Price1
    dbvarname = "Price1"
    scale = 2>

    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Price2
    dbvarname = "Price2"
    scale = 2>

    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Price3
    dbvarname = "Price3"
    scale = 2>

    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break1a
    dbvarname = "Break1a"
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break1b
    dbvarname = "Break1b"
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break2a
    dbvarname = "Break2a"
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break2b
    dbvarname = "Break2b"
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break3a
    dbvarname = "Break3a"
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break3b
    dbvarname = "Break3b"
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break4a
    dbvarname = "Break4a"
    >
    --------------------------------------------------------------------------------
    ---------------------------------
    Here is the stored procedure in MSSQL 2000:

    CREATE PROCEDURE PriceBreak (ProdID int,
    Price1 Numeric(12,2) OUTPUT,
    Price2 Numeric(12,2) OUTPUT,
    Price3 Numeric(12,2) OUTPUT,
    Break1a Numeric(4,0) OUTPUT,
    Break1b Numeric(4,0) OUTPUT,
    Break2a Numeric(4,0) OUTPUT,
    Break2b Numeric(4,0) OUTPUT,
    Break3a Numeric(4,0) OUTPUT,
    Break3b Numeric(4,0) OUTPUT,
    Break4a Numeric(4,0) OUTPUT)
    AS SELECT ZProducts.ProductID, ZProducts.OurCost
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN [OurCost]*2.5
    WHEN [OurCost]<=2.5 THEN [OurCost]*2.05
    WHEN [OurCost]<=5 THEN [OurCost]*2.0
    WHEN [OurCost]<=50 THEN [OurCost]*1.8
    WHEN [OurCost]<=100 THEN [OurCost]*1.6
    ELSE [OurCost]*1.5 END AS Price1
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN [OurCost]*2.2
    WHEN [OurCost]<=2.5 THEN [OurCost]*2.05
    WHEN [OurCost]<=5 THEN [OurCost]*2
    WHEN [OurCost]<=50 THEN [OurCost]*1.7
    WHEN [OurCost]<=100 THEN [OurCost]*1.5
    ELSE [OurCost]*1.4 END AS Price2
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN [OurCost]*2.1
    WHEN [OurCost]<=2.5 THEN [OurCost]*2
    WHEN [OurCost]<=5 THEN [OurCost]*1.9
    WHEN [OurCost]<=50 THEN [OurCost]*1.6
    WHEN [OurCost]<=100 THEN [OurCost]*1.3
    ELSE 0 END AS Price3
    , 1 AS Break1a
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN 499
    WHEN [OurCost]<=2.5 THEN 99
    WHEN [OurCost]<=5 THEN 99
    WHEN [OurCost]<=50 THEN 24
    WHEN [OurCost]<=100 THEN 9
    ELSE 3 END AS Break1b
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN 499
    WHEN [OurCost]<=2.5 THEN 99
    WHEN [OurCost]<=5 THEN 99
    WHEN [OurCost]<=50 THEN 24
    WHEN [OurCost]<=100 THEN 9
    ELSE 3 END +1 AS Break2a
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN 999
    WHEN [OurCost]<=2.5 THEN 499
    WHEN [OurCost]<=5 THEN 499
    WHEN [OurCost]<=50 THEN 49
    WHEN [OurCost]<=100 THEN 25
    ELSE 9 END AS Break2b
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN 999
    WHEN [OurCost]<=2.5 THEN 499
    WHEN [OurCost]<=5 THEN 499
    WHEN [OurCost]<=50 THEN 49
    WHEN [OurCost]<=100 THEN 25
    ELSE 9 END +1 AS Break3a
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN 4999
    WHEN [OurCost]<=2.5 THEN 999
    WHEN [OurCost]<=5 THEN 999
    WHEN [OurCost]<=50 THEN 99
    WHEN [OurCost]<=100 THEN 49
    ELSE 0 END AS Break3b
    , CASE WHEN [OurCost] =0 THEN 0
    WHEN [OurCost]<=0.5 THEN 5000
    WHEN [OurCost]<=2.5 THEN 1000
    WHEN [OurCost]<=5 THEN 1000
    WHEN [OurCost]<=50 THEN 100
    WHEN [OurCost]<=100 THEN 50
    ELSE 0 END AS Break4a
    FROM ZProducts
    WHERE (ZProducts.ProductID = ProdID)
    GO

    -------------------------------------------

    I must be close, right? Why is this not working? I have it done already with
    regular queries to the same datasource, but I'm trying to switch to stored
    procedures for the performance boost.

    I remain,

    normalized


    MarkWright Guest

  2. #2

    Default Re: Using a stored procedure

    Just grasping at straws here, but did you try putting cfprocresult after all of the params?
    philh Guest

  3. #3

    Default Re: Using a stored procedure

    Your right, I did have it out of order, but that did not solve the problem. I
    still get the same error. I must be missing something easy. This is the first
    time I've tried to use a stored procedure instead of a query. If I figure this
    out, I will eventually convert all my queries to SP's.

    Let me know if you have any other ideas.

    MarkWright Guest

  4. #4

    Default Re: Using a stored procedure

    All I can see is you most probably should include the scale attribute for the
    out params with a scale of zero, just incase it does not default to zero.

    I also think I read somewhere that with cf 6.1 (I think) that you don't use
    the "dbvarname" attribute, but the params have to be listed in the same order
    as the sp.

    Also, maybe silly, but check and ensure that Val(URL.ProductID) does infact
    contain a value.

    Ken

    The ScareCrow Guest

  5. #5

    Default Re: Using a stored procedure

    #VAL(ProductID)# definately has a value because it shows up in the URL. I use
    the dbvarname, so I don't have to pass the values in order, but I will try
    without it and let you know. I will also add the scale attribute if necessary.

    Thanks for the help so far! Hope we figure this out.

    MarkWright Guest

  6. #6

    Default Re: Using a stored procedure

    FYI, with CF MX, dbvarname is no longer used.

    [url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b16.htm#wp1102102[/url]

    ColdFusion MX:

    Changed the dbvarname attribute behavior: it is now ignored for all drivers.
    ColdFusion MX uses JDBC 2.2 and does not support named parameters.

    ColdFusion MX supports positional parameters only and you must code
    cfprocparam tags in the same order as the associated parameters in the stored
    procedure definition.[/b[

    Phil

    paross1 Guest

  7. #7

    Default Re: Using a stored procedure

    Okay, I took the dbvarname out, so now it looks like this:

    --------------------------------------------------------------------------------
    ---------------------------
    <cfstoredproc procedure="PriceBreak" datasource="store2">

    <cfprocparam type = "In"
    cfsqltype = "CF_SQL_INTEGER"
    value = "#Val(URL.ProductID)#"
    null = "No">

    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Price1
    scale = 2>

    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Price2
    scale = 2>

    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Price3
    scale = 2>

    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break1a
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break1b
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break2a
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break2b
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break3a
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break3b
    >
    <cfprocparam type = "Out"
    cfsqltype = "CF_SQL_NUMERIC"
    variable = Break4a
    >
    <cfprocresult name="PricingSQL" resultset ="1">


    --------------------------------------------------------------------------------
    -------

    I still get the same error. I'm only passing one variable into the stored
    procedure, so I definately am not going out of order. Please help with some
    more ideas! I just don't get it
    :confused;

    MarkWright Guest

  8. #8

    Default Re: Using a stored procedure

    PS

    I haven't changed my SP at all, so the order is the same as originally posted.
    MarkWright Guest

  9. #9

    Default Re: Using a stored procedure

    I assume that you are still getting an error ?

    Just because the URL.ProductID is in the url does not mean it is being passed
    to the sp.

    Try the following:

    1. cfoutput the URL.ProductID just before the sp call

    2. Remove the Val function

    3. Comment out the output vars from the cf code and the sp, just to ensure
    these are not causing the problem

    Ken

    The ScareCrow Guest

  10. #10

    Default Re: Using a stored procedure

    Before that last response, I changed my procedure call to a pass-thru query
    just to make sure I was actually passing ProdID...and the value was passed, but
    I have not tested the output variables yet. I will comment them out and do a
    regular stored procedure call and see if that works. Problem is...If it works
    by taking them out, then what? I need those values.

    I don't want to get rid of the VAL function. I have it there because I don't
    want somebody to be able to put a Delete query in the URL instead of the
    ProdID. The VAL function only allows numbers to pass into my db. I just want
    the extra level of security.

    MarkWright Guest

  11. #11

    Default Re: Using a stored procedure

    For some reason my quick reply didn't show up...so here's where I'm at.

    I commented out the output parameters...still get same error

    I removed the Val function...still get same error

    I can output the ProdID before the procedure call and I can use a pass-thru
    query to pass the value.

    I'm starting to think my coding is not the problem (although it usually is).
    Maybe there's a setting on the server that if you're logged on as MarkWright,
    stored procedures don't work : )

    Thanks for the help so far. Let's keep trying. We are just missing
    something...but what?

    MarkWright Guest

  12. #12

    Default Re: Using a stored procedure

    This may seem like a stupid question, but have you included a closing
    </cfstoredproc> tag? I don't see one on any of your posts, but you may have
    just cut off the tag when you copied the example for posting. At any rate, it
    should follow your cfprocresult tag.

    Phil

    paross1 Guest

  13. #13

    Default Re: Using a stored procedure

    OMG....no I didn't have the closing tag.

    Now I get a different error...too many parameters. I'll have to look into this further and let you know.
    MarkWright Guest

  14. #14

    Default Re: Using a stored procedure

    It works! I had added an extra parameter during all this testing that I didn't
    have in my procedure. That was an easy fix.

    The original problem was solved when I added the closing tag (another easy
    fix). The simple things are always the ones that are overlooked. You
    see....there is no such thing as a stupid question.

    I am a little annoyed that the ColdFusion MX Bible used dbvarname in the
    examples when that is no longer used, but I don't think that ever was an issue
    since I already had the parameters in the right order. I guess the book I have
    was written before this feature was removed.

    Thank you everone for sticking this one out!

    I remain,

    normalized

    MarkWright Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 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. help with a stored procedure
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 3rd, 05:35 PM

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