question Re: Nifi Connection to MSSQL server DB in Support Questions

Nifi Connection to MSSQL server DB

csankaraiah — Sat, 17 Oct 2015 22:50:51 GMT

I am trying to build a DBCPConnectionPool that can connect to MSSQL server. I downloaded the jar file and gave the path in DBCPConnectionPool. Here is my configuration

Database Connection URLInfo--> jdbc:mysql://a5d3iwbrq1.cloudapp.net:3306/chakra Database Driver Class NameInfo--> /root/sqljdbc_4.0/enu/sqljdbc4.jar Database Driver Jar UrlInfo--> No value setDatabase UserInfo--> chakra PasswordInfo--> ******

however get an error when i enable this

2015-10-17 14:55:54,352 ERROR [pool-28-thread-5] o.a.n.c.s.StandardControllerServiceNode [DBCPConnectionPool[id=ee00cbf3-7dd3-4c32-93a6-9a06a8e5e6a7]] Failed to invoke @OnEnabled method due to {} org.apache.nifi.reporting.InitializationException: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class '/root/sqljdbc_4.0/enu/sqljdbc4.jar

Please let me now how we can resolve this.

Re: Nifi Connection to MSSQL server DB

bsaini — Sat, 17 Oct 2015 23:00:10 GMT

Hey Chakra -

You need to provide the Java class name for "Database Driver Class NameInfo" and not the jar file path.

The jar file path goes in Database Driver Jar UrlInfo.

Re: Nifi Connection to MSSQL server DB

aervits — Mon, 19 Oct 2015 05:40:20 GMT

i see your jdbc URL is for MySQL but driver is SQL server. Which do you need to access? Standard SQL server port is 1433.

Re: Nifi Connection to MSSQL server DB

aervits — Mon, 19 Oct 2015 05:43:20 GMT

And url should not say jdbc:mysql it needs to be sql server specific, look that up, i dont remember exactly if its just jdbc:mssql

Re: Nifi Connection to MSSQL server DB

csankaraiah — Mon, 19 Oct 2015 20:56:43 GMT

Thanks guys for the response. I was able to modify the configuration for MS SQL server.

Database Connection URLInfo--> jdbc:sqlserver://a5d3iwbrq1.database.windows.net:1433;databaseName=chakra

Database Driver Class NameInfo--> com.microsoft.sqlserver.jdbc.SQLServerDriver

Database Driver Jar UrlInfo--> file:///usr/share/java/sqljdbc4.jar

setDatabase UserInfo--> chakra

PasswordInfo--> ******

Once you have the configuration set, you also need to use generateFlowFile or something to trigger the ExecuteSQL as Timer Driver schedule does not work on the version of Nifi that i was using.

Once this is done i ran into a bug where ExecuteSQL is not able to get the source table structure and gives a avro schema error

https://issues.apache.org/jira/browse/NIFI-1010

I am assuming that once the above bug is fixed we should be able to use ExecuteSQl for MS SQLServer DB.

Re: Nifi Connection to MSSQL server DB

fergus_o-donnel — Sat, 20 Feb 2016 08:16:04 GMT

This appears to work in the Nifi zipfile for windows available at https://nifi.apache.org/download.html.

However MSSQL does not work with the latest tarfile from Linux, nor with the latest sources due to the avro error above. To get it working in a Vagrant Linux Box I had to modify the JdbcCommon.java as per the suggestion in https://issues.apache.org/jira/browse/NIFI-1010 and recompile. It worked then.

Re: Nifi Connection to MSSQL server DB

tongur — Mon, 19 Aug 2019 12:57:25 GMT

Hi dears,

Today ı try to connect my nifi processor to SQL Server on a Windows Server. First I did not copy sqljdbc4.jar to lib folder, then configure processor like below:

But, while enabling the connector, could not succeed.

After getting above error, i tried to copy jars to lib folder but, i can not achieve to bring online?

!!!Waiting your responses, asap!!!