Skip to content

can not insert into a table with always encrypted cols using executeBatch #1360

Closed
#1378
Closed
can not insert into a table with always encrypted cols using executeBatch#1360
#1378
@shayannyc25

Description

@shayannyc25
shayannyc25
opened on Jun 15, 2020

Driver version

8.3.1.jre8-preview

SQL Server version

Microsoft SQL Azure (RTM) - 12.0.2000.8 May 15 2020 00:47:08

Client Operating System

Windows 10

JAVA/JVM version

openjdk version "1.8.0_252"
OpenJDK Runtime Environment (Zulu 8.46.0.19-win64)-Microsoft-Azure-restricted (build 1.8.0_252-b14)
OpenJDK 64-Bit Server VM (Zulu 8.46.0.19-win64)-Microsoft-Azure-restricted (build 25.252-b14, mixed mode)

Table schema

/****** Object: Table [dbo].[TACCT_EventHub] Script Date: 6/15/2020 11:10:14 PM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TACCT_EventHub](
	[CDE_ACCT_ENTITY] [char](15) NULL,
	[CDE_ACCT_STA] [char](1) NULL,
	[CDE_ACCT_SUB_TYP] [char](6) NULL,
	[CDE_ACCT_TYP] [char](6) NULL,
	[CDE_DATA_SRC] [char](15) NULL,
	[CDE_FIN_INST] [char](6) NULL,
	[IDN_ACCT] [char](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[IDN_ACCT_ROW] [int] NULL,
	[IDN_CREAT_PGM] [char](10) NULL,
	[IDN_CREAT_USER] [char](20) NULL,
	[IDN_DLET_USER] [char](20) NULL,
	[IDN_LST_UPDT_USER] [char](20) NULL,
	[IDN_LST_UPDT_PGM] [char](10) NULL,
	[IND_DLET] [char](1) NULL,
	[NUM_ACCT] [decimal](7, 0) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[NUM_FA] [decimal](3, 0) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[NUM_OFFICE] [decimal](3, 0) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[TMS_ACCT_STA_LST_UPDT] [datetime] NULL,
	[TMS_CREAT] [datetime] NULL,
	[TMS_DLET] [datetime] NULL,
	[TMS_LST_UPDT] [datetime] NULL,
	[TMS_XTRNL_ACCT_FEFR_ID] [datetime] NULL,
	[afDPFireTS] [datetime] NULL,
	[afDCFireTS] [datetime] NULL,
	[durationBeforeSqlSave] [char](10) NULL
) ON [PRIMARY]
GO

Problem description

  1. Expected behaviour: trying to insert into a table with Always encrypted cols using azure java function in a batch (using SQLServerPreparedStatement.executeBatch()). Same code works fine if i change it to use auto commit and insert one row at a time using SQLServerPreparedStatement.executeUpdate() (not batch).

  2. Actual behaviour: getting several errors

  3. Error message/stack trace:

com.microsoft.sqlserver.jdbc.SQLServerException: The incoming tabular data stream (TDS) protocol stream is incorrect. The TDS headers contained errors.

java.sql.BatchUpdateException: A result set was generated for update.

SEVERE: ConnectionID:1 ClientConnectionId: 6af635d7-434c-45eb-bb9c-3f9e3da813e3: Connection.commit: Encountered TDS_COLMETADATA (0x81). SHOWPLAN is ON, ignoring.

  1. Any other details that can be helpful:

JDBC trace logs

Reproduction code

Below is a code for Java Azure Function, running latest azure function run time (3), getting events from Event Hub and inserting into a table with always encrypted cols.

package com.mswm.azure.java;

import com.microsoft.azure.functions.annotation.*;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.microsoft.azure.functions.*;
import java.util.*;
import java.sql.Connection;
import java.sql.Driver; 
import java.sql.DriverManager;
import java.sql.Timestamp;
import java.math.BigDecimal;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;


/**
 * Azure Functions with Event Hub trigger.
 */
