01143: Tips for BBj and using SourceForge jTDS JDBC Driver for Microsoft SQL Server and other third party JDBC Databases.

Title:

Tips for BBj and using SourceForge jTDS JDBC Driver for Microsoft SQL Server and other third party JDBC Databases.

Description:

First configure the SQL Server setup to allow SQL Server user and password as opposed to Windows authentication only. 

Your sql.ini file should look similar to: 

[JDBC Drivers] 

net.sourceforge.jtds.jdbc.Driver 



[MyData] 

url=jdbc:jtds:sqlserver://localhost/MyDatabase 



Next, your code in BBj: 

SQLOPEN (mychan, MODE=”user=username,password=mypassword”) “MyData” 



Note the completely spelled out “password”. SQL Server does not work with the “PWD” property. SQL Server does work with “UID” instead of “user”. We recommend “user” sunce it is more standard among third party DBs. 

The key is that the MODE string is sent exactly as you enter it. As an example, if you enter “USER=username” and your particular DBMS expects “user” in all lowercase, then you must specify it as “user=username”. Some drivers are very specific and you may not get the expected behavior or strange errors if the string is not passed correctly or required information is omitted (i.e. Oracle does not like USER). Another example, an error is returned unable to load a particular DLL, when a user name or password was not specified when using jTDS driver for SQL Server. It was not configured completely to use the type of authentication (that requires a native code library). 

Here is a debugging tip and sample code. Execute the following lines of code where you insert your own java.sql.Driver implementation class name and valid URL for the DBMS you are using: 

java.lang.Class.forName(“com.mydriver.jdbc.Driver”) 

java.sql.DriverManager.getConnection(“jdbc:mydriver://server/mydata”, 

“username”, “password”) 

If the first statement fails, then you have not included the JAR files for your driver correctly into BBj Services. 

If the second line fails, then you have an incorrect connection string, invalid user/password, server configuration issue with your third party DBMS, or something else outside of the BBj environment. 

Last Modified: 10/12/2007 Product: BASIS BBj ODBC Driver Operating System: All platforms

BASIS structures five components of their technology into the BBx Generations.