Storing a Query Result in a Variable? lazarus / 16-Databases ---------------------------------------------------------------------- tee · 2012-09-29 09:58 Hey newbie question here...just wondering how you would go about storing data after a query in a variable? Code: [Select]procedure TfrmArtist.FormClose(Sender: TObject; var CloseAction: TCloseAction); begin   dmoCD.SQLQueryNum.Close;   dmoCD.SQLQueryNum.SQL.Text := 'SELECT ArtistID FROM Numbers;';   dmoCD.SQLQueryNum.Open;    .... There is only one record in table 'Numbers' so the result of the query is the value of field ArtistID for that one record. There will be no other records entered in this table since its primary use is for numbering using the numbers stored in each field. I created this table as a workaround instead of auto_increment due to all the gaps created after records are deleted when using auto_increment. So after the above query is executed how do I store the number from the query in a variable? Better question: Is there an easier way to workaround the gaps you find for auto_incremented fields? ---------------------------------------------------------------------- taazz · 2012-09-29 10:20 That's quite dangerous, why would you want to recycle used IDs? In any case look at the autoinc as a social security number once that ID is given even if the person that got it dies out it can't be given to anyone else again. My advice on the subject don't do it the mine field you are walking in to is bigger than the ocean it self. Now for your problem. dmoCD.SQLQueryNum.Fields[0].asInteger; or dmoCD.SQLQueryNum.FieldByName('ArtistID').AsDouble. just make sure that you are on a record before reading by calling dmoCD.SQLQueryNum.first; ---------------------------------------------------------------------- BigChimp · 2012-09-29 10:29 What do you mean by "numbering"? Use autoincrement to get unique ids that are irrelevant to the user. Edit: sorry, a bit too brief here. Autoincrement fields should either not be shown to the user or it should be made clear that these numbers are not expected to have any meaning, not even when they appear to seamlessly increment by 1. Yes, deleting a record will remove a number. So what Google the endless debates on misusing autoincrement for invoice numbers etc to get an idea about why this doesn't work (e.g. an insert transaction that is rolled back can have "squandered" an autoincrement ID). I'm getting confused about what you are trying to do. Why don't you post your DDL (the SQL that shows how to create your tables etc) and/or a description of what you are trying to do at a higher level (e.g. I want to count the number of records an artist has, or I want to store every record an artist ever made). As for your question about retrieving the result from a query: 1. If you're sure you only want the first record from a db, I'd suggest you explicitly specify that in your query, e.g. something like (air code, don't know these  SQL things by heart): Code: [Select]dmoCD.SQLQueryNum.SQL.Text := 'SELECT ArtistID FROM Numbers ORDER BY ArtistID DESC LIMIT 1;'; that at least makes it clear what you expect and will make troubleshooting easier 2. Getting the data: after opening, use e.g. this (also air code, not tested): Code: [Select]ArtistIDVariable:=dmoCD.SQLQueryNum.FieldsByName('Artist ID').AsString; //you can also use .AsInteger if it's an integer field etc Edit: taazz, AFAIR, you'll be on the first record after opening... but you could get an empty dataset (no artistid), so.. still air code - look at the wiki/forum for some real examples...: Code: [Select]if not(dmoCD.SQLQueryNum.EOF) then   ArtistIDVariable:=dmoCD.SQLQueryNum.FieldsByName('ArtistID').AsString; //you can also use   .AsInteger if it's an integer field etc ---------------------------------------------------------------------- tee · 2012-09-29 10:49 Hmm I guess I'll stick with the auto_increment then... I'm now realizing the consequences of the idea I had in mind....waay too much work... My original idea was to have a starting ArtistID number which would be stored in 'Numbers' along with 2 other fields that I need to increment numbers for. When an Artist is added to my 'Artist' table I need to create an ArtistIDNumber so I would create one on OnFormClose by retrieving the ArtistID from the 'Numbers' table, assigning it to ArtistIDNumber and adding one to to ArtistID, placing it back in the 'Numbers' table ready for the next ID to be created....but I didnt think about the other consequences... I will use auto_increment but those statements that store the query in a variable come in handy! I will be using them soon! Thank you for the help! ---------------------------------------------------------------------- BigChimp · 2012-09-29 11:06 Yes, don't fight the database As said, you're welcome to post the table createion SQL and let us have a look. ---------------------------------------------------------------------- tee · 2012-09-29 11:14 Haha thanks for the offer  but I've been mucking around a bit with the database and there's some random tables that I've made alongside the real tables which I will use(eventually). I'm using the random tables as test tables before i do anything drastic with my real tables. It's very messy... But I'm just getting some major concepts down before I do my final stuff. I was just wondering how you would work around the auto_increment issue but its not a big problem, just an extra food for thought. Also, could I just retrieve the last record's ArtistID add one to that and make this the new record's artist ID. Wouldn't this get rid of holes? ---------------------------------------------------------------------- BigChimp · 2012-09-29 11:35 Quote from: tee on September 29, 2012, 01:14:48 pmjust wondering how you would work around the auto_increment issue but its not a big problem, just an extra food for thought. Also, could I just retrieve the last record's ArtistID add one to that and make this the new record's artist ID. Wouldn't this get rid of holes? If you describe your issue with auto_increment tables, maybe we can help: what exactly are your problems with auto_increment tables? (I'm guessing there's some database theory you may not understand). It seems you don't like holes in primary key auto increment numbers. Why is that a problem? Re adding numbers yourself: that can be done... but: how would you deal with removing the 2nd record in a table with 20 records? ---------------------------------------------------------------------- tee · 2012-09-29 11:41 Agh! You're right didn't think that one through either. I dont really have any issues with the gaps, I was just wondering if there was a way in which it could be fixed but you and Taazz have enlightened me on the issues with a no gap database so I'll stick with the auto increment lol ---------------------------------------------------------------------- taazz · 2012-09-29 11:55 Quote from: BigChimp on September 29, 2012, 12:29:21 pmEdit: taazz, AFAIR, you'll be on the first record after opening... but you could get an empty dataset (no artistid), so.. still air code - look at the wiki/forum for some real examples...: Code: [Select]if not(dmoCD.SQLQueryNum.EOF) then   ArtistIDVariable:=dmoCD.SQLQueryNum.FieldsByName('ArtistID').AsString; //you can also use   .AsInteger if it's an integer field etc I got in the habit of expresing the true indent of the code a few years back when I was working with oledb and ADODB libraries when under specific contitions the dataset was not set to the first record in it but some where else. Calling First after all takes 1 millisecond max and it is not called in a loop to make any real difference in the application responsiveness. So I prefer safe than sorry on this one. Quote from: tee on September 29, 2012, 01:14:48 pmI was just wondering how you would work around the auto_increment issue but its not a big problem, just an extra food for thought. Also, could I just retrieve the last record's ArtistID add one to that and make this the new record's artist ID. Wouldn't this get rid of holes? sure the getting the last id is the easy part eg select Max(ArtistID) from Artists; that will return the max number now please answer the following 1) how many concurent users would your application have 2) how many will try to access the max(ID) to create a new record at the same time 3) how many of them will create the same ID for different Records? Is there any tests that can be run to accurately answer any of the above? If there are would they measure the worst case scenario or the worst case you can imagine? Do you know why most databases when they have got a autoinc number to insert it in a new record if anything goes wrong during execution they burn that number? Those are few of the questions you need to answer  before you even try to create a auto_inc replacement. Most autoinc implementations are not transaction aware eg. when a number is given no transaction can rollback that action or get the same number. that is something that can't be done with out some extreme fine tuned locking mechanism you can write your own if that is what you wish (in most databases that is) but your application will suffer from scalability problems there is no way you can tune it better than the server it self which can wait up to the last moment before it gets a lock to the autoinc variable while you would have to lock it before you issue the command or after you issue it in a before insert trigger, and what happens if an other client does the same thing in an other transaction would both of them get the same number would your lock be sever wide. You can't make that job faster or safer than the server it self. Regards Jo ---------------------------------------------------------------------- tee · 2012-09-29 12:09 Thanks Jo for the explanation I'll take every bit I can get. I'm brand new to lazarus and working with databases so please mind my ignorance on this subject