Link to home
Start Free TrialLog in
Avatar of bcolladay
bcolladayFlag for United States of America

asked on

Stored Procedure Not Doing Update

This is the log from a stored procedure in Pervasive SQL.  It shows the SP being created & Executed.  It  doesn't update as expected.  The next query in the log is an update that does what I want, and it successfully updates the Table.  BillBach was helping me on the original question but I think this is beyonfd the scope of the original question.


<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail8() WITH DEFAULT HANDLER
AS BEGIN
      DECLARE :BillToEmail CHAR(40);
      DECLARE :PatientEmail CHAR(40);
      DECLARE :BTBilltoID INTEGER;
      DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS FOR UPDATE;
      OPEN curs;
      FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      WHILE(SQLSTATE = '00000') DO
            IF :BillToEmail = '' THEN
                SET :PatientEMail = '';
                SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';            
                UPDATE SET Email  = :PatientEmail WHERE CURRENT OF curs;
            END IF;  
            FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      END WHILE;
      CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
Execute SyncEmail8()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
update BILLTOS
set BILLTOS.Email = PATIENTS.Email
from PATIENTS, BILLTOS
where PATIENTS.BillToID = BILLTOS.BillToID
and PATIENTS.Relationship = '1'
and PATIENTS.Email like '%@%'
and not(BILLTOS.Email like '%@%')
SQL statement(script) has executed successfully.
9 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
Avatar of knightEknight
knightEknight
Flag of United States of America image

I don't know Pervasive SQL, but shouldn't this statement specify a table to update?

UPDATE TABLENAME SET Email  = :PatientEmail WHERE CURRENT OF curs;
The "rows were affected" line is added after the statement is executed by the PCC.  It does not return the number of records affected when a stored procedure is executed.  As far as the PCC is concerned, no records were affected by the "EXEC" statement.  Records may have been affected within the SP but not by the EXEC.

Check the table after to see if the records were updated.  
Avatar of bcolladay

ASKER

knightEknight:  I tried it with and without the Table name in the update statement in the SP. same result.

mirtheil: in the example of text output above those are run within a few seconds of each other.  between the SP Execute and the regular UPDATE statement I looked at the table and the rows are not getting updated, You can see the UPDATE statement correctly reports the number of rows updates as I setup and expected in the test data.  I then look and those rows I had previously cleared are re-populated.
Can you give an example of the data you get with this query:
SELECT BillToID, Email FROM BILLTOS

Looking at the stored procedure, the only time the Update is run is when the :BillToEmail = ''.  
<<<<<<<<<<<<<<<<<<<<<<<<
   BillToID   Email                                  
===========   ========================================
         64                                          
         65   bill@gates.com                      
         66                                          
         67                                          
         68                                          
         69                                          
         71                                          
         72                                          
         80                                          
         55                                          
         56   bill@gates.com                      
         57                                          
         58                                          
         59                                          
         60                                          
         61                                          
         62   bill@gates.com                      
         63                                          
         46                                          
         47                                          
         48                                          
         49                                          
         50                                          
         51                                          
         52                                          
         53                                          
         54                                          
         37                                          
         38                                          
         39   bill@gates.com                      
         40   bill@gates.com                      
         41                                          
         42                                          
         43                                          
         44                                          
         45   bill@gates.com                      
         28                                          
         29                                          
         30                                          
         31                                          
         32                                          
         33                                          
         34                                          
         35                                          
         36                                          
         19                                          
         20                                          
         21                                          
         22                                          
         23                                          
         24   bill@gates.com                    
         25                                          
         26                                          
         27                                          
         10   bill@gates.com                      
         11                                          
         12                                          
         13   bill@gates.com                      
         14                                          
         15                                          
         16                                          
         17   bill@gates.com                      
         18                                          
          1                                          
          2                                          
          3                                          
          4                                          
          5                                          
          6                                          
          7                                          
          8   bill@gates.com                      
          9                                          
         84   bill@gates.com                    
         85                                          
         86                                          
         88                                          
         89                                          
         90                                          
         92   bill@gates.com                    
         93                                          
         94                                          
        101   bill@gates.com                      
        102                                          
        103   bill@gates.com                    
        104                                          
        105                                          
        106                                          
        107                                          
        110   bill@gates.com                      
        111   bill@gates.com                      
        112                                          
        113                                          
        114                                          
        116                                          
        117                                          
        118                                          
        119                                          
        120                                          
        121                                          
        122                                          
        123                                          
        124                                          
        125                                          
        126                                          
        127                                          
        128   bill@gates.com                      
        129                                          
        130                                          
        131                                          
        132   bill@gates.com                      
        133   bill@gates.com                    

