# DB\_EXECUTE with input and output metadata?

**URL:** <https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906>\
**Category:** CloverDX Platform\
**Created:** [August 1, 2011, 12:00am UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906 "2011-08-01T00:00:00Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![nelsonwd](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nelsonwd](https://forum.cloverdx.com/u/nelsonwd)\
**Post date:** [August 1, 2011, 12:00am UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/1 "2011-08-01T00:00:00Z")

</div>

I’m new to ETL. I need to execute a select statement that uses incoming metadata in its where clause and then passes on the results to the output port. The select statement is looking for overlapping regions on a number line so there is no joinKey.  
The input metadata is: location\_id, start\_position, and end\_position.  
The query is:

```auto

SELECT feature_id 
FROM Feature f
WHERE $start_position between f.start_position AND f.end_position 
OR $end_position between f.start_position AND f.end_position

```

The output data is: location\_id, feature\_id

The query can return 0 to n results. I’d prefer not to use a stored procedure because we have multiple DBMS vendors.  
I have not been able to find anything in the components reference that can handle the above. I’d be grateful if someone can point me to an appropriate strategy.

Thanks,  
Bill

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [August 2, 2011, 9:34am UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/2 "2011-08-02T09:34:13Z")

</div>

Hello Bill,  
you need to use [DBJoin component](http://www.cloveretl.com/documentation/UserGuide/topic/com.cloveretl.gui.docs/docs/dbjoin.html). Please see attached graph for detailed settings.

- [selectWithWhere.grf](https://cdn.cloverdx.com/forum/417%5F238fd61298e1b889bfab4b893a6533cf/selectWithWhere.grf)

---

<div class="post-metadata">

**Author:** ![nelsonwd](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nelsonwd](https://forum.cloverdx.com/u/nelsonwd)\
**Post date:** [August 2, 2011, 5:27pm UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/3 "2011-08-02T17:27:35Z")

</div>

Thanks, that worked great!

There is one little issue remaining with the graph though. I’m querying a SYBASE database. The feature\_id field in the Feature table is Numeric(12). With other DB components I map numeric fields to long in the meta data without issue, but the DB\_JOIN component says it cannot convert a decimal to long (even though it is not a decimal, it’s numeric). So, I change the the meta data to decimal instead of long and it works until the index gets too large with the error:  
‘Too many digits before decimal dot, must be 6 at most (1501129704); specified precision [8,2]’

Thanks again for your help,  
Bill

---

<div class="post-metadata">

**Author:** ![nelsonwd](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nelsonwd](https://forum.cloverdx.com/u/nelsonwd)\
**Post date:** [August 2, 2011, 8:14pm UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/4 "2011-08-02T20:14:34Z")

</div>

I was able to create a workaround with a Java transformer. I don’t know if this is the best way but it does work.

```auto
	
        @Override
	public int transform(DataRecord[] source, DataRecord[] target)
			throws TransformException
	{
		target[0].getField("map_element_id").setValue(source[0].getField("map_element_id"));
                // get the long value from the DecimalDataField input data
		long ddf = ((DecimalDataField)source[1].getField("locus_id")).getLong();
                //Create a new LongDataField with the long value
		LongDataField ldf = new LongDataField(target[0].getField("locus_id").getMetadata(),ddf);
                //transfer the LongDataField to the output data 
	   target[0].getField("locus_id").setValue(ldf);
	      
	   return RecordTransform.ALL;
	}

```

Thanks again for your help.  
Bill

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [August 3, 2011, 6:50am UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/5 "2011-08-03T06:50:59Z")

</div>

Hello Bill,  
it should be enough just to change length and scale properties for this field:

- [metadata .png](https://cdn.cloverdx.com/forum/417%5Fe583387b33e3e1bf4a602129ecce8aaa/metadata%5F.png)

---

<div class="post-metadata">

**Author:** ![nelsonwd](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nelsonwd](https://forum.cloverdx.com/u/nelsonwd)\
**Post date:** [August 3, 2011, 3:44pm UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/6 "2011-08-03T15:44:21Z")

</div>

That is definitely easier. I just noticed that there is a"DB Metadata" property for the DBJOIN component. Can I set the feature\_id to **long** there as another alternative?

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [August 4, 2011, 6:53am UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/7 "2011-08-04T06:53:54Z")

</div>

“DB Metadata” attribute defines metadata returned by query (_SELECT feature\_id FROM Feature f_), so only with one field. From above I would expect, that this field should be decimal with length=12 and scale=0.

---

<div class="post-metadata">

**Author:** ![nelsonwd](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nelsonwd](https://forum.cloverdx.com/u/nelsonwd)\
**Post date:** [August 4, 2011, 8:55pm UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/8 "2011-08-04T20:55:47Z")

</div>

It is defined as numeric(12) in our sybase database. For other DB components I’ve used (DB\_INPUT\_TABLE, and DB\_OUTPUT\_TABLE) we use long in the metadata for the id columns with no problem. You don’t think this is a bug with the DB\_JOIN component?

---

<div class="post-metadata">

**Author:** ![avackova](https://avatars.discourse-cdn.com/v4/letter/a/65b543/32.png) [@avackova](https://forum.cloverdx.com/u/avackova)\
**Post date:** [August 5, 2011, 10:01am UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/9 "2011-08-05T10:01:00Z")

</div>

Hello Biil,  
I investigated the issue a bit deeper and realized that your graph throws “_cannot convert from ‘decimal’ to ‘long’_” exception, when database metadata is not defined, but work properly if I the field is of the type _long_ in database metadata. The reason is, that by default NUMERIC field in a database is converted to DECIMAL Clover type and in CTL2 you can’t assign decimal number to a LONG field. It would work if you use implicit conversion:

```auto
	$0.feature_id = decimal2long($1.feature_id);

```

However when you set data base metadata, the type compatibility is proper for CTL2, but transfer from database can be unsuccessful **in the runtime**. In your case it should work fine, but if you, e.g. set the type to INTEGER you could get error like _"Numeric overflow in conversion of value 21,057,340,052 to type INTEGER._

---

<div class="post-metadata">

**Author:** ![nelsonwd](https://avatars.discourse-cdn.com/v4/letter/n/54ee81/32.png) [@nelsonwd](https://forum.cloverdx.com/u/nelsonwd)\
**Post date:** [August 5, 2011, 2:51pm UTC](https://forum.cloverdx.com/t/db-execute-with-input-and-output-metadata/906/10 "2011-08-05T14:51:51Z")

</div>

Thanks for your help.