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

    #46
    Re: MySQL Query fails in loop

    Joe,

    I'm looking at the data from the tables posting and postingrules.
    I see you have 55 records in posting from AlphaAnywhere , and 314 rules in postingrules that relate to AlphaAnywhere.

    Assuming we are working with just one posting, what criteria are you using to determine if a rule needs to be applied :
    ie: is a zipcode field blank, does a city/state combination not exist, are there no miles for the posting.

    I think once we get a handle on the criteria for a match, we can get this working the way you want it to work.
    Gregg
    https://paiza.io is a great site to test and share sql code

    Comment


      #47
      Re: MySQL Query fails in loop

      The criteria for a match is described in my post # 36.

      After getting the field values for the first record from postingrules table, there are 16 of those fields that make up the criteria.

      The values in those fields are looked at to construct a WHERE clause.

      The WHERE clause could contain any combination of the 16 fields.

      The WHERE clause is then used to query the posting table. The code loops through and queries every "selected" record in the posting table.

      The query is only looking at one posting record, and it returns a resultset. If the resultset is empty, there is no match, and it loops to the next posting record, and so on.

      If the resultset is not empty, it can only contain one row, which is a match.

      Tomorrow I will post a video that shows this in detail.

      Comment


        #48
        Re: MySQL Query fails in loop

        My Logic flow statement means.

        You have assumed "all swans are white" a black swan comes along crash.

        Kuhn rather than Popper.

        Comment


          #49
          Re: MySQL Query fails in loop

          Joe,
          This looks like a good place to use a stored procedure and temporary tables, especially if you have a fast server. That way, you keep it all in MySQL instead of continually making Alpha calls to SQL and sending back the results. Many times, opening and closing the connection takes longer than reading and processing the data.

          Since stored procedures can also call other stored procedures, this would allow you to make the process modular which would make it easier to troubleshoot.

          I know it is another "language" to learn, but in one instance, I had a process that made a number of SQL select statements that took 12 seconds to process using Alpha. The stored procedure did it in .8 seconds!
          Pat Bremkamp
          MindKicks Consulting

          Comment


            #50
            Re: MySQL Query fails in loop

            Originally posted by Pat Bremkamp View Post
            Joe,
            This looks like a good place to use a stored procedure and temporary tables, especially if you have a fast server. That way, you keep it all in MySQL instead of continually making Alpha calls to SQL and sending back the results. Many times, opening and closing the connection takes longer than reading and processing the data.

            Since stored procedures can also call other stored procedures, this would allow you to make the process modular which would make it easier to troubleshoot.

            I know it is another "language" to learn, but in one instance, I had a process that made a number of SQL select statements that took 12 seconds to process using Alpha. The stored procedure did it in .8 seconds!

            I am not familiar with stored procedures. I use Navicat, and it looks like I can create them there. I will do some studying. Does Alpha provide any tutorials for working with stored procedures?

            Comment


              #51
              Re: MySQL Query fails in loop

              And here are some crude videos I made that demonstrate what i am trying to achieve...


              Video 1 - how it works for a user:

              Part 1 https://www.screencast.com/t/RMrEfO1Yvxk

              Part 2 https://www.screencast.com/t/C3ubtdVZ

              Video 2 - in the debugger:

              Part 1 https://www.screencast.com/t/fz0IZEVTxM

              Part 2 https://www.screencast.com/t/K3WnloHTq

              Comment


                #52
                Re: MySQL Query fails in loop

                I used a book Stored Procedure Programming which was very helpful. Below is a sample stored procedure that includes loops. You can see, it is pretty easy to read.
                Code:
                BEGIN
                	/* scan_for_deficiencies_new Version 1 10/05/2015 by Pat
                	This version does not create the def_header nor def_lines entires.
                	Searches Projects_lines for lines where line_status is advise, deficient or recommend
                	and writes the findings into mem_inspection_summary.
                	Input is p_project and p_user_name. Output is text and html entries for IS email */
                
                DECLARE v_advs_count, v_defs_count, v_acts_count, v_recs_count INT;
                DECLARE v_advs_result, v_defs_result, v_acts_result, v_recs_result CHAR(60);
                DECLARE v_item_status, v_job_full_name, v_appliance_lsd CHAR(60);
                DECLARE v_item_id, v_section_id, v_display_order INT;
                DECLARE v_client_id, v_owner_id, v_current_id, v_consultant_id, v_has_access INT;
                DECLARE v_question, v_def, v_action, v_rec, v_comments VARCHAR(1200);
                
                DECLARE flag_end_loop, flag_header_exists, flag_adv_exists, flag_def_exists, flag_rec_exists TINYINT;
                
                DECLARE crsr_advs CURSOR FOR
                	SELECT SECTION_ID, QUESTION, DISPLAY_ORDER, DISPLAY_VALUE
                		FROM project_lines
                		WHERE PROJECT_NUMBER = p_project AND LINE_STATUS = 'Advise';
                
                DECLARE crsr_defs CURSOR FOR
                	SELECT SECTION_ID, QUESTION, DISPLAY_ORDER, DISPLAY_VALUE
                		FROM project_lines
                		WHERE PROJECT_NUMBER = p_project AND LINE_STATUS = 'Deficient';
                
                DECLARE crsr_recs CURSOR FOR
                	SELECT SECTION_ID, QUESTION, DISPLAY_ORDER, DISPLAY_VALUE
                		FROM project_lines
                		WHERE PROJECT_NUMBER = p_project AND LINE_STATUS = 'Recommend';
                
                DECLARE CONTINUE HANDLER FOR 1329 SET flag_end_loop = 1;
                
                create_hdr:BEGIN
                	SELECT PROJECT_FULL_NAME, CLIENT_COMPANY_ID, OWNER_COMPANY_ID, CURRENT_OWNER_COMPANY_ID,
                		CONSULTANT_COMPANY_ID, CAN_ACCESS_PORTAL, APPLIANCE_LSD 
                	FROM projects WHERE PROJECT_NUMBER = p_project 
                	INTO v_job_full_name, v_client_id, v_owner_id, v_current_id, v_consultant_id, v_has_access, v_appliance_lsd;
                	## insert a header if one doesn't exist. Primary key is project_number
                	SELECT count(*) FROM def_header WHERE PROJECT_NUMBER = p_project INTO flag_header_exists;
                	IF flag_header_exists = 0 THEN
                		SET v_item_status = 'Scanned';
                		INSERT INTO def_header
                			SET PROJECT_NUMBER = p_project, REPORT_STATUS = v_item_status,
                				RESPONSE_DUE_DATE = DATE_ADD(CURDATE(), INTERVAL 30 DAY), STATUS_DATE = NOW(),
                				STATUS_BY = p_user_name, DATE_ADDED = NOW(), ADDED_BY = p_user_name;
                	END IF;
                END create_hdr;
                
                get_advs:BEGIN
                	## Are there any advise items in the lines?
                	SELECT COUNT(*) FROM project_lines
                		WHERE PROJECT_NUMBER = p_project AND LINE_STATUS = 'Advise'
                			INTO v_advs_count;
                	SET v_advs_result = concat(v_defs_count,' Open Advs Found and');
                
                	IF v_advs_count > 0 THEN
                		SET flag_end_loop = 0;
                		OPEN crsr_advs;
                		get_advs_loop: LOOP
                		FETCH crsr_advs INTO v_section_id, v_question, v_display_order, v_def;
                		IF flag_end_loop = 1 THEN
                			SET flag_end_loop = 0;
                			LEAVE get_advs_loop;
                		END IF;
                		## if this advise item is not already in the table, insert it
                		SELECT COUNT(*) FROM def_lines 
                			WHERE PROJECT_NUMBER = p_project AND DISPLAY_ORDER = v_display_order
                			INTO flag_adv_exists;
                		IF flag_adv_exists = 0 THEN
                			INSERT INTO mem_inspection_summary
                			( PROJECT_NUMBER, PROJECT_FULL_NAME, ITEM_TYPE, QUESTION, DISPLAY_ORDER, APPLIANCE_LSD,
                			DEFICIENCY, FIRST_SCAN, FIRST_SCAN_BY, CLIENT_COMPANY_ID, OWNER_COMPANY_ID, CURRENT_OWNER_COMPANY_ID,
                			CONSULTANT_COMPANY_ID, CAN_ACCESS_PORTAL )
                			VALUES
                			(	p_project, v_job_full_name, 'Advise', v_question, v_display_order, v_appliance_lsd,
                			v_adv, NOW(), p_user_name, v_client_id, v_owner_id, v_current_id, v_consultant_id, v_has_access );
                		ELSE 
                			UPDATE mem_inspection_summary SET DEFICIENCY = v_def, LATEST_RESCAN = NOW(), LATEST_RESCAN_BY = p_user_name,
                				CLIENT_COMPANY_ID = v_client_id, OWNER_COMPANY_ID = v_owner_id, CURRENT_OWNER_COMPANY_ID = v_current_id,
                			CONSULTANT_COMPANY_ID = v_consultant_id, CAN_ACCESS_PORTAL = v_has_access
                				WHERE PROJECT_NUMBER = p_project AND DISPLAY_ORDER = v_display_order;
                		END IF;
                		END LOOP get_advs_loop;
                	END IF;
                END get_advs;
                
                
                get_defs:BEGIN
                	## Are there any deficiencies in the lines?
                	SELECT COUNT(*) FROM project_lines
                		WHERE PROJECT_NUMBER = p_project AND LINE_STATUS = 'Deficient'
                			INTO v_defs_count;
                	SET v_defs_result = concat(v_defs_count,' Open Defs Found and');
                
                	IF v_defs_count > 0 THEN
                		SET flag_end_loop = 0;
                		OPEN crsr_defs;
                		get_defs_loop: LOOP
                		FETCH crsr_defs INTO v_section_id, v_question, v_display_order, v_def;
                		IF flag_end_loop = 1 THEN
                			SET flag_end_loop = 0;
                			LEAVE get_defs_loop;
                		END IF;
                		## if this deficiency is not already in the def_lines table, insert it
                		SELECT COUNT(*) FROM def_lines 
                			WHERE PROJECT_NUMBER = p_project AND DISPLAY_ORDER = v_display_order
                			INTO flag_def_exists;
                		IF flag_def_exists = 0 THEN
                			INSERT INTO def_lines
                			( PROJECT_NUMBER, PROJECT_FULL_NAME, ITEM_TYPE, QUESTION, DISPLAY_ORDER, APPLIANCE_LSD,
                			DEFICIENCY, FIRST_SCAN, FIRST_SCAN_BY, CLIENT_COMPANY_ID, OWNER_COMPANY_ID, CURRENT_OWNER_COMPANY_ID,
                			CONSULTANT_COMPANY_ID, CAN_ACCESS_PORTAL )
                			VALUES
                			(	p_project, v_job_full_name, 'Deficiency', v_question, v_display_order, v_appliance_lsd,
                			v_def, NOW(), p_user_name, v_client_id, v_owner_id, v_current_id, v_consultant_id, v_has_access );
                		ELSE 
                			UPDATE def_lines SET DEFICIENCY = v_def, LATEST_RESCAN = NOW(), LATEST_RESCAN_BY = p_user_name,
                				CLIENT_COMPANY_ID = v_client_id, OWNER_COMPANY_ID = v_owner_id, CURRENT_OWNER_COMPANY_ID = v_current_id,
                			CONSULTANT_COMPANY_ID = v_consultant_id, CAN_ACCESS_PORTAL = v_has_access
                				WHERE PROJECT_NUMBER = p_project AND DISPLAY_ORDER = v_display_order;
                		END IF;
                		END LOOP get_defs_loop;
                	END IF;
                END get_defs;
                
                get_recs: BEGIN
                	## Are there any recommendations in the lines
                	SELECT COUNT(*) FROM project_lines
                		WHERE PROJECT_NUMBER = p_project
                			AND (RECOMMENDATIONS != '' OR RECOMMENDATIONS_MORE != '')
                			AND LINE_STATUS = 'Recommend'
                			INTO 	v_recs_count;
                	SET v_recs_result = concat(v_recs_count,' Open Recs Found');
                	IF v_recs_count > 0 THEN
                	SET flag_end_loop = 0;
                		OPEN crsr_recs;
                		get_recs_loop: LOOP
                		FETCH crsr_recs INTO v_section_id, v_question, v_display_order, v_rec;
                		IF flag_end_loop = 1 THEN
                			SET flag_end_loop = 0;
                			LEAVE get_recs_loop;
                		END IF;
                		## if this recommendation is not already in the def_lines table, insert it
                		SELECT COUNT(*) FROM def_lines 
                			WHERE PROJECT_NUMBER = p_project AND DISPLAY_ORDER = v_display_order
                			INTO flag_def_exists;
                		IF flag_def_exists = 0 THEN
                			INSERT INTO def_lines
                			( PROJECT_NUMBER, PROJECT_FULL_NAME, ITEM_TYPE, QUESTION, DISPLAY_ORDER, APPLIANCE_LSD,
                			RECOMMENDATION, FIRST_SCAN, FIRST_SCAN_BY, CLIENT_COMPANY_ID, OWNER_COMPANY_ID, CURRENT_OWNER_COMPANY_ID )
                			VALUES
                			( p_project, v_job_full_name, 'Recommendation', v_question, v_display_order, v_appliance_lsd,
                			v_rec, NOW(), p_user_name, v_client_id, v_owner_id, v_current_id );
                		ELSE
                			UPDATE def_lines SET RECOMMENDATION = v_rec, LATEST_RESCAN = NOW(), LATEST_RESCAN_BY = p_user_name
                				WHERE PROJECT_NUMBER = p_project AND DISPLAY_ORDER = v_display_order;
                		END IF;
                		END LOOP get_recs_loop;
                	END IF;
                END get_recs;
                
                post_counts: BEGIN
                	UPDATE def_header SET DEF_COUNT = v_defs_count , REC_COUNT = v_recs_count
                		WHERE PROJECT_NUMBER = p_project;
                END post_counts;
                	
                SET p_result = concat(v_defs_result,' ',v_recs_result);
                SELECT p_result;
                
                END
                Pat Bremkamp
                MindKicks Consulting

                Comment


                  #53
                  Re: MySQL Query fails in loop

                  Joe,

                  Impressive project.
                  Here are some basic ideas/thoughts.

                  I would fix the blanks first. A blank city/state/zip could be verified against a city/state/zip table. This seems to be the type of rule most often applied, and looks like you want it done regardless of who the customer is. I don't know how you fix blank miles, but I would guess you need valid city/state/zip info first.
                  In theory, if you want to help out the especially busy customer, you could have defaults based on entire pickup and/or delivery info being blank.

                  Fixing things like blank pieces, weight, and truck type almost seem customer specific .

                  Fix items that are wrong. You showed a great example of this with Canton /New Philladelphia.

                  My mind is still spinning about the number of loops made for a single record, so I'm going to stop now, with the thought
                  that stepping through a customer's rules based on matched criteria is more efficient than looping through all of a customer's rules.
                  Gregg
                  https://paiza.io is a great site to test and share sql code

                  Comment


                    #54
                    Re: MySQL Query fails in loop

                    Pat, I have been reading up on stored procedures. yes it will require some learning on my part.

                    If I understand correctly, I can re-write most of my xbasic function processRulesForSelectedRows in a stored procedure. Then I would need to pass in just a few values, and the procedure would update the posting table.

                    It looks like a good solution.

                    Comment


                      #55
                      Re: MySQL Query fails in loop

                      I agree with Gregg there is so much you can do before you descend into writing your own code.

                      I had a similar problem with Spelling our solution was simple.



                      Test name Return name
                      New Philladelphia = New Philadelphia
                      New Philadelphia = New Philadelphia

                      So in SQL do a join on known spelling errors for zip etc.(Test name) and return Return name.
                      Once you have a half decent address Google tells you the driving distance between 2 cities, zips etc.


                      Second Impressive. Keep us post on how you get there.

                      Comment


                        #56
                        Re: MySQL Query fails in loop

                        Originally posted by madtowng View Post
                        Joe,

                        I would fix the blanks first. A blank city/state/zip could be verified against a city/state/zip table. This seems to be the type of rule most often applied, and looks like you want it done regardless of who the customer is. I don't know how you fix blank miles, but I would guess you need valid city/state/zip info first.
                        In theory, if you want to help out the especially busy customer, you could have defaults based on entire pickup and/or delivery info being blank.

                        Fixing things like blank pieces, weight, and truck type almost seem customer specific .

                        Fix items that are wrong. You showed a great example of this with Canton /New Philladelphia.

                        My mind is still spinning about the number of loops made for a single record, so I'm going to stop now, with the thought
                        that stepping through a customer's rules based on matched criteria is more efficient than looping through all of a customer's rules.
                        I have thought about breaking up the rule processing into several functions that execute sequentially. One for weight, one for length, etc. It seems like that is just a work-around that doesn't actually address the problem.

                        And yes, everything is customer-specific. Two different customers may use the exact same value in a field, but it means one thing for customer A, and another thing for customer B.

                        I actually have the whole process working for multiple customers. For now I have placed a limit on the number of rows that can be processed at a time to 30 rows. Lots of my customers lists are under 30 rows anyway. For lists greater than 30 rows, they just run the processing routine multiple times.

                        I think the stored procedure method is the way to go.

                        Comment

                        Working...
                        X