Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 02:33 AM.

  • #2
    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.

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment


        • #5
          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.

          Comment


          • #6
            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, 11:54 AM.

            Comment


            • #7
              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

              Comment


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

                got it. great. good idea. thx

                Comment

                Working...
                X