JDBC Setup for SQL Server on Desktop Client

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1369
Roger French	1315
mark.cook	1244

Woozy

Veteran Member

Posts: 709

6/19/2009 2:03 PM

Hi All,

I'm trying to use the SQL Query Node to hit an external SQL Server 2005 database, and I'm not having any success. Whenever I click the "build" button in the node properties, I get an error.

I've put the Microsoft JDBC driver (sqljdbc4.jar) in "C:\Program Files\Lawson Software\ProcessFlow901\lib", and I've added ";.\lib\sqljdbc4.jar" to the Designer.bat classpath.

Within the node, I've used the following settings:
Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
URL: jdbc:sqlserver://srv0036;databaseName=PR_HR;integratedSecurity=true;

I've tried with and without a username/password.

When I click the "Build" button in the node, I get a "java.lang.NoClassDefFoundError".

Can anyone point me in the right direction?

Thanks!

We're running PFI 9.0.1; LSF 9; DB2-AIX

Shari

Veteran Member

Posts: 78

6/19/2009 2:26 PM

I opened a case with GSC about the same thing. I was told:

"Unfortunately due to drive compatibility issues, it will be impossible for most people to install a JDBC drive into the ProcessFlow Designer. the good news, is that all JDBC drives should be compatible with the ProcessFlow Server, itself. But what this will mean is that when testing your flows in the Designer, you will need to use the "Run on Server" option. It also means you won't be able to use the Build button--you'll need to paste in your queries from elsewhere."

You still need to setup the PFlow server side of things - that's all in the install documentation.

John Henley

Posts: 3353

6/19/2009 2:56 PM

You should use the MSSQL 2000 JDBC. It will work just fine.

Woozy

Veteran Member

Posts: 709

6/19/2009 7:29 PM

Thanks Shari. Maybe I'll have to go that route...yuck.

John, I tried the SQL 2000 JDBC and still haven't had any luck. I know nothing is set up on the server side - but it shouldn't have to be to run it on the desktop, right???

J McDevitt

New Member

Posts: 2

7/14/2009 3:38 PM

I use SQL 2000 JDBC for the client. Below are the settings that work for me...

com.microsoft.jdbc.sqlserver.SQLServerDriver
jdbc:sqlserver://myserver;databaseName=mydatabasename;

Include in the designer.bat
;.\lib\msutil.jar;.\lib\msbase.jar;.\lib\mssqlserver.jar

Woozy

Veteran Member

Posts: 709

7/14/2009 5:47 PM

Thanks JMcDevitt. Unfortunately, I'm still out-of-luck. I tried doing exactly what you suggested, but it still gives me an error.

"Unable to load JDBC database driver class com/microsoft/jdbc/sqlserver/SQLServerDriver . All required JDBC driver jars need to be included in the classpath in the Designer.bat file. Please see the ProcessFlow Admin Guide for JDBC setup details".

I've confirmed that the designer.bat includes the jar files, and that they exist in the appropriate folder.

I'm stumped. Thanks for the try, though...

J McDevitt

New Member

Posts: 2

7/30/2009 3:40 PM

Kelly,
Is there any chance you have a space at the end of any JDBC setting?
The first time I populated my settings, I copied & pasted, which added a space at the end of the Driver. This gave me the same message.

Vito Amatulli

Advanced Member

Posts: 33

7/31/2009 2:13 PM

Kelly,
I had a similar issue and I had to go to Microsoft and I downloaded the MS JDBC Driver 2.0 and installed it to my PC. I had to copy sqljdbc4.jar and sqljdbc.jar to the lib directory. I add the 2 files to the end of the CLASSPATH statement in my Designer.bat and I was able to connect. I have not uploaded to the server yet but will do so once I have completed my testing. I had a similar issue with a DB2 connection but was able to resolev it as well. Hope this helps.

John Henley

Posts: 3353

7/31/2009 2:21 PM

Woozy, where did you put the .jar files?

Vito Amatulli

Advanced Member

Posts: 33

7/31/2009 2:34 PM

John,
I will be updating mu Db2 doc that I sent to you to include the SQL2005 update as well but here is the jest if it. Donwload the MS JDBC driver. It is a self expanding exe. It will copy the files locally.

Vito Amatulli

Advanced Member

Posts: 33

7/31/2009 2:37 PM

John,
Sorry I was too quicik to hit the submit button. I have a meeting to go to. I'll update my dic and send it to you later.

Woozy

Veteran Member

Posts: 709

7/31/2009 3:11 PM

JMcDevitt - I don't have a space at the end.

John - after I ran the SQL 200o JDBC Install, I copied the three .jar files into the \ProcessFlow901\lib folder.

Vito - I'll give this a shot too. So do I need to use the JDBC 2.0 driver instead of 2000, or in addition to it?

Thanks for all your help. Hopefully this will eventually work...

Vito Amatulli

Advanced Member

Posts: 33

7/31/2009 4:13 PM

yes, You will have to download and install the MS JDBC 2.0 driver. Here is the URL.

http://www.microsoft.com/...1fdc9&displaylang=en

It will downlaod a self extarcting exe. Extract the files and then locate the 2 jar files. Copy thme to the lib direcotry in Processflow.

Add the 2 jar file names to the CLASSPATH section in Designer.bat

.\lib\sqljdbc.jar;.\lib\sqljdbc4.jar;

