Github user patricker commented on a diff in the pull request:
https://github.com/apache/nifi/pull/2231#discussion_r159027392
--- Diff:
nifi-nar-bundles/nifi-cdc/nifi-cdc-mssql-bundle/nifi-cdc-mssql-processors/src/main/java/org/apache/nifi/cdc/mssql/MSSQLCDCUtils.java
---
@@ -0,0 +1,264 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.nifi.cdc.mssql;
+
+import org.apache.nifi.cdc.CDCException;
+import org.apache.nifi.cdc.event.ColumnDefinition;
+import org.apache.nifi.cdc.mssql.event.MSSQLColumnDefinition;
+import org.apache.nifi.cdc.mssql.event.MSSQLTableInfo;
+
+import java.sql.Connection;
+import java.sql.Timestamp;
+import java.sql.Types;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+
+public class MSSQLCDCUtils {
+ private static final String _columnSplit = "\n,";
+
+ final String LIST_CHANGE_TRACKING_TABLES_SQL = "SELECT object_id,\n" +
+ " DB_NAME() AS [databaseName], \n" +
+ " SCHEMA_NAME(OBJECTPROPERTY(object_id, 'SchemaId')) AS
[schemaName], \n" +
+ " OBJECT_NAME(object_id) AS [tableName], \n" +
+ " SCHEMA_NAME(OBJECTPROPERTY(source_object_id, 'SchemaId'))
AS [sourceSchemaName],\n" +
+ " OBJECT_NAME(source_object_id) AS [sourceTableName] \n" +
+ "FROM [cdc].[change_tables]";
--- End diff --
Yes, this is true. Two reasons why I did not go this route:
- I could not find any way to build unit tests that required SQL stored
procedures in a reliable way. I'm running this processor in our production
environment and it's quickly becoming more and more important. I need to ensure
I can run unit tests on it. If you can provide me with some guidance on
building ApacheDB stored procedures and integrating them into unit tests I can
take a look, but then there is the permissions issue...
- This stored procedure requires additional permissions beyond select
permissions. I use a read-only account from NiFi with limited permissions, and
when I run this procedure with no arguments it returns no rows of data because
the account is not able to retrieve some of the required data this SP needs.
This same account is able to run with this processor. A lot of ETL accounts are
severely limited when it comes to permissions for obvious reasons, it felt best
to me to support those scenarios.
---
