Talend call SQL Server stored procedure with table structure list parameter

Purpose: To invoke SQL Server stored procedure with table structure list parameter with Talend

Implementation: Because Talend current components do not seem to implement such a function, and do not want to write a component, so use Tjavarow to encode the implementation.

1, we can first through Maven to download the comparison of new Mssql-jdbc.jar, I download the following jar test!

<Dependency>    <groupId>Com.microsoft.sqlserver</groupId>    <Artifactid>Mssql-jdbc</Artifactid>    <version>6.1.0.jre8</version></Dependency>

2. To build a test job, I built a simple restful interface that calls an example of a SQL Server stored procedure, as follows:

In this example:

Tlibraryload: The main step is to load our first download jar;

Trestrequest: The output body is set to document;

Tjavarow: Write your own code to call a stored procedure with a table structure list parameter, Base Settings code is as follows (this is just testing the sample code and actually modifying and optimizing it according to your needs):

Java.sql.Connection Mssql_conn =NULL; com.microsoft.sqlserver.jdbc.SQLServerCallableStatement mssql_statement=NULL; String RunPlan=NULL; String Runstatus= "";Try{org.dom4j.Document doc=( org.dom4j.Document) ((routines.system.Document) inputdata.body). GetDocument (); Org.dom4j.Element Root=doc.getrootelement (); System.out.println ("Root:" +root.getname ()); Org.dom4j.Element firstplanelement= Root.element ("Plan"); if(Firstplanelement! =NULL) {System.out.println ("Plan:" +Firstplanelement.gettext ()); RunPlan=Firstplanelement.gettext (); } Else{System.out.println ("Plan is null!"); } java.util.List<org.dom4j.Element> itemlistelements = root.elements ("ItemList"); System.out.println ("Itemlistelements:" +itemlistelements.size ()); Com.microsoft.sqlserver.jdbc.SQLServerDataTable SourceTable=Newcom.microsoft.sqlserver.jdbc.SQLServerDataTable (); Sourcetable.addcolumnmetadata ("ItemName", Java.sql.Types.VARCHAR); Sourcetable.addcolumnmetadata ("Weight", Java.sql.Types.DECIMAL); Sourcetable.addcolumnmetadata ("Quantity", Java.sql.Types.DECIMAL); Sourcetable.addcolumnmetadata ("Location", Java.sql.Types.VARCHAR); Sourcetable.addcolumnmetadata ("OrderNo", Java.sql.Types.VARCHAR); Sourcetable.addcolumnmetadata ("Operator", Java.sql.Types.VARCHAR);  for(org.dom4j.Element itemelement:itemlistelements) {org.dom4j.Element firstitemnameelement= Itemelement.element ("ItemName"); Org.dom4j.Element firstweightelement= Itemelement.element ("Weight"); Org.dom4j.Element firstquantityelement= Itemelement.element ("Quantity"); Org.dom4j.Element firstlocationelement= Itemelement.element ("Location"); Org.dom4j.Element firstordernoelement= Itemelement.element ("OrderNo"); Org.dom4j.Element firstoperatorelement= Itemelement.element ("Operator"); Sourcetable.addrow (Firstitemnameelement.gettext (), Double.parsedouble (Firstweightelement.gettext ()), Double.parsedouble (Firstquantityelement.gettext ()), Firstlocationelement.gettext (), Firstordernoelement.gettext (    ), Firstoperatorelement.gettext ()); } String Driverclass= "Com.microsoft.sqlserver.jdbc.SQLServerDriver";    Java.lang.Class.forName (Driverclass); String Mssqlurl= "jdbc:sqlserver://" +context.    Msdb_host; if(!"". Equals ("" +context. Msdb_port)) {Mssqlurl+ = ":" + "+ context." Msdb_port + ";"; }    if(!"". Equals (context. Msdb_database)) {Mssqlurl+ = "Databasename=" + context. Msdb_database + ";"; } Mssqlurl+ = "Appname=" + ProjectName + ";" + ""; Mssql_conn=java.sql.DriverManager.getConnection (mssqlurl, context. Msdb_username, context.    Msdb_password); Mssql_statement= (com.microsoft.sqlserver.jdbc.SQLServerCallableStatement) Mssql_conn.preparecall ("{call" + "[wdpm].[ Usp_savestockitems] "+" (?,?)} "); if(RunPlan = =NULL) {Mssql_statement.setnull (1, Java.sql.Types.VARCHAR); } Else{mssql_statement.setstring (1, RunPlan); } mssql_statement.setstructured (2, "[WDPM]. [Materialitem] ", SourceTable);    Mssql_statement.execute (); Runstatus= "Success";} Catch(Exception e) {runstatus= "Error:" +E.getmessage (); //throw new RuntimeException (e);}finally {    if(mssql_statement!=NULL){        Try{mssql_statement.close (); }Catch(java.sql.SQLException e) {e.printstacktrace (); }    }    if(mssql_conn!=NULL){        Try{mssql_conn.close (); }Catch(java.sql.SQLException e) {e.printstacktrace (); }}}row1. Plan=Runplan;row1. Status= Runstatus;

Advanced Settings code is as follows:

import com.microsoft.sqlserver.jdbc.SQLServerDriver;

So call SQL Server with the table structure list parameters of the stored procedure is basically completed, and other restful return and so on is not in this statement!

The SQL Server stored procedure script used in the example is as follows, for informational purposes only:

To create a custom table type:

CREATETYPE[WDPM].[Materialitem]  as TABLE(    [ItemName] [varchar]( -)NULL,    [Weight] [decimal]( A,4)NULL,    [Quantity] [decimal]( A,4)NULL,    [ Location] [varchar]( -)NULL,    [OrderNo] [varchar]( -)NULL,    [Operator] [varchar]( -)NULL)GO

To create a stored procedure:

Create procedure [WDPM].[Usp_savestockitems] (    @Plan varchar( $),    @ItemList [WDPM].[Materialitem]readonly) asbegin    Declare @WarehouseCode Char(2)= 'FP'    if  not exists(Select 1  from [WDPM].[TestSnapshot] where [Plan] = @Plan)    begin        RAISERROR('Plan%s does not exist! ', -,1,@Plan)        return    End    Insert [WDPM].[testtaking]([Plan],[ItemName],[Weight],[Quantity],[ Location]        , [Warehousecode],[Remark],[Operator]        , [CreatedDate],[CreatedBy],[ModifiedDate],[ModifiedBy])        Select @Plan,[ItemName],[Weight],[Quantity],[ Location]            , @WarehouseCode,"',[Operator]            , getdate(),0,getdate(),0               from @ItemListEnd

SQL Call Example:

Declare @ItemList [WDPM].[Materialitem]Declare @Plan varchar( $)= 'TEST000001'Insert @ItemList(ItemName, Weight, Quantity, location, OrderNo, Operator)SelectFabric_no, Weight, Quantity, location, Note_no,'Angusyang'         from [Fpstore]S with(NOLOCK)where [ Location] = 'Test'exec [WDPM].[Usp_savestockitems] @Plan,@ItemList

Talend call SQL Server stored procedure with table structure list parameter