You will also have to add them to the CLASSPATH section on your lawson.env file on your Lawson server. Finally, copy the jar files to the $GENDIR/bmp/folder on the Lawson server as well.
Hope this helps.

Vito Amatulli

Advanced Member

Posts: 33

8/3/2009 12:43 PM

Woozy,

Did you ever get the SQL2005 connection to work?

Woozy

Veteran Member

Posts: 709

8/4/2009 2:08 PM

Vito - I have not. I've been out of town for a few days, so I'm going to spend some more time on this and see if I can beat it into submission.

I'll let everyone know if I have any luck (or not...).

Thanks for all the ideas! Kelly

Woozy

Veteran Member

Posts: 709

8/5/2009 5:29 PM

Yay! I finally had success!! - at least on my local machine. The next step is getting the drivers set up on the server.

I was not able to get SQLServer JDBC 2.0 to work as I kept getting a "No Class Defined" error, but I was able to use the SqlServer 2000 driver.

The final roadblock was that I needed to use a user configured with SQL Server Security instead of Windows security. Duh.

Thanks for all your help and suggestions!

Joe Haveman

Advanced Member

Posts: 31

9/23/2009 7:10 PM

Kelly,

Could you explain your last comment a little better? What does "but I was able to use the SqlServer 2000 driver" mean? Thanks.

Joe

Joe Haveman

Advanced Member

Posts: 31

9/24/2009 11:33 AM

Answered my own question. I finally got this to work using SQL 2000. I never got jdbc 1.0 (2005) or jdbc 2.0 (current) to work. My big problem was finding jdbc for sql 2000. Microsoft removed it. I finally found it at:

http://drivers.softpedia....-Download-17961.html

1. Install JDBC 2000
2. Copy the 3 files to lib\ in the lawson designer path
3. Add the class path items:

Include in the designer.bat
;.\lib\msutil.jar;.\lib\msbase.jar;.\lib\mssqlserver.jar

JudeBac

Veteran Member

Posts: 129

3/17/2010 2:52 PM

I just installed PFI and now configuring JDBC. Our database is MS SQL Server 2008 64 bit. My workstation is Win XP 32bit Ver 2002.

I added this to the end of the Designer.bat

;.\lib\xml-apis.jar;.\lib\sqljdbc4.jar;.\lib\sqljdbc.jar

Parameters:
JDBC Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver
Database URL: jdbc:sqlserver://dbserver;DatabaseName=database

When I click build "Unable to load JDBC database driver class com/microsoft/jdbc/sqlserver/SQLServerDriver ..."

I even changed my computer's environment CLASSPATH to include the 2 drivers. Same result

Appreciate your help.
Jude

JudeBac

Veteran Member

Posts: 129

3/17/2010 3:14 PM

Okay, unfortunately I do not have all day to figure out how to make sqljdbc.jar and sqljdbc4.jar.

Good news is ;.\lib\msutil.jar;.\lib\msbase.jar;.\lib\mssqlserver.jar stll works on my desktop.

Regards to all

John Henley

Posts: 3353

3/17/2010 3:14 PM

What JVM version are you using? You need to match correct JDBC to JVM, e.g., you need to use (old and now unsupported) SQL2000 JDBC if you are using JVM 1.4. =

JudeBac

Veteran Member

Posts: 129

3/17/2010 5:18 PM

Hi John

The Win 2008 server JRE 1.6.0_17 I think in the websphere ver 7.0.0 fixpack 5.
My desktop is the same JRE 1.6.0_17

Would that mean I have to use sqljdbc4.jar and sqljdbc.jar on the server side?

On my desktop if I use mssqlserver.jar, msutil.jar and msbase.jar, I can build the SQL, replacing it with sqljdbc4.jar and sqljdbc.jar in Designer.bat, I will get back to the error "Unable to load JCBC..."

Additional note. When I use the old drivers, it will connect, but now the problem is the SQL String Builder will not list the tables with lawson schema, example: lawson.EMPLOYEE bu will list all the tables in other databases like LOGAN and its dbo schemas, and GEN

Thanks,

Jude

Jimmy Chiu

Veteran Member

Posts: 641

3/18/2010 4:29 PM

With JRE 1.6, you will need to use the sqljdbc4.jar on the server side.

JudeBac

Veteran Member

Posts: 129

3/19/2010 12:42 PM

Thanks, will try that next: sqljdbc4.jar.

I might open a new thread on why I cannot see the lawson schema in the SQL Query Build even my dba has my login setup to read lawson schema. I do not have issues with dbo and the custom database.

TBonney

Veteran Member

Posts: 281

10/11/2012 4:51 PM

I know this topic is an old one. However, I hope somebody still sees this post and is able to assist with a reply.

In spite of all the information in this thread, I am unable to get the SQL query node to work on the server. I am running PFI 9017 on 64bit Windows 2008 server. I continue to get the previously mentioned error, "SQLException: JDBC Driver not found: Undefined
Please add jdbc driver to ProcessFlow Server classpath."

Can anyone direct me as to what file (and directory location) I am actually supossed to define this in? As I look around, it seems that there are various bpm and/or process flow related .cfg and .properties files, in multiple directories. So far, I have not found any documentation that clearly states which file(s) need to be updated and how to update them in order to make the jdbc connnection work properly.

Any guidance would be greatly appreciated!