|
|
Re: Linking Records to Files on a Server
|
Posted: Oct 12, 2004 2:33 AM
|
|
Assuming that everyone of your records has exactly one pdf file, you could use a simple SQL command in Access:
UPDATE [engineering drawings] SET [engineering drawings].[pathToFile] = "\\server_name\folder_name\" & [drawing number] & ".pdf";
Just open your db in Access and create a new Query. Than select the SQL-View mode and copy this command there.
But ... you said every record can have multiple files.
So we have to do it the hard way.
The following program creates a list of all existing files following certain patterns ("123.pdf", "...123a.pdf", "...123b.pdf") and puts "; " between them ("file1; file2; file3").
Create a class "UpdateProgram" and copy the folling source code into it.
Than you need a JDBC/Access driver to create the connection.
import java.io.File;
import java.sql.*;
import java.util.Vector;
public class UpdateProgram {
private static void doTheUpdate() {
//You have to create a Connection to your Database here
//I use Jadozoom (IZMADO), maybe ther e are better JDBC/Access drivers around
Connection myConnection = .......
String sqlString = "SELECT [pathToFile] FROM [engineering drawings]";
boolean statementOk = false;
try {
Statement stmt = myConnection.createStatement();
statementOk = true;
ResultSet rs = stmt.executeQuery(sqlString);
final String path = "\\\\server_name\\folder_name\\";
String[] pattern = new String[] {".pdf", "a.pdf", "b.pdf"};
Vector v = new Vector();
while (rs.next()) {
String recordID = rs.getString("drawing number");
boolean fileFound = false;
if (recordID != null && recordID.length() > 0) {
//Every "line" contains the id in String format, then a list of all existing files.
Vector line = new Vector();
line.add(recordID);
for (int i = 0; i < pattern.length; i++) {
String fileName = path + recordID + pattern[i];
File pdfFile = new File (fileName);
if (pdfFile.exists()) {
fileFound = true;
line.add(fileName);
}
if (fileFound)
v.add(line);
}
}
}
rs.close();
rs = null;
// I splitted this two parts because my Access-JDBC driver supports only on open connection and only one statement at the same time
myConnection.setAutoCommit (false);
for (int i = 0; i < v.size(); i++) {
Vector line = (Vector)v.get(i);
String id = (String)line.firstElement();
String fileList = (String)line.get(1);
for (int j = 2; j < line.size(); j++)
fileList+= "; " + (String)line.get(j);
sqlString = "UPDATE [engineering drawings] SET [engineering drawings].[pathToFile] = \"" + fileList + "\" WHERE [drawing number] = " + id;
//I assume, that the id is a integer. If it is a String, then use ... WHERE [drawing number] = \"" + id + "\"";
stmt.executeUpdate(sqlString);
}
myConnection.commit();
stmt.close();
stmt = null;
myConnection.close();
myConnection = null;
} catch (SQLException e) {
//You land here if there was a error with the connection, statement or the sql string.
System.out.println (e.getMessage());
if (statementOk)
System.out.println ("lastSQLString used: " + sqlString);
else
System.out.println ("Coudln't even create the statement. Is the connection ok?");
}
}
public static void main(String[] args) {
doTheUpdate();
}
}
I sure hope I didn't make too many errors.
Someone knows a free Access JDBC driver? Jadozoom is free for 30 days, but there must be some alternative to it.
|
|
Previous Topic
