Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Execute Immediate with a Commit?

11 posts in General Discussion Last posting was on 2007-02-08 13:47:40.0Z
EY Posted on 2007-02-07 12:06:18.0Z
Sender: 4957.45c08f1d.1804289383@sybase.com
From: EY
Newsgroups: ianywhere.public.general
Subject: Execute Immediate with a Commit?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <45c9ceca.727a.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Feb 2007 05:06:18 -0700
X-Trace: forums-1-dub 1170853578 10.22.241.41 (7 Feb 2007 05:06:18 -0700)
X-Original-Trace: 7 Feb 2007 05:06:18 -0700, 10.22.241.41
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5814
Article PK: 1803

Is it necessary to use a commit statement after an Execute
Immediate statement is run?

Reason I ask is this. I have multiple exec immediate
statements in a procedure, and what is going on is basically
the statements do not execute for EACH case that they
should.

I know they should because the data is prepared and
controlled, and subsequent message statements show that they
are ready to run, but only about 75% of them run.

If I keep running the stored proc over and over, the exec
immediate statements keep running, and eventually, after
about 5 manual runs, everything is completed as it should
be.

So, as a test overnight, I put the execute immediate
statements 3x in a row, so each one would run 3x and
simulate what I did above by just running the procedure a
few times straight.

This morning, everything worked fine. All that happens is
the procedure updates one table based on another table's
data, syncronizing it more or less. I join the tables where
the columns in question are not equal, and I had nothing to
report today, all was well.

So, rather than keeping it how I have it now, with running a
command 3x straight, is there a way to force the exec
immediate statement to run how I expect it should? Can I
put a exec immediate commit type of thing to make sure it
actually runs?

Still running version 8.0 (latest patches though) if it
matters of SQL Anywhere Studio.

Any ideas?

Thanks for any help in advance.


EY Posted on 2007-02-07 12:29:56.0Z
Sender: 72fb.45c9d3f2.1804289383@sybase.com
From: EY
Newsgroups: ianywhere.public.general
Subject: Re: Execute Immediate with a Commit?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <45c9d454.7306.1681692777@sybase.com>
References: <45c9ceca.727a.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Feb 2007 05:29:56 -0700
X-Trace: forums-1-dub 1170854996 10.22.241.41 (7 Feb 2007 05:29:56 -0700)
X-Original-Trace: 7 Feb 2007 05:29:56 -0700, 10.22.241.41
Lines: 2
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5815
Article PK: 1804

Oh yeah - no errors are being thrown either, just skipping
random rows for the mass update.


Breck Carter [Team iAnywhere] Posted on 2007-02-07 13:43:10.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Execute Immediate with a Commit?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <k7ojs2leu0t7lfp7scs97m8rr2oge98jsj@4ax.com>
References: <45c9ceca.727a.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 7 Feb 2007 06:43:10 -0700
X-Trace: forums-1-dub 1170859390 64.7.134.118 (7 Feb 2007 06:43:10 -0700)
X-Original-Trace: 7 Feb 2007 06:43:10 -0700, bcarter.sentex.ca
Lines: 64
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5816
Article PK: 1805

EXECUTE IMMEDIATE does not skip stuff randomly, nor does it need
anything special to "force" it to work. But yes, you might need a
COMMIT if the EXECUTE IMMEDIATE is doing updates/inserts/deletes in
chained mode (the default).

EXECUTE IMMEDIATE inside a procedure is run on the same connection as
the rest of your operations, and if don't have server-side auto-commit
turned on (chained mode turned off), you need to (eventually) issue a
COMMIT on that connection or you run the risk of (eventually)
disconnecting without committing.

Show us your code, maybe someone can help.

Breck

On 7 Feb 2007 05:06:18 -0700, EY wrote:

