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

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

    Ok, I'll admit it right up from, I'm new to this Xbasic stuff but as a 20 year veteran of Foxpro programming, I'm trying really hard to wrap my head around how things are done in Alpha Anywhere. There is something I do a LOT of that I need to verify I can do in Alpha Anywhere. Basically, I am trying to write an Xbasic script that will import a CSV file to a temporary cursor or DBF file, then open the DBF file and loop through the records in the DBF file, use a Select Statement to determine whether a record exists in a SQL database and then depending upon whether a record exists, update the existing SQL data or create an insert statement to add any new records.

    I'm sure that the folks who use Alpha everyday think this is probably a dumb question, but after four days of watching videos and trying out code from tutorials I'm about as frustrated as it gets trying to figure out how to do something that I can do in five minutes in VFP. Here's the psuedo code in VFP:

    --------------------------------------------------------
    lnSQLConnection = (SQL CONNECTION MADE)

    Create cursor "tmpData" (rec_no I NOT NULL, name C(40) NOT NULL, address C(40) NOT NULL, city C(20) NOT NULL, state c(2) NOT NULL, zip c(10) NOT NULL)

    select tmpData
    append from "memberdata.csv" type csv
    Goto top in tmpData

    scan

    lcSQLCmd = "Select * from Members where members.rec_no = ' + transform(tmpData.rec_no)
    SQLEXEC(lnSQLConnection, lcSQLCmd, "tmpResultSet")

    if reccount('tmpResultSet') > 0

    lcUpdateCmd = "update members set name = '" + tmpData.Name "' where members.rec_no = " + transform(tmpData.rec_no)
    SQLEXEC(lnSQLConnection, lcUpdateCmd)

    else

    lcInsertCmd = "insert into members (rec_no, name) values (" + transform(tmpData.rec_no) + ",'" + tmpData.Name + "')"
    SQLEXEC(lnSQLConnection, lcInsertCmd)

    endif

    endscan

    release lnSQLConnection
    --------------------------------------------------------

    I have frankensteined a script that I can post later (not at that particular machine right now) that kinda works up to the point where I'm trying to loop through the temporary dbf and issue the SQL commands against the database where I'm getting an error about AA not being able to give me a result set while another result set is in progress. I've tried to test each line via the interactive window but no luck so far.

    I should tell you that the SQL I'm trying to work with is FirebirdSQL using the AlphaDAO ODBC connection. (I've used Firebird in the past with other projects and I've been pleased with the results, but I'm not married to it. I just couldn't decide whether to use MSSQL, MySQL or PostgreSQL and so I picked the one with which I was more familiar.) If the problems I'm experiencing are tied to using the ODBC type connection to Firebird rather than a native AlphaDAO connection string, I'm willing to swap. Unfortunately, I'm so new at the Alpha logic and the Xbasic language that I can't tell if it is my ignorance or a technical issue.

    If someone can provide a translation of the code I have included above to the XBasic language I think it would really help me out a lot. This shouldn't be that hard to do, but for some reason, I'm banging my head against the wall to figure this out.

    Thanks In Advance!
    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.

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

    Two ideas for you:

    1) Check the help file discussion and example on the While ... End While Code Block.

    2) Learn a bit more about xbasic by working through the puzzles in the Xbasic Programming Puzzles forum.

    Comment


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

      Also, look at the Append options to add unique, replace existing etc.
      See our Hybrid Option here;
      https://hybridapps.example-software.com/


      Apologies to anyone I haven't managed to upset yet.
      You are held in a queue and I will get to you soon.

      Comment


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

        why not upload the file and use a server process to import the data?
        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


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

          You can skip the DBF step, not necessary. On the other hand, my example below might seem more complex. Basically I am importing a csv file and storing the entire file in a Property Array using <array>.initialize_properties(). Then I can loop through the array and Insert records from to the SQL table. You can also use the array elements to selectively Insert, like this:

          for x = 1 to arr.size()
          if arr[x].MyValue = "THIS"
          your INSERT statement
          else
          ' comment - do not insert
          end if
          next
          Example import, Insert. Starting from a button on a UX component. You would need to review the Help on <array>.initialize_properties(). This example also uses arguments (e.g., :vcalldatetime).
          function impCallDetail2 as c (e as p)

          dim cn as sql::connection
          dim args as sql::arguments
          cn.open("::name::conn")
          fname = "c:\myfile.csv"

          fmt = "callDateTime,SiteID,'BillingID','DialedNumber','Location','callMinutes',callCost"

          dim impb as c
          impb = file.to_string(fname)
          dim arr[0] as p
          arr.initialize_properties(fmt,impb)

          vSelect = "TRUNCATE table cb_calldetail_imp"
          cn.execute(vSelect)

          for x = 1 to arr.size()
          vSelect = <<%longstring%
          INSERT INTO cb_calldetail_imp
          (callDateTime,BillingID,DialedNumber,Location,callMinutes,callCost)
          VALUES
          (
          :vcalldatetime,
          :vbillingid,
          :vdialednumber,
          :vlocation,
          :vcallminutes,
          :vcallcost
          )
          %longstring%
          vSelect = evaluate_string(vSelect)
          args.add("vcalldatetime",left(arr[x].callDateTime,19))
          args.add("vbillingid",arr[x].BillingID)
          args.add("vdialednumber",arr[x].DialedNumber)
          args.add("vlocation",arr[x].Location)
          args.add("vcallminutes",arr[x].callMinutes)
          args.add("vcallcost",arr[x].callCost)
          if cn.execute(vSelect,args) = .f.
          errorout(cn.callresult.text)
          end if

          next

          end function
          Steve Wood
          See my profile on IADN

          Comment


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

            Ok, first, I want to thank everyone for their suggestions and I'd frankly like this conversation to continue because as a longtime VFP programmer, making the mental transition from VFP to XBasic is not as obvious as it would seem. However, after spending a few more hours on my original code and killing a lot of bugs , I got the initial pieces in place and it worked! I was extremely excited to say the least as it was my first official bit of coding in Alpha Anywhere! I know it sounds pretty dumb to those who have been programming for ages with Alpha, but it's a big deal because with the success of this bit of code, I've proven that I can do what I need to do with Alpha, I built some confidence in my ability to take a great number of bits and pieces and wrangle them into something that will do the task at hand. I still have a long, long way to go and I have a LOT left to learn, but I wanted to share the code I came up with for other VFP programmers who are evaluating Alpha and frankly to hear your suggestions about how to improve what I've done.

            First some caveats:

            1) This Alpha XBasic Code performs exactly the same work described by VFP pseudo code I posted initially.

            2) There are probably a lot of ways to optimize the code I have posted. In fact, I want to review Steve Wood's suggestion to use an array, but I'm am curious about the limitations of arrays as sometimes, I have tables with hundreds of thousands of records that need processing and I'm assuming that there are some memory limitations with arrays.

            3) I can see several places where I need to so some additional error checking.

            4) I'd like to investigate how to loop through arguments and ResultSet data so that instead of hard coding the multiple lines of codes that I used to perform field by field updates. Some suggestions in this area would be appreciated as I often use For Loops to perform multiple column processes in VFP. I'm sure there is a way, but I was more focused here on proof of concept, so I was a little more verbose in my coding than I normally am.

            5) One thing that you don't see here is that I also created an Operation to perform the import to a temporary table, but I like the fact that the complete process is included from start to finish in one script (per the original post) and I like the face that my process is fully documented with all the code.

            6) I based all of this code on a Named Connection to a PostgreSQL data running the same machine as AA and the performance was very good considering I was doing batch inserts and updates. It takes less than 1 minute to process a file with almost 500 records in it. The full name and address file that I'll be actually using has some 7,000 records in it so by my estimation the full import/update process should take less than 15 minutes start to finish. I can live with that because the AA Desktop Application we are building is a migration from a DOS system and the first step is to build a method to migrate the client's data to our new PostgreSQL database. As we work on the database design, we'll modify the import scripts to keep up with the new structures so that when we are ready to make the final cut over for the client, we can just export the data from the old system and import it into the new system fully formed and operational.

            7) I have one issue that I have not resolved yet that perhaps someone can help me with. If I insert a new record inside of pgAdminIII, blank fields are populated with as a blank, NON NULL value. However, when I run the update/insert process, blank fields are updated with '' in the fields. I've tried to figure out where that is coming from and the only thing I can figure is that by using :Args, Xbasic is adding the necessary '' to surround the empty values and that is what is getting stored. But any help here would be appreciated.

            8) Note to VFP programmers: I tried to use the same naming convention where ever possible to help you understand whether a variable is Character, Object, Numeric, etc. For example, the 'lo' in 'loSQLConnection' indicates that the variable is a local object variable.

            9) Lastly, I'd like to make the progress bar more of an informational dialog so I can quit using the trace window. I'll be reviewing WAITDIALOG object or something else if anyone has any suggestions.

            Well, without dragging this out any longer, here's the Xbasic code that performs the same task as the VFP Pseudo-Code I originally posted. Thanks again to those who have responded so far and in advance for any suggestions to improve what I've come up with here.

            Code:
            DIM loSQLConnection as SQL::Connection
            DIM lcSQLCmd as C
            DIM loArgs as SQL::Arguments
            DIM loResultSet as sql::ResultSet
            DIM llSQLUpdateFlag as L 
            DIM lcTableName as C
            DIM tmpData as P
            DIM lnLoopCnt as N
            DIM lnRecTot as N
            DIM llErrorFlag as L
            
            lcTableName = "C:\A5Projects\tmp_members.dbf"
            
            import.type = 0
            import.names = "IGNORE"
            import.file = "C:\A5Projects\tmp_dataimport.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 = 10
            import.field1 = "rec_no,N,0,10,0"
            import.field2 = "fname,c,1,30,0"
            import.field3 = "mname,c,2,30,0"
            import.field4 = "lname,c,3,30,0"
            import.field5 = "suffix,c,4,10,0"
            import.field6 = "addr1,c,5,40,0"
            import.field7 = "addr2,c,6,40,0"
            import.field8 = "city,c,7,30,0"
            import.field9 = "state,c,8,2,0"
            import.field10 = "zipcode,c,9,10,0"
            Table.import()
            
            tmpData = table.open(lcTableName)
            
            llErrorFlag = loSQLConnection.open("::Name::PostgreSQLData")
            
            if llErrorFlag = .f. then
            	ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            	end 
            end if
            
            a5.command("VIEW_TRACE")' opens the Trace window
            lnLoopCnt = 0
            lnRecCnt = tmpData.records_get()
            tmpData.fetch_first()
            
            while .NOT. tmpData.fetch_eof()
            
            	loArgs.add("WhatRecNo",tmpData.rec_no)
            	loArgs.add("argFName",alltrim(tmpData.fname))
            	loArgs.add("argMName",alltrim(tmpData.mname))
            	loArgs.add("argLName",alltrim(tmpData.lname))
            	loArgs.add("argSuffix",alltrim(tmpData.suffix))
            	loArgs.add("argAddr1",alltrim(tmpData.addr1))
            	loArgs.add("argAddr2",alltrim(tmpData.addr2))
            	loArgs.add("argCity",alltrim(tmpData.city))
            	loArgs.add("argState",alltrim(tmpData.state))
            	loArgs.add("argZipCode",alltrim(tmpData.zipcode))
            
            	lcSQLCmd = "SELECT * FROM members WHERE mbrs_del_flag <> 'Y' AND mbrs_recno = :WhatRecNo"
            	llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            
            	if llErrorFlag = .f. then 
            		ui_msg_box("Error","Query Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            		end 
            	end if
            	
                loResultSet = loSQLConnection.ResultSet
            	llSQLUpdateFlag = loResultSet.NextRow()
            	
            	IF llSQLUpdateFlag = .t. THEN
            		
            	    trace.writeln("Update ->" + alltrim(tmpData.lname) + ", " + tmpData.fname + " [" + str(tmpData.rec_no,10,0) + "]")
            		
            		IF .NOT. strequal(tmpData.fname,loResultSet.data("mbrs_fname"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_fname = :argFName where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","FNAME Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            		
            		END IF
            			
            		IF .NOT. strequal(tmpData.mname,loResultSet.data("mbrs_mname"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_mname = :argMName where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","MName Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            		
            		END IF
            
            		IF .NOT. strequal(tmpData.lname,loResultSet.data("mbrs_lname"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_lname = :argLName where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","LName Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            		
            		END IF
            
            		IF .NOT. strequal(tmpData.suffix,loResultSet.data("mbrs_suffix"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_suffix = :argSuffix where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","Suffix Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            		
            		END IF
            
            		IF .NOT. strequal(tmpData.ADDR1,loResultSet.data("mbrs_address1"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_address1 = :argAddr1 where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","Addr1 Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            		
            		END IF
            
            		IF .NOT. strequal(tmpData.ADDR2,loResultSet.data("mbrs_address2"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_address2 = :argAddr2 where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","Addr2 Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            			
            		END IF
            
            		IF .NOT. strequal(tmpData.CITY,loResultSet.data("mbrs_city"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_city = :argCity where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","City Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            		
            		END IF
            
            		IF .NOT. strequal(tmpData.STATE,loResultSet.data("mbrs_state"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_state = :argState where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","State Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            		
            		END IF
            
            		IF .NOT. strequal(tmpData.ZIPCODE,loResultSet.data("mbrs_zipcode"),.f.,.T.)
            
            			lcSQLCmd = 	"UPDATE members SET mbrs_zipcode = :argZipCode where mbrs_recno = :WhatRecNo"
            			llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            		
            			if llErrorFlag = .f. then 
            				ui_msg_box("Error","ZipCode Update Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            				end 
            			END IF
            		
            		END IF
            
            	ELSE
            
            	    trace.writeln("Insert ->" + alltrim(tmpData.lname) + ", " + tmpData.fname + " [" + str(tmpData.rec_no,10,0) + "]")
            
            		lcSQLCmd = "INSERT INTO members (mbrs_recno,mbrs_fname,mbrs_mname,mbrs_lname,mbrs_suffix,mbrs_address1," + \
            		           "mbrs_address2,mbrs_city,mbrs_state,mbrs_zipcode) values " + \
            		           "(:WhatRecNo,:argFName,:argMName,:argLName,:argSuffix,:argAddr1,:argAddr2,:argCity,:argState,:argZipCode)"
            		llErrorFlag = loSQLConnection.execute(lcSQLCmd,loArgs)
            	
            		IF llErrorFlag = .f. then 
            			ui_msg_box("Error","Insert Failed. Error reported was: " + crlf() + loSQLConnection.CallResult.text)
            			end 
            		END IF
            
            	END IF
            	
                tmpData.fetch_next()
            	lnLoopCnt = lnLoopCnt + 1
            	progresspercent(lnLoopCnt/lnRecCnt*100)
            	
            END WHILE
            
            tmpData.close()
            table.erase(lcTableName, .T.)
            progresspercent(100)
            Last edited by ptarver; 06-29-2014, 06:36 PM. Reason: Updated name of Named Connection
            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


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

              Originally posted by Tom Cone Jr View Post
              1) Check the help file discussion and example on the While ... End While Code Block.
              Tom,

              Thanks for the suggestion, but it turned out that the biggest problem I was running into was in not passing the Args properly the SQL.execute() line. The error message I was getting was that I couldn't get a ResultSet while another ResultSet was active, but the fact of the matter was that while I was setting the Arg values, I didn't include the args on the SQL.execute function.

              Paul
              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


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

                Originally posted by Ted Giles View Post
                Also, look at the Append options to add unique, replace existing etc.
                Ted: It seems to me that the Append options are specifically related to updating tables and I think would work perfectly if I were to try to update an Active Link table to post my updates to SQL. However, I read somewhere that the updating SQL tables via Active Link tables is much slower than doing direct SQL updates. So in my experiment, the goal was to import the CSV file to a temporary DBF and then loop through the DBF table to perform direct SQL updates/inserts. Others can chime in and tell me if my understanding of the performance issues is correct.

                My next performance test will be to run the same updates against a duplicate database I've setup in FirebirdSQL to see if there is any major difference between PostgreSQL and FirebirdSQL. Once I get some benchmarks, I'll post an update.

                Thanks!
                Paul
                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


                  #9
                  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
                  why not upload the file and use a server process to import the data?
                  Al: Can you describe this process a little more so I can check into it? If I understand you correctly, you are suggesting that we upload the CSV directly (to a temporary table?) to the SQL Engine (PostgreSQL in my case) and then use that data to update the live tables?

                  Thanks!
                  Paul
                  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


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

                    No limit on array size other than machine memory. Its rough to see DBF-oriented code and SQL-oriented code in the same script. The two methods are so different in approach and syntax. Because of that I think most of use adopt one or the other and do not mingle. You can do everything you want with only SQL.

                    A browser app is going to ignore your ui_msg_box() function; that is a desktop GUI only feature. I use save_to_file() to pump the error msg to a file, then display at the end if there were any errors.
                    Steve Wood
                    See my profile on IADN

                    Comment


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

                      Originally posted by Steve Wood View Post
                      No limit on array size other than machine memory. Its rough to see DBF-oriented code and SQL-oriented code in the same script. The two methods are so different in approach and syntax. Because of that I think most of use adopt one or the other and do not mingle. You can do everything you want with only SQL.

                      A browser app is going to ignore your ui_msg_box() function; that is a desktop GUI only feature. I use save_to_file() to pump the error msg to a file, then display at the end if there were any errors.
                      Steve, I understand your concern about mingling, but truthful, this exercise is simply going to used to migrate a customer's btrieve data to an SQL database and if we are lucky it will only be used once for this client. I would perhaps build something different for another client that was migrating (perhaps after I learn more!), but the challenge right now is building the new database structure and populate it with live data as quickly as possible. Secondarily, I'm not worried about using Desktop only UI elements because our initial migration for this client is from a DOS-based networked application to a Windows-based network application. Long term, they want to add a web-based front-end to allow remote users to update the database with the bulk of their intranet work remaining on the desktop application. Maybe its not what is best, but it is what their budget and timeline will support.
                      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


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

                        Earlier, I promised to give some benchmarks comparing FirebirdSQL and PostgreSQL databases. Using the exact same code published above and changing only from the native AlphaDAO PostgreSQL named connection and an AlphaDAO ODBC named connection to FirebirdSQL, I ran three tests on both databases and came up with the following results:

                        Code:
                        498 Records Processed	                  PostgreSQL	FirebirdSQL
                        Empty Selects & All Inserts	          01:17.3	00:25.8
                        All Selects Tested; No Updates       	  01:42.6	00:55.2
                        39 Inserts & 20 Field Updates	          01:39.5	01:04.9
                        FirebirdSQL is the clear winner in this small, unscientific performance test. I'm sure the difference is directly related to PostgreSQL's ACID compliance, but the most interesting result to me was the second test where all we did was a single select statement to test all fields for any required updates. No changes were written to the table, so what we are seeing is the pure time it takes to select 498 records one at a time and then comparing each field within our temporary table to the SQL ResultSet and then moving on to the next one. Amazingly, PostgresSQL was still 47 seconds slower than FirebirdSQL. So all we were performing were reads and no writes. FirebirdSQL slows down a bit when we do the mix of 39 inserts and 20 field updates but only about 10 seconds, but PostgreSQL actually ran slightly faster with the inserts and updates. When performing all inserts, it wasn't even a race as FirebirdSQL was almost 3 times as fast.

                        Not sure if anyone cares about this and I'm sure that your mileage will vary. But, if you are posting large quantities of data to your SQL database, FirebirdSQL is a really night choice for great performance.
                        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


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

                          <<concern about mingling, but truthful, this exercise is simply going to used to migrate a customer's btrieve data to an SQL database and if we are lucky it will only be used once for this client.>>

                          Got it, you are right, I was thinking more about as an ongoing practice. Please be sure to join (free) at www.iadn.com for additional resources.
                          Steve Wood
                          See my profile on IADN

                          Comment


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

                            Paul, I'm delighted you've made the progress reported thus far.

                            I'd encourage you to consider posting separate questions in new message board threads. Sometimes folks want to use a single thread as a sort of informal blog. When that happens the material in the thread becomes less accessible to others who may be searching for similar issues later on. The board has become a sort of knowledgebase, and putting separate issues in separate threads helps people find solutions more quickly.

                            Congratulations on your progress, and thanks for letting us know how things turned out.

                            -- tom

                            Comment


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

                              Originally posted by ptarver View Post
                              Al: Can you describe this process a little more so I can check into it? If I understand you correctly, you are suggesting that we upload the CSV directly (to a temporary table?) to the SQL Engine (PostgreSQL in my case) and then use that data to update the live tables?

                              Thanks!
                              Paul
                              I googled PostgreSQL Import csv and found the copy command.

                              sounds pretty straight forward.
                              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

                              Working...
                              X