public class EventHubTriggerJavaToSQLDB {
    static String connectionUrl =  "jdbc:sqlserver://xxx.database.windows.net:1433;user=xxx;password=xxxxx;database=xxxx;columnEncryptionSetting=Enabled;keyVaultProviderClientId=xxxxxxxx;keyVaultProviderClientKey=xxxxx;encrypt=true;trustServerCertificate=false;sendStringParametersAsUnicode=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;DescribeInputParameters=DESCRIBEALL;DescribeOutputParameters=DESCRIBEALL";
    static Connection connection;
    static SQLServerPreparedStatement  statement;
   
    static{
        try {
                if(connection == null){
                    connection = DriverManager.getConnection(connectionUrl);
                    connection.setAutoCommit(false);
                } 
                //PreparedStatement
                if(statement == null){
                    statement = (SQLServerPreparedStatement)connection.prepareStatement("INSERT INTO [dbo].[TACCT_EventHub] values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                }      
            }catch(Exception e){
            System.out.println("Exception while inserting="+e.toString());
           // e.printStackTrace();
        }
    }
/*@FunctionName("Warmup")
    public void warmUpFunction(ExecutionContext warmupContext){
        try{
            connection = DriverManager.getConnection(connectionUrl);
            warmupContext.getLogger().info("EventHubTriggerJavaToSQLDB.WarmUp function executed.");

        }catch (SQLException e) {
            warmupContext.getLogger().info("Exception while getting="+e.toString());
            e.printStackTrace();
        }  
    }
    */
 
    /**
     * This function will be invoked when an event is received from Event Hub.
     */
    @FunctionName("EventHubTriggerJavaToSQLDB")
    public synchronized void run(
        @EventHubTrigger(name = "message", eventHubName = "eventhub2", connection = "wmarcheventhubshayan_shayanEventHubSecPolicy_EVENTHUB", consumerGroup = "testeventhubconsumergroup", cardinality = Cardinality.MANY) String[] messages,
        final ExecutionContext context
    ){
       
        //context.getLogger().info("Java Event Hub Java to SQL DB trigger function executed.");
        //context.getLogger().info("Length:" + messages.length);
        //message.forEach(singleMessage -> context.getLogger().info(singleMessage));
        Timestamp afDCFireTS =  new Timestamp((new Date()).getTime());
                   
                try{   
                        for (int i=0; i< messages.length ;i++){
                            String rawJsonData = messages[i];
                           
                           // context.getLogger().info("Raw Data:"+rawJsonData.toString());
                            ObjectMapper oM = new ObjectMapper();
                            Map<String,Object> dataPoint = oM.readValue(rawJsonData, Map.class);
                            //context.getLogger().info("Json parsed Data:"+dataPoint.toString());

                            String CDE_ACCT_ENTITY = (String)dataPoint.get("CDE_ACCT_ENTITY");
                            String CDE_ACCT_STA = (String)dataPoint.get("CDE_ACCT_STA");
                            String CDE_ACCT_SUB_TYP =(String) dataPoint.get("CDE_ACCT_SUB_TYP");
                            String CDE_ACCT_TYP = (String)dataPoint.get("CDE_ACCT_TYP");
                            String CDE_DATA_SRC = (String)dataPoint.get("CDE_DATA_SRC");
                            String CDE_FIN_INST =(String) dataPoint.get("CDE_FIN_INST");
                            String IDN_ACCT = (String)dataPoint.get("IDN_ACCT");
                            int IDN_ACCT_ROW = Integer.parseInt((String)dataPoint.get("IDN_ACCT_ROW"));
                            String IDN_CREAT_PGM =(String) dataPoint.get("IDN_CREAT_PGM");
                            String IDN_CREAT_USER =(String) dataPoint.get("IDN_CREAT_USER");
                            String IDN_DLET_USER = (String)dataPoint.get("IDN_DLET_USER");
                            String IDN_LST_UPDT_USER = (String)dataPoint.get("IDN_LST_UPDT_USER");
                            String IDN_LST_UPDT_PGM = (String)dataPoint.get("IDN_LST_UPDT_PGM");
                            String IND_DLET = (String)dataPoint.get("IND_DLET");
                            BigDecimal NUM_ACCT = new BigDecimal((String)dataPoint.get("NUM_ACCT"));
                            NUM_ACCT = NUM_ACCT.setScale(7);
                            BigDecimal NUM_FA = new BigDecimal((String)dataPoint.get("NUM_FA"));
                            NUM_FA = NUM_FA.setScale(3);
                            BigDecimal NUM_OFFICE = new BigDecimal((String)dataPoint.get("NUM_OFFICE"));
                            NUM_OFFICE = NUM_OFFICE.setScale(3);
                            Timestamp TMS_ACCT_STA_LST_UPDT = Timestamp.valueOf((String)dataPoint.get("TMS_ACCT_STA_LST_UPDT"));
                            Timestamp TMS_CREAT =Timestamp.valueOf((String)dataPoint.get("TMS_CREAT"));
                            Timestamp TMS_DLET =Timestamp.valueOf((String)dataPoint.get("TMS_DLET"));
                            Timestamp TMS_LST_UPDT =Timestamp.valueOf((String)dataPoint.get("TMS_LST_UPDT"));
                            Timestamp TMS_XTRNL_ACCT_FEFR_ID   =Timestamp.valueOf((String)dataPoint.get("TMS_XTRNL_ACCT_FEFR_ID")); 
                            Timestamp afDPFireTS   =Timestamp.valueOf((String)dataPoint.get("afDPFireTS"));
                            
                            
                    

                            statement.setString(1,CDE_ACCT_ENTITY);
                            statement.setString(2,CDE_ACCT_STA);
                            statement.setString(3,CDE_ACCT_SUB_TYP);
                            statement.setString(4,CDE_ACCT_TYP);
                            statement.setString(5, CDE_DATA_SRC);
                            statement.setString(6,CDE_FIN_INST);
                            statement.setString(7,IDN_ACCT);
                            statement.setInt(8,IDN_ACCT_ROW);
                            statement.setString(9,IDN_CREAT_PGM);
                            statement.setString(10,IDN_CREAT_USER);
                            statement.setString(11,IDN_DLET_USER);
                            statement.setString(12,IDN_LST_UPDT_USER);
                            statement.setString(13,IDN_LST_UPDT_PGM);
                            statement.setString(14,IND_DLET);
                            statement.setBigDecimal(15,NUM_ACCT,7,0);
                            statement.setBigDecimal(16,NUM_FA,3,0);
                            statement.setBigDecimal(17,NUM_OFFICE,3,0);
                            statement.setTimestamp(18,TMS_ACCT_STA_LST_UPDT);
                            statement.setTimestamp(19,TMS_CREAT);
                            statement.setTimestamp(20,TMS_DLET);
                            statement.setTimestamp(21,TMS_LST_UPDT);
                            statement.setTimestamp(22,TMS_XTRNL_ACCT_FEFR_ID);

                            statement.setTimestamp(23,afDPFireTS);
                            statement.setTimestamp(24,afDCFireTS);
                            statement.setTimestamp(25,null);

                            statement.addBatch();
                           
                        } 
                        statement.executeBatch();
                        //int[] resultsArray = statement.executeBatch();
                        //for(int j=0;j<resultsArray.length ; j++ ){
                         //   if(resultsArray[j] != 1){

                           // }

                        //}

                        connection.commit();
                        statement.clearBatch();
                        //int  result  = statement.executeUpdate();
                            //context.getLogger().info("insert for data successfull for data row["+i+"],result code="+result);
                        context.getLogger().info("batch insert for data successfull for batch with execution time of "+afDCFireTS.toString() );
                    }
                    // Handle any errors that may have occurred.
                    catch (Exception e) {
                        context.getLogger().warning("Exception while batch inserting="+e.toString());
                        //e.printStackTrace();
                        try{
                            if(statement != null ) statement.clearBatch();
                        }catch (Exception e2){//e2.printStackTrace();
                        }
                    }  
                    /*finally{
                        try{
                         if(statement != null ) statement.close();

                         if(connection != null) connection.close();
                        }catch(Exception e2){
                            context.getLogger().warning("Exception closing statment and connection="+e2.toString());
                            statement  = null;
                            connection = null;
                        }
                    }*/
           
       }
}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions