Need help translating dbisql connect string to jdbc connect string

[gwiens]

I am not very familiar with Sybase database systems and tools, but
have managed to install and use "dbisql" to retrieve data from a
command line script like this:

dbisql @options.conf @readHours.sql > clockPunches.txt

The options.conf file looks like this:

-c
"UID=clockReader;PWD=********;ENG=tkcdb_image-2;DBN=krdb;LINKS=ALL";

I think we are using ASA SQLAnywhere.

I have "jconn3.jar" and "jTDS3.jar" in my classpath. The database is
running on a server called "image-2", and the the string in the
"options.conf" file somehow allows me to execute my query in
"readHours.sql".

There is no ODBC setup on the machine I am running this script on.

When I try to compile the simplest connection example, the errors
imply that something is still missing:

C:\testing>java SampleCode

java.lang.ClassNotFoundException: com.sybase.jdbc2.jdbc.SybDriver
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at SampleCode.main(SampleCode.java:11)

Also, my connection string does not seem to have a place for the
database name:

DriverManager.registerDriver((Driver) Class.forName
("com.sybase.jdbc2.jdbc.SybDriver").newInstance());
Connection con = DriverManager.getConnection
("jdbc:sybase:Tds:tkcdb_image-2:3767", "clockReader", "********");

I am familiar with jdbc connect strings for Oracle, SQLServer, MySQL,
and PostgreSQL. All they require is that
the .jar or .zip jdbc driver file is in your classpath and that the
connect string follow their defined format.
But for some reason, I cannot get the hang of what is needed to make
Sybase's jdbc work.

Any help would be appreciated.

[Jeff Albion [Sybase iAnywhere]]

Hi gwiens,

gwiens wrote:
> dbisql @options.conf @readHours.sql > clockPunches.txt
>
> The options.conf file looks like this:
>
> -c
> "UID=clockReader;PWD=********;ENG=tkcdb_image-2;DBN=krdb;LINKS=ALL";
>
> I think we are using ASA SQLAnywhere.

That's probably a pretty good guess. :) Seems like it should be ours.

