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

Hi Pete,

Thanks for the info.

As far as use of ext goes, I was first following the example from articles written a long time ago, and as this is our first foray into java/jdbc programming on the i, I was testing various versions. Since we don't have Java 7 on IBM i 7.1, jtds.1.3.0 and later don't work. I had read recommendations of both jtds.1.2.5 and 1.2.8, so I downloaded the whole group just to see...

So as far as I can see, any .jar file in the /ext folder would have been loaded, regardless of name.

Since the code I saw for setting CLASSPATH included the jar file name, I initially inferred from that the jar had to be specified. It's clear that with respect to ext, that is not the case. However, how about other locations, such as the javasql IFS folder that I created?

At any rate, we don't have anything in ext anymore!

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 Pete Helgren
Sent: Monday, November 17, 2014 4:32 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: jTDS JDBC driver with IBM i 7.1 using JDBCR4

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!

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.