topic Re: MSSQL and MYSQL connection issues in Talend Studio

MSSQL and MYSQL connection issues

Kalel — Fri, 15 Nov 2024 21:35:36 GMT

Hello Everyone,

I hope you are doing well.

As part of a project I'm building, I faced some issues when I had to connect my TOS 8.01 to some DBMS (namely mysql, pgsql and mssql). While I succeeded with pgsql, I'm struggling to do the same process with the other two DBMS's.

When it comes to mssql I get the following message. I had the mssql_jdbc.jar issue (being unable to download it from Talend, I tried downloading it from Download Microsoft JDBC Driver 6.0 pour SQL Server from Official Microsoft Download Center perhaps I failed during the establishment). I connect to mssql using a windows authentication.For MY SQL I get a completely different message, it has to do with a timezone issue :

Connection failure. You must change the Database Settings.

java.lang.RuntimeException: java.sql.SQLException: The server time zone value 'Paris, Madrid (heure d��t�)' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:208)

at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1170)

at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:317)

at org.talend.metadata.managment.repository.ManagerConnection.check(ManagerConnection.java:290)

at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$75.runWithCancel(DatabaseForm.java:4556)

at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$75.runWithCancel(DatabaseForm.java:1)

at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)

at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)

at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)

at java.base/java.lang.Thread.run(Thread.java:833)

I tried googling my problem away, but I struggled understanding the solutions I found, as they had to do with other tools (and also because it goes beyond my modest understanding).

I'd be grateful for your time and help.

Thank you in advance and have a wonderful day.

Re: MSSQL and MYSQL connection issues

anselmopeixoto — Wed, 12 Jul 2023 12:10:09 GMT

Hello @El Kacimi Khalil

Regarding the issue with MS SQL, you can check the library installation status by doing the following:

Window -> Show view -> Talend / Modules

Look for tMSSqlConnection on the Context column and check the Status of mssql-jdbc.jar Module. It'll probably be "Not installed". Then click on "Import external jars" icon on the upper right section of this view to import the jar file.

Now about the issue with MySQL connection, you can try setting the timezone on the Additional parameters of your connection:

serverTimezone=Europe/Madrid

However, according with MySQL documentation, setting a local timezone on the connection might cause some issues when retrieving timestamps for a daylight saving time (DST). The workaround would be setting serverTimezone=UTC "and configuring other date-time connection properties according to your need" (reference). I usually set the local timezone and never had this issue, though.

Re: MSSQL and MYSQL connection issues

Kalel — Wed, 12 Jul 2023 12:31:58 GMT

hello @Anselmo Peixoto ,

First fo all, thank your for your time and your response.

The mysql solution worked perfectly, I added "serverTimezone=UTC" to my additional parameters.

Regarding mssql, here is the result displayed after i look for the "tMssqlConnection" component :

Somehow, the error I'm getting is different this time :

Re: MSSQL and MYSQL connection issues

anselmopeixoto — Wed, 12 Jul 2023 13:38:06 GMT

Hi @El Kacimi Khalil

I'm glad it worked for the MySQL connection.

I believe you'll need to reinstall the MSSQL driver.

Here's a post with a similar issue with a solution:

https://community.talend.com/s/question/0D53p00008WE3eUCAT/cannot-connect-to-microsoft-sql-server-noclassdeffounderror-javaxxmlbinddatatypeconverter?language=en_US

Re: MSSQL and MYSQL connection issues

Anonymous — Thu, 13 Jul 2023 04:20:59 GMT

Hello,

There is something wrong with your mssql driver.

For talend open studio 8.0, please try to delete TOS_DI-20211109_1610-V8.0.1\studio\configuration\.m2\repository\org\talend\libraries\mssql-jdbc directory and use mssql-jdbc-10.2.1.jre11.jar (from sqljdbc_10.2.1.0_fra.zip) with tDBInput

Best regards

Sabrina

Re: MSSQL and MYSQL connection issues

Kalel — Mon, 17 Jul 2023 07:45:27 GMT

Hey @Anselmo Peixoto ,

Thank you for your precious help.

I did manage to create a connection to mssql using a username and password I created.

I still have to find a way to do the same with “Windows Authentication”, if you have any link to share, feel free :).

Have a nice day and all the best for your projects and work.

Re: MSSQL and MYSQL connection issues

Kalel — Mon, 17 Jul 2023 07:51:55 GMT

Hye @Xiaodi Shi (Sabrina),

As stated above, I did manage to create a connection using a different username and password from the ones I use to connect to SSMS.

The version you recommended is only available on mvn ? As I have very poor to zero knowledge regarding Java language, I was a bit confused when I googled your recommendation.

Could you please provide me further explanations about your understanding of what is wrong with my mssql diver ?

Have a wonderful day.

Looking forward to hearing from you soon.

Best regards,

Khalil