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]>
Reply-To: "[email protected]" <[email protected]>
Date: Thursday, October 31, 2019 at 9:56 AM
To: "[email protected]" <[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