>Is it necessary to use a commit statement after an Execute
>Immediate statement is run?
>
>Reason I ask is this. I have multiple exec immediate
>statements in a procedure, and what is going on is basically
>the statements do not execute for EACH case that they
>should.
>
>I know they should because the data is prepared and
>controlled, and subsequent message statements show that they
>are ready to run, but only about 75% of them run.
>
>If I keep running the stored proc over and over, the exec
>immediate statements keep running, and eventually, after
>about 5 manual runs, everything is completed as it should
>be.
>
>So, as a test overnight, I put the execute immediate
>statements 3x in a row, so each one would run 3x and
>simulate what I did above by just running the procedure a
>few times straight.
>
>This morning, everything worked fine. All that happens is
>the procedure updates one table based on another table's
>data, syncronizing it more or less. I join the tables where
>the columns in question are not equal, and I had nothing to
>report today, all was well.
>
>So, rather than keeping it how I have it now, with running a
>command 3x straight, is there a way to force the exec
>immediate statement to run how I expect it should? Can I
>put a exec immediate commit type of thing to make sure it
>actually runs?
>
>Still running version 8.0 (latest patches though) if it
>matters of SQL Anywhere Studio.
>
>Any ideas?
>
>Thanks for any help in advance.

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


EY Posted on 2007-02-07 13:59:25.0Z
Sender: 744b.45c9e813.1804289383@sybase.com
From: EY
Newsgroups: ianywhere.public.general
Subject: Re: Execute Immediate with a Commit?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <45c9e94d.7461.1681692777@sybase.com>
References: <k7ojs2leu0t7lfp7scs97m8rr2oge98jsj@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Feb 2007 06:59:25 -0700
X-Trace: forums-1-dub 1170860365 10.22.241.41 (7 Feb 2007 06:59:25 -0700)
X-Original-Trace: 7 Feb 2007 06:59:25 -0700, 10.22.241.41
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5817
Article PK: 1808

Here you go -

***************
DECLARE checkLocations CURSOR FOR
SELECT patients.Unit, Stagetable.Area, patients.Room,
Stagetable.Room, Stagetable.BillingNumber FROM
"DBA"."patients"
inner join Stagetable on patients.billing_number =
stagetable.billingnumber
where patients.Unit != Stagetable.Area OR Patients.Room !=
Stagetable.Room and patients.status != 'Discharged';
*******************

All that does is find the rows that need to be updated with
the new data in the STAGETABLE. No biggie.

Here's the actual execution -

*******************
OPEN checkLocations;
WHILE SQLCODE = 0 LOOP

FETCH NEXT checkLocations into check1, check2, check3,
check4, check5;

set stmt = 'UPDATE PATIENTS SET Unit = ' + char(39) + check2
+ char(39) + ', Room = ' + char(39) + check4 + char(39) + ',
Status = ' + char(39) + 'CHANGE' + char(39) ||
+ ', onServiceReason = ' + char(39) + 'Location Change' +
char(39) + ', Update_Date = Date(Now()) WHERE ' ||
'Patients.Billing_Number = ' + char(39) + check5 + char(39);

//print stmt;
// something is keeping this from running just once, so i'm
trying it 3x as of 2/6/2007
execute immediate stmt;
execute immediate stmt;
execute immediate stmt;

FETCH NEXT checkLocations into check1, check2, check3,
check4, check5;
END LOOP;

CLOSE checkLocations;
*********************

You can see above where I do the execute immediate 3x in a
row, which worked fine for this morning's import, but I
don't want that to be the solution to the problem, it's a
band-aid for testing.

I do not have auto-commit turned on either.


Rob Waywell Posted on 2007-02-07 14:07:59.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <k7ojs2leu0t7lfp7scs97m8rr2oge98jsj@4ax.com> <45c9e94d.7461.1681692777@sybase.com>
Subject: Re: Execute Immediate with a Commit?
Lines: 82
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: cpe000c41b345a1-cm014310004450.cpe.net.cable.rogers.com
X-Original-NNTP-Posting-Host: cpe000c41b345a1-cm014310004450.cpe.net.cable.rogers.com
Message-ID: <45c9eb4f@forums-1-dub>
Date: 7 Feb 2007 07:07:59 -0700
X-Trace: forums-1-dub 1170860879 72.140.145.120 (7 Feb 2007 07:07:59 -0700)
X-Original-Trace: 7 Feb 2007 07:07:59 -0700, cpe000c41b345a1-cm014310004450.cpe.net.cable.rogers.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5819
Article PK: 1809

