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

Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates & inserts

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

    #31
    Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates & inserts

    I'm sure that's not a problem. Although I'm not 100% sure I understand what you are asking. In my example above the script loops thru the dbf and assigns the arguments. When you say build the sql statement and an array, not sure what you are getting at? You can use arrays in Alpha, of course. I do have scripts that dynamically build sql statements from MySql tables, but it's probably in a slightly different context than you are contemplating. Can you clarify?

    BTW: I don't remember the hardware. I think we were using Server 2012 but it could have been 2008. I tried to logon to the site to check but they either cut my access or otherwise disconnected my local machine there.
    Peter
    AlphaBase Solutions, LLC

    [email protected]
    https://www.alphabasesolutions.com


    Comment


      #32
      Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates & inserts

      Originally posted by Peter.Greulich View Post
      Although I'm not 100% sure I understand what you are asking. In my example above the script loops thru the dbf and assigns the arguments. When you say build the sql statement and an array, not sure what you are getting at?
      Peter: In VFP, I often build both vertical AND horizontal loops to extract data from temporary tables. Let's assume that I've created a temporary table with 10 columns named FIELD001 through FIELD010 and each field is defined as 50 characters wide. Then I import a 10 column CSV file into that temporary table. (NOTE: Data types are irrelevant at this point.) The CSV import file has 100 rows of data.

      Next, I loop through the temporary table with a While loop (this is what I meant by a vertical loop as I'm going top down from the beginning of the file to the end of the file). On each record, I run a For Next loop that goes from 1 to 10 (this would be the horizontal loop. Inside this loop, I would extract the value from field and make any necessary data type conversions using a CASE statement, then assign the resulting value to a Argument Array. Once the For Next Loop completes, I would build the final SQL statement using the Argument Array to set the INSERT values in the SQL statement.

      This method abstracts the data field names and references them by their physical position in the file. In my experience with VFP it can shorten the code for an update like this significantly. Not sure if there would be a computational penalty on a really huge files like you've described, but I've used this technique extensively within my conversion/interface work that I do a lot of and it really aids in my RAD development because I am never really that concerned with creating meaningful field names for temporary import files.

      Let me know if you want further clarification and I'll post some sample VFP code that is currently in use with some of my applications. I'm just wondering if I could transfer said techniques from VFP to Alpha.

      Thanks!
      Paul H. Tarver
      Tarver Program Consultants, Inc.
      www.TPCQPC.com
      www.TPCDataWorks.com

      If you are doing today, what you were doing three years ago; you are backing up, not standing still.

      Comment


        #33
        Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates & inserts

        Originally posted by Peter.Greulich View Post
        Can you clarify?
        Peter: After my reply to your post where I provided a top level overview of what I was trying to accomplish, the concept continued to nag at me until I could get a couple of hours this morning to get my hands dirty. In doing so, I discovered that what I was describing could only work easily if there were a one to one relationship between the source column and the destination column, ie: CSV Column 1 is mapped to SQL Column 1, CSV Column 2 is mapped to SQL Column 2, and so on in order through all the columns. Once you start mixing the orders, the ability to have a Field loop within a Record Loop vanishes. (One possible way around this would be to store the mapping details in a table and then loop through the table matching up all source and destination fields regardless of their order within the files, but that was beyond the original scope of this project and frankly, the only reason I can see to take it that far would be if you were building some kind of import mapping that was user-interactive.)

        However, I WAS able to use a FOR NEXT loop to create the temporary import table and I was very satisfied with the results of that part of the code. While I was at it, I did some code optimization to minimize variables dimensioned and to make the script a little cleaner in my opinion. I also added a MOD() function to the loop to minimize the number of WAITDIALOG updates that are required during the processing of large batches of records. This can be tweaked to provide enough screen action to confirm something is taking place and still minimize the performance impact such updates have on the processing.

        The latest version of the script uses a Named Connection to a FirebirdSQL database and completely eliminates the field update process because in my project I realized that every time I run the script I just want to drop or truncate the table and re-populate it from scratch. Since FirebirdSQL doesn't support the TRUNCATE command, you'll see where we issue the "DELETE FROM MEMBERS" command which accomplishes the same task. And for those who are worried about the performance of the "DELETE FROM MEMBERS" command, don't! The command takes only a split second to complete even with tens of thousands of records to delete.

        This is probably not the final version of the script that came from the original thread question, but it is probably as good as I can make it with my still very limited knowledge. Perhaps someone else can suggest other improvements. However, I wanted to post this version so you could compare the original script I wrote (which accomplishes EXACTLY the same task as this latest version) and compare and contrast the two so you could see the evolution of this script to this latest stage. If, down the road, I come up with some radical improvement, I'll repost the script to this thread for everyone's review.

        Code:
        'Date Created: 29-Jun-2014 02:30:46 PM
        'Last Updated: 06-Jul-2014 10:25:54 AM
        'Created By  : Paul H. Tarver
        'Updated By  : Paul H. Tarver
        '*************************************
        'Declare & Dimension All Variables
        DIM lcTableName as C
        DIM lnLoopCnt as N
        DIM lnRecTot as N
        DIM lcImportFld as C
        DIM lcFieldDef as C
        DIM tmpData as P
        DIM llErrorFlag as L
        DIM loSQLConnection as SQL::Connection
        DIM lcSQLCmd as C
        DIM loArgs as SQL::Arguments
        DIM tmpPhone as C
        DIM loProgBar as waitdialog
        
        IF table.in_use("tmp_members")
        	table.close("tmp_members")
        end IF
        
        lcTableName = "C:\A5Projects\tmp_members.dbf"
        
        import.type = 0
        import.names = "IGNORE"
        import.file = "C:\A5Projects\BL_MEMB.CSV"
        import.db = lcTablename
        import.skip_lines = 0
        import.skip_bytes = 0
        import.field_sep = ","
        import.record_sep = "<CR><LF>"
        import.record_len = 0
        import.rem_blanks = .T.
        import.text_qualIFier = "\""
        import.fields = 22
        
        FOR lnLoopCnt = 1 to import.fields
        
        		lcImportFld = "import.field" + alltrim(str(lnLoopCnt,3,0))
        		lcFieldDef = "field" + padl(alltrim(str(lnLoopCnt,3,0)),3,"0") + ",C," + alltrim(str(lnLoopCnt-1,3,0)) + ",50,0"
        
        		eval(lcImportFld) = lcFieldDef
        		
        NEXT
        
        Table.import()
        
        tmpData = table.open(lcTableName)
        
        IF .NOT. loSQLConnection.open("::Name::FirebirdSQLData") THEN
        	 
        	ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
        	END 
        
        END IF
        
        lcSQLCmd = "select count(mbrs_id) as reccnt FROM members"
        
        IF .NOT. loSQLConnection.execute(lcSQLCmd) THEN 
        
        	ui_msg_box("SQL Error","Getting Record Count For Members Table Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
        	END 
        
        ELSE
        
        	loResultSet = loSQLConnection.ResultSet
        	
        	IF loResultSet.Data("reccnt") > 0
        	
        		lcSQLCmd = "DELETE FROM members"
        		
        		IF .NOT. loSQLConnection.execute(lcSQLCmd) THEN 
        			
        			ui_msg_box("SQL Error","Truncating Members Table Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
        			END 
        		
        		END IF
        	
        	END IF
        
        END IF
        
        loProgBar.create(3,"Percent")
        loProgBar.Set_Message(" Importing Member Address Details..." )
        loProgBar.AllowCancel(.t.)
        
        lcSQLCmd = "INSERT INTO members (mbrs_id,mbrs_fname,mbrs_mname,mbrs_lname,mbrs_suffix,mbrs_addr1," + \
                   "mbrs_addr2,mbrs_city,mbrs_state,mbrs_zipcode,mbrs_spouse) values " + \
                   "(:argRecNo,:argFName,:argMName,:argLName,:argSuffix,:argAddr1,:argAddr2,:argCity,:argState," + \
                   ":argZipCode,:argSpouse)"
        
        lnLoopCnt = 0
        lnRecCnt = tmpData.records_get()
        tmpData.fetch_first()
        
        while .NOT. tmpData.fetch_eof()
        
        	loArgs.add("argRecNo",tmpData.field001)
        	loArgs.add("argFName",word(tmpData.field002,2))
        	loArgs.add("argMName",word(tmpData.field002,3))
        	loArgs.add("argLName",word(tmpData.field002,1))
        	loArgs.add("argSuffix",alltrim(tmpData.field003))
        	loArgs.add("argAddr1",alltrim(tmpData.field004))
        	loArgs.add("argAddr2",alltrim(tmpData.field005))
        	loArgs.add("argCity",alltrim(tmpData.field006))
        	loArgs.add("argState",alltrim(tmpData.field007))
        	loArgs.add("argZipCode",alltrim(tmpData.field008))
        	loArgs.add("argSpouse",alltrim(tmpData.field010))
        
        	IF .NOT. loSQLConnection.execute(lcSQLCmd,loArgs) THEN 
        		
        		ui_msg_box("Error","Member Insert Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
        		END 
        
        	ELSE
        
        	    IF mod(lnLoopCnt,5) = 0
        
        		    loProgBar.Set_Percent(lnLoopCnt,lnRecCnt)
        		    loProgBar.Set_Bottom_Message("Updating: [" + alltrim(tmpData.field001) + "] - " + alltrim(tmpData.field002) + 	
        Crlf() + "Record " + alltrim(str(lnLoopCnt,8,0)) + " of " + alltrim(str(lnRecCnt,8,0)) )
        	
        		    IF loProgBar.User_Canceled() THEN
        		
        				IF ui_msg_box("Cancel Procedure....","Are you sure you wish to cancel?",UI_YES_NO) = UI_YES_SELECTED THEN
        				    
        			    	EXIT WHILE  
        		
        				ELSE
        				
        					loProgBar.Cancel_Cancel()
        					 		
        		 		END IF
        		
        			END IF
        		
        		END IF
        
            END IF
        
            lnLoopCnt = lnLoopCnt + 1
            tmpData.fetch_next()
        
        END WHILE
        
        tmpData.close()
        table.erase(lcTableName, .T.)
        loProgBar.Set_Percent(lnRecCnt,lnRecCnt)
        
        END
        Last edited by ptarver; 07-06-2014, 02:38 PM. Reason: Moved lnLoopCnt & fextch_next() to just before END WHILE
        Paul H. Tarver
        Tarver Program Consultants, Inc.
        www.TPCQPC.com
        www.TPCDataWorks.com

        If you are doing today, what you were doing three years ago; you are backing up, not standing still.

        Comment


          #34
          Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates &amp; inserts

          does Firebird reclaim the space after all the records are deleted? Otherwise why not just drop the table and recreate it and you'll get an empty clean table?
          Al Buchholz
          Bookwood Systems, LTD
          Weekly QReportBuilder Webinars Thursday 1 pm CST

          Occam's Razor - KISS
          Normalize till it hurts - De-normalize till it works.
          Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
          When we triage a problem it is much easier to read sample systems than to read a mind.
          "Make it as simple as possible, but not simpler."
          Albert Einstein

          http://www.iadn.com/images/media/iadn_member.png

          Comment


            #35
            Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates &amp; inserts

            Originally posted by Al Buchholz View Post
            does Firebird reclaim the space after all the records are deleted? Otherwise why not just drop the table and recreate it and you'll get an empty clean table?
            According to the Firebird FAQ: "Many users wonder why they don't get their disk space back when they delete a lot of records from database. The reason is that it is an expensive operation, it would require a lot of disk writes and memory - just like doing de-fragmentation of a hard disk partition. The parts of database (pages) that were used by such data are marked as empty and Firebird will reuse them next time it needs to write new data."

            Therefore, records marked for deletion are reused as new data is written to a table. Additionally, you can do a garbage collection procedure during a backup to physically remove all deleted records at any time. My plan was to get all the client data imported and then do a clean up on the data just prior to going live to make sure the table is in pristine condition with all the client data loaded and ready for the client to use.

            FYI: It seems like I've deleted and imported a hundred times (not really, but it feels like it) and the database file is growing very, very slowly. For example, I have been working with two import CSV files totaling about about 15 megabytes in size and after all those imports, deletions and re-imports my current Firebird database is only 10.5 megabytes! Varchars and Integers are really helpful in keeping table sizes small!

            I chose to use the DELETE FROM command rather than the DROP TABLE because I have dependencies built into the table at the database level. First, although I'm importing the original Member ID, this field is configured as a Auto-Incrementing field with a trigger that kicks in if the Member ID imported is either NULL or Zero. Secondly, I plan to use VIEWS at the database level in the final application and I have several views already created that reference the Member table.

            It seems to me that will you can certainly build views & browses in Alpha Five and I'm sure I'll have many of those, there are some views that there may be a performance benefit from storing and running the view at the database level. This seems to me that this will be even more important as I move into Web Applications.

            However, this an assumption on my part that there is a performance difference between views written at the database level vs views written at the Alpha level and I'm certainly open to learn more about this from anyone who has experience using both methods.
            Paul H. Tarver
            Tarver Program Consultants, Inc.
            www.TPCQPC.com
            www.TPCDataWorks.com

            If you are doing today, what you were doing three years ago; you are backing up, not standing still.

            Comment


              #36
              Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates &amp; inserts

              Could you please put a sample for TRUNCATE , in order to delete data in a dbf table. I could not find any .
              kinds

              Comment


                #37
                Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates &amp; inserts

                its like this:

                dim tbl as p
                tbl = table.open("mytable",FILE_RW_EXCLUSIVE)
                tbl.zap(.t.)

                Although my DBF memory is pretty good, check the documentation for "<tbl>.zap" and any error trapping.
                Steve Wood
                See my profile on IADN

                Comment

                Working...
                X