Generate user defined primary key

vvsndinavahi

• October 20, 2009 00:00

• Answered

Follow

Hello everyone,
I would like to input a file and generate a user defined key and check the database table if that key exists or not.
I am thinking of using reformat component as i have to parallely input the component and also generate a key(primary key),check whether this key is unique in the database table and finally insert into this database table but unfortunately reformat has only one input
Example:
text file:
FIELD 1| FIELD 2
A | 1
B |2

database table:
primay key| FIELD 1|FIELD2
AF-2234 | C | 7
AF-2267 | P |15
here i have to insert A|1, B|2 into the databse table with a new user defined primary key.I have the code to generate the primary key.But after generating the key i have to match with the primary key of the database table to check that the new generated key does not exist in the database table as primary key should be unique.

Will anyone please guide me.

• 

  avackova

  • October 21, 2009 08:42

  0

  Comment actions Permalink

  Hello, use DBJoin or LookupJoin component. Following example shows how to put into database new keys:
  

  <?xml version="1.0" encoding="UTF-8"?>
  <Graph  name="lookupJoin" >
  <Global>
  <Metadata id="Metadata0">
  <Record fieldDelimiter="|" name="prop" recordDelimiter="\n" type="delimited">
  <Field name="key" type="integer"/>
  <Field name="value" type="string"/>
  </Record>
  </Metadata>
  <Connection dbConfig="${CONN_DIR}/mysql.cfg" id="Connection0" type="JDBC"/>
  <Property fileURL="workspace.prm" id="GraphParameter0"/>
  <Sequence cached="10" fileURL="${SEQ_DIR}/all.seq" id="Sequence1" name="all" start="1" step="1" type="SIMPLE_SEQUENCE"/>
  <Sequence cached="10" fileURL="${SEQ_DIR}/even.seq" id="Sequence0" name="even" start="1" step="2" type="SIMPLE_SEQUENCE"/>
  <LookupTable dbConnection="Connection0" id="LookupTable0" maxCached="100" metadata="Metadata0" name="dbLookup0" storeNulls="true" type="dbLookup">
  <attr name="sqlQuery"><![CDATA[select my_key as new_key, my_value as value from test where my_key=?]]></attr>
  </LookupTable>
  </Global>
  <Phase number="0">
  <Node dbConnection="Connection0"  id="DB_EXECUTE0" printStatements="true" sqlQuery="drop table test;&#10;create table test(&#10;&#9;my_key int,&#10;&#9;my_value varchar(10)&#10;);" type="DB_EXECUTE"/>
  </Phase>
  <Phase number="1">
  <Node id="DATA_GENERATOR0" recordsNumber="10" type="DATA_GENERATOR">
  <attr name="generate"><![CDATA[//#TL
  
  // Generates output record.
  function generate() {
  	$0.key := sequence(Sequence0).next;
  	$0.value := random_string(3,5);
  }
  
  // Called to return a user-defined error message when an error occurs.
  // function getMessage() {}
  
  // Called during component initialization.
   function init() {
   	sequence(Sequence0).reset;
   }
  
  // Called after the component finishes.
  // function finished() {}
  ]]></attr>
  </Node>
  <Node dbConnection="Connection0" dbTable="agata" id="DB_OUTPUT_TABLE0" type="DB_OUTPUT_TABLE"/>
  <Edge fromNode="DATA_GENERATOR0:0" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="DB_OUTPUT_TABLE0:0"/>
  </Phase>
  <Phase number="2">
  <Node id="DATA_GENERATOR1" recordsNumber="20" type="DATA_GENERATOR">
  <attr name="generate"><![CDATA[//#TL
  
  // Generates output record.
  function generate() {
  	$0.key := sequence(Sequence1).next;
  	$0.value := random_string(3,5);
  }
  
  // Called to return a user-defined error message when an error occurs.
  // function getMessage() {}
  
  // Called during component initialization.
   function init() {
   	sequence(Sequence1).reset;
   }
  
  // Called after the component finishes.
  // function finished() {}
  ]]></attr>
  </Node>
  <Node dbConnection="Connection0" dbTable="test" id="DB_OUTPUT_TABLE1" type="DB_OUTPUT_TABLE"/>
  <Node enabled="enabled" freeLookupTable="true" id="LOOKUP_JOIN0" joinKey="key" lookupTable="LookupTable0" type="LOOKUP_JOIN">
  <attr name="transform"><![CDATA[//#TL
  
  // Transforms input record into output record.
  function transform() {
  	$0.key := $0.key;
  	$0.value := $1.value;
  }
  
  // Called to return a user-defined error message when an error occurs.
  // function getMessage() {}
  
  // Called during component initialization.
  // function init() {}
  
  // Called after the component finishes.
  // function finished() {}
  ]]></attr>
  </Node>
  <Node id="SIMPLE_COPY0" type="SIMPLE_COPY"/>
  <Node id="TRASH0" type="TRASH"/>
  <Edge fromNode="DATA_GENERATOR1:0" id="Edge1" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="SIMPLE_COPY0:0"/>
  <Edge fromNode="LOOKUP_JOIN0:0" id="Edge3" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (joined records)" toNode="TRASH0:0"/>
  <Edge fromNode="LOOKUP_JOIN0:1" id="Edge4" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 1 (skipped records)" toNode="DB_OUTPUT_TABLE1:0"/>
  <Edge fromNode="SIMPLE_COPY0:0" id="Edge2" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="LOOKUP_JOIN0:0"/>
  <Edge fromNode="SIMPLE_COPY0:1" id="Edge5" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 1 (out)" toNode="LOOKUP_JOIN1:0"/>
  </Phase>
  <Phase number="3">
  <Node id="LOOKUP_JOIN1" joinKey="key" lookupTable="LookupTable0" type="LOOKUP_JOIN">
  <attr name="transform"><![CDATA[//#TL
  
  // Transforms input record into output record.
  function transform() {
  	$0.key := $0.key;
  	$0.value := $1.value;
  }
  
  // Called to return a user-defined error message when an error occurs.
  // function getMessage() {}
  
  // Called during component initialization.
  // function init() {}
  
  // Called after the component finishes.
  // function finished() {}
  ]]></attr>
  </Node>
  <Node debugPrint="true" id="TRASH1" type="TRASH"/>
  <Edge fromNode="LOOKUP_JOIN1:0" d="Edge6" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (joined records)" toNode="TRASH1:0"/>
  </Phase>
  </Graph>
  

• 

  vvsndinavahi

  • October 21, 2009 15:20

  0

  Comment actions Permalink

  Thank you very much.

Please sign in to leave a comment.