Try stepping through in the stored procedure debugger and add some error
checking. One possible explanation is that you have BLOCKING=OFF for this
connection and that the EXECUTE IMMEDIATE is being blocked and rolled back.
Since you aren't checking the return code after the EXECUTE IMMEDIATE you
aren't trapping the error and your procedure is moving on just as you told
it to. If that is the case, then by running the same statement 3X in a row
you are getting lucky and one of them is succeeding without getting blocked.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

<EY> wrote in message news:45c9e94d.7461.1681692777@sybase.com...
> Here you go -
>
> ***************
> DECLARE checkLocations CURSOR FOR
> SELECT patients.Unit, Stagetable.Area, patients.Room,
> Stagetable.Room, Stagetable.BillingNumber FROM
> "DBA"."patients"
> inner join Stagetable on patients.billing_number =
> stagetable.billingnumber
> where patients.Unit != Stagetable.Area OR Patients.Room !=
> Stagetable.Room and patients.status != 'Discharged';
> *******************
>
> All that does is find the rows that need to be updated with
> the new data in the STAGETABLE. No biggie.
>
> Here's the actual execution -
>
> *******************
> OPEN checkLocations;
> WHILE SQLCODE = 0 LOOP
>
> FETCH NEXT checkLocations into check1, check2, check3,
> check4, check5;
>
> set stmt = 'UPDATE PATIENTS SET Unit = ' + char(39) + check2
> + char(39) + ', Room = ' + char(39) + check4 + char(39) + ',
> Status = ' + char(39) + 'CHANGE' + char(39) ||
> + ', onServiceReason = ' + char(39) + 'Location Change' +
> char(39) + ', Update_Date = Date(Now()) WHERE ' ||
> 'Patients.Billing_Number = ' + char(39) + check5 + char(39);
>
> //print stmt;
> // something is keeping this from running just once, so i'm
> trying it 3x as of 2/6/2007
> execute immediate stmt;
> execute immediate stmt;
> execute immediate stmt;
>
> FETCH NEXT checkLocations into check1, check2, check3,
> check4, check5;
> END LOOP;
>
> CLOSE checkLocations;
> *********************
>
> You can see above where I do the execute immediate 3x in a
> row, which worked fine for this morning's import, but I
> don't want that to be the solution to the problem, it's a
> band-aid for testing.
>
> I do not have auto-commit turned on either.


Breck Carter [Team iAnywhere] Posted on 2007-02-07 17:57:57.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Execute Immediate with a Commit?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <r07ks2pkm1ruvuv7v15klec5715tlsb82q@4ax.com>
References: <45c9eb4f@forums-1-dub> <45c9ec1c.74b0.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 7 Feb 2007 10:57:57 -0700
X-Trace: forums-1-dub 1170874677 64.7.134.118 (7 Feb 2007 10:57:57 -0700)
X-Original-Trace: 7 Feb 2007 10:57:57 -0700, bcarter.sentex.ca
Lines: 47
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5821
Article PK: 1810

How do you know the one EXECUTE IMMEDIATE statement is not getting
executed? SQL Anywhere just does not have that kind of bug.

Do you ever do a COMMIT?

Is that *really* the code? There are two operators || + appearing in
line.

set stmt = 'UPDATE PATIENTS SET Unit = ' + char(39) + check2
+ char(39) + ', Room = ' + char(39) + check4 + char(39) + ',
Status = ' + char(39) + 'CHANGE' + char(39) ||
+ ', onServiceReason = ' + char(39) + 'Location Change' +
char(39) + ', Update_Date = Date(Now()) WHERE ' ||
'Patients.Billing_Number = ' + char(39) + check5 + char(39);

