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

  • SQL SELECT - xbasic statement to assign a variable

    Having some trouble, been working on this off and on for 3 days thinking I would "get it" eventually.
    I have read, researched,etc and I am finally asking for some help because I know I am close but ready to drown the keyboard in the bathtub.

    I have a button that does an AJAX callback to do several things, geocode, save, run a function to INSERT into jqcalendar (one of my tables)
    then I thought man it sure would be fun to do another INSERT into a different table grabbing the pk of the last INSERT
    So I added in an AJAX callback to run another xbasic function - mostly because I kept getting a zero for the pk.

    I have a guuid that gets submitted into jqcalendar so I thought I will just grab the pk from the row that matches the guuid rather than look for the lastinserted identity which seemed troublesome to me anyways.
    so here my code to SELECT the Id WHERE the guuid matches the guuid in the table

    Can someone tell me why they think this is a no go?

    Code:
    function create_task as c (e as p)'debug(1)
    dim cn as sql::Connection
    dim args as sql::arguments
    dim mynewpk as C
    
    
    	args.Add("subject",e._currentRowDataNew.Full_Name)
     	args.Add("jobguid",e._currentRowDataNew.taskguid)
    	args.Add("mynewpk",mypk)
    
    
    
    
    sqlget = "SELECT Id FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid"	
    sql3 = "INSERT INTO alphanwcopro.company_jobs_tasks set tasks_jobs_id = :mynewpk, Subject = :subject"	
    	
    cn.open("::Name::Connection1")
    cn.Execute(sqlget,args)
    dim rs as sql::ResultSet
    rs = cn.ResultSet
    mypk = rs.data("Id")
    cn.Execute(sql3,args)
    cn.close()
    end if
    end function

    Looks good to me, lol
    I could not use the debugger because I am getting img errors for the buttons that I have but i can verify that the field names are all accurate so there must be some sorta syntax/code snot.
    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?"

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

    Charles, you must debug through this to see what's wrong. Take your code and put it into a new UX, make your callbacks and see what's going on. Hard code whatever you need to in order for statements to run in a test.

    Maybe it's your cut and paste... but don't do this...

    function create_task as c (e as p)'debug(1)

    Next, use args.set... not args.add

    Next, where is mypk coming from? Nevermind... I see it now. But, you're using mypk before it's assigned.

    Next, there is no error checking in your code at all. How can you hope to know what's going on if you don't debug through it, or at least have error checking code sending back information.
    Last edited by Davidk; 05-24-2016, 01:56 PM.

    Comment


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

      I probably won't do the way you do to get the primary id, however since you are experimenting, my feeling is
      when you set the
      args.Add("mynewpk",mypk)

      mypk = rs.data("Id") does not exist. so it is not assigning I think.
      if you put that line after you assign this line then the args.add will add probably. ( args.set is better than args.add)
      I normally get the primary key from the connection object and use it.
      I do not plan to play with this so I am just thinking out loud.
      thanks for reading

      gandhi

      version 11 3381 - 4096
      mysql backend
      http://www.alphawebprogramming.blogspot.com
      ggandhi344@gmail.com
      Skype:ggandhi344@gmail.com
      1 914 924 5171

      Comment


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

        By "exists" do you mean there isn't a value in the database yet? I think there should be at this point. The previous Ajax callback runs a function to create the row in jqcalendar with that guuid value. If on the other hand you think it isn't in the .data yet you might need to explain it to me better, also I am trying to get the primary key from the record that was created in the other previous function and since after all of my reading on it, it seemed there's a bit of unreliability so like I said I just made a unique key and look for a match, but I can't seem to set a variable from a sql lookup in xbasic.
        I thought this would be easier but I am still pretty new to xbasic and alpha, the documentation is very abrupt with not enough examples or working code to help me understand.
        It's a journey!
        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


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

          Your code should blow up because...

          args.Add("mynewpk",mypk)

          is using mypk which does not exist... where you're using it.

          Comment


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

            Thanks guys, and David your words are well spoken. Error checking is left out because I was trying to strip out everything except what was required - for me and maybe this is stupid but I write error checking AFTER I get it working because I beleive that errors are just possibilities...

            Good idea on creating a test component to step through the debugging process - thanks!
            I cannot honestly say I know what I am doing, with zero training I cut copy paste and observe other peoples code and that's probably taken out of context - yes that is a recipe for disaster -
            Thanks for taking to the time to comment and whatnot. I'll work on this some more, setup a test component and see what I can do with the advice so far.
            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


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

              if you put a debug(1) above this line "mypk = rs.data("Id")" do you get a value?
              Also, this syntax is more like this:
              INSERT INTO alphanwcopro.company_jobs_tasks
              (tasks_jobs_id,Subject)
              values(:mynewpk,:subject)

              Comment


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

                Errors are certainties... unless you prepare for them... and then, sometimes, they won't appear.

                The one major error in your code is that you're using mypk before you create it. XBasic will break on this.

                Comment


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

                  Well I am still working on this, I did get an insert based on the SQL lookup by moving mypk AND instead of
                  "SELECT Id FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid"
                  I changed it to
                  "SELECT * FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid"

                  This seems weird to me, I mean why did it fail when I was being so specific as to say I just wanted the ID column?
                  if I used the sql genie, it returned the correct value...


                  SO this is an excerpt from the working code (minus the args, etc.)

                  Code:
                  sqlget = "SELECT * FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid"	
                  sql3 = "INSERT INTO alphanwcopro.company_jobs_tasks set tasks_jobs_id = :mynewpk, Subject = :subject"	
                  	
                  cn.open("::Name::Connection1")
                  cn.Execute(sqlget,args)
                  dim rs as sql::ResultSet
                  rs = cn.ResultSet
                  mypk = rs.data("Id")
                  args.set("mynewpk",mypk)
                  cn.Execute(sql3,args)
                  cn.close()

                  David I am sure your vomiting a little with my lack of error trapping...I did read your posts about using flags for the conn string, etc on the msg board - honestly I did, lol I am just trying to get this thing working!


                  Also while we are on the subject of xbasic code and crud operations with xbasic, Lee brings up a question for me - should I be doing as he suggests or as I have done. My method works - but is what he does better/preferred? I find it difficult to read when you list all the fields, then the values in order like that - does it matter? I saw here
                  http://www.downloads.alphasoftware.c...SQLTables.html

                  BOTH methods used, with INSERTS one way (like Lee says) and UPDATE another way (like I am doing)
                  to quote:

                  Syntax of an Update Statement
                  Update customer set lastname = :newLastname, firstname = :newFirstname where customer_id = :oldCustomer_Id

                  Syntax of an Insert Statement
                  Insert into customer (firstname, lastname, company) Values (:newFirstname, :newLastname, :NewCompany)

                  To me it seems interchangeable...is it?
                  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


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

                    That type of Insert works in MySQL (and maybe it's derivatives) but it's non-standard SQL. Unless it's widely adopted I would consider it a bit dangerous. If you ever decided to switch to SQL Server your statements would fail.

                    There's no reason why "Select Id" and rs.data("Id") cannot be used... as long as Id is actually the field name. Try rs.data(1) since you're only returning 1 column. But... returning all fields is a waste.

                    Comment


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

                      Charles,

                      I hope I am not going to confuse you. And I didn't read all the details.
                      But if based on an insert in a table A, there should be another insert in a table B. Then I would solve that with a trigger/procedure in the database and not with xbasic.

                      Comment


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

                        Syntax of an Update Statement
                        Update customer set lastname = :newLastname, firstname = :newFirstname where customer_id = :oldCustomer_Id

                        Syntax of an Insert Statement
                        Insert into customer (firstname, lastname, company) Values (:newFirstname, :newLastname, :NewCompany)
                        yes these two ways are acceptable.
                        in one method you are explicitly stating which field will get what value and the other which field will get what value in the enumerated list.
                        you are giving portions of the code, and expecting to solve the puzzle in bits and pieces.
                        if you go back to your code as david said there is no error trapping. a common method will be when result is dimmed as logical value
                        Code:
                        result = cn.open(.........)
                        if result then
                          result = cn.execute(sql statement and arguments)
                          if result then
                            do something
                          else 
                           do something
                          end if 
                        else
                        do something
                        end if
                        also you need to trap to see if there were any records returned before you assign the result set data to a variable.

                        and finally regarding thought process
                        if you sit down and write in plain english what you want to accomplish before you write a single line of code you might be
                        able to solve the whole thing with few ajax calls than many as i infer from the post. and possibly ask the sql back end to do the job for you as suggested by pieter rather than come back to alpha and
                        you write some more code and ask alpha to do the work.

                        edit:
                        i just tested single field query in mySql and it does return correct value for the primary key without any problem. harvesting all the fields only to use a single field is wasting the resources. not that the computer will cross its hands and refuse to do any more work for you.
                        Last edited by GGandhi; 05-25-2016, 05:16 AM.
                        thanks for reading

                        gandhi

                        version 11 3381 - 4096
                        mysql backend
                        http://www.alphawebprogramming.blogspot.com
                        ggandhi344@gmail.com
                        Skype:ggandhi344@gmail.com
                        1 914 924 5171

                        Comment


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

                          You can do this in 1 SQL statement: I've created a test...

                          create table test1
                          (
                          tID int,
                          tSubject varchar(50),
                          tLocation varchar(50)
                          )

                          Insert into Test1
                          (tID,tSubject,tLocation)
                          Select ID,Subject,Location from jqcalendar where ID = 164

                          Select * from test1

                          Comment


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

                            Your Code:
                            INSERT INTO alphanwcopro.company_jobs_tasks
                            (tasks_jobs_id,Subject)
                            SELECT Id,subject FROM alphanwcopro.jqcalendar WHERE taskguid = :jobguid

                            Comment


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

                              You guys are all awesome, and no I am not going to get confused (probably)
                              As far as using a trigger, I do not want to use one because it's up to the end user to decide if he wants to create the additional INSERT by clicking a button to perform the different action, in other words it's an option.
                              I know what your saying about writing it down in plain English, my nemesis is not knowing exactly what I want up front. I have found that so far I over think things, then trim back to what is really important. I give too many options then later realize that all my customer/end user really uses is let's say 5 of the 15 fields - OR - I suddenly realize that I should really have another table for rollbacks/history.
                              I am still working on this insert but it is now working as expected! One issue was not running things synchronously, and that goes back to what Ghandi was saying about only giving you guys partial info, but in the end the advice you all have provided has helped me figure out not only what I needed to do but also what SHOULD be done to make the code better.
                              Thanks, I will post more as I progress through it. So far so good though!
                              THANKS A MILLION!
                              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

                              Working...
                              X