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

Can SQL be used without a database?

by thegoaltender (Sexton)
on Dec 30, 2003 at 18:37 UTC ( #317735=perlquestion: print w/replies, xml ) Need Help??

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

First, I am a relative newbie to Perl (2 months programming). I was wondering if I could use SQL statements to traverse a set of hashes. Everything I seem to come accross for SQL is related to database work. While what I have is a simplistic database, I can't seem to find a clear path to use the SQL. Any help getting me jumstarted would be great. Thanks, Jim

Replies are listed 'Best First'.
Re: Can SQL be used without a database?
by b10m (Vicar) on Dec 30, 2003 at 18:40 UTC
Re: Can SQL be used without a database?
by jonadab (Parson) on Dec 30, 2003 at 18:44 UTC

    If what you want is a simple SLQ database engine that doesn't require you to install a bunch of stuff or administrate a full RDBMS server system, you may be interested in SQLite, which just stores your database in a single flat file. I've never used it, but it may be easier than installing and configuring a more full-featured RDBMS.

    $;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}} split//,".rekcah lreP rehtona tsuJ";$\=$ ;->();print$/

      I can vouch for DBD::SQLite. It rocks. :) The first hurdle with SQLite is the fact that you have to have an egg before you can have chickens. What i mean is that you first have to write a script that creates (and then optionally populates) the database file. Something as trivial as:

      use DBI; use Data::Dumper; my $dbh = DBI->connect( 'dbi:SQLite:dbname=dbfile','','', # { RaiseError => 1}, ); eval { $dbh->do('drop table foo'); $dbh->do('create table foo(id int unsigned, name char(64))'); }; my $sth = $dbh->prepare('insert into foo values (?,?)'); $sth->execute(@$_) for [1,'moe'],[2,'curly'],[3,'larry']; print Dumper $dbh->selectall_arrayref('select * from foo');
      However, you may notice from the code that i am supplying the ID. I do not think that SQLite offers auto-incremented ID's for you.

      Alternatively, if you already have your data stored in another format, check out SQL Fairy before you roll your own.

      That's right ... thanks for pointing out my error (once again!) merlyn. :)

      Here is are updated lines for the snippet above:

      $dbh->do('create table foo(id integer primary key, name char(64))'); my $sth = $dbh->prepare('insert into foo(name) values (?)'); $sth->execute($_) for qw(moe curry lary);
      Much better. ;)


      (the triplet paradiddle with high-hat)
