I am doing a Select using msqlinput, the result of this query must be send through a tRESTResponse:
If the query has results, I am just sending them and everything is fine:
But If there are no results, I am sending a 404 to the webservice. I would to send an empty list if there are no results from the query. I would like this to be faster, using same txmlmap and trestresponse!
Maybe the problem is that in this case all fields are null.
What if you try to do something like that in you tXMLMap:
row24.target == null ? "" : row24.targeta
and so on the each field?
This is caused because there are no rows passing through the service from the db query. If I understand you correctly, you can put this little hack into your query to allow this to work. Essentially you are forcing at least 1 row to be returned by the query. If your actual query works, great. However if you query returns nothing, you want it to return you an empty row. You can do this like below....you may need to tweak this to work in databases other than SQL Server....
select id, datasource from batch where id = 989334343434 UNION ALL select 0 as id, null as datasource WHERE (select count(*) from batch WHERE id = 989334343434)=0
I've given the exact query that I tested on my machine, but I assume you can rework it to your system. The WHERE Clause in your main query (above the UNION ALL) needs to be used exactly the same in the query used to test if the dummy data query should be fired. Essentially it is saying "if the main query returns at least one row, do not fire the dummy query. However if the main query returns 0 rows, fire the dummy query and give me a single row".
With this you can force an empty XML message to be returned.
You will not get any faster than this I'm afraid. How do you imagine it could be faster? You need to generate a flow of rows to get an XML message generated. You have no rows without this. You *could* use a RunIf somewhere to generate a row using tFixedFlowInput if the result of your first query returns no rows, but rerouting that will take much longer. The tXMLMap cannot generate rows.
Well, it's hard to explain but let's say that my solution should be on TXMLMAP and not on the sql query. I have been using the "select count(*)" but now I need a faster solution. But faster to write code(a lot of work), not faster in execution times.
I thought that txmlmap could have something to detect that there were no rows.