SQLDB tutorial3 ready lazarus / 16-Databases ---------------------------------------------------------------------- BigChimp · 2012-11-16 13:34 Always wanted to know how to change salaries in stringgrids Wondering what all my posts about parameterized queries really mean and how they work in reality? Keen to impress your friends with a nice 1990's style top of the line database login form? Well, you need not wait any longer - the catchily named SQLDB Tutorial3 is ready! See here: http://wiki.lazarus.freepascal.org/SQLdb_Tutorial3 Of course, corrections welcome, as always - please feel free to edit that wiki page. Thanks a lot, BigChimp ---------------------------------------------------------------------- goodname · 2012-11-16 17:33 Good job on the tutorials you have been writing. They would have lowered my early learning curve. Notice that you are opening and closing a transaction for read only queries. Have never explicitly used a transaction for read only queries. Just opened and closed them when needed. Is closing all open queries using the same transaction not enough to end the transaction? Assume that all transactions close on disconnect. Some information about the TSQLQuery OnUpdateError, On...Error events would be useful. It was a long time before I discovered these and I'm still not sure I'm using them correctly. ---------------------------------------------------------------------- BigChimp · 2012-11-16 17:36 Quote from: goodname on November 16, 2012, 06:33:24 pmGood job on the tutorials you have been writing. They would have lowered my early learning curve. Thanks. Would have helped me as well Quote from: goodname on November 16, 2012, 06:33:24 pmNotice that you are opening and closing a transaction for read only queries. Have never explicitly used a transaction for read only queries. Just opened and closed them when needed. Is closing all open queries using the same transaction not enough to end the transaction? Assume that all transactions close on disconnect. You're right, they'll close on disconnect edit: of the transaction (and connection) itself (I didn't read your post closely enough), I don't think after closing all dependent queries. However, if you do explicit transaction management there will be less surprises once you start throwing in write transactions - depending on the transaction type, your read transaction may never be updated with new data from the write transactions. That's why I like explicitly opening/closing them. Quote from: goodname on November 16, 2012, 06:33:24 pmSome information about the TSQLQuery OnUpdateError, On...Error events would be useful. It was a long time before I discovered these and I'm still not sure I'm using them correctly. No idea about that one Presumably used when using databound controls? Contributions welcome... ---------------------------------------------------------------------- goodname · 2012-11-16 20:53 I've been putting read and write queries in different transactions. For write queries I use AfterPost and AfterDelete events to do the applyUpdates, Commit, and set a changed flag. The changed flag is used by calling forms so it knows if it should refresh local datasets once the editing form is closed/hidden. As far as I can tell the OnUpdateError is like an EXCEPT block for database thrown errors that happen during applyUpdates. For example a violation of unique, foreign key, or NOT NULL constraint. The other On...Error  events seam to be used during query preparation. ---------------------------------------------------------------------- Nebula · 2012-11-16 23:20 Always nice to see more documentation (need Thumbs Up smiley) Cant help wondering if puzzled readers will be wondering whether it's [Col,Row] or [Row,Col] for Cells - a comment for at least one of the code lines might help. And is there more code that isn't shown? How do the column titles get set? Is it worth mentioning how use stringgrid1.Columns.Add to define cols such that their .visible and .alignment properties can be set? http://forum.lazarus.freepascal.org/index.php/topic,18258.msg103343.ht ml#msg103343 Cheers P.S. In MS-SQL 'TSQL' to get just 1 row it's :  SELECT TOP 1 field1, field2 etc HTH ---------------------------------------------------------------------- ludob · 2012-11-17 07:01 Quote from: goodname on November 16, 2012, 06:33:24 pmNotice that you are opening and closing a transaction for read only queries. Have never explicitly used a transaction for read only queries. Just opened and closed them when needed. Is closing all open queries using the same transaction not enough to end the transaction? Assume that all transactions close on disconnect. sqldb will start a new transaction for any sql query when no transaction is open and transactions are closed only explicitly or when closing the connection. As a result, depending on the isolation level, if you just do open and close of queries you can end up looking at the same snapshot forever. You are also potentially locking records or tables for the duration of your program. The implicit starting of transactions was meant to make life easier for the programmer and also an attempt to make the sqldb interface uniform for the different database backends. Some backends start an implicit transaction with the first SQL arriving. Some assume autocommit when no explicit transaction is started. Sqldb starts one always making porting code from one db to the other easier. Downside is that transactions need to be closed in order to save your changes and I believe it is safe to say that that is one of the top 10 questions for new sqldb users (just do a search of the forum on commit or commitretaining). ---------------------------------------------------------------------- eny · 2012-11-17 08:15 Good stuff BC ---------------------------------------------------------------------- Nebula · 2012-11-17 09:41 Quote from: Nebula on November 17, 2012, 12:20:30 am whether it's [Col,Row] or [Row,Col] for Cells - a comment for at least one of the code lines might help. P.S. In MS-SQL 'TSQL' to get just 1 row it's :  SELECT TOP 1 field1, field2 etc I've added those nuggets in comments for you ---------------------------------------------------------------------- BigChimp · 2012-11-17 14:06 Quote from: Nebula on November 17, 2012, 10:41:44 amQuote from: Nebula on November 17, 2012, 12:20:30 am whether it's [Col,Row] or [Row,Col] for Cells - a comment for at least one of the code lines might help. P.S. In MS-SQL 'TSQL' to get just 1 row it's :  SELECT TOP 1 field1, field2 etc I've added those nuggets in comments for you  Yes, I noticed. Glad to see somebody updating the thing Good one about the row/column. If we add the SQL server one, let's also add the MySQL, Firebird/ISO etc versions as well.... ---------------------------------------------------------------------- BigChimp · 2012-11-17 15:20 Quote from: goodname on November 16, 2012, 09:53:50 pmI've been putting read and write queries in different transactions. That is a good idea in itself, I do that, too. However, by itself that doesn't help as ludob and I explained if you keep the read transaction open. Of course, you can start fiddling with transaction isolation levels (see the section on transactions in the Firebird wiki page) but that isn't really the right solution. Perhaps it's time for SQLdb Tutorial4 Transaction management that shows the interaction between various transactions (no need to bring in transaction isolation level, that can be tutorial99 ) but I'm not going to start writing it anytime soon, have had enough of tutorial writing for a while... (editing/helping is another story though) Edit: clarified I'm willing to help ---------------------------------------------------------------------- Nebula · 2012-11-17 15:54 I see you edited my line already  Code: [Select]      // Lowest salary       // Note: we would like to only retrieve 1 row, but unfortunately the SQL       // used differs for various dbs. As we'll deal with db dependent SQL later       // in the tutorial, we leave this for now.       // MS SQL: 'select top 1 '...       FQuery.SQL.Text:='select ' + I thought it was clearer along the lines of - //   (MS SQL : SELECT TOP 1 ... ) - as it looked visually more like the add-on note that it is, rather than yet another line to be read regardless. And the placement of TOP 1 is clearer in relation to the fields list - otherwise a newb would have to go off and check elsewhere if the TOP 1 goes at the end of a statement like the ROWS 1 does. The capitalisation of SQL key words is a user preference of course, I find it more readable ---------------------------------------------------------------------- BigChimp · 2012-11-17 16:09 De gustibus non disputandum est To me it fits in better with the rest of the comments and the Firebird remark at the bottom which specifies that snippet would go in at the end. TBH, they don't even need to be there at all (the Firebird+MSSQL remark) as db specific stuff will be covered in a later section... ---------------------------------------------------------------------- BigChimp · 2012-11-19 17:04 @all: thanks for updating the article. I noticed this part: QuoteNote that this is a simple SQL example for illustrative purposes. Another way to retrieve the record(s) with the minimum salary would be : SELECT e.first_name, e.last_name, e.salary FROM employee e WHERE e.salary=(SELECT min(salary) FROM employee)I wonder if this is faster if e.g. there are a lot of records in employee. Wouldn't the subquery cause the query to run at least twice (or perhaps even once for each employee though I assume an optimizer would see this)? The original query was: Code: [Select]      FQuery.SQL.Text:='select ' +         '    e.first_name, ' +         '    e.last_name, ' +         '    e.salary ' +         'from employee e ' +         'order by e.salary asc '; Thanks, BigChimp ---------------------------------------------------------------------- anis2505 · 2012-11-19 17:48 QuoteCode: [Select]FQuery.SQL.Text:='select ' +         '    e.first_name, ' +         '    e.last_name, ' +         '    e.salary ' +         'from employee e ' +         'order by e.salary asc '; Well now we enter the DBMS's SQL implementation. I think getting only one records much faster The SQL Would be like this On Mysql: Code: [Select]FQuery.SQL.Text:='select ' +         '    e.first_name, ' +         '    e.last_name, ' +         '    e.salary ' +         'from employee e ' +         'order by e.salary asc'+          ' LIMIT 1 '; On Firebird: Code: [Select]FQuery.SQL.Text:='select FIRST 1 ' +         '    e.first_name, ' +         '    e.last_name, ' +         '    e.salary ' +         'from employee e ' +         'order by e.salary asc '; On Access: Code: [Select]FQuery.SQL.Text:='select TOP 1 ' +         '    e.first_name, ' +         '    e.last_name, ' +         '    e.salary ' +         'from employee e ' +         'order by e.salary asc '; And For other DBMS there is other implementations ---------------------------------------------------------------------- BigChimp · 2012-11-19 18:03 @Anis, yes I am very aware there are database dependent implementations. However, in order not to overload the reader with these details, that is covered in the next part of the tutorial where the standard deviation is used. My question here was more about performance of generic SQL that will run on any supported database... ok, likely run... who knows what ODBC monstrosities you could connect to ---------------------------------------------------------------------- anis2505 · 2012-11-19 18:20 Quote from: BigChimp on November 19, 2012, 07:03:53 pm@Anis, yes I am very aware there are database dependent implementations. However, in order not to overload the reader with these details, that is covered in the next part of the tutorial where the standard deviation is used. My question here was more about performance of generic SQL that will run on any supported database... ok, likely run... who knows what ODBC monstrosities you could connect to In that cas the perofrmance of the two SQL statement are almost similar maybe the first one is much faster you will get one record the second gives you 10 records maximum. Code: [Select]SELECT e.first_name, e.last_name, e.salary FROM employee e WHERE e.salary=(SELECT min(salary) FROM employee) the other operation are done within the DBMS so it must be faster enough. So the difference is the count of records returned ---------------------------------------------------------------------- Nebula · 2012-11-19 21:41 Quote from: BigChimp on November 19, 2012, 06:04:41 pmI noticed this part: QuoteNote that this is a simple SQL example for illustrative purposes. Another way to retrieve the record(s) with the minimum salary would be : SELECT e.first_name, e.last_name, e.salary FROM employee e WHERE e.salary=(SELECT min(salary) FROM employee)I wonder if this is faster if e.g. there are a lot of records in employee. Wouldn't the subquery cause the query to run at least twice (or perhaps even once for each employee though I assume an optimizer would see this)? With this approach you're allowing SQL to do it's thing to calculate the minimum as fast as it can with no network bottleneck (nothing is passed over the network, it's all happening on the server), possibly using an index to speed things up even further. Then, once it has the minimum, the selection of the matching records is also lightning quick (again speeded up with an index if you really feel you need the performance) and only the eventual resulting small recordset is returned to the client over the network from server to client. The two quick queries in one - is a lot faster than one long selection of all records, only to discard all but the first. ---------------------------------------------------------------------- BigChimp · 2012-11-20 07:47 @Nebula, I understand your reasoning, but would love to see suppose only performance numbers to illustrate the difference will convince me (Also how/if the query.PacketRecords setting affects which records are transferred over the network) Edit: and yes, the one method has network as a bottleneck (more records sent than the other method), the other the sql table scans (more scans than the other) anyway, doesn't really matter for the tutorial itself ---------------------------------------------------------------------- Nebula · 2012-11-20 09:31 I'd better admit that I was getting carried away with the CTE addition to the page last night, and lost sight of the "rows 1"/"TOP 1" in the tutorial example. In that case SQL is only returning one record anyway, of course. The whole point of me wading in, was to point out cleaner approaches with similar problems, even if it didn't really make much difference in this specific case. But it doesn't hurt to point beginners to alternative techniques, in case they're tempted to skip the TOP 1 and pass huge recordsets around ---------------------------------------------------------------------- BigChimp · 2012-11-20 09:58 Ehm, TBH, I think you messed up the tutorial a bit here: QuoteThe SQL query above efficiently returns one record, thanks to specifying "rows 1" or "TOP 1" (or equiv. in other SQL dialects) Where do you see rows1 or top 1 or equivalent in another SQL dialect in the code presented in the "Getting database data into normal controls" section? The entire point of that section is to show a way to use SQLDB to get data from a database into a control. On purpose, to avoid information overload, it does not introduce db specific code so you can run the same code on Firebird, PostgreSQL, Oracle, MySQL... We do indicate that you can do stuff much faster if you do use db specific stuff but the implementation (how to deal with different dialects) is left for the aptly named "Adapting SQL for various databases" section. TBH, I'd just move your new section to the end of the article if you want to keep it... or rewrite the Adapating SQL for various databases part by adding your SQL into the code for the relevant databases... What do you think? ---------------------------------------------------------------------- Nebula · 2012-11-20 10:17 I've changed it and I believe it makes sense now - the ROWS 1 / TOP 1 stuff is in the comments after all  I think it's valid to have the extra information in the separate section as it is, which can clearly be skipped if the reader isn't interested. ---------------------------------------------------------------------- BigChimp · 2012-11-20 10:35 Well, there's still a reason they're in the comments and not in the code: the reason is that SQL savvy people will immediately want to do a limit (or a subselect), and I'd like to avoid leaving that question dangling. However, I don't want to distract people just starting out with all these details. Agreed with you about the separate section etc. I hope you don't mind my changes; I've moved the section to below the part where SQL specific code was introduced, and added an introduction and the LIMIT 1 SQL to make it a bit more clear what we're talking about again. Edit: I've also indicated that the section is optional up front so the reader can decide then and there instead of afterwards. Thanks, BigChimp ---------------------------------------------------------------------- Nebula · 2012-11-20 10:39 Looks good, didn't realise you could specify SQL syntax colouring - nice one.