HOWTO: Connect to SAP Sybase IQ Multiplex using JDBC and the SQL Anywhere driver: “Connection error: TCPIP requires a server name”

SAP’s documentation, like other vendors, often sucks. In today’s wonderful documentation sucky-ness the examples from SAP to connect to IQ using a JDBC connection string like so:

“jdbc:sqlanywhere:UID=***;ENG=MyIQ;PWD=***;links=tcpip(Host=MyIQ;PORT=40000)”

This is fine except when you connect to IQ multiplex and you have logical servers set up. You won’t know which IQ node you will be on and since each IQ node has an unique name (eng=), this won’t work. Alright, let’s drop the eng=MyIQ.

“jdbc:sqlanywhere:UID=***;PWD=***;links=tcpip(Host=MyIQ;PORT=40000)”

This will result in an immediate error of “Connection error: TCPIP requires a server name”. Don’t panic, there is a fix. Drop the ENG=MyIQ and links=tcpip(Host=MyIQ;PORT=40000) replacing it with the HOST=MyIQ:40000.

“jdbc:sqlanywhere:UID=***;PWD=***;HOST=MyIQ:40000)”

That works. That is so much more simple than the demented links=tcpip(Host=MyIQ;PORT=40000) nonsense.

Now the fun bit of working code. The following “IQConnect” program accepts four parameters host port username password and spits out the names of the tables on the IQ server:

import java.io.*;
import java.sql.*;

public class IQConnect {
    public static void main(String[] args) {
        if (args.length == 4) {
            String HostName;
            int Port = 0;
            String UserName;
            String Password;

            try {
                Port = Integer.parseInt(args[1]);
            } catch (NumberFormatException e) {
                System.err.println("Port # must be an integer");
                System.exit(1);
            }

            HostName = args[0];
            UserName = args[2];
            Password = args[3];

            String arg;
            Connection con;

            try {
                String ConnectionParams = String.format("jdbc:sqlanywhere:uid=%s;pwd=%s;host=%s:%d", UserName, Password, HostName, Port);
                con = DriverManager.getConnection(ConnectionParams);

                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery("select table_name from sys.systable;");

                while (rs.next()) {
                    String TableName = rs.getString(1);
                    System.out.println(TableName);
                }

                stmt.close();
                con.close();
            }
            catch (SQLException sqe)
            {
                System.out.println("Unexpected exception : " +
                  sqe.toString() + ", sqlstate = " +
                  sqe.getSQLState());
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        } else {
            System.out.println("Requires Host Port# User Password");
        }
    }
}

Compile it with:

javac -cp $IQ16/java/sajdbc4.jar:. IQConnect.java

Execute it with

java -cp $IQ16/java/sajdbc4.jar:. IQConnect MyIQ 40000 dba sql

Of course, if you have $IQ16/java as part of your $CLASSPATH you don’t need to include “-cp $IQ16/java/sajdbc4.jar:.” to execute the program.

Share Button

3 Replies to “HOWTO: Connect to SAP Sybase IQ Multiplex using JDBC and the SQL Anywhere driver: “Connection error: TCPIP requires a server name””

  1. Hi,
    Thanks. Is MyIQ represent a host within the multiplex cluster ? what happens in case that host in not available?
    Can you give an example also for the correct idbsql syntax ?

  2. if the particular host that the dns name is pointing to is down, you won’t connect *UNLESS* you have the dns entry resolve to any of the nodes that are currently up. For example, Netscaler can determine if port 40000 is up on a node and direct traffic there (load balancing of course)

Leave a Reply

Your email address will not be published. Required fields are marked *