Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

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

SQL SELECT - xbasic statement to assign a variable

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

  • #16
    Re: SQL SELECT - xbasic statement to assign a variable

    Charles,

    I've used this function to handle this sort of thing and it has worked well for me.

    pk = cn.LastInsertedIdentity()

    Comment


    • #17
      Re: SQL SELECT - xbasic statement to assign a variable

      Yes, I looked into pk = cn.LastInsertedIdentity() and perhaps it would have worked as expected for me had I assigned my variable prior to calling it as was the case. I may re-visit that but again, there seems to be quite a bit of "may not return what you expect" in that so I devised the guuid field to match up the two records and get the pk from the original insert of the other record - plus when people reopen the record and decide that they want to create the additional record/option they will be able to match up the record even if it was already inserted...I think my thought process on that is reasonable...not sure but I think. I don't know if pk = cn.LastInsertedIdentity() will return the same value when you update the record, because it wasn't an INSERT.


      Also, here is some more fodder for discussion relevant to this very topic and David specifically. David said that I should use Args.set rather than Args.add - is this when setting the variable mypk? OR do you mean likewise when setting the args from currentRowData.new?
      I would like some clarification on when to use args.add vs set at least in this instance anyways... I am pretty sure I have seen that same thing said before about args.add - whats the difference?
      NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

      Comment


      • #18
        Re: SQL SELECT - xbasic statement to assign a variable

        Just always use args.set. Have a look in the doc to see what each does and you'll see why.

        Comment


        • #19
          Re: SQL SELECT - xbasic statement to assign a variable

          Charles,

          I did not read your post fully. I'll post again once I have a little more time. Maybe there is more complexity then I originally thought. I was mainly addressing the second insert and needing the pk for the record of your prior insert. If it's as simple as that, then it works well.

          Comment


          • #20
            Re: SQL SELECT - xbasic statement to assign a variable

            It just so happens that I tackled something very similar yesterday so I figured I'd throw in my two cents. I had to do some code to create a new record (actually a copy record function) using multiple tables. I needed the id field out of the first table for foreign keys in the secondary tables.

            I tend to use SQL to do as much as possible to try to take some load off of the application server as an SQL server can handle it much better. My solution was to use an SQL transaction and get the ID during the transaction. I'm using MSSQL and this is a snippet of my code:
            Code:
            .........................
            		if cn.open("::Name::MVWKS")	then
            			dim args as SQL::Arguments
            			args.Add("masteruid",masterid)
            			args.Add("edit",upper(a5ws_getcurrentuser()))
            			if revtest < 1 then
            				args.Add("rev",0)
            				args.Add("op",convert_type(e.dataSubmitted.OPNO,"N")+1)
            			else
            				args.Add("rev",1)
            				args.Add("prty",convert_type(e.dataSubmitted.OPNO,"N"))
            			end if
            			dim SQL as c = <<%longstring%
            				BEGIN TRANSACTION [CopyLayout]
            				BEGIN TRY
            				
            				DECLARE @newID INT
            				
            				INSERT INTO machlayoutmaster
            				(machno, machtype, partno, dwgno, mainprgno, layoutrev, opno, numparts, lastedby, [status], notes, [priority], hassubs, prgcall, lasteddt)
            				SELECT machno, machtype, partno, dwgno, mainprgno, layoutrev+:rev AS layoutrev, opno, numparts, :edit AS lastedby, 'NEW' AS [status], notes, :prty AS [priority], hassubs, prgcall, GETDATE() AS lasteddt
            				FROM machlayoutmaster
            				WHERE unique_id = :masteruid
            				
            				SELECT @newID = SCOPE_IDENTITY()
            				
            				INSERT INTO MachLayoutDetail
            				(layoutmaster_id, position, sequence, tool, toolinfo, notes, layoutord, desc2)
            				SELECT @newID AS layoutmaster_id, position, sequence, tool, toolinfo, notes, layoutord, desc2
            				FROM MachLayoutDetail
            				WHERE layoutmaster_id = :masteruid
            				
            				INSERT INTO MachLayoutSubprograms
            				(layoutmaster_id, mainprg, sequence, subprg, [description])
            				SELECT @newID AS layoutmaster_id, mainprg, sequence, subprg, [description]
            				FROM MachLayoutSubprograms
            				WHERE layoutmaster_id = :masteruid
            				
            				COMMIT TRANSACTION [CopyLayout]
            				
            				END TRY
            				BEGIN CATCH
            					ROLLBACK TRANSACTION [CopyLayout]
            					PRINT ERROR_MESSAGE()
            				END CATCH
            			%longstring%
            ...................
            The key is creating the variable at the top: DECLARE @newID INT
            and then setting that variable to the inserted identity directly after the first insert: SELECT @newID = SCOPE_IDENTITY()
            After which you can use the variable @newID for your foreign key in your child tables.

            As I understand it, if something goes wrong the transaction won't commit, but if it goes right, you have your identity for the subsequent inserts which will also cancel the transaction if they don't work properly.
            This keeps the Xbasic to a minimum, which as I've been finding lately, is almost always a good thing.

            Anyway, just another option.
            Last edited by -Jinx-; 05-26-2016, 11:09 AM.

            Comment


            • #21
              Re: SQL SELECT - xbasic statement to assign a variable

              We have used conn.LastInsertedIdentity() with very little issue over the past 4-5 years. There is also one conn.CallResult.LastInsertedIdentity and this one I find can be unreliable.
              However, code like this should do what you expect (untested but just illustrating the idea).

              Code:
              dim conn as SQL::Connection
              dim args as SQL::Arguments
              
              if conn.open("::name::conn") then
                   args.set("customer","John Smith")
                  'assumes order table has a autoincrement PK
                   sql="INSERT INTO order (customer) VALUES (:customer)"
              
                   if conn.execute(sql,args) then
                        dim order_id as N = conn.LastInsertedIdentity() 
                        args.set("order_id",order_id)
                        args.set("item_details", "test_item")
              
                        sql="INSERT INTO order_detail (order_id,details) VALUES (:order_id, :item_details)"
                        if conn.execute(sql,args) then
               
                        else
                            'handle order_detail insert failure
                        end if
                   else
                       'handle sql execute error
                   end if
                   conn.close()
              else
                 'handle connection error
              end if
              Also, if you already know the guid you can do what lvasic suggested:
              e.g.
              Code:
              args.set("jobguid","1294019-219401942-92142491")
              args.set("subject", "my subject")
              sql = "INSERT INTO alphanwcopro.company_jobs_tasks (tasks_job_id,subject) SELECT Id, :subject FROM alphanwcopro.jqcalendar WHERE taskguid=:jobguid"

              Also if you are doing a 2+ table insert, you may want to look into transactions and the conn.begintransaction() and conn.committransaction() / conn.rollbacktransaction() methods.
              Scott Moniz - Computer Programmer/Analyst
              REA Inc.
              http://reainc.net
              (416)-533-3777
              scott@reainc.net

              REA INC offers consulting services, programming services, systems design, database design, third party payment gateway integration (CHASE, PAYPAL, AUTHORIZE.NET) and developer support.
              If you need custom code, or 1-to-1 mentoring in any facet of your database/web application design,
              contact us to discuss options.

              Comment


              • #22
                Re: SQL SELECT - xbasic statement to assign a variable

                Thanks Scott, I am sure this thread will be viewed many times in the future. Everyone has been so helpful, this is the kind of stuff that documentation really cannot address in full due to so many variables but gives everyone insight into how it can be done.
                Personally I have come across code blocks like this in other areas that helped me write my VERY FIRST case select statement from scratch and it worked first time! I was even able to write in error code, I was so pleased with my self I went and had an ice cream cone.
                Cheers all ~
                NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

                Comment


                • #23
                  Re: SQL SELECT - xbasic statement to assign a variable

                  Not a problem Charles! Glad I could help give some insight. I will also go have an ice cream cone! :) Have a great weekend
                  Scott Moniz - Computer Programmer/Analyst
                  REA Inc.
                  http://reainc.net
                  (416)-533-3777
                  scott@reainc.net

                  REA INC offers consulting services, programming services, systems design, database design, third party payment gateway integration (CHASE, PAYPAL, AUTHORIZE.NET) and developer support.
                  If you need custom code, or 1-to-1 mentoring in any facet of your database/web application design,
                  contact us to discuss options.

                  Comment

                  Working...
                  X