Connect to MS SQL DB server using JDBC drivers 3

[mockler]

Hi All,

I'm trying to write a perl script to connect to a MS SQL server using JDBC drivers. I know you can use a module like DBD::Sybase. The reason I want to use JDBC drivers is because I'm already using the perl module DBD::JDBC module to connect to an Oracle database, and this is for the same project so I wanted to use the same JDBC module. Plus the script I'm creating is for a Java application which uses the JDBC drivers.

Does anybody know if this is possibe? And if so, how?

Any help would be greatly appreciated.

Thanks,

Andrew

 

[mockler]

Nevermind everybody, I got it working.

If anybody is curious as to how, reply back and I can post here what I did.

 

[icrf]

I'm going to have to speak up for the poor soul searching for an answer to this problem a year from now. Nothing drives a person batty like scouring the internet and finding the exact same question, but without solution. Share, please!

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[mockler]

Ok here it is.

First you have to install the perl modules DBI, DBD::JDBC (which will require Convert::BER).

The JDBC is really a proxy server, so you have to start up a java server specifying the drivers to use (this is explained in the DBD::JDBC module on cpan)

My java application was started using (on windows XP):

java -Djdbc.drivers=com.microsoft.jdbc.sqlserver
.SQLServerDriver -Ddbd.port=9001 -Ddbd.trace=tedious com.vizdom.dbd.jdbc.Server

The port is the only thing that you should have to change. In order to start the java application you need some files in your classpath. The files are:
- dbd_jdbc.jar (from the JDBC module)
- log4j-1.2.11.jar (from the JDBC module)
- msbase.jar (JDBC driver for MS SQL server)
- mssqlserver.jar (JDBC driver for MS SQL server)
- msutil.jar (JDBC driver for MS SQL server)



Once you have your java application running, you can write your perl script to connect to this java application which will in turn connect to your database. Below is my perl script that only connects and then disconnects (just to test the connection):

use DBI;

$user="user_name";
$password="password";

$dbh = DBI->connect('dbi:JDBC:hostname=1.1.1.1;port=9001;url=jdbc:microsoft:sqlserver://dbserver.com:1433;databaseName=sqldatabase', $user, $password);

$dbh->disconnect;


The IP address 1.1.1.1 is the address of the java application you have already running (same with the port). The "dbserver.com" is your database server, and the "sqldatabase" is the database name.

Hope that helps somebody.

And I agree, searching the net only to find the same question you have just unanswered is a pain in the arse.

-Andrew

 

[icrf]

Nice. So DBD::JDBC actually connects to a Java program that connects to the JDBC driver? Seems a strange set-up, but kind of neat that Java can just relay the connection.

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo

 

[mockler]

As far as I understand it yes. But I am new at using Perl so I very well could be wrong.



NOTE; the part of the perl script above that contains the database name isn't quite working. ("databaseName=sqldatabase") I've realized that it isn't actually connecting to the database that I specified but instead the master database on the SQL server. I'm working on a why to rectify that.

 

[mockler]

After almost throwing my computer out the window I figured out why I wasn't connecting to the proper database. The reaons is the characters ";" and "=" in the connection string (or DSN if you will) must be URL encoded.

Therefore the character ";" in URL encoded is: %3b
Therefore the character "=" in URL encoded is: %3d

So the connetion string used above should look like:

url=jdbc:microsoft:sqlserver://dbserver.com:1433%3bdatabaseName%3dsqldatabase

 

[PaulTEG]

back twice with info, we value that ;-)

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[1DMF]

Personally I use WIN32::ODBC , but have given a well deserved star for your valued input.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.