Re: Can SQL be used without a database?
by chimni (Pilgrim) on Dec 31, 2003 at 04:47 UTC

    If you are into small scale,rapid application development then SQLite is definately the thing for you.
    So what all do you need to get started with an SQLite driven application?
    1. Install the DBI (1.39 was the latest last i checked)
    2. Install DBD::SQLite (0.29)
    3. Install the dbish (11.93)
    As jeffa has shown in his examples above ,you can use a script to create a table .
    The other way to do this is too run /opt/perl5/bin/dbish
    This gives you a shell interface to your database.
    Make a file to hold your records e.g touch /record.db
    After running the dbish choose the option to connect to the sqlite database (option 3).
    Then provide the dsn e.g. dbi:SQLite:/record.db
    Once you are connected to the db you can run your create,insert,delete,update commands.
    However,if you want to shift records from a flat file into a database it is advisable to write a script . An example of this is below:
    Flat file example

    And the script to read it and insert records into a table
    use DBI; my $dir='/ipmap'; my $id=0; opendir(DIR,$dir); my @files = grep { $_ ne "." and $_ ne ".." } readdir DIR; closedir(DIR); my %attrib = ( PrintError => 0, RaiseError => 1 ); my $dbh = DBI->connect('dbi:SQLite:ipadd.db',"","",\%attrib); my $query = $dbh->prepare("insert into siteip (tla,ip,range) values (? +,?,?)"); print "@files\n"; foreach my $file (@files) { chomp($file); my @ins=''; my($tla,undef)=split(/\./,$file); print "Processing for $tla\n"; open(FI,"$dir\/$file")|| die $!; while(<FI>) { $id=+1; next if m/^\s*$/ or m/^\s*#/; my $line=$_; @ins = split(/\s+/,$line); # print "Inserting $tla $ins[0] $ins[1]"; $query->execute("$tla","$ins[0]","$ins[1]"); } } $dbh->disconnect;

    Hope this helps in your first attemp at using SLite.
Re: Can SQL be used without a database?
by jaldhar (Vicar) on Dec 31, 2003 at 07:51 UTC

    While I second the use of SQLite, if you really want to just bolt an SQL engine ontop of arbitrary stuff, what you want is SQL::Statement


Re: Can SQL be used without a database?
by CountZero (Bishop) on Dec 31, 2003 at 08:01 UTC
    My fellow Monks have all gone lyrical over the benefits of the use of DBI and databases (and very right they are: if you are going to use a database, DBI is the way to go and then you better at the same time install a real database engine, such as MySQL) but I fear that is perhaps not what you were asking: i.e. use SQL statements to traverse a set of hashes.

    If by hashes you mean the associative arrays of Perl, I don't think that you can traverse them by using SQL, or at least not in an easy and straightforward way.

    DBI::AnyData can use an Array of Arrays (in a particular format) as a data source and theoretically a Hash of Arrays is perhaps just as possible, but it is not documented.

    My advice to someone new to (Perl) programming is not to mix hashes and SQL, but rather to concentrate on getting a good grip first on each of these separately.

    If you will spend some more time in the Monastery, you will see that we are much better in providing practical answers to concrete questions than in discussing issues of a more general nature (where we tend to become rather filosophical). So if you provide us with some examples of your data, your data structure and perhaps some code snippets, we will gladly give you a "made to measure" solution.


    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

      First, thanks for everyone's help.
      Let me better explain what I am doing. I have 4 tables of data and I am trying to do joins between them without using a database.
      I changed my hashes to arrays of arrays and tried to use DBI::AnyData. It seems to be having trouble getting my arrays into table format. I am getting the following error:

      Can't coerce array into hash at C:/Perl/site/lib/DBD/ line 247.

      Here is the some of the test code I am using for 1 join:

      $dbh = DBI->connect('dbi:AnyData(RaiseError=>1):'); $dbh->func( 'rdbtable', 'ARRAY', @rdb, 'ad_import'); $dbh->func( 'asitable', 'ARRAY', @asi, 'ad_import'); $dbh->func( 'bomtable', 'ARRAY', @bom, 'ad_import'); $dbh->func( 'mpttable', 'ARRAY', @mpt, 'ad_import'); $rdbtable_sth = $dbh->prepare( "SELECT SIGNAL_NAME,LOCATION,X_COORDINA +TE,Y_COORDINATE,Z_COORDINATE,COMPONENT_SIDE,ETCH_SIDE,GROUND,GROUND_L +OCATION FROM rdbtable" ); $asitable_sth = $dbh->prepare( "SELECT SIGNAL_NAME,LOCATION,REFERENC +E_DESIGNATOR,PIN_NUMBER,DEVICE_NAME,PACKAGE_TYPE,CS_PART_NUMBER FROM +asitable WHERE LOCATION = ?" ); $rdbtable_sth->execute; while (($location,$rdb_signal_name) = $rdbtable_sth->fetchrow_array) { $asitable_sth->execute($location); $row = $asitable_sth->fetchrow_arrayref; $asi_signal_name = $row ? $row->[0] : ''; print "$rdb_signal_name : $asi_signal_name\n"; }

      Here are the 2 tables:





      Any suggestions for fixing what I have or better ways to handle this situation would be greatly appreciated.

      Thanks again,

        Can't coerce array into hash at C:/Perl/site/lib/DBD/ line 247.

        This appears to be caused by the way my array of arrays is created.

        I am creating it in the following manner:

        $test[0][0] = 'No'; $test[0][1] = 'Letter'; $test[0][2] = 'Roman'; $test[1][0] = 1; $test[1][1] = 'a'; $test[1][2] = 'i'; $test[2][0] = 2; $test[2][1] = 'b'; $test[2][2] = 'ii'; $test[3][0] = 3; $test[3][1] = 'c'; $test[3][2] = 'iii'; $test[4][0] = 4; $test[4][1] = 'd'; $test[4][2] = 'iv'; $test[5][0] = 5; $test[5][1] = 'e'; $test[5][2] = 'v'; $test[6][0] = 6; $test[6][1] = 'f'; $test[6][2] = 'vi';

        I don't currently see what the difference is from the following (which works):

        @test = [ ['No','Letter','Roman'], [1,'a','i'], [2,'b','ii'], [3,'c','iii'], [4,'d','iv'], [5,'e','v'], [6,'f','vi'] ];

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (3)
As of 2022-06-25 14:57 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (83 votes). Check out past polls.