Example of select and update needed

[BOB MECHLER]

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.

                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

[Christopher Carroll]

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

[Michael Mattias]

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.

[BOB MECHLER]

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

[BOB MECHLER]

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.

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

[Michael Mattias]

>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

[BOB MECHLER]

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