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.

embedded SQL stmt execution

10 posts in DataWindow Last posting was on 2009-05-14 16:31:36.0Z
shk Posted on 2009-05-12 11:13:06.0Z
From: shk <krishnahari18@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: embedded SQL stmt execution
Date: Tue, 12 May 2009 04:13:06 -0700 (PDT)
Organization: http://groups.google.com
Lines: 22
Message-ID: <85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com>
NNTP-Posting-Host: 59.96.48.39
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1242126786 1231 127.0.0.1 (12 May 2009 11:13:06 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 12 May 2009 11:13:06 +0000 (UTC)
Cc: harikrishna.s@xsysys.com
Complaints-To: groups-abuse@google.com
Injection-Info: a5g2000pre.googlegroups.com; posting-host=59.96.48.39; posting-account=3bgqjwoAAADrrBLTF1jYtu1n86yiCXyU
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; FDM),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!a5g2000pre.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89264
Article PK: 418516

Hi All,

iam using PB 8.0 and back end as ASA and SQL server. i code the wrote
as follows in button clicked event

String ls_ids

ls_ids = "'12','22','23'"


DELETE from employee WHERE empl_code IN (:ls_ids)


After execution of this still that records are present in my
database.while executing i did not face any SQL error also.the value
of SQLCA.sqlcode is zero.means that embeded SQL stmt exwcution is
sucess. but still that records are present in my DB.

please let me know any wrong in above menbtioned code.
please help me the same.

thanks is advance.


Chris Pollach Posted on 2009-05-12 11:33:31.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com>
Subject: Re: embedded SQL stmt execution
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a095e8b@forums-1-dub>
Date: 12 May 2009 04:33:31 -0700
X-Trace: forums-1-dub 1242128011 10.22.241.152 (12 May 2009 04:33:31 -0700)
X-Original-Trace: 12 May 2009 04:33:31 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89265
Article PK: 418517

I assume that you have a COMMIT after checking the return status of the
delete command?

--
Regards ... Chris
ISUG - NA RUG Director
http://chrispollach.pbdjmagazine.com

"shk" <krishnahari18@gmail.com> wrote in message
news:85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com...
> Hi All,
>
> iam using PB 8.0 and back end as ASA and SQL server. i code the wrote
> as follows in button clicked event
>
> String ls_ids
>
> ls_ids = "'12','22','23'"
>
>
> DELETE from employee WHERE empl_code IN (:ls_ids)
>
>
> After execution of this still that records are present in my
> database.while executing i did not face any SQL error also.the value
> of SQLCA.sqlcode is zero.means that embeded SQL stmt exwcution is
> sucess. but still that records are present in my DB.
>
> please let me know any wrong in above menbtioned code.
> please help me the same.
>
> thanks is advance.


shk Posted on 2009-05-12 11:58:21.0Z
From: shk <krishnahari18@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: embedded SQL stmt execution
Date: Tue, 12 May 2009 04:58:21 -0700 (PDT)
Organization: http://groups.google.com
Lines: 42
Message-ID: <a9297482-a9c1-4da3-82f2-41ac87448ffb@a5g2000pre.googlegroups.com>
References: <85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com> <4a095e8b@forums-1-dub>
NNTP-Posting-Host: 59.96.48.39
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1242129502 16933 127.0.0.1 (12 May 2009 11:58:22 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 12 May 2009 11:58:22 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: a5g2000pre.googlegroups.com; posting-host=59.96.48.39; posting-account=3bgqjwoAAADrrBLTF1jYtu1n86yiCXyU
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; FDM),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!a5g2000pre.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89266
Article PK: 418518


On May 12, 4:33 pm, "Chris Pollach" <cpoll...@travel-net.com> wrote:
>  I assume that you have a COMMIT after checking the return status of the
> delete command?
>
> --
> Regards ... Chris
> ISUG - NA RUG Directorhttp://chrispollach.pbdjmagazine.com
>
> "shk" <krishnahar...@gmail.com> wrote in message
>
> news:85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com...
>
>
>
> > Hi All,
>
> > iam using PB 8.0 and back end as ASA and SQL server. i code the wrote
> > as follows in button clicked event
>
> > String ls_ids
>
> > ls_ids = "'12','22','23'"
>
> > DELETE from employee WHERE empl_code IN (:ls_ids)
>
> > After execution of this still that records are present in my
> > database.while executing i did not face any SQL error also.the value
> > of SQLCA.sqlcode is zero.means that embeded SQL stmt exwcution is
> > sucess. but still that records are present in my DB.
>
> > please let me know any wrong in above menbtioned code.
> > please help me the same.
>
> > thanks is advance.- Hide quoted text -
>
> - Show quoted text -

