Permlink Replies: 47 - Pages: 4 [ 1 2 3 4 | Next ] - Last Post: 20-Jul-2015 13:45 Last Post By: RobM Threads: [ Previous | Next ]
davidekholm

Posts: 21,290
Registered: 10/18/02
Reading comments from an external database
Posted: 19-Sep-2004 13:43
  Click to reply to this thread Reply
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: 2,654
Registered: 06/18/05
Re: Reading comments from an external database
Posted: 19-Mar-2007 23:11   in response to: davidekholm in response to: davidekholm
  Click to reply to this thread Reply
Very good info! Thanks, David. :-)
twindude

Posts: 31
Registered: 10/26/05
Re: Reading comments from an external database
Posted: 03-May-2007 21:28   in response to: alan927 in response to: alan927
  Click to reply to this thread Reply
where is the index.htt page? after you create the album or under what setting

running 7.1 version
jGromit

Posts: 32,917
Registered: 01/31/06
Re: Reading comments from an external database
Posted: 04-May-2007 00:17   in response to: twindude in response to: twindude
  Click to reply to this thread 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: 21,290
Registered: 10/18/02
Re: Reading comments from an external database
Posted: 17-May-2007 13:24   in response to: twindude in response to: twindude
  Click to reply to this thread Reply
Just press CTRL+ALT+S to open the directory for the selected skin.
TomCee

Posts: 1,440
Registered: 07/11/05
Q. for David
Posted: 28-May-2007 20:11   in response to: davidekholm in response to: davidekholm
  Click to reply to this thread 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: 1,440
Registered: 07/11/05
Re: Q. for David
Posted: 28-May-2007 20:40   in response to: TomCee in response to: TomCee
  Click to reply to this thread 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: 2,654
Registered: 06/18/05
Re: Q. for David
Posted: 29-May-2007 01:21   in response to: TomCee in response to: TomCee
  Click to reply to this thread 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: 7,459
Registered: 08/30/04
Re: Q. for David
Posted: 29-May-2007 01:33   in response to: alan927 in response to: alan927
  Click to reply to this thread 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: 21,290
Registered: 10/18/02
Re: Q. for David
Posted: 29-May-2007 09:07   in response to: jimberry in response to: jimberry
  Click to reply to this thread Reply
Yes
davidekholm

Posts: 21,290
Registered: 10/18/02
Re: Q. for David
Posted: 29-May-2007 09:11   in response to: TomCee in response to: TomCee
  Click to reply to this thread 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: 7,459
Registered: 08/30/04
Re: Q. for David
Posted: 29-May-2007 15:40   in response to: TomCee in response to: TomCee
  Click to reply to this thread 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: 1,440
Registered: 07/11/05
Re: Q. for David
Posted: 29-May-2007 17:34   in response to: jimberry in response to: jimberry
  Click to reply to this thread 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: 02/22/08
Re: Reading comments from an external database
Posted: 22-Feb-2008 06:51   in response to: davidekholm in response to: davidekholm
  Click to reply to this thread 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: 21,290
Registered: 10/18/02
Re: Reading comments from an external database
Posted: 22-Feb-2008 21:46   in response to: jps292k in response to: jps292k
  Click to reply to this thread Reply
I think I can help you. Remove the ja:if exists="comment" test.
Legend
Forum admins
Helpful Answer
Correct Answer

Point your RSS reader here for a feed of the latest messages in all forums