Some database components do not allow the use of existing connection

Highlighted
One Star

Some database components do not allow the use of existing connection

There are some database components that do not allow the use of an existing connection, such as tPostgresqlOutputBulkExec (so that it is impossible to use tPerlConnection)
Is there a reason why it does not exist or you planned to fix it later?
Here is the suggested patch for one component, can somebody go through the list of all components and fix them all?
diff -rN -u old-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_begin.perljet new-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_begin.perljet
--- old-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_begin.perljet 2009-03-25 10:19:28.000000000 -0400
+++ new-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_begin.perljet 2009-03-25 10:19:28.000000000 -0400
@@ -41,12 +41,28 @@
"__ENCODING__"
);

-String dbh = "dbh_" + cid;

List<IMetadataTable> metadatas = node.getMetadataList();
if ((metadatas!=null)&&(metadatas.size()>0)) {
IMetadataTable metadata = metadatas.get(0);
if (metadata!=null) {
+ String useExistingConnection = ElementParameterParser.getValue(
+ node,
+ "__USE_EXISTING_CONNECTION__"
+ );
+
+ String connection = ElementParameterParser.getValue(
+ node,
+ "__CONNECTION__"
+ );
+
+ String dbh = "dbh_";
+ if (useExistingConnection.equals("true")) {
+ dbh+= connection;
+ }
+ else {
+ dbh+= cid;
+ }
%>

use DBI;
@@ -82,6 +98,9 @@
],
);

