Alpha Video Training
Page 2 of 2 FirstFirst 12
Results 31 to 37 of 37

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

  1. #31
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,644

    Default 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.

  2. #32

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

    Quote 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.

  3. #33

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

    Quote 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 at 03: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.

  4. #34
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates & 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.

  5. #35

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

    Quote 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.

  6. #36

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

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

  7. #37
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,842

    Default Re: Use Xbasic To Import to DBF then loop through DBF to apply conditional SQL updates & 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
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

Similar Threads

  1. _help.dbf is not a help file after last two updates
    By kwparker80 in forum Alpha Five Version 10 - Desktop Applications
    Replies: 4
    Last Post: 08-07-2010, 02:30 AM
  2. SQL & dbf's
    By StevenMcLean in forum Alpha Five Version 9 - Desktop Applications
    Replies: 2
    Last Post: 02-25-2010, 11:19 AM
  3. FIELD RULES in Alpha DBF's & SQL Tables
    By 411tech in forum Application Server Version 9 - Web/Browser Applications
    Replies: 8
    Last Post: 06-30-2009, 05:24 PM
  4. A_CASE.DBF & A_GLOSS.DBF
    By Richard Froncek in forum Alpha Five Version 5
    Replies: 2
    Last Post: 09-14-2002, 01:29 PM
  5. A_CASE.DBF & A_GLOSS.DBF
    By Sigrist in forum Alpha Five Version 4
    Replies: 4
    Last Post: 09-14-2001, 09:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •