Looks like you are using the MSFT JDBC SQL Server connection string instead
of the JTDS connection string:
http://jtds.sourceforge.net/faq.html#urlFormat
Why not just use the MSFT JDBC SQL Server driver? It works well...really,
really, well.
Craig Pelkie
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Graves, Chuck
Sent: Monday, January 14, 2013 1:36 PM
To: RPG programming on the IBM i / System i
Subject: RE: accessing SQL db in RPG
Scott,
I do apologize for getting you into this issue, but I so appreciate your
assistance. Below are the results of my latest attempt(s)
Scott,
Thank you for looking at this...AGAIN
FYI, with the user & password in the program, I can connect to the SQL
server with a Remote Desktop Connecton AND the database with the SQL Server
Management Console
************************************************************
ENVIRONMENT
*************************************************************
Work with Environment Vars (*JOB)
Type options, press Enter.
1=Add 2=Change 4=Remove 5=Display details 6=Print
Opt Name Value
CLASSPATH '/java/jdbc/jtds-1.3.0.jar:/java/jd' >
*******************************************************
First, I verify that I can ping the SQL Server from the iSeries
*******************************************************
ping 'svr-co01-sql'
Verifying connection to host system svr-co01-sql.roddapaint. at address
192.168.1.21.
PING reply 1 from 192.168.1.21 took 1 ms. 256 bytes. TTL 128.
PING reply 2 from 192.168.1.21 took 0 ms. 256 bytes. TTL 128.
PING reply 3 from 192.168.1.21 took 1 ms. 256 bytes. TTL 128.
PING reply 4 from 192.168.1.21 took 0 ms. 256 bytes. TTL 128.
PING reply 5 from 192.168.1.21 took 0 ms. 256 bytes. TTL 128.
Round-trip (in milliseconds) min/avg/max = 0/0/1.
Connection verification statistics: 5 of 5 successful (100 %).
****************************************************************************
**************
Next I check the java Version on the iSeries
****************************************************************************
**************
java version "1.4.2"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_19-b04)
Classic VM (build 1.4, build JDK-1.4, native threads, jitc_de) Java program
completed
****************************************************************************
*******
Below is the source for the program I am running..
(I have tried this without the %trim functions as well)
****************************************************************************
*********
H bnddir('JDBC')
/copy jdbc_h
D*
D***************************************************************
D* Data Definitions
D***************************************************************
D*
D conn s like(connection)
D userid s 50
D password s 50
D database s 50
D driver s 256a
D errmsg s 50a
D url s 256a
C*
C***************************************************************
C* MAINLINE LOGIC
C***************************************************************
C*
/free
eval *inlr = *on;
userid='xxxxxxx';
password='xxxxxxxxxx';
driver = 'net.sourceforge.jtds.jdbc.Driver';
url = 'java:jdbc:sqlserver://SVR-CO01-SQL:1433:RODDAVic';
conn=JDBC_Connect(%trim(driver):
%trim(url):
%trim(userid):
%trim(password));
if conn = *null;
errMsg = 'Unable to connect';
else;
errMsg = 'CONNECTED!!!!!!!!';
endif;
dsply errMsg;
**************************************************************************
Per your suggestion I did the OVRPRTF commands
**************************************************************************
OVRPRTF FILE(STDOUT)
TOFILE(QSYSPRT)
OUTQ(CHUCK/CHUCK)
HOLD(*YES)
OVRSCOPE(*JOB)
OVRPRTF FILE(STDERR)
TOFILE(QSYSPRT)
OUTQ(CHUCK/CHUCK)
HOLD(*YES)
OVRSCOPE(*JOB)
************************************************************************
I run the job, and this is the resulting display
**************************************************************************
Display Program Messages
Job 381120/CHUCK/CHUCK_RG2 started on 01/14/13 at 12:27:04 in subsystem
QINT DSPLY Unable to connect
**************************************************************************
Below is the output from the program
***************************************************************************
java.lang.UnsupportedClassVersionError: net/sourceforge/jtds/jdbc/Driver at
java.lang.Throwable.<init>(Throwable.java:195)
at java.lang.Error.<init>(Error.java:49)
at
java.lang.UnsupportedClassVersionError.<init>(UnsupportedClassVersionError
.java:34) at java.lang.ClassLoader.defineClass(ClassLoader.java:567)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:168)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:313)
at java.net.URLClassLoader.access$300(URLClassLoader.java:86)
at java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:636)
at java.net.URLClassLoader.findClass(URLClassLoader.java:222)
at java.lang.ClassLoader.loadClass(ClassLoader.java:317)
at java.lang.ClassLoader.loadClass(ClassLoader.java:310)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
at java.lang.ClassLoader.loadClass(ClassLoader.java:263)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:330)
****************************************************************************
*****************
Next I create an over-ride to a different java version
****************************************************************************
*****************
qsh cmd('echo java.version=1.6> /home/chuck/SystemDefault.properties')
****************************************************************************
*******************
I then check the version
****************************************************************************
********************
java version "1.6.0"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.6.0_11-b03)
Classic VM (build 1.6, build JDK-1.6, native threads, jitc_de) Java program
completed
****************************************************************************
**************************
and run the program with the file overrides once again
****************************************************************************
***************************
Display Program Messages
Job 381224/CHUCK/CHUCK_RG2 started on 01/14/13 at 12:47:37 in subsystem
QINT DSPLY Unable to connect
****************************************************************************
*****************************
program output
****************************************************************************
******************************
java.lang.UnsupportedClassVersionError: net/sourceforge/jtds/jdbc/Driver at
java.lang.Throwable.<init>(Throwable.java:196)
at java.lang.Error.<init>(Error.java:49)
at
java.lang.UnsupportedClassVersionError.<init>(UnsupportedClassVersionError.j
ava:34)
at java.lang.ClassLoader.defineClass(ClassLoader.java:652)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:170)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:315)
at java.net.URLClassLoader.access$100(URLClassLoader.java:85)
at java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:651)
at
com.ibm.as400.system.nativ.AccessController.doPrivilegedInner(AccessControll
er.java:92)
at
com.ibm.as400.system.nativ.AccessController.doPrivileged(AccessController.ja
va:66)
at java.net.URLClassLoader.findClass(URLClassLoader.java:221)
at sun.misc.Launcher$ExtClassLoader.findClass(Launcher.java:229)
at java.lang.ClassLoader.loadClass(ClassLoader.java:338)
at java.lang.ClassLoader.loadClass(ClassLoader.java:331)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:283)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:351)
****************************************************************************
***************************
Next I try it all again with a different lower version (delete & recreate
the properties file)
****************************************************************************
*****************************
qsh cmd('echo java.version=1.3> /home/chuck/SystemDefault.properties')
****************************************************************************
****************************
double check the version
****************************************************************************
****************************
java version "1.3.1"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.1_08-b03)
Classic VM (build 1.3, build JDK-1.3, native threads, jitc_de) Java program
completed
****************************************************************************
**********************************
same results, more messages
****************************************************************************
*********************************
Display Program Messages
Job 381248/CHUCK/CHUCK_RG2 started on 01/14/13 at 12:52:03 in subsystem
QINT DSPLY Unable to connect
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+.
..
java.lang.UnsupportedClassVersionError: net/sourceforge/jtds/jdbc/Driver
java/lang/Throwable.<init>(Ljava/lang/String;)V+4 (Throwable.java:85)
java/lang/Error.<init>(Ljava/lang/String;)V+1 (Error.java:41)
java/lang/UnsupportedClassVersionError.<init>(Ljava/lang/String;)V+1
(UnsupportedClassVersionError.java:32)
java/lang/ClassLoader.defineClass(Ljava/lang/String;[BIILjava/security/Prote
ctionDomain;)Ljava/lang/Class;+82 (ClassLoader.java:515)
java/net/URLClassLoader.defineClass(Ljava/lang/String;Lsun/misc/Resource;)Lj
ava/lang/Class;+199 (URLClassLoader.java:303)
java/net/URLClassLoader.access$300(Ljava/net/URLClassLoader;Ljava/lang/Strin
g;Lsun/misc/Resource;)Ljava/lang/Class;+1 (URLClassLoader.java:80)
java/net/URLClassLoader$ClassFinder.run()Ljava/lang/Object;+40
(URLClassLoader.java:622)
java/net/URLClassLoader.findClass(Ljava/lang/String;)Ljava/lang/Class;+16
(URLClassLoader.java:216)
java/lang/ClassLoader.loadClass(Ljava/lang/String;Z)Ljava/lang/Class;+41(Cla
ssLoader.java:321)
java/lang/ClassLoader.loadClass(Ljava/lang/String;Z)Ljava/lang/Class;+21
(ClassLoader.java:314)
sun/misc/Launcher$AppClassLoader.loadClass(Ljava/lang/String;Z)Ljava/lang/Cl
ass;+33 (Launcher.java:281)
java/lang/ClassLoader.loadClass(Ljava/lang/String;)Ljava/lang/Class;+1
(ClassLoader.java:277)
java/lang/ClassLoader.loadClassInternal(Ljava/lang/String;)Ljava/lang/Class;
+1 (ClassLoader.java:337)
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Scott Klement
Sent: Tuesday, December 18, 2012 9:54 PM
To: RPG programming on the IBM i / System i
Subject: Re: accessing SQL db in RPG
Hi Chuck,
If you are running this program interactively, there should be information
about the errors that scrolls up your screen -- possibly,
it's to fast to read? So, please start a fresh sign on screen, and do
this:
OVRPRTF FILE(STDOUT) TOFILE(QSYSPRT) HOLD(*YES) OVRSCOPE(*JOB) OVRPRTF
FILE(STDERR) TOFILE(QSYSPRT) HOLD(*YES) OVRSCOPE(*JOB)
now run your program that uses JDBC, in the same interactive job.
Then, sign off.
When you log on again, with any luck, you should find spooled files that
contain the text of the errors.
Also, let me explain some of the stuff you keep posting so you don't waste
anymore time looking at it. Here's the stuff I'm referring to:
JNIENV_P = SPP:EEDB1E8B3E002000
Drv=12
Mid=8
Str=15
Prop=8
All these say is where data is stored in the computers memory (in the case
of JNIENV_P) or in the Java Virtual Machine's memory (For the other
4 items.) It doesn't tell me what data is stored in those memory locations
-- it only tells me where they happen to be in your computer.
And they have the potential to be different every time you run the program.
See what I mean? That's not useful information...
This line:
conn = CallConnectMethod(JNIENV_P: drv: mid: str: prop)
This line tells you that the error is occuring while calling the 'connect'
routine in your JDBC driver. So at this point it's calling out to the JAR
file you downloaded.
So I guess that's good to know -- but, not really a surprise. Any time it
says it has trouble connecting, that's going to be where it occurs.
Please consider trying to capture the error messages, as I described above.
Let's see what they tell us -- they may tell us exactly what the problem is.
The other thing, that I'm now suggesting for the 3rd time in this thread
-- I strongly suspect there's a problem with the use of the Microsoft JDBC
driver. I strongly recommend upgrading to the jTDS driver.
Experience shows that this has more features, runs much faster, and is much
more reliable.
But, now that you've got me involved, I'm REALLY curious as to what error
you're getting with the Microsoft driver -- so please at least try getting
the error messages before switching drivers, and show me what you're
getting.
-SK
On 12/18/2012 7:11 PM, Graves, Chuck wrote:
Scott,
Thank you so much for your help. You were correct. I'm not sure where
I found that, but I had copied it from somewhere on the internet
(??? lies on the internet??? OMG)
At any rate, I got past all my java errors, (by pointing the iSeries to
java version 1.6 via a SystemDefault.properties file in my /home/me
directory) but I still cannot connect. I'm failing here:
conn = CallConnectMethod(JNIENV_P: drv: mid: str: prop)
in the JDBCR4 *Srvpgm
The values going in are:
JNIENV_P = SPP:EEDB1E8B3E002000
Drv=12
Mid=8
Str=15
Prop=8
I have tried every conceivable combination of everything at this
point, including trying to connect to different versions of SQL
servers, different users, with & without properties, with & without
domain names, with & without the domain property value, I can sign-on
to the SQL server(s), I can access the database all with the various
ID's/passwords I've tried through SQL Server Management Console. I've
done "netstat -a" and verified that port 1433 is listening
I have NO DOUBT that it is something in my environment and not your
code, just don't know where to look
This is what I get everytime:
Java Virtual Machine is Classic.
JVM properties were loaded from a properties file.
Unable to connect.
So, before I hit my head on the table... again, I'll go try to get SSH
working to our new bank tomorrow and come back to this.
THANK YOU again...
Chuck
--
This is the RPG programming on the IBM i / System i (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.
[Rodda Paint Company!]
Chuck Graves
Director of Information Systems
Rodda Paint Co.<
http://www.roddapaint.com>
6107 N. Marine Drive
Portland, Oregon 97203
(503) 737-6042
--
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.
midrange.com
