JDBC Connection

[vinc1564]

Hi,
I have installed the Lite version for testing this scenario, get data from am mqtt broker and join data with a MS Sql Server table.
First I created the connection to the mqtt broker and it worked well, after I tried the same thing with an JDBC connection to MS Sql Server (2017) but it failed.
I wrote a simple Java code which tests the JDBC connection to MS Sql server and it works right.

I tried also the create a connection to a MySql istance with the same result. (failed)

Has someone tested the connection to Ms Sql Server? (or Mysql)
thanks

Regards

[ssharma]

Hi,

In order to connect with MS SQL Server in you pipeline, please add the driver jar required for connection. Following are the steps to do the same :

1. After creating the data pipeline, go to the Upload Jar icon on the panel on the right.
2. Upload the required version connection driver jar here and update the pipeline.
   
   

Post the above steps one should be able to connect and run the join query. Let us know if you face more issues.

Thanks
Sonam

[vinc1564]

Hi Sonam,
I something is not clear.
I did this:
a) from the home page, I added a JDBC connection to MS Server. (as indicated in my first msg, the test failed)
b) I created a workspace and into the workspace a message type to manage the MS Sqlserver query.
c) I uploaded the jar file "mssql-jdbc-7.0.0.jre8.jar".
d) I created the JDBC Channel which references the JDBC connection (created on the home page), the message type defined into the point b and the query to read data from MS Sqlserver. (i.e select * from table)
into the JDBC wizard, I moved into the "view schema" tab but it returns "Given SQL Query either not valid or please specify schema/database Name along with table name in query"
Here I stopped.

I suppose the issue is related the JDBC connection created (on the home page) which failed.

thanks
regards

[Mohit_Bhindwal]

Hi Vinc,

Please place MSSql/MySql connector jars to below locations and restart StreamAnalytix Lite.

/server/tomcat/webapps/StreamAnalytix/WEB-INF/lib/
/conf/thirdpartylib/

Regards,
Mohit

[vinc1564]

Hi Mohit,
I confirm you now it works, in next day I'll test better.

As I don't find documentation, I ask you if it's possible:
- integrate with Sql Server Change Data Capture. (I saw you support Attunity but I'm interested in native MS SQL cdc)
- To load "in-memory" data loaded from Ms Sql Server
- Join streaming data (like mqtt) with batch data (like jdbc connection to MS Sql)

Thanks for your support.

[Mohit_Bhindwal]

Hi Vinc

• Although we support changed data capture feature with oracle but currently it not available with StreamAnalytix Lite version.For now you can use custom channel as per your requirements.
• To load data from MsSQL database, you can use Register Table Feature to interact with table data within pipeline.
• With the registered table you can join the data with streaming pipeline.

Regards,
Mohit