Indeed, the PutDatabaseRecord processor will, in this instance, only work when specifying the DB name (called Catalog Name, I believe), schema name and table name.
However, even when I specified the fully qualified name, the PutDatabaseRecord processor results in “USE Statement is not supported to switch between databases”. This has lead me to believe that NiFi, or one of the underlining libraries, sends a USE-SQL Query, such as USE <DBName> to the AzureSQL server. That would be totally fine if the SQL server was MySQL or MSSQL but because its AzureSQL, the USE-Query unfortunately fails since AzureSQL doesn’t support USE-Queries. Am I overlooking something? Or could this be an issue? Thank you! Von: Karteek Yadavilli <[email protected]> Gesendet: Donnerstag, 31. Oktober 2019 17:08 An: [email protected] Betreff: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL How about the three part naming convention? <DBName>.<SchemaName>.<TableName> On Oct 31, 2019, at 12:06 PM, Koeller, Arne <[email protected]<mailto:[email protected]>> wrote: Hello there, I’m trying to Insert Avro Records into a AzureSQL database. The Flow is depicted below. Up until after the ExecuteSQL processor everything works fine. The output of the ExecuteSQL processor is as follows: <image004.png> Afterwards, this output is directed to the PutDatabaseRecord processor which has an AvroReader. The property configuration is as follows: <image006.png> If I leave the Catalog Name and/or the Schema Name blank, Nifi cant map the fields. <image007.png> If I however fill I Catalog Name and Schema Name, Nifi results in the Error “USE Statement is not supported to switch between databases”. Nifi apparently tries to use the “USE”-SQL Statement here. However, AzureSQL doesn’t support the “USE”-SQL Statement. A complete Stacktrace is added below. If you need additional information, I am happy to help. Thank you in advance. Kind regards, Arne Nifi Flow: <image012.jpg> Stracktrace: Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: USE statement is not supported to switch between databases. Use a new connection to connect to a different database. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:283) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:129) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:26) at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:3019) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(SQLServerConnection.java:3024) at com.microsoft.sqlserver.jdbc.SQLServerConnection.setCatalog(SQLServerConnection.java:3357) at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.switchCatalogs(SQLServerDatabaseMetaData.java:385) at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(SQLServerDatabaseMetaData.java:614) at org.apache.commons.dbcp2.DelegatingDatabaseMetaData.getColumns(DelegatingDatabaseMetaData.java:227) at org.apache.commons.dbcp2.DelegatingDatabaseMetaData.getColumns(DelegatingDatabaseMetaData.java:227) at org.apache.nifi.processors.standard.PutDatabaseRecord$TableSchema.from(PutDatabaseRecord.java:1047) at org.apache.nifi.processors.standard.PutDatabaseRecord.lambda$executeDML$9(PutDatabaseRecord.java:603) ... 27 common frames omitted
