dbQuery check for null results

Data Integration CData Arc
, , ,
1

Is there a better way to check for null results from a dbQuery, I feel dirty for coming up with this:

<arc:set attr="db.query">select null as something;</arc:set><arc:call op="dbQuery" in="db" out="results">    <arc:if exp="'[results.*]' == '<table></table>'">        <!-- handle null -->    </arc:if>     ...</arc:call>

2

Hi Travis,

I have reviewed your query, and I understand why you added ‘select null as something’ as a check to handle empty results. However, I wanted to provide you with a bit more context. Let me explain a working example script with you.

<arc:set attr="db.driver" value="System.Data.CData.MySql" /><arc:set attr="db.conn" value="Server=localhost;Database=new;UID=****;Password=****;"/><arc:set attr="db.query" value="SELECT * FROM `test`"/><arc:set attr="output.data"><Items><arc:call op="dbQuery" item="db"><test><arc:enum item="db"><arc:if exp="[_attr | startswith('db:')]"><[_attr | split(:,2)]>[_value]</[_attr | split(:,2)]></arc:if></arc:enum><arc:else><arc:throw code="myerror" desc="null Value" /></arc:else></test></arc:call></Items></arc:set><arc:set attr="output.filename" value="test.txt" /><arc:push item="output" />

 

What this script does

  • It sets up a database connection (MySQL in this case).
  • Runs the query SELECT * FROM ‘test’.
  • Iterates through the results (arc:enum item=”db”) and outputs each row/column.
  • If no data is returned, the arc:else branch is triggered and throws a custom error (null Value). You can modify the error description in this line according to your needs: <arc:throw code=“myerror” desc=“null Value” />

So, in effect, you don’t need to rely on comparing the raw <table></table> output — this script gracefully handles the case where no records are returned by explicitly checking and throwing an error if the result is null/empty.

If scripting is the only option in your current use case, this solution works fine. But for cleaner handling and long-term maintainability, we recommend using a dedicated database connector (like the MySQL connector). Connectors usually provide built-in ways to handle empty result sets or errors, so you won’t need to manually check for <table></table> or add workarounds.

I hope this helps you achieve your use case. Please feel free to reach out to us at arcsupport@cdata.com if you have any further questions.