# DB\_EXECUTE exist bug, it jump input

**URL:** <https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290>\
**Category:** CloverDX Platform\
**Created:** [March 4, 2008, 12:00am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290 "2008-03-04T00:00:00Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![hwhwhw](https://avatars.discourse-cdn.com/v4/letter/h/9d8465/32.png) [@hwhwhw](https://forum.cloverdx.com/u/hwhwhw)\
**Post date:** [March 4, 2008, 12:00am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/1 "2008-03-04T00:00:00Z")

</div>

This function of the realization of meaningful, but its existence bug.

DELIMITED\_DATA\_READER-\>DB\_EXECUTE-\>DELIMITED\_DATA\_WRITER

DB\_EXECUTE exist bug, it jump input.

create or replace procedure p\_hw1(args1 in numeric, args2 in varchar2,args3 in date,C1 out varchar2) as  
c\_1 numeric(10,2);  
c\_2 varchar2(50);  
c\_3 date;  
begin  
c\_1 := args1;  
c\_2 := args2;  
c\_3 := args3;  
begin  
insert into hwtest1 (c1,c2,c3) values(c\_1,c\_2,c\_3);  
commit;  
C1:=‘insert’ || c\_1 || ’ Success’;  
Exception WHEN OTHERS Then  
rollback;  
C1 := ‘insert ’ || c\_1 || ’ Failed’ ;  
end;  
end p\_hw1;

in.txt  
1;a;2008-01-01  
2;b;2008-02-02  
3;c;2008-03-03  
4;d;2008-04-04  
5;e;2008-05-05  
6;f;2008-06-06

out.txt  
insert 1 Success  
insert 3 Success  
insert 5 Success

---

<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:** [March 4, 2008, 9:18am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/2 "2008-03-04T09:18:54Z")

</div>

Could you show your graph?  
I have on output:  
±------±-----------------+  
|Record |out |  
±------±-----------------+  
|# 1 |insert1 Success |  
|# 2 |insert2 Success |  
|# 3 |insert3 Success |  
|# 4 |insert4 Success |  
|# 5 |insert5 Success |  
|# 6 |insert6 Success |  
±------±-----------------+

---

<div class="post-metadata">

**Author:** ![hwhwhw](https://avatars.discourse-cdn.com/v4/letter/h/9d8465/32.png) [@hwhwhw](https://forum.cloverdx.com/u/hwhwhw)\
**Post date:** [March 4, 2008, 9:46am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/3 "2008-03-04T09:46:51Z")

</div>

{call p\_hw1(?,?,?,?)}

---

<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:** [March 4, 2008, 10:42am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/4 "2008-03-04T10:42:17Z")

</div>

Pls, check your metadata because this graph works properly:

```auto

<?xml version="1.0" encoding="UTF-8"?>
<Graph created="Mon Jan 28 09:38:32 CET 2008" guiVersion="1.9" id="1201509797271" licenseType="Evaluation license." modified="Tue Mar 04 11:40:38 CET 2008" name="CopyDB" revision="1.61">
<Global>
<Metadata id="Metadata3">
<Record name="mssqltable" recordSize="-1" type="delimited">
<Field delimiter=";" name="Field0" nullable="true" shift="0" type="numeric"/>
<Field delimiter=";" name="Field1" nullable="true" shift="0" type="string"/>
<Field delimiter="\n" format="yyyy-MM-dd" name="Field2" nullable="true" shift="0" type="date"/>
</Record>
</Metadata>
<Metadata id="Metadata1">
<Record name="out" recordSize="-1" type="delimited">
<Field delimiter="\n" name="C1" type="string"/>
</Record>
</Metadata>
<Metadata id="Metadata0">
<Record name="test_table_out" recordSize="-1" type="delimited">
<Field delimiter="," name="id" nullable="true" shift="0" type="integer"/>
<Field delimiter="," name="name" nullable="true" shift="0" type="string"/>
<Field delimiter="," name="email" nullable="true" shift="0" type="string"/>
<Field delimiter="\n" name="active" nullable="true" shift="0" type="integer"/>
</Record>
</Metadata>
<Connection dbConfig="oracle2.cfg" id="Connection1" type="JDBC"/>
</Global>
<Phase number="0">
<Node callStatement="true" dbConnection="Connection1" enabled="enabled" guiHeight="25" guiName="DB_EXECUTE_0" guiWidth="50" guiX="245" guiY="20" id="DB_EXECUTE_0" inParameters="1:=$Field0;2:=$Field1;3:=$Field2" outParameters="4:=$C1" printStatements="true" sqlStatementDelimiter=";" type="DB_EXECUTE">
<attr name="SQLCode"><![CDATA[{call p_hw1(?,?,?,?)}]]></attr>
</Node>
<Node charset="GB18030" dataPolicy="Lenient" enabled="enabled" fileURL="in.txt" guiHeight="25" guiName="DELIMITED_DATA_READER_0" guiWidth="50" guiX="20" guiY="20" id="DELIMITED_DATA_READER_0" numRecords="-1" skipRows="0" type="DELIMITED_DATA_READER"/>
<Node append="false" charset="GB18030" enabled="enabled" fileURL="out.txt" guiHeight="25" guiName="DELIMITED_DATA_WRITER_0" guiWidth="50" guiX="470" guiY="20" id="DELIMITED_DATA_WRITER_0" outputFieldNames="false" recordsPerFile="-1" type="DELIMITED_DATA_WRITER"/>
<Edge fromNode="DB_EXECUTE_0:0" guiBendpoints="" id="TASK_EDGE_1" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (procedure output)" toNode="DELIMITED_DATA_WRITER_0:0"/>
<Edge fromNode="DELIMITED_DATA_READER_0:0" guiBendpoints="" id="TASK_EDGE_0" inPort="Port 0 (input parameters)" metadata="Metadata3" outPort="Port 0 (out)" toNode="DB_EXECUTE_0:0"/>
</Phase>
</Graph>

```

---

<div class="post-metadata">

**Author:** ![hwhwhw](https://avatars.discourse-cdn.com/v4/letter/h/9d8465/32.png) [@hwhwhw](https://forum.cloverdx.com/u/hwhwhw)\
**Post date:** [March 5, 2008, 1:09am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/5 "2008-03-05T01:09:47Z")

</div>

I am sorry to find reasons, it is my metadata in the document Field in.txt more than two Field,  
But I found other problems, MS-SQL database access, as long as outputFields can not empty, its contents and returned to the field in the database did not name any relationship.  
Field metadata in the document name and the database name to return to the field to do.  
I think it was through the field outputFields to control the output of the order?

create table hwtest1 (c1 numeric(10,2),c2 varchar(50),c3 datetime);  
CREATE PROCEDURE p\_hw1 @c1 numeric(10,2),@c2 varchar(40) ,@c3 datetime  
AS  
BEGIN  
insert into hwtest1 values(@c1,@c2,@c3);  
SELECT ‘insert ’ + cast(@c1 as varchar ) + ’ Success’ as c1,@c2 as c2,@c3 as c3 ;  
END;

 {call p\_hw1(?,?,?)} 

dbexe\_DELIMITED\_DATA\_READER\_0\_0\_\_MetaData.fmt

\<?xml version="1.0" encoding="gb2312"?\> 

dbexe\_DELIMITED\_DATA\_WRITER\_0\_0\_\_MetaData.fmt

\<?xml version="1.0" encoding="gb2312"?\>

---

<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:** [March 5, 2008, 11:22am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/6 "2008-03-05T11:22:06Z")

</div>

If you expect procedure will produce result set, you **have** to specify output fields. When output fields are not specified DBExucute component don’t even check if database returns anything.

---

<div class="post-metadata">

**Author:** ![hwhwhw](https://avatars.discourse-cdn.com/v4/letter/h/9d8465/32.png) [@hwhwhw](https://forum.cloverdx.com/u/hwhwhw)\
**Post date:** [March 6, 2008, 1:02am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/7 "2008-03-06T01:02:00Z")

</div>

Oracle database through the cursor result set to return, this approach can be realized?

PL/SQL:  
create or replace package hwTypes  
is  
type cursorType is ref cursor;  
end hwTypes;

create or replace procedure getRec( p\_cursor in **out hwTypes.cursorType** )  
is  
begin  
open p\_cursor for select c1,c2,c3 from hwtest1;  
end getRec;

java code:

String query = “call getRec(?)”;  
CallableStatement cstmt = conn.prepareCall(query);  
cstmt.registerOutParameter(1, **OracleTypes.CURSOR** );  
cstmt.execute();  
ResultSet rset = ( **ResultSet** ) cstmt.getObject(1);

---

<div class="post-metadata">

**Author:** ![hwhwhw](https://avatars.discourse-cdn.com/v4/letter/h/9d8465/32.png) [@hwhwhw](https://forum.cloverdx.com/u/hwhwhw)\
**Post date:** [March 6, 2008, 1:13am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/8 "2008-03-06T01:13:10Z")

</div>

1. 

DB\_EXECUTE nodes, did not detect whether outputFields attribute field in the document and metadata fields real match.

Samples: outputFields attribute is a field, metadata in the document is three fields.

1. 

Metadata field names in the file and storage process of the return of column names are different.

graph:

 {call p\_hw1(?,?,?)} 

dbexe\_DELIMITED\_DATA\_WRITER\_0\_0\_\_MetaData.fmt

\<?xml version="1.0" encoding="gb2312"?\> 

procedure:

SELECT ‘insert ’ + cast(@c1 as varchar ) + ’ Success’ as **c1** ,@c2 as **c2** ,@c3 as **c3** ;

---

<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:** [March 6, 2008, 10:51am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/9 "2008-03-06T10:51:28Z")

</div>

I found and fixed bug with output metadata.  
Database specific behavior will be probably implemented in next Clover version. Could you describe example of usage Oracle cursor type?

---

<div class="post-metadata">

**Author:** ![hwhwhw](https://avatars.discourse-cdn.com/v4/letter/h/9d8465/32.png) [@hwhwhw](https://forum.cloverdx.com/u/hwhwhw)\
**Post date:** [March 7, 2008, 1:55am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/10 "2008-03-07T01:55:15Z")

</div>

Below is a design idea, please refer to,

The expansion in the future, we can enrich the clover types, then add a cursor type, DataFieldMetadata.CURSOR\_FIELD, this type can not achieve the specific details of the nodes DB\_EXECUTE outParameters inParameters and attribute data types through the metadata file transfer,  
This design means good.

In classes12.jar in OracleTypes.java the constants defined below  
public static final int CURSOR = -10;

SQLUtil.java edit documents, modify the jetelType2Sql,

public static int jetelType2sql(char jetelType) {  
…  
case DataFieldMetadata.CURSOR\_FIELD:  
return -10;  
…

}

dbexe\_DELIMITED\_DATA\_READER\_0\_0\_\_MetaData.fmt

\<?xml version="1.0" encoding="gb2312"?\> 

dbexe\_DELIMITED\_DATA\_WRITER\_0\_0\_\_MetaData.fmt

\<?xml version="1.0" encoding="gb2312"?\> 

oracle return cursor result graph(outParameters,outputFields):

\<Node id=“DB\_EXECUTE\_0” type=“DB\_EXECUTE” printStatements=“Y” dbConnection=“testorcl” inTransaction=“N” sqlStatementDelimiter=“;” callStatement=“Y” inParameters=“1:=$Field0;2:=$Field1;3:=$Field2” **outParameters** =“ **4:=cursor** ” **outputFields** =“c1;c2;c3” \>  
{call p\_hw1(?,?,?, **?** )}

oracle return parameters graph (outParameters,outputFields):

\<Node id=“DB\_EXECUTE\_0” type=“DB\_EXECUTE” printStatements=“Y” dbConnection=“testorcl” inTransaction=“N” sqlStatementDelimiter=“;” callStatement=“Y” inParameters=“1:=$Field0;2:=$Field1;3:=$Field2” **outParameters** =“ **4:=integer;5:=string;6:=date** ” **outputFields** =“c1;c2;c3” \>  
{call p\_hw1(?,?,?, **?,?,?** )}

ms-sql return record result graph (outputFields):

\<Node id=“DB\_EXECUTE\_0” type=“DB\_EXECUTE” printStatements=“Y” dbConnection=“testmssql” inTransaction=“N” sqlStatementDelimiter=“;” callStatement=“Y” inParameters=“1:=$Field0;2:=$Field1;3:=$Field2” **outputFields** =“c1;c2;c3” \>  
{call p\_hw1(?,?,?)}

OutParameters attributes is imported clover type, rather than through clover fields name analytical types.

Storage process is the result of a return of property through outputFields

---

<div class="post-metadata">

**Author:** ![bpilla](https://avatars.discourse-cdn.com/v4/letter/b/ac8455/32.png) [@bpilla](https://forum.cloverdx.com/u/bpilla)\
**Post date:** [May 30, 2008, 4:46pm UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/11 "2008-05-30T16:46:39Z")

</div>

Hi:

I created a DB\_Execute that calls a stored proc “procA” with a single OUT ref\_cursor parameter. The out parameter p\_out\_refcursor is a refcursor.  
Can you send me a sample to execute and process the OUT refcursor result?

{call procA (?)}

outParameters=“1:=$p\_out\_refcursor”

procedure procA (  
p\_out\_refcursor out sys\_refcursor  
);

thanks again.

---

<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:** [June 2, 2008, 7:50am UTC](https://forum.cloverdx.com/t/db-execute-exist-bug-it-jump-input/290/12 "2008-06-02T07:50:36Z")

</div>

Hi, while refcursor is Oracle specific feature Clover can’t handle with it. For select statement you should use DBInputTable.