!!!JDBCProvider
%%commentbox
!Sorry for the inconvenience!
This being a Wiki, [the previous incarnation of this page|http://www.jspwiki.org/wiki/JDBCProviders?version=63] can be found by browsing the revision history.\\
We thought it was time to put forth a new release of both the code and the present page...\\
Please note that all the attachments on this page are old versions! You should always get the latest release [here!|http://jdbcprovider.berlios.de]
%%
!!The March 2007 Release of the [JDBCProvider|http://jdbcprovider.berlios.de] is out!
!Features:
*Configurable connection provider:
**~JDBCConnectionProvider (~DriverManager based)
**~DBCPConnectionProvider (for connection pools managed by JDBCProvider ([Apache commons DBCP|http://jakarta.apache.org/commons/dbcp] based))
**C3P0ConnectionProvider (for connection pools managed by JDBCProvider ([C3P0|http://www.mchange.com/projects/c3p0] based)) 
**~JNDIConnectionProvider (for container managed data sources)
*Change notes now work!
*Added support for [PostgreSQL|http://www.postgresql.org]
*Added support for [SQL Anywhere|http://www.ianywhere.com/products/sql_anywhere.html]
*(Re-)added support for [Microsoft SQLServer|http://www.microsoft.com/sql/default.mspx]
*The ~VersioningProvider interface is now implemented
*Supports driver specific connection properties
*Supports DBCP- and C3P0-specific properties (initial pool size, etc.)
*Configuration properties are moved out of jspwiki.properties to separate properties files
**Only 3 properties need to be added to jspwiki.properties!

Other than that, the code has been massively refactored and cleaned up.

!Compatibility
The March 2007 release is built against JSPWiki v2.4.91
It is tested against these JSPWiki versions:
*2.5.22
** Latest development release (at testing time)
**Works!

*2.4.91
** Latest stable release (at testing time)
**Works!

*2.3.50-alpha
**Oldest available 2.3-release
**Works!

*2.2.33
**Latest 2.2-release
**Does NOT work! The API is just too different. Sorry.
**Please see [the previous incarnation of this page|http://www.jspwiki.org/wiki/JDBCProviders?version=63] for 2.2 usage. There's a link to a release that should work on (at least) 2.2.28

!A note on database versions

*[MySQL|http://www.mysql.org]
**Required minimum version: 4.1
**JDBCProvider tested on
***4.1.21
***5.0.27
***5.0.33

*[PostgreSQL|http://www.postgresql.org]
**Required minimum version: ?
**JDBCProvider tested on
***8.2.3

*[SQL Anywhere|http://www.ianywhere.com/products/sql_anywhere.html]
**Required minimum version: ?
**JDBCProvider tested on
***?

*[Sybase|http://www.sybase.com/products/databasemanagement]
**Required minimum version: ?
**JDBCProvider tested on
***?

*[Microsoft SQLServer|http://www.microsoft.com/sql/default.mspx]
**Required minimum version: ?
**JDBCProvider tested on
***MSDE (Microsoft SQL Server Desktop Engine 2000) SP4
***Microsoft SQL Server 2005 Express (SQL Server 9.0.3042)

*[DB2|http://www.ibm.com/developerworks/downloads/im/udbexp/]
**DB2 Version 9
**Look at the [DB2Support] page for installation information and sample files

!So, where do I get the March 2007 Release?
The latest release can always be found [here|http://jdbcprovider.berlios.de].\\
__Please note that all the attachments on this page are old versions! You should always get the latest release by following the link above!__

!How do I use it?
Please see the readme.txt file in the distribution. It has sections on installing for the first time, upgrading from previous versions, and for migrating from other providers. It is fairly comprehensive, we believe... :)
%%(color:red;)
__NB! We seem to have overlooked a runtime-dependency:\\
Apache commons-collections is actually required at runtime by the commons-pool libs. If you use the ~DBCPConnectionProvider, and you do not have commons-collections on the class path for the JSPWiki webapp, you should get [commons-collections|http://jakarta.apache.org/commons/collections/] and place the jar in ...jspwiki/WEB-INF/lib__\\
Thank you, Bob, for pointing this out!
%%
!I can't get it to work properly. What now?
If you get stuck, you are welcome to post a message on one of the [JDBCProvider forums|http://developer.berlios.de/forum/?group_id=6099]:
*[The Help forum|http://developer.berlios.de/forum/forum.php?forum_id=18309] for support
*[The Open Discussion forum|http://developer.berlios.de/forum/forum.php?forum_id=18308] for general discussions
You could also post a comment to this page.

We hope you'll find this useful!

Sincerely,\\
The JDBCProvider Developer Team
%%sub
Xan Gregg\\
Søren Berg Glasius\\
Mikkel Troest\\
Milt Taylor
%%


----

I followed all install instructions. Everything seems ok.

How can I test this? How can I save a page as a test?

New here, thanks for any help.

--AnonymousCoward, 13-Jul-2007


----

Has anybody out there some experiences with JSPWiki and Oracle DB???

MySQL is running without any problems ... ;-)

--Mantsch Thomas, 27-Dec-2007


----

Hey Thomas, for [Oracle] take a look [here|http://www.jspwiki.org/wiki/JSPWiki%20and%20Websphere%20Application%20Server%206.0.2.5#section-JSPWiki+and+Websphere+Application+Server+6.0.2.5-Step4ImplementingOracleAsDatabase]. To work with [Oracle], I have modified some files of this package.

--145.225.60.7, 04-Jan-2008

----

Hello, Thx for your help/information.

But Ive got another question. How can I use DBCPConnectionProvider. Are there any special things to consider?

--Mantsch Thomas, 20-Mar-2008


----

Good evening,

I have fixed little bug(feature) related to migration of attachments history. Without this patch there are migrated only latest versions of attachments, with it there is migrated everything incl. older versions of attachment files.

__version of Wiki engine__: JSPWiki v2.4.104\\
__version of JDBCProvider__: JDBCProvider 2007-03-05 Version

JKr

{{{
JDBCAttachmentProvider.java
***************
*** 506,514 ****
              List attachments = importProvider.listAllChanged( new Date( 0 ) );
              for( Iterator i = attachments.iterator(); i.hasNext(); ) {
                  Attachment att = ( Attachment ) i.next();
!                 InputStream data = importProvider.getAttachmentData( att );
!                 info("Migrating Attachment: "+att);
!                 putAttachmentData(att,data);
              }
          } catch( ProviderException e ) {
              throw new IOException( e.getMessage() );
--- 506,518 ----
              List attachments = importProvider.listAllChanged( new Date( 0 ) );
              for( Iterator i = attachments.iterator(); i.hasNext(); ) {
                  Attachment att = ( Attachment ) i.next();
!               List attachments_history=importProvider.getVersionHistory(att);
!               for( Iterator ii = attachments_history.iterator(); ii.hasNext(); ) {
!                   Attachment att_history = ( Attachment ) ii.next();
!                   InputStream data = importProvider.getAttachmentData( att_history );
!                   info("Migrating Attachment: "+att_history);
!                   putAttachmentData(att_history,data);
!               }
              }
          } catch( ProviderException e ) {
              throw new IOException( e.getMessage() );
}}}

--JKr, 01-Jun-2008


----

Good afternoon,

I need to migrate from text files founded in src/wikipages (JSPWiki project) to database tuples, but I can't do it with the current version of JDBCPageProvider because the following error in the JDBCPageProvider.initialize method:

int count = getPageCount();
debug("Page count at startup: "+count);
debug("Database->"+getConfig().toString());
if( getConfig().hasDesireToMigrate())
{
    if(count == 0) {
        migratePages( engine);
    
count needs to be different from zero to migrate the N pages in the file system, ok? 
The solution I adopted for this problem without alter the JDBCProvider was create a class that extends the JDBCPageProvider and implements initialize and migratePages. Then I wrote count != 0 in the if statement.
 

--rafael.vieira, 22-Aug-2008

__Possible Solution:__I think this is caused by a permissions error (at least on Sybase it is).  You need to grant select/delete/update/insert to the user that is accessing the tables.
--Lou, 2009-04-07

----
Hi,
\\
is there a way to get the source files for the classes included in the JDBCProvider.jar? I wanted to change the code so I am able (as a user) to delete weblogentries!
\\thanks for the help,
\\--Jonathan, 25-Aug-2008


----
Hi! 
\\
there are incorrect mysql queries in "jdbcprovider.mysql.properties".
page.getAllPages and page.getAllPagesSince

must be
{{{
page.getAllPages = SELECT WIKI_PAGE.NAME, VERSION, CHANGE_TIME, CHANGE_BY, CHANGE_NOTE FROM WIKI_PAGE,(SELECT MAX(WIKI_PAGE.VERSION) AS PV,WIKI_PAGE.NAME FROM WIKI_PAGE GROUP BY NAME) AS _WIKI_PAGE WHERE (WIKI_PAGE.VERSION = _WIKI_PAGE.PV) AND (WIKI_PAGE.NAME = _WIKI_PAGE.NAME)

page.getAllPagesSince = SELECT WIKI_PAGE.NAME, VERSION, CHANGE_TIME, CHANGE_BY, CHANGE_NOTE FROM WIKI_PAGE,(SELECT MAX(WIKI_PAGE.VERSION) AS PV, NAME FROM WIKI_PAGE GROUP BY NAME) AS _WIKI_PAGE WHERE WIKI_PAGE.VERSION = _WIKI_PAGE.PV AND WIKI_PAGE.NAME = _WIKI_PAGE.NAME AND WIKI_PAGE.CHANGE_TIME > ?
}}}
instead of

{{{
page.getAllPages = SELECT NAME, VERSION, CHANGE_TIME, CHANGE_BY, CHANGE_NOTE FROM WIKI_PAGE,(SELECT MAX(WIKI_PAGE.VERSION) AS PV FROM WIKI_PAGE GROUP BY NAME) AS _WIKI_PAGE WHERE WIKI_PAGE.VERSION = _WIKI_PAGE.PV

page.getAllPagesSince = SELECT NAME, VERSION, CHANGE_TIME, CHANGE_BY, CHANGE_NOTE FROM WIKI_PAGE,(SELECT MAX(WIKI_PAGE.VERSION) AS PV FROM WIKI_PAGE GROUP BY NAME) AS _WIKI_PAGE WHERE WIKI_PAGE.VERSION = _WIKI_PAGE.PV AND WIKI_PAGE.CHANGE_TIME > ?
}}}


--Konstantin Chekushin, 11-Feb-2009
----
FYI, I have pages and attachments running under 2.8.1 with no changes to the class files.

--Lou, 2009-04-07


----

Just a clarification, I needed to make the changes Konstantin Chekushin proposes above to get the Lucene searches to work when using the JDBC provider.


--Joakim, 09-Apr-2009 10:30


----

Hi guys.. does this plugin work with the latest release of jspwiki 2.8.2? 

--ziggy, 11-Jul-2009 02:42