111 rows were affected.

>>>>>>>>>>>>>>>>>>>>>>>>
SELECT BillToID, Email FROM PATIENTS where email like '%@%' and relationship = '1'

<<<<<<<<<<<<<<<<<<<<<<<<
   BillToID   Email                                  
===========   ========================================
         17   bill@gates.com                      
         39   bill@gates.com                      
         62   bill@gates.com                      
         45   bill@gates.com                      
         65   bill@gates.com                      
          8   bill@gates.com                      
         10   bill@gates.com                      
         40   bill@gates.com                      
         56   bill@gates.com                      
         13   bill@gates.com                      
         84   bill@gates.com                    
         92   bill@gates.com                    
        101   bill@gates.com                      
        103   bill@gates.com                    
        110   bill@gates.com                    
         13   bill@gates.com                    
         24   bill@gates.com                    
        128   bill@gates.com                      
        132   bill@gates.com                      
        133   bill@gates.com                    

20 rows were affected.

>>>>>>>>>>>>>>>>>>>>>>>>
Those results are actually after a successful update statement.   Before I try the SP, I delete some of the BILLTOS email addresses so they would be eligible for updating.
In the following tables, I would expect the following rows to be updated:

BILLTOS.BillToID 8, 10, 13, 17




SELECT BillToID, Email FROM PATIENTS where email like '%@%' and relationship = '1' order by billtoid
<<<<<<<<<<<<<<<<<<<<<<<<
   BillToID   Email                                  
===========   ========================================
          8   bill@gates.com                      
         10   bill@gates.com                      
         13   bill@gates.com                      
         17   bill@gates.com                      
         24   bill@gates.com                    
         39   bill@gates.com                      
         40   bill@gates.com                      
         45   bill@gates.com                      
         56   bill@gates.com                      
         62   bill@gates.com                      
         65   bill@gates.com                      
         84   bill@gates.com                    
         92   bill@gates.com                    
        101   bill@gates.com                      
        103   bill@gates.comm                    
        110   bill@gates.com                    
        128   bill@gates.com                      
        132   bill@gates.com                      
        133   bill@gates.com                    

20 rows were affected.

>>>>>>>>>>>>>>>>>>>>>>>>




SELECT BillToID, Email FROM BILLTOS
<<<<<<<<<<<<<<<<<<<<<<<<
   BillToID   Email                                  
===========   ========================================
          1                                          
          2                                          
          3                                          
          4                                          
          5                                          
          6                                          
          7                                          
          8                          
          9                                          
         10                          
         11                                          
         12                                          
         13                          
         14                                          
         15                                          
         16                                          
         17                          
         18                                          
         19                                          
         20                                          
         21                                          
         22                                          
         23                                          
         24   bill@gates.com                    
         25                                          
         26                                          
         27                                          
         28                                          
         29                                          
         30                                          
         31                                          
         32                                          
         33                                          
         34                                          
         35                                          
         36                                          
         37                                          
         38                                          
         39   bill@gates.com                      
         40   bill@gates.com                      
         41                                          
         42                                          
         43                                          
         44                                          
         45   bill@gates.com                      
         46                                          
         47                                          
         48                                          
         49                                          
         50                                          
         51                                          
         52                                          
         53                                          
         54                                          
         55                                          
         56   bill@gates.com                      
         57                                          
         58                                          
         59                                          
         60                                          
         61                                          
         62   bill@gates.com                      
         63                                          
         64                                          
         65   bill@gates.com                      
         66                                          
         67                                          
         68                                          
         69                                          
         71                                          
         72                                          
         80                                          
         84   bill@gates.com                    
         85                                          
         86                                          
         88                                          
         89                                          
         90                                          
         92   bill@gates.com                    
         93                                          
         94                                          
        101   bill@gates.com                      
        102                                          
        103   bill@gates.com                    
        104                                          
        105                                          
        106                                          
        107                                          
        110   bill@gates.com                      
        111   bill@gates.com                      
        112                                          
        113                                          
        114                                          
        116                                          
        117                                          
        118                                          
        119                                          
        120                                          
        121                                          
        122                                          
        123                                          
        124                                          
        125                                          
        126                                          
        127                                          
        128   bill@gates.com                      
        129                                          
        130                                          
        131                                          
        132   bill@gates.com                      
        133   bill@gates.com                    

111 rows were affected.

