Обсуждение: Re: Moving around in a SQL database
Hello Willem, if its not too much data you could do an select * from table an store the result in an array of rows. Then you can iterate over it. If this is too bloated then create a cursor with: declare <cursorname> .... (refer to http://www.postgresql.org/docs/8.1/interactive/sql-declare.html ). Then use fetch to get the desired row. After you are finished use CLOSE to close it. With kind regards Florian Reiser -- http://www.ra-bc.de RA Unternehmensberatung F�hren durch pr�zise Daten "WillemF" <jwhferguson@zoology.up.ac.za> schrieb im Newsbeitrag news:1145533472.685669.176210@i39g2000cwa.googlegroups.com... >I am a novice to SQL. Hope my naiivity does not show so clearly. After > consulting the Internet extensively and studying the two thick > reference books that I have, I cannot find an answer to my problem, > which I suspect has no immediate solution. I need to move through a > table and extract the records sequentially one by one and display the > contents on screen. The order of the records is never changed. I can > easily create an index and find a record with an instructuion such as > SELECT * FROM tablename WHILE record_id = 20 , or some similar way. > But if I need to progress to the next record this is an extremely > inefficient way to do it since one has to serach the whole database > just to get to the next record. It would be much easier to have a > pointer to each of the records in the table and use these pointers to > directly jump to the appropriate record. In the old days of DBase there > was an instruction such as GOTO recordnumber. That would set the > pointer to the appropriate record which could then be accessed. With > PgSQL being so much more sophisticated than DBase, I am convinced there > must be an elegant way of solving this problem. Kind regards. >
Hello Willem, you are writing an edit mask? Then do the following. Feed the mask with the data from the record. If the user changes anything and presses the save button, then issue an UPDATE .... WHERE ID=<id> to the database. Always remember: you are working with a database, not with a dbf-file. If your transaction fails because of power outage or anything else, postgresql will revert to the consistent state before that transaction. If you want to batch update the records, then do a BEGIN TRANSACTION SELECT .... FOR UPDATE. After that collect the data you want to change. Then issue your update statements. After that do a COMMIT TRANSACTION. If anything fails between BEGIN and COMMIT TRANSACTION all changes will be reverted to the state before the transaction. With kind regards Mit freundlichen Gr�ssen Florian Reiser -- http://www.ra-bc.de RA Unternehmensberatung F�hren durch pr�zise Daten "WillemF" <jwhferguson@zoology.up.ac.za> schrieb im Newsbeitrag news:1145601779.703719.62700@i40g2000cwc.googlegroups.com... > Thank you very much for your reply, Florian. It's probably the best > that one can do. I have two complicating factors. Firstly, I need to > udate the records one at a time and, secondly, I am accessing the table > via a Java front end through JDBC. Loading all the data into memory > opens the possibility for data corruption since, if anything goes wrong > on the computer or power supply, there is no guarantee that all updates > will have been written to file. Writing each update to file upon > accessing each record again requires working with the whole table when > manipulating a single record. The use of cursors is an elegant solution > with the drawback that updates cannot be performed (that is, if I > unerstand the documentation correctly!). Thank you very much for your > time. Kind regards. Willem. >
Florian, Thank you so much for your time. You have given me a whole lot to think about. I suspect you have defined most of the important aspects of the solution to my problem. I will go and implement this. Mit freundlichen Grüssen, Willem