EOF required before writing DB?

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

Replies are listed 'Best First'.
Re: EOF required before writing DB? by strat (Canon) on Mar 06, 2002 at 18:13 UTC
I am not a database guru, but I think there's all right so. If you query a table, the order you will get will mostly be accidentally, so that you can't rely on a fixed order. But SQL has very good means to prevent this and deliver data in a sorted way, e.g. `SELECT * FROM table ORDER BY id` [download] I just hope that somebody will answer here who will really know it, and not just guess like I have to :-) Best regards, perl -le "s==F=e=>y~\martinF~stronat~=>s~[^\w]~~g=>chop,print"	[reply] [d/l]
Re: EOF required before writing DB? by VSarkiss (Monsignor) on Mar 06, 2002 at 18:16 UTC
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. ;-)	[reply] [d/l] [select]
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]
Re (3) EOF required before writing DB? by VSarkiss (Monsignor) on Mar 06, 2002 at 18:34 UTC
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]
Re: (3) EOF required before writing DB? by talexb (Chancellor) on Mar 06, 2002 at 18:31 UTC
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]
Re: EOF required before writing DB? by mpeppler (Vicar) on Mar 06, 2002 at 18:18 UTC
strat is right - when you query a SQL database the order in which rows are returned is undefined, unless you specify an ORDER BY clause. Some database engines will return rows in a predictable order without an ORDER BY clause (for example, Sybase returns rows in the order of the clustered index, if it exists), but this is general a side effect of the underlying data storage, and shouldn't be relied on. HTH. Michael	[reply]
Re: Re: EOF required before writing DB? by sdyates (Scribe) on Mar 06, 2002 at 18:36 UTC
Appreciate the input guys. So it is no bad form to have records not written by appending to the end of the table? I will impliment the order by clause. I was under the impression that I was not doing things correctly. I rather learn the correct way of doing things. Regards, Simon	[reply]
Re: Re: Re: EOF required before writing DB? by sdyates (Scribe) on Mar 07, 2002 at 18:36 UTC
I looked into this a little further. Calling $dbh->disconnect closes the database connection. I am calling this script at the end of each SQL command. All seems to be working well so far. The ...ORDER BY was also handy and I should have thought of this before. See what happens when one does not use perl everyday...	[reply]