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

SAP IQ: dbisql is unable to load the SybaseIQ SQLAnywhere plugins. SOLVED!

I recently patched an SAP IQ server to 16.0 SP8 PL30 and ran into an interesting error message when trying to start dbisql:

$ dbisql
Interactive SQL could not load the "SQLAnywhere" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "SybaseIQ" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "HANA" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "GenericODBC" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL cannot start because it is not installed correctly. No database plug-ins has been registered.
To fix this problem, you should reinstall the program.

If you scan your IQ directory, you will notice there isn’t a “ngdbc.jar” file. You take a look at another IQ box that is working and it doesn’t have the ngdbc.jar file either. The error message is incorrect. The message should report that it isn’t able to access the saip16.jar (or saip11.jar if you’re on IQ 15.x) and/or the jodbc4.jar file in the $SYBASE/IQ-(IQ RELEASE)/java directory (e.g. $SYBASE/IQ-16_0/java).
Verify that the two files exist and the permissions are correct:

316 -rwxr-xr-x.  1 sybase sybase  320071 Mar 20 14:51 jodbc4.jar
112 -rwxr-xr-x.  1 sybase sybase  112325 Mar 20 14:56 saip16.jar

If everything looks okay and it still gives the error, you will need to re-register the plugins. The first thing is to move the dbisql ‘registry’:

$ mv $SYBASE/IQ-(IQ RELEASE)/bin64/dbisql_64.rep $SYBASE/IQ-(IQ RELEASE)/bin64/dbisql_64.rep.old

Next, re-register (for IQ 16):

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa16 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip16.jar:$(pwd)/jodbc4.jar"

for IQ 15:

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa11 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip11.jar:$(pwd)/jodbc4.jar"

Newer IQ 15 patches use SQL Anywhere 12, so if you have saip12.jar instead of saip11.jar in your java dir, use that:

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa12 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip12.jar:$(pwd)/jodbc4.jar"

dbisql should now work 🙂

The dbisql_64.rep is simply a glorified ini file that contains the following which should be transferrable but I’ve found that dbisql is very finicky with regards to this file.

[SybaseIQ]
classLoaderName=sa16
mainclass=com.sybase.saisqlplugin.IQISQLPlugin
classpath=/opt/client/SAP-IQ/IQ-16_0/java/saip16.jar:/opt/client/SAP-IQ/IQ-16_0/java/jodbc4.jar

You may need to update the permissions if other users rely on this particular dbisql installation.

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

Want a free ebook? Java Application Development on Linux

Nikesh Jauhari over on the Linux Poison blog, posted about the free eBook – Java Application Development on Linux, a 599 page ebook from informIT. Yeah, when you download the book, you will need to give informIT your contact info. If you don’t want to, you could probably just use an email address that you don’t use except for such registration things.

Share Button

Free online course from Sang Shin starting September 1st (tomorrow): JavaTM EE (J2EE) Programming (with Passion!) Basics

Sang Shin’s online Java courses are very informative and are well paced.  I highly recommend attending the free online courses even if you think you know all there is to know about Java.

The next and the 16th session will start on Sep. 1st, 2009. From this session, this course covers basic topics while “Java EE programming Advanced (with Passion!)” course covers more advanced topics. Just like other online courses I teach, this course is offered online only.   For those of you who are not sure what it’s like to take this course online, please see What it’s like to take Sang Shin’s online courses.  Just to set the expectation correctly, there is no real-time webcasting.

Did I mention the course is FREE? If you ever wanted to learn Enterprise Java, now is the time to sign up!

Share Button

Sybase’s PowerBuilder v12 is powerful, .NET based, and wonderful! Why you shouldn’t use it

The following is MY perception of Sybase’s PowerBuilder:

Years ago PowerBuilder was king.  No one could touch it.  It was relatively inexpensive.  Microsoft’s Visual Basic matured and the Pascal based Borland’s Delphi was released.  Then it fell and fall it did.

Sybase
Sybase

As it was falling from the throne Sybase purchased Powersoft, makers of PowerBuilder.  As the the market share continued to shrink, PowerBuilder developers had more difficulty in finding new projects.  Most new development was written in Visual Basic or Java.

Years went by with marketing of PowerBuilder little more than the occasional road show, TechWave presentations and the ISUG Technical Journal ads catered towards existing customers.  Little to no effort was put forth by Sybase to gain new PowerBuilder customers.

During this week’s Sybase TechWave, PowerBuilder version 12 was released.  It has all the whistles and kitchen sinks you could ask for.   An amazing tool for development!  Too bad no one outside of the die hard PowerBuilder programmers will use it.

Blasphemy!  Heretic!

Consider this:

Sybase owns PowerBuilder.  It owns the PowerBuilder software, PowerBuilder language, PowerScript, the PowerBuilder vm, and everything PowerBuilder.

No problem right?

What will happen to PowerBuilder when Sybase is bought out by another company?  Products with tiny market share like PowerBuilder would likely be killed or in a state of limbo for several years.  Anyone remember what happened when IBM bought Informix?

Do you really want to bet your career and business on a software development tool that is locked to a single smallish vendor?

Maybe, perhaps, if Sybase were to release the PowerBuilder 4GL language and PowerScript to the world like Microsoft did with the C# and Visual Basic languages and Sun with Java…   Perhaps if Sybase would allow 3rd parties to develop tools based around the PowerBuilder language royalty free…

Sybase:  PLEASE FREE THE POWERBUILDER 4GL LANGUAGE!

I mean, really, what benefit could Sybase have to cripple the PowerBuilder developers?

Share Button

Video: Sun/Oracle’s Netbeans 6.7 and the GlassFish version 3 application server

Share Button

Going Green in California – Why can’t I go green in Chicago?

Two of my best friends, Ryan & Anna Lubke, just went completely green.  No, not like the Incredible Hulk, but as in going off of the electric grid in California.  Due to silly laws in Chicago, my family can’t do much to go green.

Going Green

Ryan Lubke
Ryan Lubke

Thursday Jan 01, 2009

I’m not one to normally post much of anything personal on the web, however, since several co-workers found my family’s new situation interesting, I thought I might share.

This year my family and I are going green(er).  Specificially, we’ve moved into a house that is off the grid meaning the house doesn’t use the typical public utilities (i.e. city water, electric, etc).

read more…

Share Button

Getting “A suitable JVM could not be found” with ASE 15.0.2 setup

$ ./setup -is:$JAVA_HOME -console 
InstallShield Wizard
Initializing InstallShield Wizard...
Searching for Java(tm) Virtual Machine...
A suitable JVM could not be found. Please run the program again using the option -is:javahome
Permission denied
Permission denied

Have you ever run into this? Well, I just did and for some reason the /tmp directory had permissions of drwxr-xr-x. A quick chmod 1777 /tmp fixed it. This is an fyi in case someone else runs across this problem.

Share Button