Problem with SQL - query lazarus / 16-Databases ---------------------------------------------------------------------- timmermanj · 2010-07-05 17:29 I'm a bit unexperienced with SQL. I understand the basics but i have a problem to make following query (see attachment). I hope someone can help me started with this. I would really appreciate. Thanks a lot!!! Jeffrey ---------------------------------------------------------------------- eny · 2010-07-05 20:29 Quote from: timmermanj on July 05, 2010, 07:29:37 pmI'm a bit unexperienced with SQL. I understand the basics but i have a problem to make following query (see attachment). I hope someone can help me started with this. I would really appreciate. Thanks a lot!!! Jeffrey 1. What db? (unfortunately there is no such thing as standard SQL.) 2. What have you tried so far? ---------------------------------------------------------------------- timmermanj · 2010-07-06 10:37 Sorry, the database I use for my application is MS Access. I've tried something like this. SELECT SerialNumber AS SN,            (SELECT SUM(FinishedHours) FROM FollowUpTable WHERE FUDate BETWEEN              #07/01/2010# AND #07/05/2010# AND SerialNumber = SN             AND Department = "Shear") AS ShearTotal,            (SELECT SUM(FinishedHours) FROM FollowUpTable WHERE FUDate BETWEEN              #07/01/2010# AND #07/05/2010# AND SerialNumber = SN             AND Department = "Punch") AS PunchTotal,            (SELECT SUM(FinishedHours) FROM FollowUpTable WHERE FUDate BETWEEN              #07/01/2010# AND #07/05/2010# AND SerialNumber = SN             AND Department = "Break") AS BreakTotal,            (SELECT SUM(FinishedHours) FROM FollowUpTable WHERE FUDate BETWEEN              #07/01/2010# AND #07/05/2010# AND SerialNumber = SN             AND Department = "AS") AS ASTotal  FROM FollowUpTable WHERE FUDate BETWEEN #07/01/2010# AND #07/05/2010# as you can  see i'am using subqueries & it makes this very long difficult to read. I hope everything is clear. Thanks a lot! ---------------------------------------------------------------------- LazaruX · 2010-07-06 12:09 I am not very good in SQL too, but if you are using MSAccess I would suggest you to do the queries inside Access itself and then just select from those Query tables. This is much faster than doing a query yourself, because in this way the query will be seen as a table. ---------------------------------------------------------------------- eny · 2010-07-06 14:03 Not really a Lazarus question but a pure SQL question. You cannot easily do reporting with SQL (i.e. building tables with nice headers and transposed results). You'll need some sort of reporting component for that. I haven't look at it but maybe this topic gives you something useful. Now for the query: you can use a GROUP BY clause on the SerialNumber and Department columns. That will give you the correct results in a simple table: serialnumberdepartmentsum11111shear811111Punch211111Break011111AS02222 2shear4etc... If you're lucky ACCESS supports the latest PIVOT function that is available in SQL Server 2005, so you can get the requested result. If not then you'll need either a reporting component as mentioned above or stick to the query you already built yourself. ---------------------------------------------------------------------- JuhaManninen · 2010-07-06 17:46 Quote from: BPsoftware on July 06, 2010, 02:09:30 pmI am not very good in SQL too, but if you are using MSAccess I would suggest you to do the queries inside Access itself and then just select from those Query tables. This is much faster than doing a query yourself, because in this way the query will be seen as a table. In my experience it is not really faster. I haven't tried the latest versions of MS Access though. You can read the Query table like it was a normal table but Access does execute the query always when you read it. I tried to use it for a design tool which connects to a DB table for components. It felt like a good idea to let an Access query connect 2 tables together on the fly but it was so slow I finally had to make a new joined table. The speed difference was maybe *20. Juha ---------------------------------------------------------------------- timmermanj · 2010-07-08 13:22 I've made the SQL - query with the Wizard Query in MS Access. I've got this SQL statement as result: TRANSFORM Sum(FollowUpTable.[FinishedHours]) AS SomVanFinishedHours SELECT FollowUpTable.[SerialNumber], Sum(FollowUpTable.[FinishedHours]) AS [Totaal FinishedHours] FROM FollowUpTable GROUP BY FollowUpTable.[SerialNumber] PIVOT FollowUpTable.[Department] When i try this query in Lazarus i got following error: "Cannot open a non-select statement." So I tried the same SQL query but now i didn't try to open it, instead i tried executing it (ExecSQL). I've got the SQL statement from the wizard in access & in Access itself I get the result i want. However when i try to open or execute it in an application in Lazarus I'am getting these errors. Anybody any idea what's wrong? ---------------------------------------------------------------------- eny · 2010-07-08 15:54 This seems like a Lazarus parser error, that is incompatible with the MS Access SQL Dialect. I cannot test it myself but have you tried setting ParseSQL to false? (Assuming you are using a TSQLQuery...) ---------------------------------------------------------------------- timmermanj · 2010-07-09 06:34 I'm using the TSQLQuery component. I've set ParseSQL to false but still no success... To do such a Transform - Pivot SQL query with the TSQLQuery do i have to use "Open" or "ExecSQL" to open the query? I've tried them both & with both no luck. Could you please try to open a simple "TRANSFORM - PIVOT" query in a lazarus app? Maybe you can find the error easily this way. Thanks in advance! ---------------------------------------------------------------------- eny · 2010-07-09 10:55 It's a bit difficult testing when I don't have your setup. I don't mind looking at the problem, but then you'd have to upload a minimal version of your software that shows the error. ---------------------------------------------------------------------- timmermanj · 2010-07-09 13:14 Ok! I'll sent a simplified version when it's ready! Thanks a lot! ---------------------------------------------------------------------- timmermanj · 2010-07-11 11:34 I've finished a mini application with the table and query i've tried. If you start the app. you can see the table in it's normal condition. The box beneath the table contains the SQL - query i've tried to pivot the table. I've got this SQL - statement from the wizard in Access 2003. PS. if you open the query tab in the access database, you can see the cross-table query that the wizard has generated. If you want to, you can give me your e-mail or tell me where to upload & i'll send you a rar with these files. Thanks in advance! ---------------------------------------------------------------------- Lacak2 · 2010-07-12 05:59 Quote from: timmermanj on July 08, 2010, 03:22:18 pmI've made the SQL - query with the Wizard Query in MS Access. I've got this SQL statement as result: TRANSFORM Sum(FollowUpTable.[FinishedHours]) AS SomVanFinishedHours SELECT FollowUpTable.[SerialNumber], Sum(FollowUpTable.[FinishedHours]) AS [Totaal FinishedHours] FROM FollowUpTable GROUP BY FollowUpTable.[SerialNumber] PIVOT FollowUpTable.[Department] When i try this query in Lazarus i got following error: "Cannot open a non-select statement." So I tried the same SQL query but now i didn't try to open it, instead i tried executing it (ExecSQL). I've got the SQL statement from the wizard in access & in Access itself I get the result i want. However when i try to open or execute it in an application in Lazarus I'am getting these errors. Anybody any idea what's wrong? IMHO you do nothing wrong. Simply there is SQLParser, which do not understand token TRANSFORM + SELECT as valid select statement. You should report this as bug in bug tracker. ---------------------------------------------------------------------- eny · 2010-07-12 08:11 Quote from: Lacak2 on July 12, 2010, 07:59:27 amSimply there is SQLParser, which do not understand token TRANSFORM + SELECT as valid select statement. You should report this as bug in bug tracker. The query given is not a valid select statement, it's some sort of M$ specific TRANSFORM thingy. The way Lazarus should handle this is execute the query without interpretation, and if it gets a valid resultset back, process it accordingly. The bug, if there is one, is somehow in the preprocessing that's done before the query is executed. And there should be a way to skip that. ---------------------------------------------------------------------- Lacak2 · 2010-07-12 12:21 Quote from: eny on July 12, 2010, 10:11:57 amThe query given is not a valid select statement, it's some sort of M$ specific TRANSFORM thingy. It is not valid in POV of SQL standard spec., but from MS POV it is kind of select. Quote from: eny on July 12, 2010, 10:11:57 amThe bug, if there is one, is somehow in the preprocessing that's done before the query is executed. Yes, exactly. Before Prepare is invoked SQLParser, which returns StatementType. if it does not recognize as "select" or "exec procedure" and you "Open" then exception is raised. Quote from: eny on July 12, 2010, 10:11:57 amAnd there should be a way to skip that. But IMHO there is ATM no such way ---------------------------------------------------------------------- eny · 2010-07-12 12:37 Quote from: timmermanj on July 11, 2010, 01:34:42 pmIf you want to, you can give me your e-mail or tell me where to upload & i'll send you a rar with these files. Just attach it to the posting (don't include exe's and other mammoths). ---------------------------------------------------------------------- timmermanj · 2010-07-12 13:51 The attachment contains a simple example of the SQL - query i tried in Lazarus. If you start the app. you can see the table in it's normal condition (standard tabel). The box beneath the table contains the SQL - query where I've entered the query trying to pivot the table. You can try opening the query by pushing the 'open sql" button. or you can try executing the query by pushing the 'exe sql' button. I've got this SQL - query from the wizard in Access 2003. P.S. If you open the database with access, you can see the cross-table query that the wizard has generated. This is the one I entered in the box in the app. If it is a bug, how can this be solved? Thanks a lot! ---------------------------------------------------------------------- timmermanj · 2010-07-15 07:46 Anybody any ideas yet? I'am stuck with my app. Thanks! ---------------------------------------------------------------------- Lacak2 · 2010-07-15 12:07 Quote from: timmermanj on July 12, 2010, 03:51:13 pmIf it is a bug, how can this be solved? IMHO yes it is a bug, but ATM it can be solved only by modifying sources of fcl-db package (sqlDB), as far as you want use TODBCConnection component. You can post bug report and wait when will be fixed, then download snapshot ... or fix it by your own hands. search for odbcconn.pas and add this protected inherited method : function TODBCConnection.StrToStatementType(s : string) : TStatementType; begin   S:=Lowercase(s);   if s = 'transform' then Result:=stSelect //MS Access   else Result := inherited StrToStatementType(s); end; ---------------------------------------------------------------------- timmermanj · 2010-07-15 16:20 I've modified the odbcconn.pas like you said but it's still not working. I'm still getting the same error that it cannot open a non select statement. I've added the pas file in the attachment. Am I forgetting something? I've tried rebuilding Lazarus but still the same wrong result. Please advice. Thanks! ---------------------------------------------------------------------- Lacak2 · 2010-07-16 05:33 For me it works with your test application! But I use Lazarus with FPC 2.5.1 (you use some old version of FPC) We can try this: 1. copy you modified odbcconn.pas into your application directory and try compile ... you must see, in message window, that odbcconn.pas is compiled 2. if some errors about not found files (for example registersqldb) appears, try add into your Project options / Paths / Search Unit path: $(LazarusDir)\components\sqldb\; 3. if it does not solve problem try download Daily Snapshot with FPC 2.5.1 (or at least 2.4.x) and apply patch there ---------------------------------------------------------------------- timmermanj · 2010-07-16 11:35 I'm now using the latest Lazarus fix from daily snapshots:   lazarus-0.9.28.3-26670-20100716-src.zip The FPC version = 2.4.0 I've rebuild Lazarus again after I replaced the odbccon.pas file. But still the same result. Which odbccon.pas are you using? Did it work for you with the one i put in the attachment? If not could you send me the working odbccon.pas file you are using? Thanks a lot! Jeffrey ---------------------------------------------------------------------- timmermanj · 2010-07-16 12:32 It's working!!! I've added the odbccon.pas file in the application. Then i've added the path into the project options. This didn't work yet. So after this "disaster" I want to undo these changes. First i removed the path in the project options. Then i was about to remove the odbccon.pas from the project inspector. But instead of removing the odbccon.pas, i removed SQLDBLaz (per accident). I didn't noticed i removed the wrong file in project inspector. I recompiled to see if my app works again & i did. The only difference is can now open transform queries. Really weird! Maybe you have a explanation? Anyway, thanks for all the help! I really appreciate! ---------------------------------------------------------------------- timmermanj · 2010-07-16 12:43 I've tried something again. I've added a new TSQLQuery on the form. This added the SQLDBLaz back into the project inspector. I've tried compiling again but then i got the error: registersqldb.pas(14,36) Fatal: Can't find unit registersqldb used by SQLDBLaz Somehow i doesn't work when this is in the project inspector... ---------------------------------------------------------------------- Lacak2 · 2010-07-19 05:30 Quote from: timmermanj on July 16, 2010, 02:43:48 pmI've added a new TSQLQuery on the form. This added the SQLDBLaz back into the project inspector. Yes, SQLdbLaz is required package Quote from: timmermanj on July 16, 2010, 02:43:48 pmregistersqldb.pas(14,36) Fatal: Can't find unit registersqldb used by SQLDBLaz As I wrote: 2. if some errors about not found files (for example registersqldb) appears, try add into your Project options / Paths / Search Unit path: $(LazarusDir)\components\sqldb\; Please add also bug report into bug tracker, with link to this discussion. ---------------------------------------------------------------------- timmermanj · 2010-07-20 11:56 I've just installed Lazarus with FPC 2.4.3 After adding the adjusted odbccon.pas in the project inspector I don't have any problems running the transform query. I also don't get following error anymore: registersqldb.pas(14,36) Fatal: Can't find unit registersqldb used by SQLDBLaz I will report this bug. thanks a lot for all the help!!! ---------------------------------------------------------------------- eny · 2010-07-23 20:49 Good thing it's working now. Have fun. (Sorry for the late reply but I had to rebuild my PC; MB and PSU crash  ) ---------------------------------------------------------------------- Lacak2 · 2010-08-02 05:58 Reported as bug in bugtracker : http://bugs.freepascal.org/view.php?id=17086