+<%
+if (useExistingConnection.equals("false")) {
+%>
my $<%=dbh%> = DBI->connect(
sprintf(
'DBI:Pg:dbname=%s;host=%s;port=%s;',
@@ -103,6 +122,10 @@
$<%=dbh%>->do("set names '".<%=encoding %>."'")
or die ' cannot set encoding';

+<%
+}
+%>
+
tPostgresqlOutput::Postgresql::performTableAction(
tableAction => '<%=tableAction%>',
dbschema => <%=dbschema %>,
diff -rN -u old-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_end.perljet new-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_end.perljet
--- old-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_end.perljet 2009-03-25 10:19:28.000000000 -0400
+++ new-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_end.perljet 2009-03-25 10:19:28.000000000 -0400
@@ -13,19 +13,41 @@
INode node = (INode)codeGenArgument.getArgument();
String cid = node.getUniqueName();

-String dbh = "dbh_" + cid;
-
List<IMetadataTable> metadatas = node.getMetadataList();
if ((metadatas!=null)&&(metadatas.size()>0)) {
IMetadataTable metadata = metadatas.get(0);
if (metadata!=null) {
+ String useExistingConnection = ElementParameterParser.getValue(
+ node,
+ "__USE_EXISTING_CONNECTION__"
+ );
+
+ String connection = ElementParameterParser.getValue(
+ node,
+ "__CONNECTION__"
+ );
+
+ String dbh = "dbh_";
+ if (useExistingConnection.equals("true")) {
+ dbh+= connection;
+ }
+ else {
+ dbh+= cid;
+ }
%>
$<%=dbh%>->pg_endcopy
or die ' cannot end copy';
$<%=dbh%>->commit()
or die ' cannot commit';
+<%
+if (useExistingConnection.equals("false")) {
+%>
$<%=dbh%>->disconnect();
<%
+}
+%>
+
+<%
}
}
%>
diff -rN -u old-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_main.perljet new-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_main.perljet
--- old-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_main.perljet 2009-03-25 10:19:28.000000000 -0400
+++ new-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_main.perljet 2009-03-25 10:19:28.000000000 -0400
@@ -16,8 +16,6 @@
INode node = (INode)codeGenArgument.getArgument();
String cid = node.getUniqueName();

-String dbh = "dbh_" + cid;
-
String commitEvery = ElementParameterParser.getValue(
node,
"__COMMIT_EVERY__"
@@ -27,6 +25,23 @@
if (metadatas != null && metadatas.size() > 0) {
IMetadataTable metadata = metadatas.get(0);
if (metadata!=null) {
+ String useExistingConnection = ElementParameterParser.getValue(
+ node,
+ "__USE_EXISTING_CONNECTION__"
+ );
+
+ String connection = ElementParameterParser.getValue(
+ node,
+ "__CONNECTION__"
+ );
+
+ String dbh = "dbh_";
+ if (useExistingConnection.equals("true")) {
+ dbh+= connection;
+ }
+ else {
+ dbh+= cid;
+ }
%>

my $line_<%=cid%> = join("\t", @$<%=cid%>)."\n";
diff -rN -u old-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_perl.xml new-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_perl.xml
--- old-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_perl.xml 2009-03-25 10:19:28.000000000 -0400
+++ new-TOS-All-r20205-V3.0.2/plugins/org.talend.designer.components.localprovider_3.0.2.r20205/components/tPostgresqlOutputBulkExec/tPostgresqlOutputBulkExec_perl.xml 2009-03-25 10:19:28.000000000 -0400
@@ -46,10 +46,27 @@
/>

<PARAMETER
+ NAME="USE_EXISTING_CONNECTION"
+ FIELD="CHECK"
+ NUM_ROW="20"
+ >
+ <DEFAULT>false</DEFAULT>
+ </PARAMETER>
+
+ <PARAMETER
+ NAME="CONNECTION"
+ FIELD="COMPONENT_LIST"
+ FILTER="REGEXP:^t(Postgresql|Perl)*Connection$"
+ NUM_ROW="20"
+ SHOW_IF="USE_EXISTING_CONNECTION == 'true'"
+ />
+
+ <PARAMETER
NAME="HOST"
FIELD="TEXT"
NUM_ROW="30"
REPOSITORY_VALUE="SERVER_NAME"
+ SHOW_IF="USE_EXISTING_CONNECTION == 'false'"
>
<DEFAULT>''</DEFAULT>
</PARAMETER>
@@ -59,6 +76,7 @@
FIELD="TEXT"
NUM_ROW="40"
REPOSITORY_VALUE="PORT"
+ SHOW_IF="USE_EXISTING_CONNECTION == 'false'"
>
<DEFAULT>'5432'</DEFAULT>
</PARAMETER>
@@ -69,6 +87,7 @@
NUM_ROW="50"
REPOSITORY_VALUE="SID"
REQUIRED="true"
+ SHOW_IF="USE_EXISTING_CONNECTION == 'false'"
>
<DEFAULT>''</DEFAULT>
</PARAMETER>
@@ -88,6 +107,7 @@
NUM_ROW="70"
REPOSITORY_VALUE="USERNAME"
REQUIRED="true"
+ SHOW_IF="USE_EXISTING_CONNECTION == 'false'"
>
<DEFAULT>''</DEFAULT>
</PARAMETER>
@@ -98,6 +118,7 @@
NUM_ROW="80"
REPOSITORY_VALUE="PASSWORD"
REQUIRED="true"
+ SHOW_IF="USE_EXISTING_CONNECTION == 'false'"
>
<DEFAULT>''</DEFAULT>
</PARAMETER>
Employee

Re: Some database components do not allow the use of existing connection

There are some database components that do not allow the use of an existing connection, such as tPostgresqlOutputBulkExec (so that it is impossible to use tPerlConnection)
Is there a reason why it does not exist or you planned to fix it later?

The reason is the same that why we've removed the "commit every" option in database output component. So depending on the discussion in your topic 6006 we may change this in the future.
tPostgresqlOutputBulkExec absolutely needs to perform commits. If it shares a connection with another tPostgresqlOutput_1 it will commit the tPostgresqlOutput_1 rows.
Concerning tOracleBulkExec, we don't really use the DBI connection (only to get some information about the table schema to load) because the sqlldr external program is used.
One Star

Re: Some database components do not allow the use of existing connection

The reason is the same that why we've removed the "commit every" option in database output component. So depending on the discussion in your topic 6006 we may change this in the future.

Looks like I am the only one who is interested Smiley Happy
Let me push this topic up.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog