in reply to EOF required before writing DB?

It looks like you're just doing select id, name from table. In this case, there is no guaranteed row order for your result set. If you want to impose an order, use an order by clause: $dbh->do('select id, name from table order by id') I wrapped a DBI call around just to make it Perl; otherwise, this is really a SQL question. ;-)

Replies are listed 'Best First'.
Re: Re: EOF required before writing DB?
by sdyates (Scribe) on Mar 06, 2002 at 18:22 UTC
    You both have excellent points, but order by id just provides a workaround. I would prefer to write to the DB in the correct order: append each new insert at the end of the DB.

    So the question is more of how do I write to the db...

    INSERT INTO support VALUES ('','$name',....)

    the '' is becuase I have an autoincrement value set there. The id field does autoincrement properly, but instead of being at the end, is in the middle of the table some place. Someone mentioned that I may need to tell perl to tell myswl to write it at the 'end of the file' -- I want the tables to be written in order of id...

    Thanks

[reply]

      It's not a workaround, that's how relational databases work. You're thinking of a flat file, where you append to the end of the file; a relational table is usually a B-tree of disk pages, even when it's stored in a single disk file. There's no notion of "order" in a table, only a notion of "order" in the result set that you retrieve.

      That said, some databases support ordering in a clustered index, so you can predict (mostly) where the next write will go. I don't know if MySQL supports that.

      But the question just begs to be asked: why do you even care where the row is? One of the big wins of RDBMS is that you don't have to care; the underlying database manages the space for you. All you really care about is how it looks when it gets to you, and the order by is meant to give you that.

[reply]
[d/l]
      I think you're missing the point .. don't worry about where the data is being written to .. just order it correctly when you reading from the database.

      --t. alex

      "There was supposed to be an earth-shattering kaboom!" --Marvin the Martian

[reply]

In Section Seekers of Perl Wisdom