DB_EXECUTE exist bug, it jump input

CloverDX Platform
1

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

2

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 |
±------±-----------------+

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

Pls, check your metadata because this graph works properly:


<?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>
5

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"?>
6

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.

7

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);

8

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.

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;

9

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?

10

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

11

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.

12

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