Alpha Video Training
Results 1 to 8 of 8

Thread: 4 hours to get results from a SP, now need help creating function

  1. #1
    Member
    Real Name
    jim wilson
    Join Date
    May 2019
    Posts
    21

    Default 4 hours to get results from a SP, now need help creating function

    EDIT: Items 1 & 2 solved



    I wanted to explore calling (and retrieving a result from) a MSSQL stored Procedure.
    Just a learning example... pass an ItemID and let it do a simple calc. Then return something.

    Here's my working code:

    Code:
    DIM session.UserID as c
    DIM DiscountPrice as N
    dim cn as sql::connection
    dim args as sql::arguments
    dim rs as sql::ResultSet
    'session.UserID = a5ws_getcurrentuser()   'once we start logging in
    session.UserID = "jimwwID"
    
    args.set("itemid",convert_type(e.dataSubmitted.itemid,"N" ))
    args.set("theUser",session.UserID )
    args.set("discountprice",-1,sql::ArgumentUsage::InputOutputArgument)
    
    cn.Open("::Name::bruce")
    cn.Execute("EXEC calcext :itemid, :theUser, :discountprice",args)
    
    if (rtc.flagRecordWasSaved) then
            e.javascript = e.javascript + "alert('Discount price is $' + " + convert_type(round(args[3].data,2),"C") + ");" + crlf()
    end if


    I have this in Server-Side Events in a UX control.
    In the afterDialogValidate function.

    I understand that the preferred method for JS code is to move them into the JS functions CODE section, rather than have lots of JS code fragments directly attached to buttons, etc.

    1) How does one do this for a XBasic function?
    I can bring up an empty function window, and paste in what I've written above, but I need a "function xxx as ..." wrapper and I don't know what to put.
    The afterDialogValidate function starts with this: function afterDialogValidate as v (e as p)
    Do I pass "e" to my new XBasic function? Having trouble with syntax.

    2) Now, once I've written this function to call the SP I also might want to call it other places, which is why I'm trying to get it moved out of the afterDialogValidate function. Then I can simply call it from a button.
    I don't see how I will have the "e" structure available to me inside the XBasic function (if I call it from a button on a UX) so I think I need to pass IN the ItemID? instead of having it access "e.dataSubmitted.itemid" as it is written now.

    3) For now, I am showing a JS Alert. I didn't really want this but its a way to test the SP result. I wanted to stuff the answer into a control on the UX. But I can't seem to make that happen.
    a) I don't know how to properly reference a control (I thought it was e.control.myvar)
    b) Maybe whats happening is that the ExecuteServerSideAction is set to "Edit record just submitted", so I think I might be setting the discount value correctly into a control, but its getting erased when the controls refresh after the rec was saved.

    ideas appreciated, thx
    jim
    Last edited by jimww; 05-21-2019 at 02:33 AM.

  2. #2
    Member
    Real Name
    jim wilson
    Join Date
    May 2019
    Posts
    21

    Default Re: 4 hours to get results from a SP, now need help creating function

    ok, that was easier than I thought... to make it into a function.

    here's the XBasic function:
    Code:
    function SP_calc_ext as c (theItem as N, theJS as C)
    
    DIM session.UserID as c
    DIM DiscountPrice as N
    dim cn as sql::connection
    dim args as sql::arguments
    session.UserID = "jimwwID"  'session.UserID = a5ws_getcurrentuser()   'once we start logging in
    
    'optional method to build SQL
    'sql = <<%sql% 
    'EXEC [SP_CalcExt] :itemid, :theUser 
    '%sql%
    
    args.set("itemid",theItem)
    args.set("theUser",session.UserID )
    args.set("discountprice",-1,sql::ArgumentUsage::InputOutputArgument)
    
    cn.Open("::Name::bruce")
    cn.Execute("EXEC calcext :itemid, :theUser, :discountprice",args)
    
    theJS = theJS + "alert('Discount price is $' + " + convert_type(round(args[3].data,2),"C") + ");" + crlf()
    end function

    And I'm calling it like this: SP_calc_ext(convert_type(e.dataSubmitted.itemid,"N" ), e.javascript)


    Problem is I do not get an alert anymore.

  3. #3
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,419

    Default Re: 4 hours to get results from a SP, now need help creating function

    Changing this line
    theJS = theJS + "alert('Discount price is $' + " + convert_type(round(args[3].data,2),"C") + ");" + crlf()
    to
    SP_calc_ext = theJS + "alert('Discount price is $' + " + convert_type(round(args[3].data,2),"C") + ");" + crlf()
    Should do the trick to give you the alert.

  4. #4
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,594

    Default Re: 4 hours to get results from a SP, now need help creating function

    Couple hints... do not dim session variables, simply declare them. Also, whenever you have a cn.open() you should have a cn.close().
    Pat Bremkamp
    MindKicks Consulting

  5. #5
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,541

    Default Re: 4 hours to get results from a SP, now need help creating function

    Jim,

    Here are some questions, concerns, and suggestions...

    1. Why are you passing e.javascript into your SP_calc_ext function?
    2. When you call your SP_calc_ext function from afterDialogValidate... you will get returned to afterDialogValidate
    3. You can pass in the "e" object to your function... if you want to. There's usually a lot of stuff in "e", but you can do this... SP_calc_ext(e). You SP_calc_ext function header would then be...
    Code:
    function SP_calc_ext as c (e as p)
    4. It's often easier to return a call to a Javascript function, passing in params, than it is stringing together Javascript statements.
    5. If you're returning data from SP_calc_ext then you must set that return to a variable when calling.

    Here's a rough walk-through of what you're trying to do.

    Code:
    function afterDialogValidate as v (e as p)
    
    ExecuteServerSideAction("Save Data::Save Data")
    
    debug(1)
    
    dim callRet as c
    callRet = SP_calc_ext(e)
    
    e.javascript = e.javascript + callRet
    
    end function
    Code:
    function SP_calc_ext as c (e as p)
    	
    debug(1)
    	
    SP_calc_ext = "JSFunctionReturned('test');"	
    	
    end function
    Code:
    function JSFunctionReturned(test){
    
    	debugger;
    	alert(test);
    
    }
    Finally, you should always post the Alpha build you're working with. Alpha has bugs that appear and disappear rapidly. For example, in the current pre-release, XBasic Live Preview, the above code does not produce an alert. XBasic Working Preview does, and Browser Live Preview does. It's a head shaker, but that's typical Alpha.

  6. #6
    Member
    Real Name
    jim wilson
    Join Date
    May 2019
    Posts
    21

    Default Re: 4 hours to get results from a SP, now need help creating function

    Thx for suggestions.

    Call MSSQL Stored Procedure with return value
    Build 5667

    For completeness, here's my "final" code:

    Code:
    function SP_calc_ext as c (theItem as N)
    
    DIM session.UserID as c
    DIM DiscountPrice as N
    dim cn as sql::connection
    dim args as sql::arguments
    session.UserID = "jimwwID"  'session.UserID = a5ws_getcurrentuser()   'once we start logging in
    
    args.set("itemid",theItem)
    args.set("theUser",session.UserID )
    args.set("discountprice",-1,sql::ArgumentUsage::InputOutputArgument)
    
    cn.Open("::Name::bruce")
    cn.Execute("EXEC calcext :itemid, :theUser, :discountprice",args)
    cn.Close("::Name::bruce")
    
    SP_calc_ext = "alert('Discount price is $' + " + convert_type(round(args[3].data,2),"C") + ");" + crlf()
    'debug(1)
    end function

    Called in afterDialogValidate
    Code:
    e.javascript = e.javascript +  SP_calc_ext(convert_type(e.dataSubmitted.itemid,"N" ))
    The Stored Procedure
    Code:
    /****** Object:  StoredProcedure [dbo].[calcExt]    Script Date: 5/23/2019 11:49:37 AM ******/
    ALTER PROCEDURE [dbo].[calcExt]   
    	@itemid float,
    	@theUser varchar(50),
    	@discountprice float OUTPUT
    
    AS
    BEGIN
    	SET NOCOUNT ON;
    	UPDATE items
    		SET	ext = qty * price,
    			updatedby = @theUser
    		WHERE itemid = @itemid; 
    
    	Set @discountprice = (
    		SELECT ext * 0.85
    		FROM items
    		WHERE itemid = @itemid );
    END


    One major thing that took so much time was the other/older examples in the forum.
    Syntax for various DBs varies.
    This doesn't get a ResultSet - no none of that is necc.
    For MSSQL, "SET NOCOUNT ON;" was mandatory.
    Older forum examples had "EXEC theSPname (:arg1, :arg2)" <--- the args in params screwed me up for a long time. That will break the SP in MSSQL 2016
    Last edited by jimww; 05-23-2019 at 11:54 AM.

  7. #7
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,541

    Default Re: 4 hours to get results from a SP, now need help creating function

    As mentioned by Pat, don't dim session variables. You don't need to and it's a non-standard practice.
    For your cn.Close you only need cn.Close(). You don't need to supply the connection you're closing... since you already have it. I'm surprise that didn't blow up on you.

    I wouldn't call your XBasic function which calls an SP on the same line as you're setting up the return. In Alpha that sort of thing is just too dangerous... too fragile.

    Code:
    dim spReturn as c
    spReturn = SP_calc_ext(convert_type(e.dataSubmitted.itemid,"N" ))
    e.javascript = e.javascript +  spReturn

  8. #8
    Member
    Real Name
    jim wilson
    Join Date
    May 2019
    Posts
    21

    Default Re: 4 hours to get results from a SP, now need help creating function

    got it. great. good idea. thx

Similar Threads

  1. Creating Next and Prev function within Form View control
    By Td203 in forum Tablet-Optimized Form Capabilities Beta
    Replies: 2
    Last Post: 03-14-2016, 11:31 AM
  2. Browse Table results show Find by Form results
    By gitpicker in forum Alpha Five Version 10 - Desktop Applications
    Replies: 1
    Last Post: 01-29-2013, 12:07 PM
  3. Trouble Creating Function
    By cdibi in forum Alpha Five Version 9 - Desktop Applications
    Replies: 7
    Last Post: 05-18-2008, 10:27 AM
  4. Lookup function vs creating set
    By Lawrence Fox in forum Alpha Five Version 5
    Replies: 10
    Last Post: 07-17-2003, 12:38 PM
  5. Adding Hours
    By Richard Lillibridge in forum Alpha Five Version 4
    Replies: 1
    Last Post: 12-17-2001, 06:55 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
  •