ExecuteSQL rets empty record if no any data retrieved with SQL

[Former Member]

Hi All ))

I sent for ex. "this" :

<DoQuery>

set dateformat ymd

select ItmsGrpCod, ItmsGrpNam, createDate, updateDate from OITB where createDate > '20100101'

</DoQuery>

And I receive this:

<ExecuteSQLResponse xmlns="http://www.sap.com/SBO/DIS">

<BOM><BO><AdmInfo>

<Object>oRecordset</Object>

</AdmInfo>

<OITB><row><ItmsGrpCod>0</ItmsGrpCod><ItmsGrpNam/><createDate/><updateDate/></row></OITB></BO></BOM></ExecuteSQLResponse>

Why? How could I get no records if no data?

Edited by: Slava Vladimirov on Sep 9, 2008 5:09 PM

[Former Member]

Adding "for xml raw" for doesn't add an ampty string - you wrong. MSSQLServer works as it need. No data - no rows. So this is only sap feature.

At any rate thanks for all for your opinions. I will add this stupid ckeck (

[Former Member]

Shure! This row not exist in our database!

I just wrote an example.

Any sql requests works same. If there is no any data returned by sqlserver - diserver adds an empty row.

I just try to know - is it normally? Or maybe some flag in sap exists to fix this "feature"...

[Former Member]

Slava,

I think that thsi behavior is normaly for SBO and for other system too. In result is header of request (defined in sql statement). If you try to make select directly on sql with post fix FOR XML RAW, you will receive the same, it not depends only on SAP.

You should check in returned xml if contains grpcod>0</... and if yes, there is no row returned or change the query as I suggested in first post.

[Nussi]

well, Petr was again faster

i wanted to suggest the same - test other queries and look if a empty select always returns "0".

lg David

[Former Member]

So... any "select" returns at least one row, right? Inspite of sql statement returns nothing... Is it normally for sapb1?

[Nussi]

Slava,

you receive a XML here - you need something back when you executed the query.

i think you should just approve the result, i don't think that <ItmsGrpCod>0</ItmsGrpCod> exist.

lg David

[Former Member]

its because the sql returns header of result (fields from db). If you dont want it, change the query as (just example)

<DoQuery>

set dateformat ymd

if 0 < (select count(*) as x from OITB where createDate > '20100101')

begin

select ItmsGrpCod, ItmsGrpNam, createDate, updateDate from OITB where createDate > '20100101'

end

</DoQuery>

or with case when statement. There is a lot of ways.