How are the variables stmt, check2, etc declared?

Why are you using EXECUTE IMMEDIATE? Why not just UPDATE?

UPDATE PATIENTS SET Unit = check2, Room = check4,
Status = 'CHANGE', onServiceReason = 'Location Change', Update_Date =
Date(Now()) WHERE Patients.Billing_Number = check5;

Show us the rest of the procedure.

Breck

On 7 Feb 2007 07:11:25 -0700, EY wrote:

>If that is the case, how would some of the statements get
>executed, and some of them not get executed (if I only list
>the statement 1x, rather than 3x)? That is what was
>confusing me.
>
>What error am I looking for after the statement? Or should
>I just do a message of any possible error? The statements
>all look 100% to me, I have done about 15 prints of the
>actual commands before I actually executed them to be sure.
>
>Thanks for the help so far, hope to get this fixed!

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


EY Posted on 2007-02-07 18:28:12.0Z
Sender: 79a9.45ca2627.1804289383@sybase.com
From: EY
Newsgroups: ianywhere.public.general
Subject: Re: Execute Immediate with a Commit?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <45ca284c.79da.1681692777@sybase.com>
References: <r07ks2pkm1ruvuv7v15klec5715tlsb82q@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Feb 2007 11:28:12 -0700
X-Trace: forums-1-dub 1170876492 10.22.241.41 (7 Feb 2007 11:28:12 -0700)
X-Original-Trace: 7 Feb 2007 11:28:12 -0700, 10.22.241.41
Lines: 62
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5822
Article PK: 1811

I know that the statement is not working entirely because I
isolated the entire procedure piece by piece so I could
actually find it. Here's the entire procedure that I used
for this isolation....

I thought I needed to use an Execute Immediate in the case
of dynamic SQL Statements, such as I have with the different
billing number variable changing for each row?

BEGIN

DECLARE check1 char(128); // that's check ONE......
DECLARE check2 char(128); // just some temp variables
that don't do anything
DECLARE check3 char(128); // just some temp variables
that don't do anything
DECLARE check4 char(128); // just some temp variables
that don't do anything
DECLARE check5 char(128); // just some temp variables
that don't do anything
DECLARE stmt char(512); // our statement to run

DECLARE checkLocations CURSOR FOR
SELECT patients.Unit, Stagetable.Area, patients.Room,
Stagetable.Room, Stagetable.BillingNumber FROM
"DBA"."patients"
inner join Stagetable on patients.billing_number =
stagetable.billingnumber
where patients.Unit != Stagetable.Area OR Patients.Room !=
Stagetable.Room and patients.status != 'Discharged';


////////////////////////////////////////

OPEN checkLocations;
WHILE SQLCODE = 0 LOOP

FETCH NEXT checkLocations into check1, check2, check3,
check4, check5;

set stmt = 'UPDATE PATIENTS SET Unit = ' + char(39) + check2
+ char(39) + ', Room = ' + char(39) + check4 + char(39) + ',
Status = ' + char(39) + 'CHANGE' + char(39) ||
+ ', onServiceReason = ' + char(39) + 'Location Change' +
char(39) + ', Update_Date = Date(Now()) WHERE ' ||
'Patients.Billing_Number = ' + char(39) + check5 + char(39);

//print stmt;
// something is keeping this from running just once, so i'm
trying it 3x as of 2/6/2007
execute immediate stmt;
execute immediate stmt;
execute immediate stmt;

set stmt = '';

FETCH NEXT checkLocations into check1, check2, check3,
check4, check5;
END LOOP;

CLOSE checkLocations;
END


Breck Carter [Team iAnywhere] Posted on 2007-02-07 21:42:37.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Execute Immediate with a Commit?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <2miks2dphnccnrkcoroedr9vap1a6ua3uf@4ax.com>
References: <r07ks2pkm1ruvuv7v15klec5715tlsb82q@4ax.com> <45ca284c.79da.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 7 Feb 2007 14:42:37 -0700
X-Trace: forums-1-dub 1170888157 64.7.134.118 (7 Feb 2007 14:42:37 -0700)
X-Original-Trace: 7 Feb 2007 14:42:37 -0700, bcarter.sentex.ca
Lines: 144
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5823
Article PK: 1812


