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
* [email protected] or [email protected]
* 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
