I am doing an update thru sql passthru and I am tyring to get a status where I know if it did any updates.

  
SQL SQLMSS
UPDATE NONCONFORM.dbo.NCEMAILADDRESS
SET emEmail = '&F2'
WHERE emVendor = &F1;
TABLE FILE SQLOUT1
PRINT
*
ON TABLE HOLD AS SQLOUT1
END

-RUN

-SET &R1 = &RECORDS;
-SET &R2 = &RETCODE;
-SET &R3 = &LINES;
-TYPE LINES: &R3
-TYPE SQL ERROR: &R2
-TYPE RECORDS: &R1

What i get back is this.

  
(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND  : 0/UPDATE
 LINES:        0
 SQL ERROR:        0
 RECORDS:        0

How do i get a status code that tells me how many records were affected?

On a update where there is records i get the same results

  
 (FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND  : 1/UPDATE
 LINES:        0
 SQL ERROR:        0
 RECORDS:        0

This message has been edited. Last edited by: Kerry,

You can change a setting on the adapter.

Look for "Obtaining the Number of Rows Updated or Deleted" in Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS V7.6.2. It's

ENGINE [SQLMSS] SET PASSRECS {ON|OFF}

You can code the command in the FEX itself, just before your update - then &RECORDS should be populated by the number of rows affected.

quote:

ENGINE [SQLMSS] SET PASSRECS {ON|OFF}

My data adapter already has the option turned on. I evan tried entering the line in the fex and it always returns 0.

Try removing this code, you are doing an update you haven't done a select.

quote:

TABLE FILE SQLOUT1
PRINT
*
ON TABLE HOLD AS SQLOUT1
END

First, I don't think the FOCUS or WebFOCUS ever puts anything in any & variable for SQL INSERT, UPDATE or DELETE.

With your app, the thing to keep in mind is that when doing updates like this, you are only doing 1 row at a time so your status:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND  : 1/UPDATE

is telling you that your program update the row.

To do multiple rows, if all of your updates are in SAVE.FTM you have to do something like this:

-LOOP1
-READ SAVE NOCLOSE &AGENCY.3. &FUND.8. &SFY.4. &WARRANT.10. &CLIENT.8.,
-, &SEQ.5. &PLCSQ.5. &CASEID.8.
-IF &IORETURN NE 0 THEN GOTO END1;
-*
 SET SQLENGINE=SQLORA
 SQL SET SERVER KIDS
 SQL UPDATE TCLIENT_PAYMENT
        SET CAS_ID = &CASEID ,
            PLC_SEQ_NBR = &PLCSQ
      WHERE AGCY_ISSUED_BY = &AGENCY
        AND FUND_PAID_FROM = &FUND
        AND STATE_FISCAL_YR = &SFY
        AND WARRANT_NBR = &WARRANT
        AND CL_ID = &CLIENT
        AND SEQ_NBR = &SEQ ;
 SQL COMMIT ;
 END
-RUN
-GOTO LOOP1
-*
-END1
-CLOSE SAVE

With this, you will get a FOC1364 for every update. This can generate a lot of output if one is updating hundreds or thousands of rows. That is why I will set PASSRECS ON or OFF depending on the number of rows expected. If you set PASSRECS OFF, the you can do your own counts within the Dialogue Manager.

Note, even MODIFY generates a separate SQL UPDATE command for every transaction. But MODIFY does an additional SQL SELECT to see if the row exists. By coding your own SQL UPDATE, you save the I/O for that additional SQL SELECT.

jgelona,

You're right and you're wrong.

You're right about &RECORDS not being updated even though PASSRECS is set to ON. The manual states:

quote:

In addition, the adapter updates the &RECORDS system variable with the number of rows affected. You can access this variable using Dialogue Manager

You're wrong about the number of rows updated by a SQL script coded like Wilky's example.

Here's a script I ran:

ENGINE SQLMSS SET PASSRECS ON
-RUN

SQL SQLMSS
UPDATE EIDW.dbo.SAPI_Save
SET OUTPUTFORMAT = 'HTML'
WHERE OUTPUTFORMAT = 'PDF';
END
-RUN

-TYPE &RECORDS

The result:

 (FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND  : 45/UPDATE
RECORDS:        0
LINES:        0

So, a simple SQL update command can update multiple rows, even if it's not driven by some input data file.

Francis, I took the original posters question to be about keyed updates, like MODIFY. I also do updates like your example were I want to update multiple rows based on an index or partial key and get results like your example.

I just run into the same problem and found this on the forum.
So what is the precise problem?
And (if there is one) what is the precise solution?

Minos,

What is your problem with this?
Because what this topic describes is that when you do a sql passthru command, then you will not be notified of the amount of records affected other then in this message (FOC1364) string, regardless of the SET PASSRECS setting.