Re: jTDS JDBC driver with IBM i 7.1 using JDBCR4

Hello René,

Sounds like the problem was due to you having a JAR file in the 'ext' directory. I would recommend never putting JDBC drivers into that directory.

The idea behind 'ext' is that it's for extensions to the Java runtime environment. As such, anything you put there will always be loaded (regardless of the file name) for every single Java application on your system. If you put JDBC drivers there, they will always load for everything, which means you can't have multiple versions of the same drivers -- which isn't good.

When you have more than one JAR file containing the same classes. (i.e. multiple versions of the same JDBC driver) and you've told Java to load them all, then it's hard to predict which one will be used. In your case, you must've had a copy of jTDS that required Java version 7, and it was getting loaded instead of the older versions that required Java 6 that you were trying to use.

I recommend removing all drivers from the 'ext' directory. (Renaming doesn't help, it loads everything from that directory no matter the name.)

instead, put them in a regular IFS directory, such as the /javasql directory you mentioned. Then, add them to the CLASSPATH.

Not sure why the OVRPRTF FILE(STDOUT) didn't work for you. Maybe you need to OVRPRTF FILE(STDERR) as well. But, it's a moot point if you've already figured out the problem.

-SK



On 11/17/2014 2:42 PM, René A Valencourt wrote:

Hello Scott,

It turns out that my problem was multi-faceted.

First, I have confirmed that anything in QIBM\UserData\Java400\ext is
available for inspection when Java be searching for a class, whether the jar
be directly referenced or not. I originally put there six versions of the
jtds driver (beginning with the 1.2.5 driver, going through the 1.3.1
driver) and sqljdbc.jar. My program was working for both drivers.

We then decided to use a separate IFS location for foreign databases
("\javasql"), and to use that location for CLASSPATH. I moved those jars to
the new location, and left a single jTDS jar along with the MS jar in
QIBM\UserData\Java400\ext, having renamed the jars with a prefix of 'x_'.

The Microsoft driver continued to work, although at this point, the jTDS
driver ceased to work for me.

It wasn't until I actually deleted x_sqljdbc.jar that my program failed when
referencing the Microsoft driver; then I got "Exception in thread 'main'
java.lang.NoClassDefFoundError:
com.microsoft.sqlserver.jdbc.SQLServerDriver". I have been unable to
replicate this message, even if I delete the jar file from the ext folder
without terminating the group job; if I re-run the program, it finds the
driver jar in the javasql folder (unless it may have cached the file).

However, at the point that I deleted x_jtds.1.3.1.jar from the ext folder I
started to get this error for Jtds:

Exception in thread "main" java.sql.SQLException: Single-Sign-On is only
supported on Windows. Please specify a user name.
at
net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(TdsCore.java:1878)
at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:588)
at
net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:345)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)

It was now finding the a correct version and attempting to connect; it turns
out that jTDS doesn't recognize the property named 'userName', but only
'user'; Microsoft's driver recognizes both. When I changed this I ceased to
get the "Single-Sign-On" error, and jTDS began to work (for versions 1.2.5,
1.2.6, 1.2.7, and 1.2.8). My original error was probably due to Java
finding QIBM\UserData\Java400\ext\x_jtds-1.3.1.jar ahead of
javasql\jtds-1.2.5.jar for jTDS.

*************************
So, it appears that conflicting drivers in the path, for jTDS, as well as
for sure a misnamed property, were the cause of my problems.
*************************

BTW, regarding these points:

The important thing for this is to know what error message is showing on

your display. I'm not sure if we are seeing the whole message, since it
starts with "at"...

That first line is actually the last line of the dump message... I was
apparently doing some reformatting and left that line. The rest of the text
is precisely what I got every time.

Type OVRPRTF FILE(STDOUT) TOFILE(QSYSPRT) HOLD(*YES) OVRSCOPE(*JOB)

I followed the steps for producing spooled file output, this never did
produce anything... What is printing to STDOUT?

René A Valencourt, CCP, MCTS
Senior Programmer/Analyst
IS Department
CTB, Inc.
A Berkshire Hathaway Company


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott
Klement
Sent: Monday, November 17, 2014 3:00 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: jTDS JDBC driver with IBM i 7.1 using JDBCR4

Hello René,

The important thing for this is to know what error message is showing on
your display. I'm not sure if we are seeing the whole message, since it
starts with "at", which looks like something from the middle of the stack
dump.

Can you please try running your application like this:

1) Start with a fresh session (SIGNOFF, then SIGNON to start a fresh
session)

2) Type OVRPRTF FILE(STDOUT) TOFILE(QSYSPRT) HOLD(*YES) OVRSCOPE(*JOB)

3) Run your program.

4) Sign off.

This should cause the error messages to go to a spooled file instead of your
display, and the spooled file should contain the entire message.

From the part you did have, it looks like UnsupportedClassVersionError
-- this means that the version of the Java class you are using is not
compatible with the version of the JVM you are running. It's not clear to
me if this is the right message, however, since it appears in the middle of
the output you sent instead of at the start.

Also, not sure that I would set a CLASSPATH in a subprocedure...? Keep
in mind that the first time RPG uses Java in a given job, it loads the JVM
into memory, and the CLASSPATH is only read at that time. That means that
if you run your subprocedure in a job where the JVM has already been loaded
(from running any prior Java code) it will not check CLASSPATH a second
time, so any changes to the CLASSPATH will be ignored. (Until the job is
ended/restarted.)


On 11/14/2014 3:16 PM, René A Valencourt wrote:

This question is for Scott Klement, or anyone else who may have solved
something like this...

