Difference between revisions of "Connecting To SQL Server"

From PresenceWiki
Jump to: navigation, search
(Pooling Options)
 
(3 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
{{DatabaseConnectivityHeader}}
 +
 
The settings for your SQL Database Resource in Presence should be:-
 
The settings for your SQL Database Resource in Presence should be:-
  
Line 8: Line 10:
 
This can be found in the sqljdbc.jar (or sqljdbc4.jar for Java version 6 which Presence 3.5.5 uses).
 
This can be found in the sqljdbc.jar (or sqljdbc4.jar for Java version 6 which Presence 3.5.5 uses).
  
These jars can in turn be found in the Microsoft SQL Server 2005 driver for JDBC
+
These jars can in turn be found in the Microsoft SQL Server 2005 driver for JDBC which can be downloaded from:-
 +
http://www.microsoft.com/downloads/
  
 
Import the jar via Presence - Tools - Jar Manager and then browse to the SQLServerDriver class.
 
Import the jar via Presence - Tools - Jar Manager and then browse to the SQLServerDriver class.
Line 32: Line 35:
 
As the Presence Jar Manager caches the classes you should restart the server if you have changed the
 
As the Presence Jar Manager caches the classes you should restart the server if you have changed the
 
location of a class.
 
location of a class.
 +
 +
 +
=== Pooling Options ===
 +
 +
If the Microsoft SQL Server engine is restarted any connections are retained but are no longer usable. For this reason you should always specify a custom query to validate the connection. Select the second tab in the Database Resource Editor and check the option "Use custom query to validate connections". Enter an SQL Statement in the text field provided, preferably something that is quick to run.
 +
 +
 +
{{DatabaseConnectivityHeader}}

Latest revision as of 11:01, 4 May 2011

Database Connectivity > Connecting To SQL Server

The settings for your SQL Database Resource in Presence should be:-

http://www.international-presence.com/images/docs/jdbc/sqlserverdetails.png

Note, the driver class is:- com.microsoft.sqlserver.jdbc.SQLServerDriver

This can be found in the sqljdbc.jar (or sqljdbc4.jar for Java version 6 which Presence 3.5.5 uses).

These jars can in turn be found in the Microsoft SQL Server 2005 driver for JDBC which can be downloaded from:- http://www.microsoft.com/downloads/

Import the jar via Presence - Tools - Jar Manager and then browse to the SQLServerDriver class.

The connection URL takes the form:- jdbc:sqlserver://ServerName:1433:databaseName=MyDatabase;

Note if you are using Microsoft SQL Server 2000 driver for JDBC then the driver class is
com.microsoft.jdbc.sqlserver.SQLServerDriver (i.e. the jdbc and the sqlserver are swapped around)

After hitting test and using the correct username and password you should see the following:-

http://www.international-presence.com/images/docs/jdbc/sqlconnect.png

You can then use the Resource withing Presence in the SQL node.

http://www.international-presence.com/images/docs/jdbc/sqlresults.png

Presence Version 3.5.4 uses the java 1.5 JVM which in turn will need the sqljdbc.jar
Presence Version 3.5.5 uses the java 1.6 JVM which in turn uses sqljdbc4.jar

As the Presence Jar Manager caches the classes you should restart the server if you have changed the location of a class.


Pooling Options

If the Microsoft SQL Server engine is restarted any connections are retained but are no longer usable. For this reason you should always specify a custom query to validate the connection. Select the second tab in the Database Resource Editor and check the option "Use custom query to validate connections". Enter an SQL Statement in the text field provided, preferably something that is quick to run.


Database Connectivity > Connecting To SQL Server