If you put three part naming, USE is not required.
> On Oct 31, 2019, at 12:34 PM, Koeller, Arne <[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
