Re: Use PSQLFS for photo storage

[Reid Thompson <reid.thompson@xxxxxxxx>]

On Tue, 2009-01-13 at 18:22 -0600, Jason Long wrote:
> Never used Python or Perl.  I use primarily Java.  I was thinking of
> doing something like
> INSERT INTO pictures (filename,data) VALUES ('filename','/path/to/my/image/img0009.jpg');
> 
> But, this syntax doesn't seem to be supported.
> 
> Maybe I can use a custom C function to get the contents of the file.  Then do something like
> 
> INSERT INTO pictures (filename,data) VALUES ('/path/to/my/image/img0009.jpg',getBinaryFileContents('/path/to/my/image/img0009.jpg'));
> 
> Is there some postgres contrib for something like this?
> 
> 
> 
> 
Simple java stub test program attached.  Modify to meet your needs.  See
file header for URL of original example.

/**
 * A demo program to show how jdbc works with postgresql
 * Nick Fankhauser 10/25/01
 * nickf@xxxxxxxxx or nick@xxxxxxxxxxxxxxx
 * This program may be freely copied and modified
 * Please keep this header intact on unmodified versions
 * The rest of the documentation that came with this demo program
 * may be found at http://www.fankhausers.com/postgresql/jdbc
 */



import java.sql.*;   // All we need for JDBC
import java.text.*;
import java.io.*;

public class HelloPostgresql
{
  Connection       db;        // A connection to the database
  Statement        sql;       // Our statement to run queries with
  DatabaseMetaData dbmd;      // This is basically info the driver delivers
                              // about the DB it just connected to. I use
                              // it to get the DB version to confirm the
                              // connection in this example.

  public HelloPostgresql(String argv[])
    throws ClassNotFoundException, SQLException
  {
    String database = argv[0];
    String username = argv[1];
    String password = argv[2];
    String imagefilepath = argv[3];
    //System.out.println("img fp: "+imagefilepath+"\n");

    Class.forName("org.postgresql.Driver"); //load the driver
    db = DriverManager.getConnection("jdbc:postgresql:"+database,
                                     username,
                                     password); //connect to the db
    dbmd = db.getMetaData(); //get MetaData to confirm connection
    System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
                       dbmd.getDatabaseProductVersion()+" successful.\n");
    sql = db.createStatement(); //create a statement that we can use later

    String sqlText = "create table jdbc_demo_bytea (imgname text, img bytea)";
    PreparedStatement ps = db.prepareStatement(sqlText);
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);

    File file = new File(imagefilepath);

    //System.out.println("filepath: "+file.getPath()+"\n");
    String dupimagefilepath = new String(file.getPath() + "_Duplicate");
    //System.out.println("dup img fp: "+dupimagefilepath+"\n");
    try {
        FileInputStream fis = new FileInputStream(file);
        long startts = System.currentTimeMillis();
        ps = db.prepareStatement("INSERT INTO jdbc_demo_bytea VALUES (?, ?)");
        ps.setString(1, file.getName());
        ps.setBinaryStream(2, fis, (int)file.length());
        int rows_inserted = ps.executeUpdate();
        //System.out.println("inserted: "+rows_inserted+"\n");
        ps.close();
        long stopts = System.currentTimeMillis();
        System.out.println("Store time = " + (stopts - startts));
        fis.close();
    }
    catch ( Exception e)
    {
        System.out.println(e.toString());
    }

    //try {
        // Thread.currentThread().sleep(1000);
        long startts = System.currentTimeMillis();
        ps = db.prepareStatement("SELECT img FROM jdbc_demo_bytea WHERE imgname = ?");
        ps.setString(1, imagefilepath);

        ResultSet rs = ps.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        //System.out.println("fp : "+ rsmd.getColumnName(1) +"\n");
        //System.out.println("#columns : "+rsmd.getColumnCount()+"\n");
        while (rs.next()) {
            //System.out.println("got next\n");
            //byte[] imgBytes = rs.getBytes(1);
            byte[] imgBytes = new byte[40000000];
                imgBytes = rs.getBytes(1);
        long stopts = System.currentTimeMillis();
        System.out.println("Retreive time = " + (stopts - startts));
            try {
                File dupfile = new File(dupimagefilepath );
                //System.out.println("write it : "+imgBytes.length+"\n");
                OutputStream fos = new FileOutputStream (dupfile ) ;
                fos.write(imgBytes);
                fos.close();
                fos = null;
            }
            catch ( Exception e)
            {
                System.out.println(e.toString());
            }
        }
        rs.close();
    //}
   // catch(InterruptedException ie){
        //If this thread was intrrupted by nother thread
    //}

    ps.close();
    sqlText = "drop table jdbc_demo_bytea";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);

    db.close();
  }

  public static void correctUsage()
  {
    System.out.println("\nIncorrect number of arguments.\nUsage:\n "+
                       "java   \n");
    System.exit(1);
  }

  public static void main (String args[])
  {
    if (args.length != 4) correctUsage();
    try
    {
      HelloPostgresql demo = new HelloPostgresql(args);
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }
  }
}

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general