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

Connect to Microsoft SQL Server (including Azure instances) from Linux (x86/x86-64) using ODBC and JDBC

Connecting to Microsoft SQL Server from Linux can be done through two different methods: ODBC and JDBC. Unfortunately, FreeTDS doesn’t connect to the newest versions of SQL Server unless you want to enable legacy connections.

The Microsoft JDBC Driver 4.0 for SQL Server, a Type 4 JDBC driver provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5 and 6.

The Microsoft ODBC Driver (Linux) For SQL Server provides native connectivity from Windows to Microsoft SQL Server and Windows Azure SQL Database on Linux.

Share Button

Installing OpenClient with Sybase’s ASE 15.7+ installer (setup.bin -i console)

Installing Sybase OpenClient 15.7 or higher on *nix with setup.bin and don’t have X-Windows available, you will need to start the setup.bin with:

./setup.bin -i console

If you want to install just OpenClient or some other thing, the problem comes with the poorly worded “Choose Product Features”:

ENTER A COMMA_SEPARATED LIST OF NUMBERS REPRESENTING THE FEATURES YOU WOULD
LIKE TO SELECT, OR DESELECT. TO VIEW A FEATURE’S DESCRIPTION, ENTER
‘?<NUMBER>’. PRESS <RETURN> WHEN YOU ARE DONE:

Read that twice.  So, does what exactly does that mean?  Well, unless you work with people that write proposals to be voted on in your local government, it is rather confusing.  It basically means:  Each item is a boolean.

  • You want to install item and it is marked?  Do nothing
  • You want to install item and it is NOT marked?  Add the number to your answer
  • You don’t want to install an item and it is marked?  Add the number to your answer
  • You don’t want to install an item and it is not marked?  Do nothing

For example, if I wanted to install just:

  • Open Client
  • DB-Library
  • Embedded SQL/C
  • Embedded SQL/Cobol
  • XA Interface Library for ASE Distributed Transaction Manager
  • Additional Connectivity Language Modules
  • ASE Extension Module for Python
  • ASE Extension Module for PHP
  • jConnect 7.0 for JDBC
  • ASE ODBC Driver
  • Interactive SQL
  • QPTune
  • Sybase Central
  • Adaptive Server Plug-in for Sybase Central
  • SySAM License Utilities

Choose Product Features
———————–

ENTER A COMMA_SEPARATED LIST OF NUMBERS REPRESENTING THE FEATURES YOU WOULD
LIKE TO SELECT, OR DESELECT. TO VIEW A FEATURE’S DESCRIPTION, ENTER
‘?’. PRESS WHEN YOU ARE DONE:

1- [-] Adaptive Server Enterprise
2- |-[ ] Additional ASE Language Modules
3- |-[X] Sybase Control Center Agent for ASE
4- |-[ ] Sybase Control Center SNMP Agent for ASE
5- [-] Open Client
6- |-[X] DB-Library
7- |-[ ] Embedded SQL/C
8- |-[ ] Embedded SQL/Cobol
9- |-[ ] XA Interface Library for ASE Distributed Transaction Manager
10- |-[ ] Additional Connectivity Language Modules
11- |-[X] ASE Extension Module for Python
12- |-[X] ASE Extension Module for PHP
13- [X] jConnect 7.0 for JDBC
14- [X] ASE ODBC Driver
15- [X] Interactive SQL
16- [X] QPTune
17- [X] Sybase Central
18- |-[X] Adaptive Server Plug-in
19- [-] SySAM License Utilities
20- |-[ ] SySAM License Server

Please choose the Features to be installed by this installer.: 1,7,8,9,10

You get one shot at choosing what you want installed.  Seriously Sybase?

After agreeing to the User License Agreement, why am I agreeing to it here instead of at the start of the installation or right before the files are copied???, the installer will list what you’re about to install.  Review it carefully, if something is wrong, ctrl-c out of  the installer and try again.

Pre-Installation Summary

————————

Please Review the Following Before Continuing:

Product Name:
Sybase Adaptive Server Enterprise Suite

Install Folder:
/home/homer.simpson

Product Features:
Open Client,
DB-Library,
Embedded SQL/C,
Embedded SQL/Cobol,
XA Interface Library for ASE Distributed Transaction Manager,
Additional Connectivity Language Modules,
ASE Extension Module for Python,
ASE Extension Module for PHP,
jConnect 7.0 for JDBC,
ASE ODBC Driver,
Interactive SQL,
QPTune,
Sybase Central,
Adaptive Server Plug-in,
SySAM License Utilities

Disk Space Information (for Installation Target):

 

Share Button