URL to access Teradata DB with Node DB connector - Issues

[Guillem2222]

Hi,
I am trying to connect to a Teradata database with the Node DB connector.

The details provided by the IT admin of my organization are:
Host: “teradata” (He is not very sure about it)
port:1025
database name: “teradata”

The credentials in the DB connector are correct.

When connecting to the same DB via Teradata Studio, I can succesfully connect. Those are the connection parameters.

The URL I am using in DB connector Node is that one:
jdbc:teradata://teradata:1025/teradata

And I am obtaining the error below.

Is the error in the URL? What would be the correct URL?

I have already looked at similar posts and haven’t been able to find any solution.

Thanks for your help!

ERROR DB Connector 0:1 Execute failed: [Teradata JDBC Driver] [TeraJDBC 17.00.00.02] [Error 1000] [SQLState 08S01] Login failure for Connection to teradata:1025 Wed Nov 11 15:04:42 CET 2020 socket orig=teradata:1025 cid=79b3c38f sess=0 java.net.UnknownHostException: teradata:1025 at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method) at java.net.InetAddress$2.lookupAllHostAddr(InetAddress.java:929) at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1324) at java.net.InetAddress.getAllByName0(InetAddress.java:1277) at java.net.InetAddress.getAllByName(InetAddress.java:1193) at java.net.InetAddress.getAllByName(InetAddress.java:1127) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF$Lookup.doLookup(TDNetworkIOIF.java:223) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF$Lookup.isLiteralIpAddress(TDNetworkIOIF.java:249) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.connectToHost(TDNetworkIOIF.java:336) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.createSocketConnection(TDNetworkIOIF.java:156) at com.teradata.jdbc.jdbc_4.io.TDNetworkIOIF.(TDNetworkIOIF.java:142) at com.teradata.jdbc.jdbc.GenericTeradataConnection.getIO(GenericTeradataConnection.java:143) at com.teradata.jdbc.jdbc.GenericLogonController.run(GenericLogonController.java:100) at com.teradata.jdbc.jdbc_4.TDSession.(TDSession.java:211) at com.teradata.jdbc.jdk6.JDK6_SQL_Connection.(JDK6_SQL_Connection.java:36) at com.teradata.jdbc.jdk6.JDK6ConnectionFactory.constructSQLConnection(JDK6ConnectionFactory.java:25) at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:181) at com.teradata.jdbc.jdbc.ConnectionFactory.createConnection(ConnectionFactory.java:171) at com.teradata.jdbc.TeraDriver.doConnect(TeraDriver.java:236) at com.teradata.jdbc.TeraDriver.connect(TeraDriver.java:162) at org.knime.database.connection.UrlDBConnectionController$ControlledDriver.connect(UrlDBConnectionController.java:95) at org.knime.database.connection.UrlDBConnectionController.createConnection(UrlDBConnectionController.java:308) at org.knime.database.connection.UserDBConnectionController.createConnection(UserDBConnectionController.java:249) at org.knime.database.connection.AbstractConnectionProvider.createConnection(AbstractConnectionProvider.java:89) at org.knime.database.connection.impl.DBConnectionManager.lambda$2(DBConnectionManager.java:501) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)

[tobias.koetter]

Hello,
according to the Teradata JDBC documentation the connection string should look like this:
jdbc:teradata://DatabaseServerName
So in your case the connection string should be:
jdbc:teradata://teradata
You do not need to specify the port since it is the default.
Bye
Tobias

[Guillem2222]

Many thanks Tobias, it worked perfectly!

I have another issue know when extracting data from the Teradata connection, I hope you can help me as well :blush.

I am using the DB Connector + the DB query reader to execute a SQL staetment agaist the teradata. The query is validated because it’s working on Business Objects to obtain a report. But when I write down the query text in the DB Query Reader, I get a weird last sentence on the Parsed Statement:

" AS “tempTable_8605659751840835785” LIMIT 10 " -> Why does that sentece appears in the end of the parsed statment? How can I avoid it?

That leads to the error " [Teradata Database] [TeraJDBC 17.00.00.02] [Error 3706] [SQLState 42000] Syntax error: expected something between the word ‘tempTable_8605659751840835785’ and the ‘LIMIT’ keyword."

And, even if I execute the Node, the DB Query Reader produces an empty table. In stead, it should return a table of thousands of rows.

Can you help me what am I doing wrong?

Many thanks,

[ipazin]

Hello @Guillem2222,

have you managed to figure it out?

From what I know and have read DB Query Reader after pressing Evaluate button surrounds your statement with a limit statement as shown in the left part of the preview window. This is done to prevent fetching all values just for evaluation and can fail for special statements that can not be wrapped within a limit statement. So executing the node should be successful as long as statement is correct as unaltered statement is supposed to be sent to database.

Have you tried any other statement?

Br,
Ivan

[tobias.koetter]

Hello,
the query validation fails because the node uses the SQL92 dialect to limit the number of rows by appending a limit expression to the statement. Unfortunately Teradata does not support this but uses like SQL Server the TOP statement. So to solve this problem you need to register the Teradata driver with the DB type Microsoft SQL Server which then allows you to use the Microsoft SQL Server dialect.
However this should not have any influence on the result of the query when executing the node. In this case we send the query without any changes to the db and read the result. So can you please double check that the query is actually returning any data. Maybe you can also start with a simpler query and go from there.
Bye
Tobias

[Guillem2222]

Hi all,

sorry for not responding to this threat.
As Tobias asid, the shown failure didn’t have any influence on the result of the query.

The querys were executed perfectly.

Regards,