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
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
-
<Node id="DELIMITED_DATA_READER_0" type="DELIMITED_DATA_READER" fileURL="c:/test/in.txt" charset="GB18030" skipRows="0" dataPolicy="Lenient" numRecords="-1" />
<Node id="DB_EXECUTE_0" type="DB_EXECUTE" printStatements="Y" dbConnection="testorcl133" inTransaction="N" sqlStatementDelimiter=";" callStatement="Y" inParameters="1:=$Field0;2:=$Field1;3:=$Field2" outParameters="4:=$C1" >
<attr name="SQLCode">{call p_hw1(?,?,?,?)}</attr>
</Node>
<Node id="DELIMITED_DATA_WRITER_0" type="DELIMITED_DATA_WRITER" fileURL="c:/test/out.txt" charset="GB18030" append="false" outputFieldNames="false" recordsPerFile="-1"/>
<Edge id="TASK_EDGE_0" fromNode="DELIMITED_DATA_READER_0:0" toNode="DB_EXECUTE_0:0" metadata="dbexe_DELIMITED_DATA_READER_0_0__MetaData" />
<Edge id="TASK_EDGE_1" fromNode="DB_EXECUTE_0:0" toNode="DELIMITED_DATA_WRITER_0:0" metadata="dbexe_DELIMITED_DATA_WRITER_0_0__MetaData" /> -
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> -
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;
<Node id="DELIMITED_DATA_READER_0" type="DELIMITED_DATA_READER" fileURL="c:/test/in.txt" charset="GB18030" skipRows="0" dataPolicy="Lenient" numRecords="-1" />
<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=" " >
<attr name="SQLCode">{call p_hw1(?,?,?)}</attr>
</Node>
<Node id="DELIMITED_DATA_WRITER_0" type="DELIMITED_DATA_WRITER" fileURL="c:/test/out.txt" charset="GB18030" append="false" outputFieldNames="false" recordsPerFile="-1"/>
<Edge id="TASK_EDGE_0" fromNode="DELIMITED_DATA_READER_0:0" toNode="DB_EXECUTE_0:0" metadata="dbexe_DELIMITED_DATA_READER_0_0__MetaData" />
<Edge id="TASK_EDGE_1" fromNode="DB_EXECUTE_0:0" toNode="DELIMITED_DATA_WRITER_0:0" metadata="dbexe_DELIMITED_DATA_WRITER_0_0__MetaData" />
dbexe_DELIMITED_DATA_READER_0_0__MetaData.fmt
<?xml version="1.0" encoding="gb2312"?>
<Record name="" type="delimited">
<Field name="Field0" type="numeric" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="Field1" type="string" format="" nullable="true" default="" size="2" length="2" scale="0" delimiter=";"/>
<Field name="Field2" type="date" format="yyyy-MM-dd HH:mm:ss" nullable="true" default="" size="5" length="5" scale="0" delimiter="\n"/>
<!--
<Field name="Field3" type="string" format="" nullable="true" default="" size="2" length="2" scale="0" delimiter=";"/>
<Field name="Field4" type="string" format="" nullable="true" default="" size="4" length="4" scale="0" delimiter="\n"/>
-->
</Record>
dbexe_DELIMITED_DATA_WRITER_0_0__MetaData.fmt
<?xml version="1.0" encoding="gb2312"?>
<Record name="" type="delimited">
<Field name="Ca1" type="string" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="Ca2" type="string" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="Ca3" type="date" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter="\n"/>
</Record> -
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); -
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.
2)
Metadata field names in the file and storage process of the return of column names are different.
graph:
<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="randomFieldName" >
<attr name="SQLCode">{call p_hw1(?,?,?)}</attr>
</Node>
dbexe_DELIMITED_DATA_WRITER_0_0__MetaData.fmt
<?xml version="1.0" encoding="gb2312"?>
<Record name="" type="delimited">
<Field name="Ca1" type="string" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="Ca2" type="string" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="Ca3" type="date" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter="\n"/>
</Record>
procedure:
SELECT 'insert ' + cast(@c1 as varchar ) + ' Success' as c1,@c2 as c2,@c3 as c3; -
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"?>
<Record name="" type="delimited">
<Field name="Field0" type="integer" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="Field1" type="string" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="Field2" type="date" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter="\n"/>
</Record>
dbexe_DELIMITED_DATA_WRITER_0_0__MetaData.fmt
<?xml version="1.0" encoding="gb2312"?>
<Record name="" type="delimited">
<Field name="C1" type="string" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="C2" type="string" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter=";"/>
<Field name="C3" type="date" format="" nullable="true" default="" size="1" length="1" scale="0" delimiter="\n"/>
</Record>
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" >
<attr name="SQLCode">{call p_hw1(?,?,?,?)}</attr>
</Node>
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" >
<attr name="SQLCode">{call p_hw1(?,?,?,?,?,?)}</attr>
</Node>
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" >
<attr name="SQLCode">{call p_hw1(?,?,?)}</attr>
</Node>
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 -
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.
Please sign in to leave a comment.
Comments 11