Announcement

Collapse
No announcement yet.

Example of select and update needed

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

  • Example of select and update needed

    I'm working on a data scrubbing program where each field has a set of rules for masking the data. Ex. Leave the first two letters of each word alone and set alpha to 'z' and numeric to 9 unless it's a key word. Leave key words as is and then update the record.

    Not being a guru on creating SELECT and UPDATE statements that work together I ended up creating a rowset with a single SELECT statement. Using SQL_Fetch(%NEXT_ROW) I looped through each record and extracted a field to mask into a string variable. I then created an sql statement to UPDATE that row using a 'WHERE' clause and the unique id. I put all the UPDATE sql statements in a string array until I had exhausted the recordset created by the SELECT statement.

    I then looped through the array of prepped (UPDATE) sql statements and executed each one.

    Worked fine but I was wondering if it could be done using 2 sql statement numbers against the same database as I'm fetching each one from the original SELECT statement.

    Code:
                    strSQL$ = "SELECT COUNT(*) FROM DNUCLEUS"
                    lresult& = SQL_Stmt(%SQL_STMT_IMMEDIATE,strSQL$)
                    Fetch_Ok& = SQL_Fetch(%NEXT_ROW)
                    Tot_Rows& = SQL_ResColSInt(1)
                    DIM PEND_UPDATE$(SQL_ResColSInt(1))                  
                    strSQL$ = "SELECT CODE,REFERENCE FROM DNUCLEUS ORDER BY CODE"
                    lresult& = SQL_Stmt(%SQL_STMT_IMMEDIATE,strSQL$)
                    Fetch_Ok& = SQL_Fetch(%NEXT_ROW)
                    CONTROL SET TEXT CBHNDL,%LBL_SQLTABLE," Creating Update Statements "
                    WHILE Fetch_Ok& = 0
                      T$ = SQL_ResColStr(2)
                      strSQL$ = "UPDATE DNUCLEUS SET REFERENCE='" + TRIM$(MASKING(T$,2)) + "' WHERE CODE = '" + SQL_ResColStr(1) +"'"
                      'masking function not shown
                      INCR p_u&
                      PEND_UPDATE$(p_u&) = strSQL$          
                      Fetch_Ok& = SQL_Fetch(%NEXT_ROW)                 
                      IF (p_u& MOD 100) = 0 THEN
                        lPos& = lPos& + 5
                        IF lPos& > 100 THEN
                          lPos& = 5
                        END IF
                        ShowProgress(BYVAL CBHNDL,BYVAL %PRB_MSCTLS_PROGRESS32_1,lPos&)
                      END IF 
                    WEND
                    CONTROL SET TEXT CBHNDL,%LBL_SQLTABLE," Executing Update Statements "
                    FOR xp_u& = 1 to p_u&
                      strSQL$ = PEND_UPDATE$(xp_u&)
                      lresult& = SQL_Stmt(%SQL_STMT_IMMEDIATE,strSQL$)
                      IF (xp_u& MOD 100) = 0 THEN
                        lPos& = lPos& + 5
                        IF lPos& > 100 THEN
                          lPos& = 5
                        END IF
                        ShowProgress(BYVAL CBHNDL,BYVAL %PRB_MSCTLS_PROGRESS32_1,lPos&)
                      END IF 
                    NEXT                         
                    ShowProgress(BYVAL CBHNDL,BYVAL %PRB_MSCTLS_PROGRESS32_1,100)
    At the place in the code where I put the strSQL$ for the UPDATE statement into the array element I did execute using the long syntax this statement with statement number 2. The outer loop all used statement number 1. The fetching in the outer loop lost track of where it was and only updated one record.

    My question is whether you can update the record you are on while looping through a record set fetching one record at a time? Should I have created a different connection to the database for this approach instead of just a second statement in the first database connection?

    Bob Mechler
    Last edited by BOB MECHLER; 18 Apr 2009, 05:36 PM. Reason: More questions

  • #2
    Bob

    The 2 ways are both valid.
    - 1: Execute a SELECT statement to build a recordset. Loop through the recordset, create an UPDATE statement for each record and store in an array. When initial loop finished, loop through the array and execute each UPDATE statement.
    - 2: Execute a SELECT statement to build a recordset. Loop through the recordset, create an UPDATE statement for each record, execute the UPDATE statement; then move to the next record.

    I would favour the second method as it avoids the extra step of creating an array.

    In case 2, there is no need to create two database connections, but you would need to use two statement numbers: one for the SELECT statement; and one for each UPDATE statement.

    However, in both cases, when the UPDATE statement is called, I would test that it was successful. If yes, move on to the next statement/record. If not, then decide what to do

    A suggestion: place the UPDATE statement in its own loop, and if it fails, pause and repeat (maybe 2 or 3 times). If it still fails, flag this and move onto the next statement/record. There may be errors in the data, or there could be timing issues writing the record to the hard disk.

    When I have done similar exercises, I add a temporary column to the database table and put some data in it if the UPDATE fails. That makes it easier to search on or filter the appropriate records for further action.

    Christopher

    Comment


    • #3
      I just did exactly this, but I used the OLE interface (with ADO) instead of the ODBC interface to create an updatable RecordSet. I don't have a complete demo of this set up, but if you are willing to use OLE+ADO instead of ODBC in your program for this I will find and post the salient portion of the code for you here.

      I never even tried this via ODBC.
      Michael Mattias
      Tal Systems (retired)
      Port Washington WI USA
      [email protected]
      http://www.talsystems.com

      Comment


      • #4
        Thanks for replying.

        Using two statements should work, one to create a record set and another to do an update to each record in the data set while using SQL_FetchResult(1,1,%NEXT_ROW).

        It could have simply been a typo or something.

        The idea of a temp field added before the operation I'm guessing should put something in that field which would be changed if the update was successful. Maybe set the field to null. Then review the dataset and if there are no not null temp fields on any record, drop the column. If there are, it would be like a second pass to make sure they were all updated.

        Bob Mechler

        Comment


        • #5
          Updating while looping through a recordset

          I couldn't get two statements to work but using two db connections and the following code worked fine. I time tested both the array version and this version with identical times.



          Code:
          IF CBCTLMSG = %BN_CLICKED OR CBCTLMSG = 1 THEN 
            SQL_result& = SQL_OpenDatabase(1,"DSN=" + DSN$,%PROMPT_TYPE_NOPROMPT)
            IF SQL_Errorpending THEN
              lresult& = Sql_ErrorNumber
              IF lresult& <> %SUCCESS_WITH_INFO THEN
                SQL_MsgBox SQL_ErrorQuickOne + " Opening Database 1 ", MSGBOX_OK
                EXIT FUNCTION
              END IF
            ELSE  
              lresult& = Sql_ErrorClearOne
            END IF
            SQL_result& = SQL_OpenDatabase(2,"DSN=" + DSN$,%PROMPT_TYPE_NOPROMPT)
            IF SQL_Errorpending THEN
              lresult& = Sql_ErrorNumber
              IF lresult& <> %SUCCESS_WITH_INFO THEN
                SQL_MsgBox SQL_ErrorQuickOne + " Opening Database 2 ", MSGBOX_OK
                EXIT FUNCTION
              END IF
            ELSE  
              lresult& = Sql_ErrorClearOne
            END IF
            CONTROL SET TEXT CBHNDL,%LBL_SQLTABLE," Successfully opened " + DSN$
            'all masking is done in this section updating %LBL
            strSQL$ = "SELECT COUNT(*) FROM DNUCLEUS"
            lresult& = SQL_Statement(1,1,%SQL_STMT_IMMEDIATE,strSQL$)
            IF SQL_Errorpending THEN
              lresult& = Sql_ErrorNumber
              IF lresult& <> %SUCCESS_WITH_INFO THEN
                SQL_MsgBox SQL_ErrorQuickOne + " Get count ", MSGBOX_OK
                EXIT FUNCTION
              END IF
            ELSE  
              lresult& = Sql_ErrorClearOne
            END IF
            Fetch1_Ok& = SQL_FetchResult(1,1,%NEXT_ROW)
            IF SQL_Errorpending THEN
              lresult& = Sql_ErrorNumber
              IF lresult& <> %SUCCESS_WITH_INFO THEN
                SQL_MsgBox SQL_ErrorQuickOne + " Fetch count ", MSGBOX_OK
                EXIT FUNCTION
              END IF
            ELSE  
              lresult& = Sql_ErrorClearOne
            END IF
            Tot_Rows& = SQL_ResultColumnSInt(1,1,1)
            strSQL$ = "SELECT CODE,REFERENCE FROM DNUCLEUS"
            lresult& = SQL_Statement(1,1,%SQL_STMT_IMMEDIATE,strSQL$)
            IF SQL_Errorpending THEN
              lresult& = Sql_ErrorNumber
              IF lresult& <> %SUCCESS_WITH_INFO THEN
                SQL_MsgBox SQL_ErrorQuickOne + " Get recordset ", MSGBOX_OK
                EXIT FUNCTION
              END IF
            ELSE  
              lresult& = Sql_ErrorClearOne
            END IF
            Fetch1_Ok& = SQL_FetchResult(1,1,%NEXT_ROW)
            IF SQL_Errorpending THEN
              lresult& = Sql_ErrorNumber
              IF lresult& <> %SUCCESS_WITH_INFO THEN
                SQL_MsgBox SQL_ErrorQuickOne + " Fetch first record ", MSGBOX_OK
                EXIT FUNCTION
              END IF
            ELSE  
              lresult& = Sql_ErrorClearOne
            END IF
            CONTROL SET TEXT CBHNDL,%LBL_SQLTABLE," Creating Update Statements "
            WHILE Fetch1_Ok& = 0
              T$ = SQL_ResultColumnStr(1,1,2)
              strSQL$ = "UPDATE DNUCLEUS SET REFERENCE='" + TRIM$(MASKING(T$,2)) + "' WHERE CODE = '" + SQL_ResultColumnStr(1,1,1) +"'"
              INCR p_u&
              lresult2& = SQL_Statement(2,1,%SQL_STMT_IMMEDIATE,strSQL$)
              IF SQL_Errorpending THEN
                lresult& = Sql_ErrorNumber
                IF lresult& <> %SUCCESS_WITH_INFO THEN
                  SQL_MsgBox SQL_ErrorQuickOne + " Update attempt ", MSGBOX_OK
                  EXIT FUNCTION
                END IF
              ELSE  
                lresult& = Sql_ErrorClearOne
              END IF 
              Fetch1_Ok& = SQL_FetchResult(1,1,%NEXT_ROW)                 
              IF SQL_Errorpending THEN
                lresult& = Sql_ErrorNumber
                IF lresult& <> %SUCCESS_WITH_INFO THEN
                  SQL_MsgBox SQL_ErrorQuickOne + " Fetch next record from db connection 1 ", MSGBOX_OK
                  EXIT FUNCTION
                END IF
              ELSE  
                lresult& = Sql_ErrorClearOne
              END IF
              IF (p_u& MOD 100) = 0 THEN
                lPos& = lPos& + 5
                IF lPos& > 100 THEN
                  lPos& = 5
                END IF
                ShowProgress(BYVAL CBHNDL,BYVAL %PRB_MSCTLS_PROGRESS32_1,lPos&)
              END IF 
              CONTROL SET TEXT CBHNDL,%LBL_SQLTABLE," Masking " + "DNUCLEUS " + STR$(p_u&)
            WEND
            ShowProgress(BYVAL CBHNDL,BYVAL %PRB_MSCTLS_PROGRESS32_1,100)
            CONTROL SET TEXT CBHNDL,%LBL_SQLTABLE," Updates completed "
          END IF
          Bob Mechler
          Last edited by BOB MECHLER; 19 Apr 2009, 03:19 PM. Reason: editing

          Comment


          • #6
            >I couldn't get two statements to work but using two db connections

            That happened to me on Friday.... first time in eight years that particular program encountered the MAX_CONCURRENT_ACTIVITIES limit (number of open statements on a connection handle). That was with two SELECTs, but I'll bet it would have been the same with one SELECT and one UPDATE.... which is what I think you just discovered.

            You can check your DBMS using SqlGetInfo (that's the API call, I don't know what it would be using SQL Tools) to see if your DBMS will support more than one open statement on a connection.

            FWIW: My 'default' Jet (Access) database supported more than one; the customer's system was Sql/Server and that does not. I have no idea if this is user-configurable or not, but since I have to code for 'lowest common denominator' anyway I did not pursue it.


            MCM
            Michael Mattias
            Tal Systems (retired)
            Port Washington WI USA
            [email protected]
            http://www.talsystems.com

            Comment


            • #7
              The error I was getting was HY000 and some text that indicated the connection was busy with another sql statement (SELECT statement rowset) when executing the UPDATE statement as the second statement on the single db connection.

              I would rarely use more than a couple connections so it's fine.

              Bob Mechler

              Comment

              Working...
              X