Difference between revisions of "Database Resource"

From PresenceWiki
Jump to: navigation, search
(Connection Details)
(Connection Details)
Line 9: Line 9:
 
http://www.international-presence.com/images/docs/res/sql/sqlres_connectiondetails.png
 
http://www.international-presence.com/images/docs/res/sql/sqlres_connectiondetails.png
  
We can also include a properties file to set the connection properties via a file.
+
You may include a properties file to pass extra properties to the connection and to override existing properties.
  
For instance in the case shown, you could override the username and password by creating a file props.txt
+
Existing Properties that may be overwritten
 
+
user=admin
+
password=presadmin
+
 
+
You may include a properties file to pass extra properties to the connection and to override existing properties.
+
  
Existing Properties that may be overwritten
 
 
  driver=
 
  driver=
 
  url=
 
  url=

Revision as of 10:01, 28 January 2010

Connection Details

In the Connection Details tab we specify the name of the resource and the driver class, which may need to be imported via the jar manager.

After this the we need the connection url which may or may not include the database name.

Note, this tab also displays the internal Resource ID for this resource.

http://www.international-presence.com/images/docs/res/sql/sqlres_connectiondetails.png

You may include a properties file to pass extra properties to the connection and to override existing properties.

Existing Properties that may be overwritten

driver=
url=
user=
password=

A test of the connection should reveal:-

http://www.international-presence.com/images/docs/res/sql/sqlres_connected.png

Connection Pooling

Here you can tell Presence to "Re-use connection between queries" for this resource.

To create a new connection can be time consuming for the server, so this tells Presence to cache the connections and reuse them.

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

Sometimes connections can be lost or dropped between calls, so it is advisable to specify a custom query.

Presence will first run this query on the retrieved connection to check that the connection is still valid.

Because of this the custom query should be just a small query on a small table.

This will be sufficient to let Presence know whether the connection for that resource still exists, before it uses it.

If the connection is broken then Presence will remove it from the cache and create a new one.

Global Password Update

As it is common to store the username and password in the SQL Node, rather than the Resource, the Global Password Update tab

provides a way to update the username and password for all SQL Nodes that use this SQL Resource.

http://www.international-presence.com/images/docs/res/sql/sqlres_passwordupdate.png

Overriding values via variables

Note it is also possible to override the username and password and even the SQL Resource for an SQL Node via presence variables, namely:-

http://www.international-presence.com/images/docs/res/sql/sqlres_vars.png

We set the variables before an SQL Node

${control.database.override} If set to a Resource ID then the SQL Node will this Resource rather than its specified Resource.
 
${control.database.user} SQL node will use this value for the username in the connection

${control.database.password} SQL node will use this value for the password in the connection

Further Reading

For an overview of the SQL Node that we use to run SQL statements see SQL Statement Nodes

For more info on how to connect to different types of database and how to aid connection pooling see Database Connectivity