>>>>>>>>>>>>>>>>>>>>>>>>
ASKER CERTIFIED SOLUTION
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is just weird isn't it?  Here is the Text Output from the various steps.  I am clearing all of the BILLTOS.Email values before I start now.  I refresh the grid view after each step.  The Exceute or call SyncEmail process is leaving them all blank.  The UPDATE statement works as expected.    I even tired changing the qualifier from null to a specific string and putting that string in one BILLTOS.Email record.  it didn't update that either.

<<<<<<<<<<<<<<<<<<<<<<<<
Drop Procedure SyncEmail
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
      DECLARE :BillToEmail CHAR(40);
      DECLARE :PatientEmail CHAR(40);
      DECLARE :BTBilltoID INTEGER;
      DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE Email is null FOR UPDATE;
      OPEN curs;
      FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      WHILE(SQLSTATE = '00000') DO
            IF :BillToEmail is NULL THEN
                SET :PatientEMail = NULL;
                SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
                UPDATE SET Email  = :PatientEmail WHERE CURRENT OF curs;
            END IF;  
            FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      END WHILE;
      CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
execute SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
call SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
update BILLTOS
set BILLTOS.Email = PATIENTS.Email
from PATIENTS, BILLTOS
where PATIENTS.BillToID = BILLTOS.BillToID
and PATIENTS.Relationship = '1'
and PATIENTS.Email like '%@%'
and not(BILLTOS.Email like '%@%')
SQL statement(script) has executed successfully.
19 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

FYI -- when the query ran correctly, I got a return code of 0, not -1, even if it did not update any records.  

Try creating a LOG file with the three fields in it of the appropriate length) and add the INSERT statement back into the SP.  Also, I have no way of seeing your table definitions -- so please verify the spelling of all field names, as well as the data types of each field.  There might be a minor typo somewhere...

The SP I provided above worked just fine with the following definitions:
CREATE TABLE "BillTos"(
 "BillToID" IDENTITY DEFAULT '0',
 "Email" CHAR(40),
 UNIQUE ("BillToID"));

CREATE TABLE "Patients"(
 "BillToID" INTEGER,
 "Email" CHAR(40),
 "Relationship" CHAR(1));

CREATE TABLE "Log"(
 "BilltoId" INTEGER,
 "BillEmail" CHAR(40),
 "PatientEmail" CHAR(40));

Open in new window

Just a quick question that may clarify this.  My Tables have "other" columns in them in addidion to the ones I am specifying, does that changes anything?

other than that here are the relevant parts of my tables:

CREATE TABLE "BILLTOS"(
 "BillToID" IDENTITY DEFAULT '0',
 "Email" CHAR(40),
);
CREATE UNIQUE INDEX "PrimaryKey" ON "BILLTOS"("BillToID");

CREATE TABLE "PATIENTS"(
 "PatientID" IDENTITY DEFAULT '0',
 "BillToID" UINTEGER,
 "Relationship" CHAR(1),
 "Email" CHAR(40),
);
CREATE INDEX "BillToKey" ON "PATIENTS"("BillToID", "BirthDate");
CREATE UNIQUE INDEX "PrimaryKey" ON "PATIENTS"("PatientID");


I will try the log fil, thanks
Log file also empty

<<<<<<<<<<<<<<<<<<<<<<<<
CREATE PROCEDURE SyncEmail() WITH DEFAULT HANDLER
AS BEGIN
      DECLARE :BillToEmail CHAR(40);
      DECLARE :PatientEmail CHAR(40);
      DECLARE :BTBilltoID INTEGER;
      DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE Email is null FOR UPDATE;
      OPEN curs;
      FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      WHILE(SQLSTATE = '00000') DO
            IF :BillToEmail is NULL THEN
                SET :PatientEMail = NULL;
                SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
                INSERT Into Log VALUES (:BTBilltoID, :BillToEmail, :PatientEMail);            
                UPDATE SET Email  = :PatientEmail WHERE CURRENT OF curs;
            END IF;  
            FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      END WHILE;
      CLOSE curs;
END
SQL statement(script) has executed successfully.
0 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
execute SyncEmail()
SQL statement(script) has executed successfully.
-1 rows were affected.
>>>>>>>>>>>>>>>>>>>>>>>>
Still getting a -1.  Something is wrong with the cursor, I think.  Try running this query by itself:
    SELECT BillToID, Email FROM BILLTOS WHERE Email is null

Then, try running the second query, too:
    SELECT TOP 1 EMail FROM PATIENTS WHERE BillToID = <#> AND Relationship = '1' AND Email like '%@%';