> C:\testing>java SampleCode
>
> java.lang.ClassNotFoundException: com.sybase.jdbc2.jdbc.SybDriver
> at java.net.URLClassLoader$1.run(Unknown Source)
> at java.security.AccessController.doPrivileged(Native Method)
> at java.net.URLClassLoader.findClass(Unknown Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at java.lang.ClassLoader.loadClassInternal(Unknown Source)
> at java.lang.Class.forName0(Native Method)
> at java.lang.Class.forName(Unknown Source)
> at SampleCode.main(SampleCode.java:11)

So some information to start out with, "jconn3.jar" is Sybase's jConnect
JDBC driver (See:
http://www.sybase.com/products/allproductsa-z/softwaredeveloperkit/jconnect
). In recent history, there have been two versions, "com.sybase.jdbc2"
(the JDBC 2.0-spec driver), released with jConnect 5, and
"com.sybase.jdbc3" (the JDBC 3.0-spec driver), released with jConnect 6.

In short, "jconn3.jar" (from jConnect 6) does not have a class "jdbc2"
contained within it. This line should instead be changed to "jdbc3":

DriverManager.registerDriver((Driver)
Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance());

---

jConnect works using a underlying protocol known as 'TDS' (Tabular data
stream) which can communicate to a number of Sybase products, including
ASE and SQL Anywhere. TDS utilizes the TCP network protocol.

> Also, my connection string does not seem to have a place for the
> database name:

TDS (the underlying protocol jConnect uses) does not know anything about
SQL Anywhere connection strings. (e.g. "ENG=","DBN="). These strings
normally rely on a UDP broadcast to locate a server and report the TCP
port the database service is listening on. For TDS, you must already
know the hostname and port that the service is listening on - it cannot
be discovered using engine names and database names.

So if your server's hostname is 'image-2', and the database service is
on port 2638 (it is by default), then this line would look like:

Connection con =
DriverManager.getConnection("jdbc:sybase:Tds:image-2:2638",
"clockReader", "********");

---

By contrast, this is different than the iAnywhere JDBC ("Type 2") JDBC
driver, which is Sybase iAnywhere's and does understand the usual
connection strings for SQL Anywhere:

http://www.sybase.com/detail?id=1037304

Hope that helps!

--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/

[Kory Hodgson (Sybase iAnywhere)]

It sounds like you are trying to use JConnect 6.5 (jconn3.jar) however
you are referencing a JConnect 5.5 class (com.sybase.jdbc2.jdbc.SybDriver)

You should be using classes from here:
import com.sybase.jdbc3.jdbc.*

Please see the following documentation regarding the use of JConnect and
then post with any follow up questions.

http://dcx.sybase.com/index.php#http%3A%2F%2Fdcx.sybase.com%2Fhtml%2Fdbpgen10%2Fpg-jconnect-using-jdbxextra.html

[gwiens]

Thanks to all who have offered suggestions and links. I am still
working on this problem, er challenge.

I started over, re-reading the instructions, getting a new JConnect
download, setting my JAVA_HOME and classpaths, and validating that
they are pointing in the right direction.

I also worked on my code to something I was a little more familiar
with. Here is a screen capture of my latest attempts. I am hoping this
isn't as difficult as it seems to be, but I can't seem to get the
classpath right or the DriverManager to find the class it needs.

===================================

(1) Setting JAVA HOME:

c:\classes\testing> set JAVA_HOME
JAVA_HOME="C:\Program Files\Java\jdk1.6.0_14"

(2) Setting CLASSPATH:

c:\classes\testing> SET classpath
classpath=.;C:\classes\sqljdbc4.jar;C:\classes\jconn3.jar;C:\classes
\jTDS3.jar

(3) Directory listings:

c:\classes\testing> dir c:\classes
Directory of c:\classes

2005.09.30 01:08 465,869 jconn3.jar
2005.09.30 01:08 136,109 jTDS3.jar
2009.08.31 14:23 <DIR> testing

(4) My java code:

file: JCnx2.java
==========

import com.sybase.jdbc3.jdbc.*;
import java.sql.*;
import java.util.Properties;

public class JCnx2 {

public static void main(String argv[]) {

Properties props = new Properties();
props.put("User","clockReader");
props.put("Password","*******");
props.put("DatabaseName","krdb");

try {
System.out.println("Loading driver...");
DriverManager.registerDriver((Driver)Class.forName
("com.sybase.jdbc3.jdbc.*").newInstance());
System.out.println("Connecting...");
Connection conn = DriverManager.getConnection
("jdbc.sybase:Tds:image-2",props);
System.out.println("Creating statement...");
Statement stmt = conn.createStatement();
System.out.println("Executing query...");
ResultSet rs = stmt.executeQuery("select getdate() as TodayIs");
while(rs.next()) {
System.out.println("rsString: " + rs.getString(1));
}
rs.close();
stmt.close();
conn.close();
} catch( Exception e) {
System.out.println("error: " + e.getMessage());
System.out.println("error: " + e);
}
}
}

(5) Compile and execute:

c:\classes\testing>javac JCnx2.java

c:\classes\testing>java JCnx2
Loading driver...
error: com.sybase.jdbc3.jdbc.*
error2: java.lang.ClassNotFoundException: com.sybase.jdbc3.jdbc.*

(Is this like "No suitable driver found for " error?)

(6) Listing out the contents of the jar files:

c:\classes\testing> jar -tf ..\jconn3.jar | find /i "tds"

com/sybase/jdbc3/tds/DoneToken.class
com/sybase/jdbc3/tds/b.class
com/sybase/jdbc3/tds/d.class
com/sybase/jdbc3/tds/LanguageToken.class
com/sybase/jdbc3/tds/LoginToken.class
com/sybase/jdbc3/tds/LogoutToken.class
com/sybase/jdbc3/tds/ParamDataFormat2.class
com/sybase/jdbc3/tds/PduInputFormatter.class
com/sybase/jdbc3/tds/PduOutputFormatter.class
com/sybase/jdbc3/tds/ReusableLanguageToken.class
com/sybase/jdbc3/tds/RowDataFormat2.class
com/sybase/jdbc3/tds/RowFormatToken.class
com/sybase/jdbc3/tds/RowFormat2Token.class
com/sybase/jdbc3/tds/RowToken.class
com/sybase/jdbc3/tds/Tds.class
com/sybase/jdbc3/tds/Token.class
com/sybase/jdbc3/tds/CapabilityToken.class
com/sybase/jdbc3/tds/DataFormat.class
com/sybase/jdbc3/tds/DbrpcToken.class
com/sybase/jdbc3/tds/EedToken.class
com/sybase/jdbc3/tds/ParamFormatToken.class
com/sybase/jdbc3/tds/ParamFormat2Token.class
com/sybase/jdbc3/tds/ParamsToken.class
com/sybase/jdbc3/tds/e.class
com/sybase/jdbc3/tds/Slurp.class
com/sybase/jdbc3/tds/c.class
com/sybase/jdbc3/tds/TdsDataInputStream.class
com/sybase/jdbc3/tds/TdsDataOutputStream.class
com/sybase/jdbc3/tds/TdsNumeric.class
com/sybase/jdbc3/tds/TdsDateTime.class
com/sybase/jdbc3/tds/TdsJdbcInputStream.class
com/sybase/jdbc3/tds/TdsParam2.class
com/sybase/jdbc3/tds/TdsParam.class
com/sybase/jdbc3/tds/TdsProtocolContext.class
com/sybase/jdbc3/tds/EnvChangeToken.class
com/sybase/jdbc3/tds/EventThread.class
com/sybase/jdbc3/tds/Iana.class
com/sybase/jdbc3/tds/TdsDataObject.class
com/sybase/jdbc3/tds/TdsEventContext.class
com/sybase/jdbc3/tds/TdsInt.class
com/sybase/jdbc3/tds/TdsParamSet.class
com/sybase/jdbc3/tds/TdsReal.class
com/sybase/jdbc3/tds/TdsResultSet.class
com/sybase/jdbc3/tds/TdsScrollResultSet.class
com/sybase/jdbc3/tds/BlobOutputStream.class
com/sybase/jdbc3/tds/CapabilitySet.class
com/sybase/jdbc3/tds/CurCloseToken.class
com/sybase/jdbc3/tds/CurDeclare2Token.class
com/sybase/jdbc3/tds/CurDeclare3Token.class
com/sybase/jdbc3/tds/CurDeclareToken.class
com/sybase/jdbc3/tds/CurDeleteToken.class
com/sybase/jdbc3/tds/CurFetchToken.class
com/sybase/jdbc3/tds/a.class
com/sybase/jdbc3/tds/CurInfoToken.class
com/sybase/jdbc3/tds/CurOpenToken.class
com/sybase/jdbc3/tds/CurUpdateToken.class
com/sybase/jdbc3/tds/Dynamic2Token.class
com/sybase/jdbc3/tds/DynamicToken.class
com/sybase/jdbc3/tds/KerberosSessionContext.class
com/sybase/jdbc3/tds/KeyToken.class
com/sybase/jdbc3/tds/Language.class
com/sybase/jdbc3/tds/LoginAckToken.class
com/sybase/jdbc3/tds/TdsCursor.class
com/sybase/jdbc3/tds/TdsUpdateParam.class
com/sybase/jdbc3/tds/EventToken.class
com/sybase/jdbc3/tds/SybBigDecimal.class
com/sybase/jdbc3/tds/TdsConst.class
com/sybase/jdbc3/tds/TdsInputStream.class
com/sybase/jdbc3/tds/TdsInputStreamIOException.class
com/sybase/jdbc3/tds/TdsOutputStream.class
com/sybase/jdbc3/tds/CachedTdsInt.class
com/sybase/jdbc3/tds/CachedTdsJdbcInputStream.class
com/sybase/jdbc3/tds/CachedTdsReal.class
com/sybase/jdbc3/tds/HASessionContext.class
com/sybase/jdbc3/tds/MsgToken.class
com/sybase/jdbc3/tds/SybTimestamp.class
com/sybase/jdbc3/timedio/TDSTunnellingIS.class
com/sybase/jdbc3/timedio/TDSTunnellingOS.class

c:\classes\testing>jar -tf ..\jTDS3.jar
META-INF/
META-INF/MANIFEST.MF
com/sybase/jdbc3/tds/SrvAcceptor.class
com/sybase/jdbc3/tds/SrvReceiver.class
com/sybase/jdbc3/tds/SrvRequestListener.class
com/sybase/jdbc3/tds/SrvNotifier.class
com/sybase/jdbc3/tds/SrvSession.class
com/sybase/jdbc3/tds/SrvLoginToken.class
com/sybase/jdbc3/tds/SrvLogoutToken.class
com/sybase/jdbc3/tds/SrvLanguageToken.class
com/sybase/jdbc3/tds/SrvDbrpcToken.class
com/sybase/jdbc3/tds/SrvDataInputStream.class
com/sybase/jdbc3/tds/SrvCurDeclare3Token.class
com/sybase/jdbc3/tds/SrvCurDeclare2Token.class
com/sybase/jdbc3/tds/SrvCurDeclareToken.class
com/sybase/jdbc3/tds/SrvCurInfoToken.class
com/sybase/jdbc3/tds/SrvCursor.class
com/sybase/jdbc3/tds/SrvCurOpenToken.class
com/sybase/jdbc3/tds/SrvCurCloseToken.class
com/sybase/jdbc3/tds/SrvCurFetchToken.class
com/sybase/jdbc3/tds/SrvCapabilityToken.class
com/sybase/jdbc3/tds/SrvTypeException.class
com/sybase/jdbc3/tds/SrvDataFormat.class
com/sybase/jdbc3/tds/SrvFormatToken.class
com/sybase/jdbc3/tds/SrvSQLTypeFormatter.class
com/sybase/jdbc3/tds/SrvTypeFormatter.class
com/sybase/jdbc3/tds/SrvEnvChangeToken$VariableInfo.class
com/sybase/jdbc3/tds/SrvEnvChangeToken.class
com/sybase/jdbc3/tds/SrvDataOutputStream.class
com/sybase/jdbc3/tds/SrvAttentionException.class
com/sybase/jdbc3/tds/SrvBulkException.class
com/sybase/jdbc3/tds/SrvPdu.class
com/sybase/jdbc3/tds/SrvJavaTypeFormatter.class
com/sybase/jdbc3/tds/SrvBlobInputStream.class
com/sybase/jdbc3/tds/SrvCurDeleteToken.class
com/sybase/jdbc3/tds/SrvCurUpdateToken.class
com/sybase/jdbc3/tds/SrvDataToken.class
com/sybase/jdbc3/tds/SrvDoneInProcToken.class
com/sybase/jdbc3/tds/SrvDoneToken.class
com/sybase/jdbc3/tds/SrvDoneProcToken.class
com/sybase/jdbc3/tds/SrvDumpInputStream.class
com/sybase/jdbc3/tds/SrvDumpOutputStream.class
com/sybase/jdbc3/tds/SrvEedToken.class
com/sybase/jdbc3/tds/SrvErrorToken.class
com/sybase/jdbc3/tds/SrvEventListener.class
com/sybase/jdbc3/tds/SrvEventToken.class
com/sybase/jdbc3/tds/SrvKeyToken.class
com/sybase/jdbc3/tds/SrvLoginAckToken.class
com/sybase/jdbc3/tds/SrvParamFormatToken.class
com/sybase/jdbc3/tds/SrvParamsToken.class
com/sybase/jdbc3/tds/SrvProtocolException.class
com/sybase/jdbc3/tds/SrvReturnStatusToken.class
com/sybase/jdbc3/tds/SrvRowFormatToken.class
com/sybase/jdbc3/tds/SrvRowToken.class
com/sybase/jdbc3/tds/SrvTest$SrvRegRunner.class
com/sybase/jdbc3/tds/SrvTest.class
com/sybase/jdbc3/tds/SrvUnknownPacketException.class
com/sybase/jdbc3/tds/SrvControlToken$ControlInfo.class
com/sybase/jdbc3/tds/SrvControlToken.class
com/sybase/jdbc3/tds/SrvDebugToken.class
com/sybase/jdbc3/tds/SrvOrderByToken.class
com/sybase/jdbc3/tds/SrvAltFmtToken$OperatorInfo.class
com/sybase/jdbc3/tds/SrvAltFmtToken.class
com/sybase/jdbc3/tds/SrvAltNameToken$NameInfo.class
com/sybase/jdbc3/tds/SrvAltNameToken.class
com/sybase/jdbc3/tds/SrvAltRowToken.class
com/sybase/jdbc3/tds/SrvColInfoToken$ColumnInfo.class
com/sybase/jdbc3/tds/SrvColInfoToken.class
com/sybase/jdbc3/tds/SrvDynamicToken.class
com/sybase/jdbc3/tds/SrvInfoToken.class
com/sybase/jdbc3/tds/SrvMsgToken.class
com/sybase/jdbc3/tds/SrvOffsetToken.class
com/sybase/jdbc3/tds/SrvOptionCmdToken.class
com/sybase/jdbc3/tds/SrvRPCToken$RPCFormat.class
com/sybase/jdbc3/tds/SrvRPCToken.class
com/sybase/jdbc3/tds/SrvReturnValueToken$ReturnFormat.class
com/sybase/jdbc3/tds/SrvReturnValueToken.class
com/sybase/jdbc3/tds/SrvTabNameToken.class
com/sybase/jdbc3/tds/SrvTextImageData.class
com/sybase/jdbc3/tds/SrvVarCharData.class
com/sybase/jdbc3/tds/SrvDynamic2Token.class
com/sybase/jdbc3/tds/SrvOrderBy2Token.class
com/sybase/jdbc3/tds/SrvParamDataFormat2.class
com/sybase/jdbc3/tds/SrvParamFormat2Token.class
com/sybase/jdbc3/tds/SrvRowDataFormat2.class
com/sybase/jdbc3/tds/SrvRowFormat2Token.class
com/sybase/jdbc3/tds/SrvSQLTypeFormatter2.class
com/sybase/jdbc3/tds/SrvCurInfo3Token.class
com/sybase/jdbc3/tds/Dumpable.class
com/sybase/jdbc3/utils/DumpFilter.class
com/sybase/jdbc3/utils/DumpInfo.class
com/sybase/jdbc3/tds/jtds.class

[Chis Keating (Sybase iAnywhere)]

DriverManager.registerDriver((Driver)Class.forName
("com.sybase.jdbc3.jdbc.*").newInstance());

should be

DriverManager.registerDriver( (Driver)
Class.forName(
"com.sybase.jdbc3.jdbc.SybDriver").newInstance()
);

(see previous posts which also indicated to use that form). If you
continue to run into problems, please review the documentation that Jeff
referenced as it contains the step by step for jConnect.

-chris