You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by "Koeller, Arne" <A....@eickhoff-bochum.de> on 2019/10/31 16:06:04 UTC
Nifi Bug with AzureSQL
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:
[cid:image004.png@01D5900D.79EA4AF0]
Afterwards, this output is directed to the PutDatabaseRecord processor which has an AvroReader. The property configuration is as follows:
[cid:image006.png@01D5900D.79EA4AF0]
If I leave the Catalog Name and/or the Schema Name blank, Nifi cant map the fields.
[cid:image007.png@01D5900D.79EA4AF0]
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:
[cid:image012.jpg@01D5900D.79EA4AF0]
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
AW: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Posted by "Koeller, Arne" <A....@eickhoff-bochum.de>.
This actually did the trick!
I also had to fill in the Schema Name and then the PutDatabaseRecord processor started working faultlessly with our AzureSQL database.
Big thanks and appreciation to you, Shayne.
Von: Shayne Burgess <Sh...@microsoft.com>
Gesendet: Donnerstag, 7. November 2019 00:45
An: Koeller, Arne <A....@eickhoff-bochum.de>
Betreff: Re: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Maybe this is just too simple but looking at your processor properties, the one thing I see that is different is that you have surrounded your table name in “[]” brackets. I haven’t had an opportunity to look at the code to see if this is the cause but on my sample flow when I add the “[]” around table name I see the same error as you are showing below and it goes away when I remove them. Worth a shot?
From: "Koeller, Arne" <A....@eickhoff-bochum.de>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Wednesday, November 6, 2019 at 2:31 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: AW: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Hi Shayne,
thanks for your reply and advice.
However, specifying the database name in the jdbc connection string didn’t resolve the issue for me.
So I’ve tried the following now:
PutDatabaseRecord Processor (v1.9.2)
[cid:image006.jpg@01D59495.B5353A20]
DBConnectionPool Config (v1.9.2)
jdbc:sqlserver://<servername>.database.windows.net:1433;database=<databasename>;
Error I get with this configuration
[cid:image007.png@01D59495.B5353A20]
Even if I specify the schema name (but not the catalog name), I get the same error as above.
If I do specify the catalog and schema name I get the USE Statement error
[cid:image008.png@01D59495.B5353A20]
The stacktrace for the USE Statement exception can be inspected further below.
So if I do specify the catalog name, the SQLServerDatabaseMetaData.switchCatalogs does not return null and calls connection.setCatalog(catalogName) which then causes the USE statement exception.
[cid:image009.jpg@01D59495.B5353A20]
However, if I do not specify the catalog name, the PutDatabaseRecord.generateInsert() method seems to throw the “None of the fields in the record map…”-Exception. Maybe it would be interesting to see what the SQlServerDatabaseMetaData.getColumns() does in this case, as my recordSchema seems to be empty:
[cid:image010.jpg@01D59495.B5353A20]
I’m using NiFi v1.9.2.
Greatly appreciating your help!
If you need any more info I’m happy to help.
Kind regards,
Arne
Stacktrace of USE Exception:
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
Von: Shayne Burgess <Sh...@microsoft.com>>
Gesendet: Dienstag, 5. November 2019 18:14
An: users@nifi.apache.org<ma...@nifi.apache.org>
Betreff: Re: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
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" <A....@eickhoff-bochum.de>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Monday, November 4, 2019 at 1:16 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
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 <Sh...@microsoft.com>>
Gesendet: Donnerstag, 31. Oktober 2019 18:48
An: users@nifi.apache.org<ma...@nifi.apache.org>
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 <ya...@icloud.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Thursday, October 31, 2019 at 9:56 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
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 <A....@eickhoff-bochum.de>> 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 <ya...@icloud.com>>
Gesendet: Donnerstag, 31. Oktober 2019 17:08
An: users@nifi.apache.org<ma...@nifi.apache.org>
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 <A....@eickhoff-bochum.de>> 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
AW: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Posted by "Koeller, Arne" <A....@eickhoff-bochum.de>.
Hi Shayne,
thanks for your reply and advice.
However, specifying the database name in the jdbc connection string didn’t resolve the issue for me.
So I’ve tried the following now:
PutDatabaseRecord Processor (v1.9.2)
[cid:image006.jpg@01D59495.B5353A20]
DBConnectionPool Config (v1.9.2)
jdbc:sqlserver://<servername>.database.windows.net:1433;database=<databasename>;
Error I get with this configuration
[cid:image007.png@01D59495.B5353A20]
Even if I specify the schema name (but not the catalog name), I get the same error as above.
If I do specify the catalog and schema name I get the USE Statement error
[cid:image008.png@01D59495.B5353A20]
The stacktrace for the USE Statement exception can be inspected further below.
So if I do specify the catalog name, the SQLServerDatabaseMetaData.switchCatalogs does not return null and calls connection.setCatalog(catalogName) which then causes the USE statement exception.
[cid:image009.jpg@01D59495.B5353A20]
However, if I do not specify the catalog name, the PutDatabaseRecord.generateInsert() method seems to throw the “None of the fields in the record map…”-Exception. Maybe it would be interesting to see what the SQlServerDatabaseMetaData.getColumns() does in this case, as my recordSchema seems to be empty:
[cid:image010.jpg@01D59495.B5353A20]
I’m using NiFi v1.9.2.
Greatly appreciating your help!
If you need any more info I’m happy to help.
Kind regards,
Arne
Stacktrace of USE Exception:
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
Von: Shayne Burgess <Sh...@microsoft.com>
Gesendet: Dienstag, 5. November 2019 18:14
An: users@nifi.apache.org
Betreff: Re: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
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" <A....@eickhoff-bochum.de>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Monday, November 4, 2019 at 1:16 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
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 <Sh...@microsoft.com>>
Gesendet: Donnerstag, 31. Oktober 2019 18:48
An: users@nifi.apache.org<ma...@nifi.apache.org>
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 <ya...@icloud.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Thursday, October 31, 2019 at 9:56 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
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 <A....@eickhoff-bochum.de>> 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 <ya...@icloud.com>>
Gesendet: Donnerstag, 31. Oktober 2019 17:08
An: users@nifi.apache.org<ma...@nifi.apache.org>
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 <A....@eickhoff-bochum.de>> 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
Re: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Posted by Shayne Burgess <Sh...@microsoft.com>.
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" <A....@eickhoff-bochum.de>
Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
Date: Monday, November 4, 2019 at 1:16 AM
To: "users@nifi.apache.org" <us...@nifi.apache.org>
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 <Sh...@microsoft.com>
Gesendet: Donnerstag, 31. Oktober 2019 18:48
An: users@nifi.apache.org
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 <ya...@icloud.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Thursday, October 31, 2019 at 9:56 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
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 <A....@eickhoff-bochum.de>> 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 <ya...@icloud.com>>
Gesendet: Donnerstag, 31. Oktober 2019 17:08
An: users@nifi.apache.org<ma...@nifi.apache.org>
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 <A....@eickhoff-bochum.de>> 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
AW: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Posted by "Koeller, Arne" <A....@eickhoff-bochum.de>.
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 <Sh...@microsoft.com>
Gesendet: Donnerstag, 31. Oktober 2019 18:48
An: users@nifi.apache.org
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 <ya...@icloud.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Thursday, October 31, 2019 at 9:56 AM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
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 <A....@eickhoff-bochum.de>> 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 <ya...@icloud.com>>
Gesendet: Donnerstag, 31. Oktober 2019 17:08
An: users@nifi.apache.org<ma...@nifi.apache.org>
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 <A....@eickhoff-bochum.de>> 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
Re: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Posted by Shayne Burgess <Sh...@microsoft.com>.
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 <ya...@icloud.com>
Reply-To: "users@nifi.apache.org" <us...@nifi.apache.org>
Date: Thursday, October 31, 2019 at 9:56 AM
To: "users@nifi.apache.org" <us...@nifi.apache.org>
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 <A....@eickhoff-bochum.de>> 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 <ya...@icloud.com>>
Gesendet: Donnerstag, 31. Oktober 2019 17:08
An: users@nifi.apache.org<ma...@nifi.apache.org>
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 <A....@eickhoff-bochum.de>> 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
Re: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Posted by Karteek Yadavilli <ya...@icloud.com>.
If you put three part naming, USE is not required.
> On Oct 31, 2019, at 12:34 PM, Koeller, Arne <A....@eickhoff-bochum.de> 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 <yakarteek@icloud.com <ma...@icloud.com>>
> Gesendet: Donnerstag, 31. Oktober 2019 17:08
> An: users@nifi.apache.org <ma...@nifi.apache.org>
> 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 <A.Koeller@eickhoff-bochum.de <ma...@eickhoff-bochum.de>> 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
AW: [Spam:***** SpamScore] Re: Nifi Bug with AzureSQL
Posted by "Koeller, Arne" <A....@eickhoff-bochum.de>.
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 <ya...@icloud.com>
Gesendet: Donnerstag, 31. Oktober 2019 17:08
An: users@nifi.apache.org
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 <A....@eickhoff-bochum.de>> 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
Re: Nifi Bug with AzureSQL
Posted by Karteek Yadavilli <ya...@icloud.com>.
How about the three part naming convention?
<DBName>.<SchemaName>.<TableName>
> On Oct 31, 2019, at 12:06 PM, Koeller, Arne <A....@eickhoff-bochum.de> 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