We have a new Power 8 IBM i running v7r1. QShell tells me that we are
running Java version "1.6.0" (which I guess is Java 6).

java version "1.6.0"
Java(TM) SE Runtime Environment (build pap3260sr16-20140418_01(SR16))
IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 OS/400 ppc-32
jvmap3260sr16-20140418_01 (JIT enabled, AOT enabled) J9VM -
20140416_196573 JIT - r9_20130920_46510ifx5
GC - GA24_Java6_SR16_20140416_1614_B196573)
JCL - 20140406_01

I want to query a MS SQL Server 2005 database running on a Windows
server from an RPG ILE program. I created a program based on
mssqltest.rpgle that Scott Klement made available. I created an IFS
directory called /javasql and put various driver .jar files there:
· sqljdbc.jar
· jtds-1.2.5.jar
· jtds-1.2.6.jar
· jtds-1.2.7.jar
· jtds-1.2.8.jar
· jtds-1.3.0.jar
· jtds-1.3.1.jar

When I specify Microsoft's driver this works every time. Over the
last couple of days, 'jtds-1.2.5.jar' has worked for me several dozen
times (the later versions all got either the "major version" or

"major-minor version"

error). Now today only the Microsoft driver works.

I believe that the only difference be that at day's end yesterday I
made changes in my program to put the code that checks and alters the
CLASSPATH variable into a subprocedure, and I put the code that opens
the JDBC connection into another subprocedure. However, I'm not sure
that this could cause this problem, and the program appears to run
correctly with expected values in the variables.

Before I run my program, QShell shows no CLASSPATH variable,
afterwards there is one, so I know that code works:

CLASSPATH=.::/javasql/jtds-1.2.5.jar

My program's OpenJDBCconnection subprocedure calls the JDBC_ConnProp
subprocedure inside the JDBCR4 service program. Through RDi debug, I
could see that just before the execution of 'new_Driver' subprocedure
in JDBCR4, the path to the Driver class was set as expected
('net.sourceforge.jtds.jdbc.Driver'), and the URL also
('jdbc:jtds:sqlserver://172.18.120.21:1433'), though the program never
gets to use it if it can't instantiate the driver...

// Find & Instantiate Driver

drv = new_Driver(drivname);
if (drv = *NULL);
jdbc_end_object_group();
return *NULL;
endif;

The program fails inside the JDBCR4 "new_Driver" subprocedure, at this

code:

cls = FindClass(env: drv_name);
if (jni_CheckError(*omit));
return *NULL;
endif;

The value of 'drv_name' just before getting translated to ASCII was
'net/sourceforge/jtds/jdbc/Driver'.

With the failure to instantiate the driver I get this display in my
IBM i
session:

at java.lang.ClassLoader.loadClass(ClassLoader.java:643)
Exception in thread "main" java.lang.UnsupportedClassVersionError:
JVMCFRE003 bad major version; class=net/sourceforge/jtds/jdbc/Driver,
offset=6
at java.lang.ClassLoader.defineClass(ClassLoader.java:287)
at
java.security.SecureClassLoader.defineClass(SecureClassLoader.java:74)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:540)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:451)
at java.net.URLClassLoader.access$300(URLClassLoader.java:79)
at

java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:1038)

at
java.security.AccessController.doPrivileged(AccessController.java:362)
at java.net.URLClassLoader.findClass(URLClassLoader.java:429)
at sun.misc.Launcher$ExtClassLoader.findClass(Launcher.java:281)
at java.lang.ClassLoader.loadClass(ClassLoader.java:677)
at java.lang.ClassLoader.loadClass(ClassLoader.java:669)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:358)
at java.lang.ClassLoader.loadClass(ClassLoader.java:643)

As I mentioned, Microsoft's driver works without fail, and the jTDS
1.2.5 driver was working yesterday!

René A Valencourt, CCP, MCTS
Senior Programmer/Analyst
IS Department
CTB
CTB, Inc.
A Berkshire Hathaway Company


Re: Quick JDBCR4 questions

_____


<http://archive.midrange.com/rpg400-l/201402/msg00061.html> -
* Subject: Re: Quick JDBCR4 questions
* From: Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
* Date: Thu, 13 Feb 2014 14:51:37 -0600
* List-archive: <http://archive.midrange.com/rpg400-l>
* List-help: <mailto:rpg400-l-request@xxxxxxxxxxxx?subject=help>
* List-id: "RPG programming on the IBM i \(AS/400 and iSeries\)"
<rpg400-l.midrange.com>
* List-post: <mailto:rpg400-l@xxxxxxxxxxxx>
* List-subscribe:
<http://lists.midrange.com/mailman/listinfo/rpg400-l>,
<mailto:rpg400-l-request@xxxxxxxxxxxx?subject=subscribe>
* List-unsubscribe:
<http://lists.midrange.com/mailman/listinfo/rpg400-l>,
<mailto:rpg400-l-request@xxxxxxxxxxxx?subject=unsubscribe>

fixed <http://archive.midrange.com/rpg400-l/201402/msg00061.html>
_____

Chuck,

JDBCR4 works with Java 1.3 or higher. However, the individual database
drivers also have their requirements.

In your case, it sounds like you are using the jTDS driver (for
Microsoft SQL Server).

If I recall correctly the version of Java that jTDS requires varies
with the version of jTDS that you're using. jTDS 1.3.x requires Java
7, and jTDS 1.2.x works with older versions of Java.

So, you'll either want to install Java 7 (sometimes called "1.7") and
if necessary, set your JAVA_HOME environment variable to point to
it... or get an older version of jTDS. I used 1.2.5 successfully with

older JVMs.

Good luck!