Arne, that’s my observation as well – if you specify the catalog name or database name in the processor, the java sql library always tries to inject a USE statement.
However, if you specify the database name in the DBConnectionPool connection url it does not include a USE statement and the processor will work (or at least it does for me). You need to use this exact form: jdbc:sqlserver://<servername>.database.windows.net:1433;database=<databasename>; We’ll dig in and see what options there are to address this but for now I think that should unblock you. If not let me know and we’ll dig in. Shayne From: "Koeller, Arne" <[email protected]> Reply-To: "[email protected]" <[email protected]> Date: Monday, November 4, 2019 at 1:16 AM To: "[email protected]" <[email protected]> Subject: AW: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL Thanks Shayne for verifying this issue. Even if I add “Initial Catalog=my_catalog_name;” to the connection string of my controller service, while leaving the “Catalog Name” property of PutDatabaseRecord empty, NiFi still can’t seem to find the table: None of the fields in the record map to the columns defined by the [my_schema_name].[my_table_name] table If I add a value to the “Catalog Name” property of PutDatabaseRecord I get the error: USE Statement is not supported to switch between databases It seems to me, that writing records to an AzureSQL DB is not possible at the moment – at least not with the PutDatabaseRecord processor. Von: Shayne Burgess <[email protected]> Gesendet: Donnerstag, 31. Oktober 2019 18:48 An: [email protected] Betreff: Re: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL I see the same “USE statement not supported” error whenever a Catalog/Schema name is specified in PutDatabaseRecord as it will always try to include a “USE” statement. I am able to work-around this by specifying the Catalog directly in the connection string of the DB Connection Pool but this then requires a connection pool per db. Karteek, where are you expecting I provide the three part naming? If I specify it in the table name I get a different error on PutDatabaseRecord, is there another place? I get.. Failed to process StandardFlowFileRecord due to None of the fields in the record map to the columns defined by the table Shayne From: Karteek Yadavilli <[email protected]<mailto:[email protected]>> Reply-To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Date: Thursday, October 31, 2019 at 9:56 AM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Subject: Re: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL If you put three part naming, USE is not required. On Oct 31, 2019, at 12:34 PM, Koeller, Arne <[email protected]<mailto:[email protected]>> wrote: 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]<mailto:[email protected]>> Gesendet: Donnerstag, 31. Oktober 2019 17:08 An: [email protected]<mailto:[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
