Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Inserting file into a database

by Anonymous Monk
on Jan 09, 2003 at 18:08 UTC ( #225593=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I havent worked with DBI in Perl so I wondering if I am in the right direction for inserting a record into a database. I want to retrieve a file from a directory and insert it into my Access database. Please let me know how I can get the filename and then insert it into a database? I think I have the database entry part correct so far? Any help would be appreciated.
use strict; my $filename = "File_Name_One"; my $letter = "A"; my $dbh = DBI->connect("DBI:ODBC:databasename", "","")) my $firstvar = $dbh->quote($filename); my $secondvar = $dbh->quote($letter); my $statement = "Insert into tableOne (filename, letter) values ($firstname, $letter)";

Replies are listed 'Best First'.
Re: Inserting file into a database
by rbc (Curate) on Jan 09, 2003 at 18:24 UTC
    Hi Anony,

    I don't use Access very much. I usually use Oracle and
    Oracle comes with a utility called sqlldr which is
    used to load files into database tables. I bet Access
    has similar functionality.

    If not or if you just feel like it you can go with
    the roll-your-own approach. But I would suggest using
    "place holders" in your SQL ...
    my $statement = "Insert into tableOne (filename, letter) values (?, ?) +"; ... $sth->execute($filename, $letter);
Re: Inserting file into a database
by derby (Abbot) on Jan 09, 2003 at 18:35 UTC
    I'm not up on Access, but I believe this piece of SQL will just load the filename and the letter 'A' into the table. Do you want to load the contents of the file into the table? As one field in a table? Then you're going to have to read the file contents into a scalar and then insert that into the table:

    use strict; my $filename = "File_Name_One"; my $letter = "A"; my $filecontents = ""; open( FH, "<$filename" ) || die "unable to open $filename ($!)\n"; { local( $/ ); undef $/; $filecontents = <FH>; } close( FH ); my $firstvar = $dbh->quote($filecontents); my $secondvar = $dbh->quote($letter); my $statement = "Insert into tableOne (filename, letter) values ($fil +econtents, $letter)";

    The usual caveats apply - the contents of the file need to match the definition of the column (large enough, binary vs text, etc).


      Thanks for many responses. I just want to load the filename and the letter in the database not the contents of the file.

      Is the above script going to work? I cant seem to get it to work.
        Okay. For your above script, put some error checking into it so you can see where the failure is taking place.

        #!/usr/bin/perl -w use strict; my $filename = "File_Name_One"; my $letter = "A"; my $dbh = DBI->connect("DBI:ODBC:databasename", "","") || die "error: ($DBI::errstr)\n"; my $firstvar = $dbh->quote($filename); my $secondvar = $dbh->quote($letter); my $statement = "Insert into tableOne (filename, letter) values ($firstname, $letter)"; # Now you need to actually execute the statement $dbh->do( $statment ) || die "error: $dbh->errstr\n"; # rest of code


Re: Inserting file into a database
by Fletch (Chancellor) on Jan 09, 2003 at 18:54 UTC

    I have no idea if Access supports it, but most databases provide some sort of `large object' or `blob' (binary large object) functions which support efficiently storing large chunks of otherwise unstructured text or binary data in the database. Check the corresponding DBD documentation and see if it makes any mention of blobs and functions for accessing them.

Re: Inserting file into a database
by LanceDeeply (Chaplain) on Jan 09, 2003 at 19:13 UTC
    Not sure of what your asking( so i should probably stay quiet... oh well). But if you're having trouble with the connection string, you can set it up like so:
    my $dsn = "Driver={Microsoft Access Driver (*.mdb)};" . "Dbq=c:\\your_nifty_folder\\your_nifty_db.mdb;" . "Uid=admin;" . "Pwd="; my $dbh = DBI->connect("dbi:ODBC:$dsn");
    i found that here.
    actually this link may better serve you. it discusses inserting binary data into ms access.
    i've found this thread w/ code examples of inserting into ms access memo fields
    good luck!
Re: Inserting file into a database
by Ryszard (Priest) on Jan 09, 2003 at 21:02 UTC
    Your question may be interpreted two ways:
    1. You want to store a file in Access in a field
    2. You want to load a file into a table as records of that table

    For option one, i'm not really sure how to do it in Access, however its pretty damn easy with oracle. (one way is to unpack your file (if it is binary) to hex, then store it in a CLOB).

    For option two, Access has some pretty sophisticated filters that will allow the importing of files into a table... couple that with some of the VB "magic" you have there, and you can easily put a file into Access (altho' i think this is the wrong forum for that.)

      About your oracle solution, why would you want to unpack and store in a clob... I would directly store the binary data in a blob... (even if i wanted to store it in a text format, i'd probably prefer to use base64, not base 16:hex )

      I really cannot imagine having the data take twice it's real size (hex<=>binary).

        The 1st thing i tried was a CLOB, it worked, so i kept the solution in place (i was in a rush). I've never retrieved binary data using the DBI, so wasnt sure it would work, or how easy it would be.

        With the infrastructure in place the application performed within acceptable parameters, so I never bothered researching it further. Lazy? you bet... :-)

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://225593]
Approved by rbc
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (4)
As of 2021-10-15 20:40 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (68 votes). Check out past polls.