Re: jTDS JDBC driver with IBM i 7.1 using JDBCR4 -- RPG400-L

Rene,

I haven't been following the thread closely but your last explanation caught my attention. The Oracle Java documentation says this:

--------------------------------------------------
How the Java Launcher Finds Classes

The Java launcher, java, initiates the Java virtual machine. The virtual machine searches for and loads classes in this order:

Bootstrap classes - Classes that comprise the Java platform, including the classes in rt.jar and several other important jar files.
Extension classes - Classes that use the Java Extension mechanism. These are bundled as .jar files located in the extensions directory.
User classes - Classes defined by developers and third parties that do not take advantage of the extension mechanism. You identify the location of these classes using the -classpath option on the command line (the preferred method) or by using the CLASSPATH environment variable. (See Setting the Classpath for Windows or Unix.)

----------------------------------------------------

So as far as I can see, any .jar file in the /ext folder would have been loaded, regardless of name. So I don't know what class it would have tried to load if it found 6 copies of the same class file. My hunch is that they would be loaded in the order they were discovered. Don't know why you would have put 6 of the same jars with different versions in the /ext folder. Just the most recent one would have done the trick. OR, if you are looking for just one class, open the .jar with 7zip (or whatever) and see which one has the correct name and validate the version.

In general, I don't mess with the system folders when it comes to classpath or PATH. I usually invoke with a CL that sets up the whole environment for me. You can run into very strange problems when you start dumping jars into /ext that are "local" to some app you are running. The next person to run an app without knowing you modified the /ext folder could be in for an unpleasant surprise.

However, it looks like you discovered it by trial and error, which may be a more "memorable" (if more painful) way to learn.

Pete Helgren
www.petesworkshop.com
GIAC Secure Software Programmer-Java

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!