Friday, 20 October 2017

Cycle Handling in PostGreSQL

Below is the way to handle Cycle data in Postgresql database

 CREATE TABLE FOLDER(ID INT , NAME VARCHAR(255), PARENT INT);

INSERT INTO FOLDER VALUES(1, '/', null);
INSERT INTO FOLDER VALUES(2, 'src', 1);
INSERT INTO FOLDER VALUES(3, 'main', 2);
INSERT INTO FOLDER VALUES(4, 'org', 3);
INSERT INTO FOLDER VALUES(5, 'test', 2);


With RECURSIVE CTE(Id,NAME,Parent,Pathh,path, cycle)
as
(
Select Id,NAME,parent, cast(Id  as varchar(30)) pathh,ARRAY[Folder.Id] path,false from Folder
where parent is null
union all
Select 
F.id,F.name,F.Parent,cast(F.Id||'-->'||C.Pathh as varchar(30))  pathh,
C.Path||F.id path,F.id = any(path) as cycle
from 
Folder F,CTE C
where F.Parent=C.ID  and not cycle
)
Select C.* from CTE C
order by 1 


Friday, 13 October 2017

ORACLE PLSQL Exception Handling



SQL> create table a (x number (10), y number(10);
Table created.

SQL> insert into a values(1, 5);
SQL> insert into a values(2, 10);
SQL> insert into a values(3, 15);
SQL> insert into a values(3, 0);
SQL> insert into a values(4, 20);
SQL> insert into a values(5, 25);
SQL> commit;
SQL> select x, y from a order by x;

X Y
---------- ----------
1 5
2 10
3 15
3 0
4 20
5 25
SQL>
declare
v_temp number:= 30;
v_tot number;
v_x number;
v_y number;
cursor c_temp is
select x, y from a order by x;
begin
open c_temp;
loop
fetch c_temp into v_x, v_y;
exit when c_temp%notfound;
v_tot:=v_x+v_temp/v_y;
dbms_output.put_line('Total is :'||v_tot);
end loop;
close c_temp;
exception
when others then
dbms_output.put_line('Found Oracle error: '||SQLERRM);
end;
/

Total is :7
Total is :5
Total is :5
Found Oracle error: ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

Only Partial Records are Printed. 

Below Code Prints : Complete Numbers 

declare
  v_temp number:= 30;
  v_tot number;
  v_x number;
  v_y number;
  cursor c_temp is
    select x, y from a order by x;
begin
  open c_temp;
  loop
    fetch c_temp into v_x, v_y;
    exit when c_temp%notfound;
    begin
      v_tot:=v_x+v_temp/v_y;
      dbms_output.put_line('Total is :'||v_tot);
    exception
      when others then
      dbms_output.put_line('Found Oracle error: '||SQLERRM);
    end;
  end loop;
  close c_temp;
end;

Thursday, 12 October 2017

PLSQL / Works Cursors with Exception Handling

Simple Stored Procedure to Load the Cursor Dynamically:

create or replace procedure asf_cursor is 
TYPE EmpCurTyp  IS REF CURSOR;
c2  EmpCurTyp;
cursor C1 is (select 1 a from dual union all select 2 a from dual);
dynamic_query varchar2(1000);
i int;
var1 varchar(100);
BEGIN
open c1;
loop
fetch c1 into i;
EXIT WHEN c1%NOTFOUND;
dynamic_query:= 'select '''||'~'||i||'''  from dual';
open c2 for dynamic_query;
  LOOP
    FETCH c2 INTO var1;
    EXIT WHEN c2%NOTFOUND;
  END LOOP;
close c2;
DBMS_OUTPUT.PUT_LINE(var1);
end loop;
close c1;
END; 

set serveroutput on 
execute asf_cursor;

 

Exception Handling for Table Not Found:

-- set serveroutput on;
DECLARE 
TYPE EmpCurTyp  IS REF CURSOR;
c2  EmpCurTyp;
var1 varchar2(30);
drop_tab varchar2(100):='Select * from duall union all Select cast(2 as varchar(5)) from duall';
clob_out clob;
BEGIN
/*
open c2 for select * from duall;
LOOP
    FETCH c2 INTO var1;
    EXIT WHEN c2%NOTFOUND;
    dbms_output.put_line(var1);
    close c2;
  END LOOP;
  */
  open c2 for drop_tab;
  loop
  fetch c2 into var1;
   EXIT WHEN c2%NOTFOUND;
   clob_out:=clob_out||var1;
   end loop;
   close c2;
   dbms_output.put_line(clob_out);
  EXCEPTION
WHEN OTHERS THEN 
IF (SQLCODE = -942) THEN
DBMS_OUTPUT.PUT_LINE('Table Not Found');
clob_out:='~'||null;
dbms_output.put_line(clob_out);
END IF;
END;

Tuesday, 10 October 2017

Setting Different Java Version for Eclipse

  • Open eclipse config file eclipse.ini in your Eclipse folder.
  • Add -vm yourPath\Java\jre6\bin\javaw.exe like:

    -startup  plugins/org.eclipse.equinox.launcher_1.3.0.v20120522-1813.jar  --launcher.library  plugins/org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.200.v20120522-1813  -product  org.eclipse.epp.package.java.product  --launcher.defaultAction  openFile  --launcher.XXMaxPermSize  256M  -vm   C:\Java\jre6\bin\javaw.exe  ...

Friday, 6 October 2017

NT Authentication In Talend

The solution is to place ntlmauth.dll in windows/system32 (or sysWow64) folder. but instead of placing it in system32 folder, we can place it in our own local folder and loaded by our talend jobs while establishing a connection to MS SQL Server.


Design time configuration:

1. Copy ntlmauth.dll to bin folder inside talend installed location. bin folder wont be present, create your own.

2. While executing the job, go to Advanced settings and select "Use Specific JVM arguments" and add the following additional argument

-Djava.library.path=./bin

3. Execute the talend job, it will use windows authentication

Inline image 1


Runtime configuration

1. While creating the package, ensure "bin" folder is also bundled along with ntlmauth.dll
2. Edit the .bat or .sh file and add the following entry

-Djava.library.path=./bin


Inline image 2

3. Execute the batch file, the job should run without any SSO error.

Tuesday, 3 October 2017

Talend Console logs to file

Redirecting Talend Console logs to file

Talend Open studio for data integration does not have any component to redirect console trace to a file. This blog post provides the solution for Redirecting Talend Console logs to file and is very easy to follow and extend. In previous article logging-using-talend we have seen how to customize Talend logs. This article shows how to redirect Talend console logs to file. In talend exchange, there is a custom component to perform similar job.

Solution



Redirecting Talend console logs is required by almost all Talend projects. This is very easy to implement in talend. Use tJava component at the start of any job and enter following lines into it.

Redirecting Talend Console logs

Redirecting Talend Console logs

java.io.File folder = new java.io.File("_logs/");
if (!folder.exists()) {
if (folder.mkdir()) {
System.out.println("Directory is created!");

}
else {
System.out.println("Failed to create directory!");
}
}
java.io.File logFile = new java.io.File("_log/"+projectName+"_.log");
java.io.PrintStream ts = new java.io.PrintStream(new java.io.FileOutputStream(logFile));
System.setOut(ts);
System.setErr(ts);

After executing the job, above code creates a flat file with "projectName.log" and starts writing data which appears on the console. You can create a new file at each execution by post/pre fixing with the time stamp in the file name or use following code. Attached image shows

java.io.File file = new java.io.File(projectName+"_"+TalendDate.getDate("CCYYMMDDhhmmss")+".log");

projectName is the variable name provided by the Talend and you can get the postfix date stamp by another variable TalendDate.getDate("CCYY-MM-DD hh:mm:ss"). Remove all the special characters in the time stamp. ProjectName can also be replaced by another global variable jobName.

After successfully executing this job, you can see ProjectName_TimeStamp.log  file is created in the root folder of Talend. This file contains complete console stack trace.

Drawback



 Drawback of implementing this strategy is that, it will not print or display anything on the console while using IDE or independent execution. This becomes difficult when developer is developing or debugging the job. Because, it is an extra effort for a developer to open a file every time to find out whether the job is executed successfully and generated error or not.

Monday, 2 October 2017

Talend Logging

java.io.File file = new java.io.File("C:/temp/myTalendLog.txt");
java.io.PrintStream ps = new java.io.PrintStream(new java.io.FileOutputStream(file));
System.setOut(ps);