Hi chris,

i checked with commit; stmt also after execution of delete stmt. but
still records are present in my DB


Chris Pollach Posted on 2009-05-12 12:42:18.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com> <4a095e8b@forums-1-dub> <a9297482-a9c1-4da3-82f2-41ac87448ffb@a5g2000pre.googlegroups.com>
Subject: Re: embedded SQL stmt execution
Lines: 52
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a096eaa@forums-1-dub>
Date: 12 May 2009 05:42:18 -0700
X-Trace: forums-1-dub 1242132138 10.22.241.152 (12 May 2009 05:42:18 -0700)
X-Original-Trace: 12 May 2009 05:42:18 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89270
Article PK: 418522

a) Are you sure that you are looking at the correct instance of your
database?
b) What does SQLCA.sqlnrows report after the Delete?



"shk" <krishnahari18@gmail.com> wrote in message
news:a9297482-a9c1-4da3-82f2-41ac87448ffb@a5g2000pre.googlegroups.com...

On May 12, 4:33 pm, "Chris Pollach" <cpoll...@travel-net.com> wrote:
> I assume that you have a COMMIT after checking the return status of the
> delete command?
>
> --
> Regards ... Chris
> ISUG - NA RUG Directorhttp://chrispollach.pbdjmagazine.com
>
> "shk" <krishnahar...@gmail.com> wrote in message
>
> news:85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com...
>
>
>
> > Hi All,
>
> > iam using PB 8.0 and back end as ASA and SQL server. i code the wrote
> > as follows in button clicked event
>
> > String ls_ids
>
> > ls_ids = "'12','22','23'"
>
> > DELETE from employee WHERE empl_code IN (:ls_ids)
>
> > After execution of this still that records are present in my
> > database.while executing i did not face any SQL error also.the value
> > of SQLCA.sqlcode is zero.means that embeded SQL stmt exwcution is
> > sucess. but still that records are present in my DB.
>
> > please let me know any wrong in above menbtioned code.
> > please help me the same.
>
> > thanks is advance.- Hide quoted text -
>
> - Show quoted text -

Hi chris,

i checked with commit; stmt also after execution of delete stmt. but
still records are present in my DB


Ivaylo Ivanov Posted on 2009-05-12 12:07:50.0Z
From: "Ivaylo Ivanov" <NOSPAM_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com>
Subject: Re: embedded SQL stmt execution
Lines: 44
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a096696@forums-1-dub>
Date: 12 May 2009 05:07:50 -0700
X-Trace: forums-1-dub 1242130070 10.22.241.152 (12 May 2009 05:07:50 -0700)
X-Original-Trace: 12 May 2009 05:07:50 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89267
Article PK: 418519

