in reply to Re^8: Query database in Prolog
in thread Query database in Prolog

I tend to use raw DBI and fetchrow_hashref. Now I have columns by name. That won't be portable across databases (different databases handle capitalization of field names differently) but I've generally only found myself working with one database at a time so it isn't an issue for me.

Note that if you're processing large amounts of data, making those anonymous hashes is slower. I've never found that to be a bottleneck for me, but I've always had to deal with remote databases and have never worked with more than a few billion rows. (And on large datasets, my performance expectations were already set by the amount of data.) YMMV.

Replies are listed 'Best First'.
Re^10: Query database in Prolog
by jZed (Prior) on Dec 31, 2005 at 01:22 UTC
    I tend to use raw DBI and fetchrow_hashref. Now I have columns by name. That won't be portable across databases (different databases handle capitalization of field names differently)
    If you set $dbh->{fetchHashKeyName} = NAME_uc or NAME_lc at the top of your scripts, then the hash fetches will be portable across databases since they'll all be in either uc or lc.
[reply]
Re^10: Query database in Prolog
by zby (Vicar) on Dec 31, 2005 at 09:50 UTC
    It is not about DBI - but about converting relations to logic predicates. I know how to get the data from the database (and if you looked at my first post in this thread you would discover that I use an ORM for that - that is order independent), it is the conversion that is the difficult and in my solution order dependent step. I don't want to be rude but I think you did not read the whole thread here and now you attack me with some straw man tactics. It is insulting when you suppose that I don't know basic facts.
[reply]
Re^10: Query database in Prolog
by radiantmatrix (Parson) on Jan 05, 2006 at 21:21 UTC

    > Note that if you're processing large amounts of data, making those anonymous hashes is slower.

    Yep. I have had that be an occasional performance bottleneck. Solved it thusly (IIRC): 

    $sth = $dbh->prepare("Select * FROM datatable");
$sth->execute;
my %map;
{
   my @col = @{ $sth->{NAME_lc} };
   for (0..$#col) { $map{$col[$_]} = $_ }
}
while( my $row = $sth->fetchrow_arrayref ) {
   # I want the cols named ID, name, city, in that order
   printf "%d: %s from %s\n"
     ,$row->{$map{id}}
     ,$row->{$map{name}}
     ,$row->{$map{city}};
}

    [download]

    In this way, you use the fastest fetchrow (fetchrow_arrayref) on each iteration, which seems to be much faster.

    Granted, specifying the field order in the SELECT and then using constants to reference field order (or just passing the list to printf) is faster still. But, if (like me) someone else updates the SQL that your code is calling (like when you just call stored procedures), the code above just keeps working, whereas using numerical constants could break.

    I second the cry of YMMV, but this has always worked well for me.

    <-radiant.matrix->
    A collection of thoughts and links from the minds of geeks
    The Code that can be seen is not the true Code
    "In any sufficiently large group of people, most are idiots" - Kaa's Law
[reply]
[d/l]
[select]

In Section Meditations