Reading comments from an external ...

davidekholm

Posts: 8,982
Registered: 18-Oct-2002

Reading comments from an external database
Posted: 19-Sep-2004 13:43

Some users have the comments for images stored in an external database and want JAlbum to use this info. Here's how to connect JAlbum to an external database. The example assumes that you are able to create an "ODBC datasource" for the database. In Windows you do this using the Control Panel. The datasource in this example is called "commentdb" and it happens to point to an MS Access database (could be any database) that contains a table called "comments". The table has two columns, one "filename" and one "comment" column. Here's how to read the data from this table into JAlbum and add the right comment to each image:

Step 1 Create a comments table in a database with the two columns "filename" and "comment".

Step 2. Fill the table with some data.

Step 3, create an ODBC datasource called "commentdb" pointing to your database (With MS Access you point the name to the .mdb file itself).

Step 4, start JAlbum, select a skin you wish to modify and add the following to the top of the index.htt file of the skin:

<%!
  import java.sql.*;
  Map comments = new HashMap();
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  Connection con = DriverManager.getConnection("jdbc:odbc:commentdb");
  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select * from comments");
  while (rs.next()) {
    String fileName = rs.getString(1);
    String comment = rs.getString(2);
    comments.put(fileName, comment);
  }
  rs.close();
  stmt.close();
  con.close();
%>

The whole contents of the "comments" table is now stored in the Map type object called "comments". Now edit the slide.htt file and add the comment for each image where you want it to show. Write like this:

<%= comments.get(fileName) %>

For more on using Java with databases, see this tutorial: http://java.sun.com/docs/books/tutorial/jdbc/basics/connecting.html

alan927

Posts: 695
Registered: 18-Jun-2005

Re: Reading comments from an external database
Posted: 19-Mar-2007 23:11

in response to: davidekholm

Reply

Very good info! Thanks, David.

twindude

Posts: 1
Registered: 26-Oct-2005

Re: Reading comments from an external database
Posted: 03-May-2007 21:28

in response to: alan927

Reply

where is the index.htt page? after you create the album or under what setting

running 7.1 version

jGromit

Posts: 18,269
Registered: 31-Jan-2006

Re: Reading comments from an external database
Posted: 04-May-2007 00:17

in response to: twindude

Reply

where is the index.htt page? after you create the
album or under what setting

The index.htt is the template file from which a skin creates its index pages. So, if you change something in index.htt, that change affects every album you create with that skin.

The template files, in a normal installation, will be under c:\program files\jalbum\skins, and then under each skin subdirectory.

davidekholm

Posts: 8,982
Registered: 18-Oct-2002

Re: Reading comments from an external database
Posted: 17-May-2007 13:24

in response to: twindude

Reply

Just press CTRL+ALT+S to open the directory for the selected skin.

TomCee

Posts: 845
Registered: 11-Jul-2005

Q. for David
Posted: 28-May-2007 20:11

in response to: davidekholm

Reply

Hi,

is this easy expandable to 3 columns?? Or can you only read & write filename = comment??

regards,
Tom

Oh, and a second question: where to store the database, in the input folder??

Message was edited by: TomCee

TomCee

Posts: 845
Registered: 11-Jul-2005

Re: Q. for David
Posted: 28-May-2007 20:40

in response to: TomCee

Reply

ah, I think the easiest way to get comments and for example texts is to create 2 tables and 2 hasmaps, 1 for the comments and one for the texts.

Tom

correction 1 table with 3 rows, 2 hashmaps
Now this sounds like fun David! I'm for sure gonna play with this!

Message was edited by: TomCee

alan927

Posts: 695
Registered: 18-Jun-2005

Re: Q. for David
Posted: 29-May-2007 01:21

in response to: TomCee

Reply

The intended location for the database would be on your ISP's server in a MySQL or PostgreSQL database. I wouldn't try this using a file in the directory structure.

jimberry

Posts: 1,835
Registered: 30-Aug-2004

Re: Q. for David
Posted: 29-May-2007 01:33

in response to: alan927

Reply

The intended location for the database would be on
your ISP's server in a MySQL or PostgreSQL database.

But isn't this intended to be a method for extracting data from the database during generation, i.e. locally not on the server?

davidekholm

Posts: 8,982
Registered: 18-Oct-2002

Re: Q. for David
Posted: 29-May-2007 09:07

in response to: jimberry

Reply

Yes

davidekholm

Posts: 8,982
Registered: 18-Oct-2002

Re: Q. for David
Posted: 29-May-2007 09:11

in response to: TomCee

Reply

is this easy expandable to 3 columns?? Or can you
only read & write filename = comment??

It is easily expandable for sure. Instead of storing a plain String as the value for the comments Map object, store a whole Map with several fields in it for instance.

jimberry

Posts: 1,835
Registered: 30-Aug-2004

Re: Q. for David
Posted: 29-May-2007 15:40

in response to: TomCee

Reply

Oh, and a second question: where to store the
database, in the input folder??

If you don't already have a server for the db, maybe xampp wiould be useful. It is an easily installed distribution of Apache, with MySQL and PHP included.

TomCee

Posts: 845
Registered: 11-Jul-2005

Re: Q. for David
Posted: 29-May-2007 17:34

in response to: jimberry

Reply

Hi Jim,

my q. was not aimed at external storage, but the place where david's code for extracting the comments is aimed at. My guess is the "" image directory".

jps292k

Posts: 1
Registered: 22-Feb-2008

Re: Reading comments from an external database
Posted: 22-Feb-2008 06:51

in response to: davidekholm

Reply

Hi, David,

I am new to Jalbum. I have taken many many photos and would like to organize it in digital album by using browser to view them. I 've been trying to use your code to bring in comments from a Access table. But it did not work form. The comments did not show in the album.

Here is what I did:

1. Create an Access Database file with a table named as "comments"
2. In the table "comments", I defined 3 columns: ID, fileName, comments
I also tried to delete one column (ID). It did not work either.
3. Cut and paste your code in "index.htt", in the directory: C:\jAlbum74\skins\Chameleon
4. placed <%= comments.get(fileName) %> in "slide.htt" file as illustrated below.

<img id="slide" src="$imagePath" class="slideImage" width="${mImageWidth}" height="${mImageHeight}" ${copyProtectStr}alt="$fileName<ja:if exists="comment"> -

<%= comments.get(fileName) %>

</ja:if>" title=" $text.downsizedImage ~~$fileName - <%=fileSize/1024%>kB~~ "<ja:if test=<%=!chameleon.imageNavigationNone%>> usemap="#imagemap"</ja:if> /></td>

5. Setup ODBC Data source
6. Run JAlbum, and set it up too.

When I check the album, place the coursor on a slides, no input comments is shown. The comments is not placed under the photo either in the slide show.

Could you help me to resolve the problem?

Thank you,

Jim

davidekholm

Posts: 8,982
Registered: 18-Oct-2002

Re: Reading comments from an external database
Posted: 22-Feb-2008 21:46

in response to: jps292k

Reply

I think I can help you. Remove the ja:if exists="comment" test.