On 7 Feb 2007 11:28:12 -0700, EY wrote:

>I know that the statement is not working entirely because I
>isolated the entire procedure piece by piece so I could
>actually find it.

I know I'm being particularly stupid, but I still don't understand how
you determined the statement wasn't working.

Is it possible the cursor returns zero rows from time to time? You
have a WHERE clause with an OR and AND without any ( braces ) to
control precedence, so I hope you want the AND done before the OR
(I'll bet the answer is "no, that's not what I want" :)

Or maybe there's one of those confusing cursor sensitivity issues,
where you're changing the rowset on the fly... I'm just guessing
here... cursors are not my friends.

>Here's the entire procedure that I used
>for this isolation....
>
>I thought I needed to use an Execute Immediate in the case
>of dynamic SQL Statements, such as I have with the different
>billing number variable changing for each row?

The variable *value* is changing, that doesn't make it "dynamic". If
you were changing the *names* at runtime (e.g., different table names,
column names, variable names, etc), then you'd be in The Dynamic Zone.

So I don't see the need for the extra complexity.

BTW, have you checked out the FOR loop? Makes writing cursor loops
ever so much easier...

-- Caveat: I have NOT tested this code...
BEGIN
FOR f_fetch AS c_fetch NO SCROLL CURSOR FOR
SELECT patients.Unit AS @patients_Unit, -- not used
Stagetable.Area AS @Stagetable_Area,
patients.Room AS @patients_Room, -- not used
Stagetable.Room AS @Stagetable_Room,
Stagetable.BillingNumber AS @Stagetable_BillingNumber
FROM "DBA"."patients"
inner join Stagetable
on patients.billing_number = stagetable.billingnumber
where patients.Unit != Stagetable.Area
OR Patients.Room != Stagetable.Room
and patients.status != 'Discharged'
FOR READ ONLY
DO
UPDATE PATIENTS
SET Unit = @Stagetable_Area,
Room = @Stagetable_Room,
Status = 'CHANGE',
onServiceReason = 'Location Change',
Update_Date = Date(Now())
WHERE Patients.Billing_Number = @Stagetable_BillingNumber;
END FOR;
END

Another possibility, if the cursor loop isn't processing millions of
rows, might be to just do the UPDATE in one swell foop, no steenking
cursor...

UPDATE PATIENTS
inner join Stagetable
on patients.billing_number = stagetable.billingnumber
SET Patients.Unit = Stagetable.Area,
Patients.Room = Stagetable.Room,
Patients.Status = 'CHANGE',
Patients.onServiceReason = 'Location Change',
Patients.Update_Date = Date(Now())
where patients.Unit != Stagetable.Area
OR Patients.Room != Stagetable.Room
and patients.status != 'Discharged';

Still not sure about that AND OR logic though.

...hey, I'll bet you'd enjoy reading my book.

Breck

>
>BEGIN
>
>DECLARE check1 char(128); // that's check ONE......
>DECLARE check2 char(128); // just some temp variables
>that don't do anything
>DECLARE check3 char(128); // just some temp variables
>that don't do anything
>DECLARE check4 char(128); // just some temp variables
>that don't do anything
>DECLARE check5 char(128); // just some temp variables
>that don't do anything
>DECLARE stmt char(512); // our statement to run
>
>DECLARE checkLocations CURSOR FOR
>SELECT patients.Unit, Stagetable.Area, patients.Room,
>Stagetable.Room, Stagetable.BillingNumber FROM
>"DBA"."patients"
>inner join Stagetable on patients.billing_number =
>stagetable.billingnumber
>where patients.Unit != Stagetable.Area OR Patients.Room !=
>Stagetable.Room and patients.status != 'Discharged';
>
>
>////////////////////////////////////////
>
>OPEN checkLocations;
>WHILE SQLCODE = 0 LOOP
>
>FETCH NEXT checkLocations into check1, check2, check3,
>check4, check5;
>
>set stmt = 'UPDATE PATIENTS SET Unit = ' + char(39) + check2
>+ char(39) + ', Room = ' + char(39) + check4 + char(39) + ',
>Status = ' + char(39) + 'CHANGE' + char(39) ||
>+ ', onServiceReason = ' + char(39) + 'Location Change' +
>char(39) + ', Update_Date = Date(Now()) WHERE ' ||
>'Patients.Billing_Number = ' + char(39) + check5 + char(39);
>
>//print stmt;
>// something is keeping this from running just once, so i'm
>trying it 3x as of 2/6/2007
>execute immediate stmt;
>execute immediate stmt;
>execute immediate stmt;
>
>set stmt = '';
>
>FETCH NEXT checkLocations into check1, check2, check3,
>check4, check5;
>END LOOP;
>
>CLOSE checkLocations;
>END


--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


EY Posted on 2007-02-08 11:41:25.0Z
Sender: 895.45cb1964.1804289383@sybase.com
From: EY
Newsgroups: ianywhere.public.general
Subject: Re: Execute Immediate with a Commit?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <45cb1a75.8a0.1681692777@sybase.com>
References: <2miks2dphnccnrkcoroedr9vap1a6ua3uf@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Feb 2007 04:41:25 -0700
X-Trace: forums-1-dub 1170938485 10.22.241.41 (8 Feb 2007 04:41:25 -0700)
X-Original-Trace: 8 Feb 2007 04:41:25 -0700, 10.22.241.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5824
Article PK: 1813

I determined it wasn't working because after it ran, there
were still unmatched rows in the PATIENTS table when being
compared to the data in STAGETABLE. I realized this because
patient locations were not being updated, and after a ton of
troubleshooting, I got this to isolate the issue, whatever
it may be.

All I did was do a join on the PATIENT table with the
STAGETABLE to see how many rows did not have matching
locations after an import. There is a unique identifier in
each table that is the same for each patient, so I'm able to
look it up easily enough.

I have to read up on some of your syntax, I'm not as
familiar with it. I thought I could just put a "COMMIT"
after I do my execute immediate and that'd do the trick, but
I wasn't sure.

Thanks for some ideas.


Rob Waywell Posted on 2007-02-08 13:47:40.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <45c9eb4f@forums-1-dub> <45c9ec1c.74b0.1681692777@sybase.com>
Subject: Re: Execute Immediate with a Commit?
Lines: 54
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: rwaywell-xp2.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-xp2.sybase.com
Message-ID: <45cb380c$1@forums-1-dub>
Date: 8 Feb 2007 06:47:40 -0700
X-Trace: forums-1-dub 1170946060 10.25.98.235 (8 Feb 2007 06:47:40 -0700)
X-Original-Trace: 8 Feb 2007 06:47:40 -0700, rwaywell-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5825
Article PK: 1814

At this point there is no indication that a statement isn't executing. It is
far more likely that all the statements are executing but that some of those
statements succeed while others return error codes. As a starting point on
how to check for errors take a look at:

SQL Anywhere® Server - SQL Usage

Using Procedures, Triggers, and Batches

Errors and warnings in procedures and triggers

Error handling with ON EXCEPTION RESUME

SQL Anywhere® Server - SQL Reference

SQL Language Elements

Special values

SQLCODE special value

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

<EY> wrote in message news:45c9ec1c.74b0.1681692777@sybase.com...
> If that is the case, how would some of the statements get
> executed, and some of them not get executed (if I only list
> the statement 1x, rather than 3x)? That is what was
> confusing me.
>
> What error am I looking for after the statement? Or should
> I just do a message of any possible error? The statements
> all look 100% to me, I have done about 15 prints of the
> actual commands before I actually executed them to be sure.
>
> Thanks for the help so far, hope to get this fixed!