in reply to Re: EZDBI is an easier interface to SQL databases
in thread EZDBI is an easier interface to SQL databases

Says PrincePawn:
how would you hide the user and pass outside of an EZDBI script?
The same way you'd hide them outside of a DBI script, I guess.

I would rather get back hashrefs instead of arrays.
I was thinking of putting that in, but I thought that it might be better to release right away and see what people said. Thanks for your input!

And are those arrays all in memory at once? Or are they tied to something that does incremental fetches?
They're in memory. Incremental fetching would be slow, and it's better to squander space than time.

And how about a convenience update_unless_insert which inserts a row if it can, otherwise, does an update on it....
I thought there was an option to Update that would do that? I will put it on the to-do list.

--
Mark Dominus
Perl Paraphernalia

  • Comment on Re: EZDBI is an easier interface to SQL databases

Replies are listed 'Best First'.
Re: Re: EZDBI is an easier interface to SQL databases
by tommyw (Hermit) on Oct 08, 2001 at 21:00 UTC

    It's not always better to squander space: doing so may result in the program not working at all. Squandering time just makes it run slowly. But that's more of a philosophical point.

    WRT the update_unless_insrt: there isn't such an option: PrincePawn's referring to the sort of situation where you've got enough information to create a record from scratch, or replace an existing one.

    Thus (under Oracle) you get code similar to: 

    BEGIN
  INSERT INTO mytab (id, ...) VALUES (id_value, ...);
EXCEPTION
WHEN DUP_VAL THEN
  UPDATE mytab
  SET .. = ...
  WHERE id=id_value;
END;

    [download]
    . id would presumably be limited to the primary key, rather than any unique key. Personally, I think I'd prefer to see it as inserting if the update fails (which avoids the problem of duplicating a different column).

[reply]
[d/l]
      tommyw says:
      It's not always better to squander space
      Well, I didn't say it was always better, did I? I just said it was better.
      WRT the update_unless_insrt: there isn't such an option
      I think I may have been thinking of MySQL's REPLACE syntax. It might be well worth emulating REPLACE with if (UPDATE fails) { try INSERT } if it could be done portably. But I do not think it can be. One problem is that the UPDATE might fail for some other reason, and understanding whether it is safe to proceed with the INSERT seems difficult.

      You also suggest that I ignore the issue of multiple unique keys, and concentrate on just the primary key. But this doesn't make the problem any easier, and it would make the behavior a lot more confusing for the user. I think doing that would do the wrong thing a lot of the time, and then people would be upset.

      Still, I should look into making this easier than it presently is. Right now, you would have to write something like 

          eval { UPDATE ... } or INSERT ...;

      [download]
      Which seems rather bizarre.

      --
      Mark Dominus
      Perl Paraphernalia

[reply]
[d/l]
        This can be very difficult to get right. For example, Dominus's suggestion to use 
            eval { UPDATE ... } or INSERT ...;
        introduces a potential race condition. If two processes are simultaneously trying to update-or-insert a row using that technique then the following can happen: 
        (initially the row is not there)
process A: UPDATE fails
process B: UPDATE fails
process A: INSERT succeeds
process B: INSERT fails
        causing an unexpected failure in one of the processes. It's safer to do it the other way around: 
        eval { INSERT ... } or UPDATE ...;
        which ought to be safe, because a successful INSERT will lock the affected row. I think this difficulty nicely illustrates the advantage of having a REPLACE option in EZDBI, though it would have to be implemented slightly differently for each different type of database.
[reply]

In Section Meditations