try one these variants:
I.
DELETE from employee WHERE charindex(''''||empl_code||'''', :ls_ids) > 0
(this variant puts empl_code column's values around single quotes and
searches the results within your ls_ids string by using MS SQL substring
match. Putting quotes around the column's values before the search is
important when you have values which are substrings of another values!)

II.
string ls_sql
ls_sql = "DELETE from employee WHERE empl_code IN (" + ls_ids + ")"
EXECUTE IMMEDIATE :ls_sql;

What you're missing is that your IN clause compares empl_code column with
the constant "'12','22','23'", not with some array of values.

Ivaylo

"shk" <krishnahari18@gmail.com> wrote in message
news:85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com...
> Hi All,
>
> iam using PB 8.0 and back end as ASA and SQL server. i code the wrote
> as follows in button clicked event
>
> String ls_ids
>
> ls_ids = "'12','22','23'"
>
>
> DELETE from employee WHERE empl_code IN (:ls_ids)
>
>
> After execution of this still that records are present in my
> database.while executing i did not face any SQL error also.the value
> of SQLCA.sqlcode is zero.means that embeded SQL stmt exwcution is
> sucess. but still that records are present in my DB.
>
> please let me know any wrong in above menbtioned code.
> please help me the same.
>
> thanks is advance.


shk Posted on 2009-05-12 12:26:29.0Z
From: shk <krishnahari18@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: embedded SQL stmt execution
Date: Tue, 12 May 2009 05:26:29 -0700 (PDT)
Organization: http://groups.google.com
Lines: 52
Message-ID: <f91f92f9-6d51-4d8d-9f8b-3983fe982fba@d7g2000prl.googlegroups.com>
References: <85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com> <4a096696@forums-1-dub>
NNTP-Posting-Host: 59.96.48.39
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1242131189 22662 127.0.0.1 (12 May 2009 12:26:29 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 12 May 2009 12:26:29 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: d7g2000prl.googlegroups.com; posting-host=59.96.48.39; posting-account=3bgqjwoAAADrrBLTF1jYtu1n86yiCXyU
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; FDM),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!d7g2000prl.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89269
Article PK: 418520

On May 12, 5:07 pm, "Ivaylo Ivanov" <NOSPAM_i.iva...@isy-dc.com>

wrote:
> try one these variants:
> I.
> DELETE from employee WHERE charindex(''''||empl_code||'''', :ls_ids) > 0
> (this variant puts empl_code column's values around single quotes and
> searches the results within your ls_ids string by using MS SQL substring
> match. Putting quotes around the column's values before the search is
> important when you have values which are substrings of another values!)
>
> II.
> string ls_sql
> ls_sql = "DELETE from employee WHERE empl_code IN (" + ls_ids + ")"
> EXECUTE IMMEDIATE :ls_sql;
>
> What you're missing is that your IN clause compares empl_code column with
> the constant "'12','22','23'", not with some array of values.
>
> Ivaylo
>
> "shk" <krishnahar...@gmail.com> wrote in message
>
> news:85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com...
>
>
>
> > Hi All,
>
> > iam using PB 8.0 and back end as ASA and SQL server. i code the wrote
> > as follows in button clicked event
>
> > String ls_ids
>
> > ls_ids = "'12','22','23'"
>
> > DELETE from employee WHERE empl_code IN (:ls_ids)
>
> > After execution of this still that records are present in my
> > database.while executing i did not face any SQL error also.the value
> > of SQLCA.sqlcode is zero.means that embeded SQL stmt exwcution is
> > sucess. but still that records are present in my DB.
>
> > please let me know any wrong in above menbtioned code.
> > please help me the same.
>
> > thanks is advance.- Hide quoted text -
>
> - Show quoted text -

thanks Ivaylo
noe it's working fine
thanks a lot


KL Posted on 2009-05-13 20:43:31.0Z
Sender: 5875.4a0b2b9b.1804289383@sybase.com
From: KL
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: embedded SQL stmt execution
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a0b30f3.5907.1681692777@sybase.com>
References: <85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 May 2009 13:43:31 -0700
X-Trace: forums-1-dub 1242247411 10.22.241.41 (13 May 2009 13:43:31 -0700)
X-Original-Trace: 13 May 2009 13:43:31 -0700, 10.22.241.41
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89277
Article PK: 418531

From Help for DELETE:

"Error handling It is good practice to test the
success/failure code after executing a DELETE statement. To
see if the DELETE was successful, you can test SLQCode for a
failure code. However, if nothing matches the WHERE clause
and no rows are deleted, SQLCode is still set to zero. To
make sure the delete affected at least one row, check the
SQLNRows property of the transaction object. "

Something like (if your trans object is SQLCA):

DELETE from employee WHERE empl_code IN (:ls_ids)
Using SQLCA;
//Check SQLCA codes
// Just for test:
MessageBox('sqlcodes', 'SQLCA.SQLCode: ' +
string(sqlca.sqlcode) + '~nSQLNRows: ' +
string(sqlca.sqlnrows)+ '~nSQLERRText: ' +
string(sqlca.sqlerrtext))

If sqlca.sqlcode = 0 and sqlca.sqlnrows > 0 Then
Commit Using SQLCA;
Else
// Error message....if you want
End If

HTH KL

> Hi All,
>
> iam using PB 8.0 and back end as ASA and SQL server. i
> code the wrote as follows in button clicked event
>
> String ls_ids
>
> ls_ids = "'12','22','23'"
>
>
> DELETE from employee WHERE empl_code IN (:ls_ids)
>
>
> After execution of this still that records are present in
> my database.while executing i did not face any SQL error
> also.the value of SQLCA.sqlcode is zero.means that embeded
> SQL stmt exwcution is sucess. but still that records are
> present in my DB.
>
> please let me know any wrong in above menbtioned code.
> please help me the same.
>
> thanks is advance.


Ivaylo Ivanov Posted on 2009-05-14 07:47:01.0Z
From: "Ivaylo Ivanov" <NOSPAM_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <85faf3bd-0781-442c-b4a6-e77c2c644410@a5g2000pre.googlegroups.com> <4a0b30f3.5907.1681692777@sybase.com>
Subject: Re: embedded SQL stmt execution
Lines: 94
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a0bcc75@forums-1-dub>
Date: 14 May 2009 00:47:01 -0700
X-Trace: forums-1-dub 1242287221 10.22.241.152 (14 May 2009 00:47:01 -0700)
X-Original-Trace: 14 May 2009 00:47:01 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89281
Article PK: 418533

Your check says: "if the statement has been executed successfully and at
least one row was deleted, do COMMIT. Otherwise its ERROR". This means that
you'll raise ERROR even when no rows are deleted at all! Do you think this
situation as an error?

We must say that the check for SQLNRows is a decision of the developer
itself depending on his logic - whether you want to make a multiuser check
or you delete something that's not necessary to exist.

I would do this check on 2 steps, because the issued DELETE statement may
not be the last one in the transaction:

boolean lb_display_warning = FALSE

DELETE from employee WHERE charindex(''''||empl_code||'''', :ls_ids) > 0;
if SQLCA.SQLCode = -1 then // first check for a real DB error
// preserve SQLCA properties for error information
ROLLBACK;
// Error message ...
return
end if

// Second (optional) check for the number of affected rows
if SQLCA.SQLNRows <> 3 then // 3 is the number of ids in the string
variable from the example
lb_display_warning = TRUE
end if

// other SQL statements ...

COMMIT;
if lb_display_warning then
MessageBox("Warning", "Some of the selected employees have already been
deleted meanwhile!")
end if

Regards,
Ivaylo

<KL> wrote in message news:4a0b30f3.5907.1681692777@sybase.com...
> From Help for DELETE:
>
> "Error handling It is good practice to test the
> success/failure code after executing a DELETE statement. To
> see if the DELETE was successful, you can test SLQCode for a
> failure code. However, if nothing matches the WHERE clause
> and no rows are deleted, SQLCode is still set to zero. To
> make sure the delete affected at least one row, check the
> SQLNRows property of the transaction object. "
>
> Something like (if your trans object is SQLCA):
>
> DELETE from employee WHERE empl_code IN (:ls_ids)
> Using SQLCA;
> //Check SQLCA codes
> // Just for test:
> MessageBox('sqlcodes', 'SQLCA.SQLCode: ' +
> string(sqlca.sqlcode) + '~nSQLNRows: ' +
> string(sqlca.sqlnrows)+ '~nSQLERRText: ' +
> string(sqlca.sqlerrtext))
>
> If sqlca.sqlcode = 0 and sqlca.sqlnrows > 0 Then
> Commit Using SQLCA;
> Else
> // Error message....if you want
> End If
>
> HTH KL
>
>> Hi All,
>>
>> iam using PB 8.0 and back end as ASA and SQL server. i
>> code the wrote as follows in button clicked event
>>
>> String ls_ids
>>
>> ls_ids = "'12','22','23'"
>>
>>
>> DELETE from employee WHERE empl_code IN (:ls_ids)
>>
>>
>> After execution of this still that records are present in
>> my database.while executing i did not face any SQL error
>> also.the value of SQLCA.sqlcode is zero.means that embeded
>> SQL stmt exwcution is sucess. but still that records are
>> present in my DB.
>>
>> please let me know any wrong in above menbtioned code.
>> please help me the same.
>>
>> thanks is advance.


KL Posted on 2009-05-14 14:33:55.0Z
Sender: 5875.4a0b2b9b.1804289383@sybase.com
From: KL
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: embedded SQL stmt execution
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a0c2bd3.74e6.1681692777@sybase.com>
References: <4a0bcc75@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 May 2009 07:33:55 -0700
X-Trace: forums-1-dub 1242311635 10.22.241.41 (14 May 2009 07:33:55 -0700)
X-Original-Trace: 14 May 2009 07:33:55 -0700, 10.22.241.41
Lines: 119
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89288
Article PK: 418540

Yes, you are correct, but: 1. OP did not indicate any
other statements in the transactions. 2. OP indictaed he
EXPECTED this particular statement to do a delete, so not
doing the delete is an error to the OP.
Remember, OP said sqlcode = 0, but NO deletes were taking
place.
He was suggesting that the sqlcode = 0 'was wrong'. (We know
that is not true.)

My script is really just to help him get started. The
message just confirms that no rows were deleted. It's up to
the OP to 'prove' that they should have been and to discover
why not.

Once he gets that working satisfactorily, he can go to a
more sophisticated approach such as that you have suggested.


JMTSW KL

> Your check says: "if the statement has been executed
> successfully and at least one row was deleted, do COMMIT.
> Otherwise its ERROR". This means that you'll raise ERROR
> even when no rows are deleted at all! Do you think this
> situation as an error?
>
> We must say that the check for SQLNRows is a decision of
> the developer itself depending on his logic - whether you
> want to make a multiuser check or you delete something
> that's not necessary to exist.
>
> I would do this check on 2 steps, because the issued
> DELETE statement may not be the last one in the
> transaction:
>
> boolean lb_display_warning = FALSE
>
> DELETE from employee WHERE charindex(''''||empl_code||''''
> , :ls_ids) > 0; if SQLCA.SQLCode = -1 then // first check
> for a real DB error
> // preserve SQLCA properties for error information
> ROLLBACK;
> // Error message ...
> return
> end if
>
> // Second (optional) check for the number of affected rows
> if SQLCA.SQLNRows <> 3 then // 3 is the number of ids in
> the string variable from the example
> lb_display_warning = TRUE
> end if
>
> // other SQL statements ...
>
> COMMIT;
> if lb_display_warning then
> MessageBox("Warning", "Some of the selected employees
> have already been deleted meanwhile!")
> end if
>
> Regards,
> Ivaylo
>
> <KL> wrote in message
> > news:4a0b30f3.5907.1681692777@sybase.com... From Help
> for DELETE: >
> > "Error handling It is good practice to test the
> > success/failure code after executing a DELETE statement.
> > To see if the DELETE was successful, you can test
> > SLQCode for a failure code. However, if nothing matches
> > the WHERE clause and no rows are deleted, SQLCode is
> > still set to zero. To make sure the delete affected at
> > least one row, check the SQLNRows property of the
> transaction object. " >
> > Something like (if your trans object is SQLCA):
> >
> > DELETE from employee WHERE empl_code IN (:ls_ids)
> > Using SQLCA;
> > //Check SQLCA codes
> > // Just for test:
> > MessageBox('sqlcodes', 'SQLCA.SQLCode: ' +
> > string(sqlca.sqlcode) + '~nSQLNRows: ' +
> > string(sqlca.sqlnrows)+ '~nSQLERRText: ' +
> > string(sqlca.sqlerrtext))
> >
> > If sqlca.sqlcode = 0 and sqlca.sqlnrows > 0 Then
> > Commit Using SQLCA;
> > Else
> > // Error message....if you want
> > End If
> >
> > HTH KL
> >
> >> Hi All,
> >>
> >> iam using PB 8.0 and back end as ASA and SQL server. i
> >> code the wrote as follows in button clicked event
> >>
> >> String ls_ids
> >>
> >> ls_ids = "'12','22','23'"
> >>
> >>
> >> DELETE from employee WHERE empl_code IN (:ls_ids)
> >>
> >>
> >> After execution of this still that records are present
> in >> my database.while executing i did not face any SQL
> error >> also.the value of SQLCA.sqlcode is zero.means
> that embeded >> SQL stmt exwcution is sucess. but still
> that records are >> present in my DB.
> >>
> >> please let me know any wrong in above menbtioned code.
> >> please help me the same.
> >>
> >> thanks is advance.
>
>


Ivaylo Ivanov Posted on 2009-05-14 16:31:36.0Z
From: "Ivaylo Ivanov" <NOSPAM_i.ivanov@isy-dc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4a0bcc75@forums-1-dub> <4a0c2bd3.74e6.1681692777@sybase.com>
Subject: Re: embedded SQL stmt execution
Lines: 134
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a0c4768@forums-1-dub>
Date: 14 May 2009 09:31:36 -0700
X-Trace: forums-1-dub 1242318696 10.22.241.152 (14 May 2009 09:31:36 -0700)
X-Original-Trace: 14 May 2009 09:31:36 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89289
Article PK: 418542

I think we've moved the theme to another topic - error trapping and
multiuser.
OP's question was simply why he gets SQLCode of 0 (successfull DB
operation), but the records still existed - it was the original topic
question. He was doing a DELETE statement which contained improperly formed
WHERE clause - "IN" usage was incorrect.

Everything we discuss here is out of the main topic :-)

Regards,
Ivaylo

<KL> wrote in message news:4a0c2bd3.74e6.1681692777@sybase.com...
> Yes, you are correct, but: 1. OP did not indicate any
> other statements in the transactions. 2. OP indictaed he
> EXPECTED this particular statement to do a delete, so not
> doing the delete is an error to the OP.
> Remember, OP said sqlcode = 0, but NO deletes were taking
> place.
> He was suggesting that the sqlcode = 0 'was wrong'. (We know
> that is not true.)
>
> My script is really just to help him get started. The
> message just confirms that no rows were deleted. It's up to
> the OP to 'prove' that they should have been and to discover
> why not.
>
> Once he gets that working satisfactorily, he can go to a
> more sophisticated approach such as that you have suggested.
>
>
> JMTSW KL
>
>
>> Your check says: "if the statement has been executed
>> successfully and at least one row was deleted, do COMMIT.
>> Otherwise its ERROR". This means that you'll raise ERROR
>> even when no rows are deleted at all! Do you think this
>> situation as an error?
>>
>> We must say that the check for SQLNRows is a decision of
>> the developer itself depending on his logic - whether you
>> want to make a multiuser check or you delete something
>> that's not necessary to exist.
>>
>> I would do this check on 2 steps, because the issued
>> DELETE statement may not be the last one in the
>> transaction:
>>
>> boolean lb_display_warning = FALSE
>>
>> DELETE from employee WHERE charindex(''''||empl_code||''''
>> , :ls_ids) > 0; if SQLCA.SQLCode = -1 then // first check
>> for a real DB error
>> // preserve SQLCA properties for error information
>> ROLLBACK;
>> // Error message ...
>> return
>> end if
>>
>> // Second (optional) check for the number of affected rows
>> if SQLCA.SQLNRows <> 3 then // 3 is the number of ids in
>> the string variable from the example
>> lb_display_warning = TRUE
>> end if
>>
>> // other SQL statements ...
>>
>> COMMIT;
>> if lb_display_warning then
>> MessageBox("Warning", "Some of the selected employees
>> have already been deleted meanwhile!")
>> end if
>>
>> Regards,
>> Ivaylo
>>
>> <KL> wrote in message
>> > news:4a0b30f3.5907.1681692777@sybase.com... From Help
>> for DELETE: >
>> > "Error handling It is good practice to test the
>> > success/failure code after executing a DELETE statement.
>> > To see if the DELETE was successful, you can test
>> > SLQCode for a failure code. However, if nothing matches
>> > the WHERE clause and no rows are deleted, SQLCode is
>> > still set to zero. To make sure the delete affected at
>> > least one row, check the SQLNRows property of the
>> transaction object. " >
>> > Something like (if your trans object is SQLCA):
>> >
>> > DELETE from employee WHERE empl_code IN (:ls_ids)
>> > Using SQLCA;
>> > //Check SQLCA codes
>> > // Just for test:
>> > MessageBox('sqlcodes', 'SQLCA.SQLCode: ' +
>> > string(sqlca.sqlcode) + '~nSQLNRows: ' +
>> > string(sqlca.sqlnrows)+ '~nSQLERRText: ' +
>> > string(sqlca.sqlerrtext))
>> >
>> > If sqlca.sqlcode = 0 and sqlca.sqlnrows > 0 Then
>> > Commit Using SQLCA;
>> > Else
>> > // Error message....if you want
>> > End If
>> >
>> > HTH KL
>> >
>> >> Hi All,
>> >>
>> >> iam using PB 8.0 and back end as ASA and SQL server. i
>> >> code the wrote as follows in button clicked event
>> >>
>> >> String ls_ids
>> >>
>> >> ls_ids = "'12','22','23'"
>> >>
>> >>
>> >> DELETE from employee WHERE empl_code IN (:ls_ids)
>> >>
>> >>
>> >> After execution of this still that records are present
>> in >> my database.while executing i did not face any SQL
>> error >> also.the value of SQLCA.sqlcode is zero.means
>> that embeded >> SQL stmt exwcution is sucess. but still
>> that records are >> present in my DB.
>> >>
>> >> please let me know any wrong in above menbtioned code.
>> >> please help me the same.
>> >>
>> >> thanks is advance.
>>
>>