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

MySQL Query fails in loop

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

  • MySQL Query fails in loop

    This is a simple function that gets a count of selected rows from a List control, and then executes a SELECT statement against a MySQL table.

    flag = cn.execute(sql) returns true on the first loop, but returns false on the second an subsequent loop.

    I cannot figure out why it fails.



    Code:
    function test as c (e as p)
    	
    dim flag as l
    dim sql as c
    dim cn as sql::Connection
    dim args as sql::Arguments
    dim listCnt as n
    dim i as n
    
    flag = cn.open("::Name::ConnectionName")
    
    listCnt = e.dataSubmitted.List.size()
    
    for i = 1 to listCnt
    
    sql = <<%a%
    SELECT Posting_ID
    FROM posting
    WHERE Posting_ID = 1
    %a%
    
    flag = cn.execute(sql)
    
    debug(1)
    next i		
    
    cn.Close()
    			
    end function

  • #2
    Re: MySQL Query fails in loop

    I don't understand why you are doing it this way and not using the Alpha list filter.

    However, should your select not use an argument Posting_ID it is always set to 1?

    Comment


    • #3
      Re: MySQL Query fails in loop

      This is just a test function I made because a more complex function is having this problem. I am executing the same select statement every time.

      Why should it not use Posting_ID = 1?

      Comment


      • #4
        Re: MySQL Query fails in loop

        Code:
        listCnt = e.dataSubmitted.List.size()
        
        for i = 1 to listCnt
        
        sql = <<%a%
        SELECT Posting_ID
        FROM posting
        WHERE Posting_ID = 1
        %a%
        
        flag = cn.execute(sql)
        next i
        you are not cycling thru i
        so it should be
        WHERE Posting_ID = i

        better will be to use arguments
        like
        Code:
        listCnt = e.dataSubmitted.List.size()
        dim args as sql::arguments
        for i = 1 to listCnt
        
        sql = <<%a%
        SELECT Posting_ID, and some_other_fields 
        FROM posting
        WHERE Posting_ID =:newPostId
        %a%
        args.set("newPostId", i)
        
        flag = cn.execute(sql, args)
        
        next i
        thanks for reading

        gandhi

        version 11 3381 - 4096
        mysql backend
        http://www.alphawebprogramming.blogspot.com
        [email protected]
        Skype:[email protected]
        1 914 924 5171

        Comment


        • #5
          Re: MySQL Query fails in loop

          OK. I can use an argument so the query is different in every loop.

          That does not address the problem I am having.

          The problem is that the query succeeds in the first loop, then fails in subsequent loops.


          Code:
          function test as c (e as p)
          	
          dim flag as l
          dim sql as c
          dim cn as sql::Connection
          dim listCnt as n
          dim i as n
          
          flag = cn.open("::Name::ConnectionName")
          
          listCnt = e.dataSubmitted.List.size()
          dim args as sql::Arguments
          for i = 1 to listCnt
          
          sql = <<%a%
          SELECT Posting_ID, originCity
          FROM posting
          WHERE Posting_ID = :newPostId
          %a%
          args.set("newPostId", i)
          
          flag = cn.execute(sql,args)
          debug(1)	
          
          next i		
          
          cn.Close()
          			
          end function

          Comment


          • #6
            Re: MySQL Query fails in loop

            Why are you re-inventing the wheel?

            Alpha does it all for you with their action JavaScript.

            Comment


            • #7
              Re: MySQL Query fails in loop

              can you put your debug(1) at the beginning of your code right after function declaration and check if the listCnt is coming out with proper number
              also it will show what error is after the first loop. if there is any error it will pop up.
              thanks for reading

              gandhi

              version 11 3381 - 4096
              mysql backend
              http://www.alphawebprogramming.blogspot.com
              [email protected]
              Skype:[email protected]
              1 914 924 5171

              Comment


              • #8
                Re: MySQL Query fails in loop

                It doesn't matter, for your test, if you use an argument... I can't see why that would affect anything. The issue is that you're not using the resultSet before attempting to get another one.

                When you hit your debug statement you should examine cn and find cn.CallResult.Text which will give you the MySQL error you're getting... which is...

                Commands out of sync; you can't run this command now
                If you add...

                Code:
                dim rs as sql::ResultSet
                to the section where you're dimming.. and then add

                Code:
                rs = cn.ResultSet
                after your cn.execute, then the select will succeed for each loop.

                Comment


                • #9
                  Re: MySQL Query fails in loop

                  David, I added the 2 lines of code as you suggest.

                  Prior to adding the 2 lines, cn.CallResult.Text returned "Success" on every loop.

                  flag = cn.execute(sql,args) now returns .T. on every loop, which is the result I was after.

                  But I don't understand why flag = cn.execute(sql,args) fails without attempting to populate a resultSet. It is just executing a query. Why would the resultSet affect whether the query was successful or unsuccessful?

                  Comment


                  • #10
                    Re: MySQL Query fails in loop

                    cn.CallResult.Text doesn't return "Success" for your original code. Only the 1st time. It depends on when and how you're looking at cn. When your code breaks into the debugger, enter cn.CallResult.Text in the Expression column... and then continue to step through. You'll see the Text change to the error. If you always add cn as a new expression to check, it always shows success. But if you add it, and watch it... you'll see the error.

                    Another concern I saw was using listCnt = e.dataSubmitted.List.size() to get a List count. You don't say how your List is set up, or how you're selecting List rows, but .size() on a List returns an array that's much larger than the number of rows selected... at least for me... with a List set up for multi-select. This is because it's returning an array of all the data selected, not a count of the rows selected.

                    Comment


                    • #11
                      Re: MySQL Query fails in loop

                      i ran a quick test and it is the same if you run the query repeatedly without using the result set.
                      see the reference from mysql:
                      https://dev.mysql.com/doc/refman/5.7...t-of-sync.html
                      thanks for reading

                      gandhi

                      version 11 3381 - 4096
                      mysql backend
                      http://www.alphawebprogramming.blogspot.com
                      [email protected]
                      Skype:[email protected]
                      1 914 924 5171

                      Comment


                      • #12
                        Re: MySQL Query fails in loop

                        i ran a quick test and it is the same if you run the query repeatedly without using the result set.
                        I'm unsure what this means... but if you use the resultSet after each select, then every select runs without error... otherwise you get the MySQL error I posted.

                        If you're getting different results then post your full code and I'll run it.

                        Comment


                        • #13
                          Re: MySQL Query fails in loop

                          Hi Joe,

                          I made some minor adjustments to your code to help you out.
                          Sometime sql statements fail for reasons you don't expect. It's always good to know what the system thinks is happening at
                          the time of the failure.
                          I don't know how many records you're dealing with so I set it up that debug only activates when you have a hard error (this will not help you with logical errors)
                          This should get you the answer of why your code is failing after the initial pass through the loop.

                          Code:
                          function test as c (e as p)
                          	
                          dim flag as l
                          dim sql as c
                          dim cn as sql::Connection
                          dim listCnt as n
                          dim i as n
                          dim successful as l = .f.
                          dim sqlerr as c
                          flag = cn.open("::Name::ConnectionName")
                          debug(0)
                          listCnt = e.dataSubmitted.List.size()
                          dim args as sql::Arguments
                          
                          'I moved the sql section because as long as you're not changing
                          'it, there is no need to have it in the loop
                          
                          sql = <<%a%
                          SELECT Posting_ID, originCity
                          FROM posting
                          WHERE Posting_ID = :newPostId
                          %a%
                          
                          for i = 1 to listCnt
                          args.set("newPostId", i)
                          
                          successful = cn.execute(sql,args)
                          
                          if .not. successful then
                          debug(1)
                          'send an email to the appropriate person/people
                          'put it in a log file	
                          sqlerr = cn.callresult.text
                          'This tells you what caused the failure.
                          
                          else
                          'put the stuff in here you want done after a successful
                          'call to your sql database
                          
                          end if
                          debug(0)
                          next i		
                          
                          cn.Close()
                          test="This function ended properly"			
                          end function
                          Gregg
                          https://paiza.io is a great site to test and share sql code

                          Comment


                          • #14
                            Re: MySQL Query fails in loop

                            Originally posted by Davidk View Post
                            I'm unsure what this means... but if you use the resultSet after each select, then every select runs without error... otherwise you get the MySQL error I posted.

                            If you're getting different results then post your full code and I'll run it.
                            i get the same false after the first, david. may be i wasn't very clear.
                            did you see the reference?
                            thanks for reading

                            gandhi

                            version 11 3381 - 4096
                            mysql backend
                            http://www.alphawebprogramming.blogspot.com
                            [email protected]
                            Skype:[email protected]
                            1 914 924 5171

                            Comment


                            • #15
                              Re: MySQL Query fails in loop

                              OK, I am becoming more educated. Thanks to everyone.

                              I am now seeingthe error 'Commands out of sync; you can't run this command now'.

                              listCnt = e.dataSubmitted.List.size() is returning just the number of rows selected. In my case I have a list control set to return the primary key - Posting_ID

                              Ultimately I am trying to troubleshoot a more complex function that is failing - seemingly randomly - during a loop.

                              In creating this test function I ran into this particular problem, which did not occur in the original function that I was troubleshooting, because (I have now learned) in that function I an obtaining a resultset.

                              Tomorrow I will work on the more complex function that is giving me trouble, and post an example.

                              Comment

                              Working...
                              X