Be sure to replace <#> with a BillToId from your original query.

Additionally, post your version.  I tested this (successfully) on PSQLv11.01.
Email is definitely blank and not null:

SELECT BillToID, Email FROM BILLTOS WHERE Email = ''; 101 rows
SELECT BillToID, Email FROM BILLTOS WHERE Email is null; 0 rows

OK -- change all of these back from "IS NULL" to "= ''", then.  
SELECT TOP 1 EMail FROM PATIENTS WHERE BillToID = 5 AND Relationship = '1' AND Email like '%@%';
SELECT BillToID, Email FROM BILLTOS WHERE Email = '';
SELECT BillToID, Email FROM BILLTOS WHERE Email is null;

<<<<<<<<<<<<<<<<<<<<<<<<
Email                                  
========================================
bill@gates.com                      

1 row was affected.

>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
   BillToID   Email                                  
===========   ========================================
         64                                          
         65                                          
         66                                          
         67                                          
         68                                          
         69                                          
         71                                          
         72                                          
         80                                          
         55                                          
         56                                          
         57                                          
         58                                          
         59                                          
         60                                          
         61                                          
         62                                          
         63                                          
         46                                          
         47                                          
         48                                          
         49                                          
         50                                          
         51                                          
         52                                          
         53                                          
         54                                          
         37                                          
         38                                          
         39                                          
         40                                          
         41                                          
         42                                          
         43                                          
         44                                          
         45                                          
         28                                          
         29                                          
         30                                          
         31                                          
         32                                          
         33                                          
         34                                          
         35                                          
         36                                          
         19                                          
         20                                          
         21                                          
         22                                          
         23                                          
         24                                          
         25                                          
         26                                          
         27                                          
         10                                          
         11                                          
         12                                          
         13                                          
         14                                          
         15                                          
         16                                          
         18                                          
          1                                          
          2                                          
          3                                          
          4                                          
          5                                          
          6                                          
          7                                          
          9                                          
         85                                          
         86                                          
         88                                          
         89                                          
         90                                          
         93                                          
         94                                          
        102                                          
        104                                          
        105                                          
        106                                          
        107                                          
        111                                          
        112                                          
        113                                          
        114                                          
        116                                          
        117                                          
        118                                          
        119                                          
        120                                          
        121                                          
        122                                          
        123                                          
        124                                          
        125                                          
        126                                          
        127                                          
        129                                          
        130                                          
        131                                          

101 rows were affected.

>>>>>>>>>>>>>>>>>>>>>>>>

<<<<<<<<<<<<<<<<<<<<<<<<
   BillToID   Email                                  
===========   ========================================

0 rows were affected.

>>>>>>>>>>>>>>>>>>>>>>>>
Yep, something in between there, did it....!

This is what I ended up with:
Drop Procedure TestUpdate;
CREATE PROCEDURE TestUpdate() WITH DEFAULT HANDLER
AS BEGIN
      DECLARE :BillToEmail CHAR(40);
      DECLARE :PatientEmail CHAR(40);
      DECLARE :BTBilltoID INTEGER;
      DECLARE curs CURSOR FOR SELECT BillToID, Email FROM BILLTOS WHERE Email = '' FOR UPDATE;
      OPEN curs;
      FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      WHILE(SQLSTATE = '00000') DO
            IF :BillToEmail = '' THEN
                SET :PatientEMail = '';
                SELECT TOP 1 EMail INTO :PatientEMail FROM PATIENTS WHERE BillToID = :BTBilltoID AND Relationship = '1' AND Email like '%@%';
                INSERT Into Log VALUES (:BTBilltoID, :BillToEmail, :PatientEMail);            
                UPDATE BILLTOS SET Email  = :PatientEmail WHERE CURRENT OF curs;
            END IF;  
            FETCH NEXT FROM curs INTO :BTBilltoID, :BillToEmail;
      END WHILE;
      CLOSE curs;
END;
execute TestUpdate();

Open in new window

Empty in that there are no records in the Log table or that there are records but they are empty values?  

Take off the "WHERE Email is null" in the original SELECT in your procedure and try again.  
Ok, this is weird again, but...if I take out the line:

INSERT Into Log VALUES (:BTBilltoID, :BillToEmail, :PatientEMail);

it fails, put it back in, it works?
Well that's just nuts!  Same thing happens here!  Perhaps there's a bug in the engine???  Which version are you running?
10.30.017.000
I will try it on the client with 9.71 when I get it tested on a bigger data set.  For now I will just add the Log file delete it when It is finished.  Thanks.