Re: [Gambas-user] How to address more than one table in resultset
On Thursday 29 January 2009, Ron wrote: Bit stuck. In an attempt to optimize some db queries. I changed this (gets all events first then check if enabled, then gets the trigger fields for that event etc): DIM rResult, rResultTrig, rResultAction AS Result rResult = Main.hDB.Exec(SELECT * FROM events) IF rResult THEN IF rResult.Count THEN FOR EACH rResult IF rResult!enabled THEN IF rResult!trigger1 THEN rResultTrig = Main.hDB.Exec(SELECT * FROM triggers WHERE id = 1 , rResult!trigger1) IF rResultTrig THEN IF rResultTrig!param1 = ... NEXT to this (gets selected events and triggers table/fields): DIM rResult AS Result rResult = Main.hDB.Exec(SELECT * FROM events, triggers WHERE events.trigger1 = triggers.id AND triggers.type = 3 AND events.enabled AND triggers.param1 = 1, iId) IF rResult THEN IF rResult.Count THEN FOR EACH rResult ... NEXT In the sql query tool from database manager it returns exactly the correct records/fields etc But how can I get the fields from the triggers table from this resultset? I cannot do something like rResult!triggers.id Is this possible? Thanks. Regards, Ron_2nd. rResult = Main.hDB.Exec(SELECT * FROM events, triggers WHERE rResult = Main.hDB.Exec(SELECT *, triggers.fieldx as triggerfieldx FROM events, triggers WHERE You should get all field names from both tables side by side. (As far I remember) You can use the as triggerfield to declare the specific field from triggers. Lets asume both have a ID field, normal result will be then ID event xxx yyy ID trigger xxx yyy Using the ___triggers.ID as triggerID___ then you get ID event xxx yyy ID trigger xxx yyy triggerID Best regards, Ron_1st -- A: Delete the text you reply on. Q: What to do to get my post on top? A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? -- This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] How to address more than one table in resultset
Ron_1st schreef: On Thursday 29 January 2009, Ron wrote: Bit stuck. In an attempt to optimize some db queries. I changed this (gets all events first then check if enabled, then gets the trigger fields for that event etc): DIM rResult, rResultTrig, rResultAction AS Result rResult = Main.hDB.Exec(SELECT * FROM events) IF rResult THEN IF rResult.Count THEN FOR EACH rResult IF rResult!enabled THEN IF rResult!trigger1 THEN rResultTrig = Main.hDB.Exec(SELECT * FROM triggers WHERE id = 1 , rResult!trigger1) IF rResultTrig THEN IF rResultTrig!param1 = ... NEXT to this (gets selected events and triggers table/fields): DIM rResult AS Result rResult = Main.hDB.Exec(SELECT * FROM events, triggers WHERE events.trigger1 = triggers.id AND triggers.type = 3 AND events.enabled AND triggers.param1 = 1, iId) IF rResult THEN IF rResult.Count THEN FOR EACH rResult ... NEXT In the sql query tool from database manager it returns exactly the correct records/fields etc But how can I get the fields from the triggers table from this resultset? I cannot do something like rResult!triggers.id Is this possible? Thanks. Regards, Ron_2nd. rResult = Main.hDB.Exec(SELECT * FROM events, triggers WHERE rResult = Main.hDB.Exec(SELECT *, triggers.fieldx as triggerfieldx FROM events, triggers WHERE You should get all field names from both tables side by side. (As far I remember) You can use the as triggerfield to declare the specific field from triggers. Lets asume both have a ID field, normal result will be then ID event xxx yyy ID trigger xxx yyy Using the ___triggers.ID as triggerID___ then you get ID event xxx yyy ID trigger xxx yyy triggerID Best regards, Ron_1st Now that's the last kind of solution I was thinking of. Where this you get this info from, have been searching gambasdoc over and over again. But it works nicely! Note that single quotes are needed. --- DIM value AS String rResult = Main.hDB.Exec(SELECT *,triggers.param2 as 'value' FROM events, triggers WHERE events.trigger1 = triggers.id AND triggers.type = 3 AND events.enabled AND triggers.param1 = 1, iId) IF rResult THEN IF rResult.Count THEN FOR EACH rResult SELECT rResult!value ... --- Thanks alot!! Regard, Ron_2nd -- This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] How to address more than one table in resultset
On jeudi 29 janvier 2009, Ron wrote: Bit stuck. In an attempt to optimize some db queries. I changed this (gets all events first then check if enabled, then gets the trigger fields for that event etc): DIM rResult, rResultTrig, rResultAction AS Result rResult = Main.hDB.Exec(SELECT * FROM events) IF rResult THEN IF rResult.Count THEN FOR EACH rResult IF rResult!enabled THEN IF rResult!trigger1 THEN rResultTrig = Main.hDB.Exec(SELECT * FROM triggers WHERE id = 1 , rResult!trigger1) IF rResultTrig THEN IF rResultTrig!param1 = ... NEXT to this (gets selected events and triggers table/fields): DIM rResult AS Result rResult = Main.hDB.Exec(SELECT * FROM events, triggers WHERE events.trigger1 = triggers.id AND triggers.type = 3 AND events.enabled AND triggers.param1 = 1, iId) IF rResult THEN IF rResult.Count THEN FOR EACH rResult ... NEXT In the sql query tool from database manager it returns exactly the correct records/fields etc But how can I get the fields from the triggers table from this resultset? I cannot do something like rResult!triggers.id Is this possible? The '!' syntactic sugar works only if you use an identifier. Otherwise you must use the [] syntax: rResult[triggers.id]. Regards, -- Benoit Minisini -- This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] How to address more than one table in resultset
On Thursday 29 January 2009, Ron wrote: Ron_1st schreef: On Thursday 29 January 2009, Ron wrote: Bit stuck. In an attempt to optimize some db queries. I changed this (gets all events first then check if enabled, then gets the trigger fields for that event etc): DIM rResult, rResultTrig, rResultAction AS Result rResult = Main.hDB.Exec(SELECT * FROM events) IF rResult THEN IF rResult.Count THEN FOR EACH rResult IF rResult!enabled THEN IF rResult!trigger1 THEN rResultTrig = Main.hDB.Exec(SELECT * FROM triggers WHERE id = 1 , rResult!trigger1) IF rResultTrig THEN IF rResultTrig!param1 = ... NEXT to this (gets selected events and triggers table/fields): DIM rResult AS Result rResult = Main.hDB.Exec(SELECT * FROM events, triggers WHERE events.trigger1 = triggers.id AND triggers.type = 3 AND events.enabled AND triggers.param1 = 1, iId) IF rResult THEN IF rResult.Count THEN FOR EACH rResult ... NEXT In the sql query tool from database manager it returns exactly the correct records/fields etc But how can I get the fields from the triggers table from this resultset? I cannot do something like rResult!triggers.id Is this possible? Thanks. Regards, Ron_2nd. rResult = Main.hDB.Exec(SELECT * FROM events, triggers WHERE rResult = Main.hDB.Exec(SELECT *, triggers.fieldx as triggerfieldx FROM events, triggers WHERE You should get all field names from both tables side by side. (As far I remember) You can use the as triggerfield to declare the specific field from triggers. Lets asume both have a ID field, normal result will be then ID event xxx yyy ID trigger xxx yyy Using the ___triggers.ID as triggerID___ then you get ID event xxx yyy ID trigger xxx yyy triggerID Best regards, Ron_1st Now that's the last kind of solution I was thinking of. Where this you get this info from, have been searching gambasdoc over and over again. But it works nicely! Note that single quotes are needed. --- DIM value AS String rResult = Main.hDB.Exec(SELECT *,triggers.param2 as 'value' FROM events, triggers WHERE events.trigger1 = triggers.id AND triggers.type = 3 AND events.enabled AND triggers.param1 = 1, iId) IF rResult THEN IF rResult.Count THEN FOR EACH rResult SELECT rResult!value ... --- Thanks alot!! Regard, Ron_2nd Experience with MS-Access-MySQL combination, used a lot in the past, and programming with php for websites. About the quotes, to be exactly there should be backticks for MySQL field/table names too. In first case I had them but you did not used it for the table name so I deleted them. Normal to be safe MySQL use backticks for field and table names, special when spaces and non-alpha characters are involved in them. For the alias name after AS you could using single or double quotes. Depends on to front and end quote used if passed as string. The reason wy it is not in the gambas doc is simple. The default method does not using DB.exec() while this is backend sensitive. Your code runs now on MySQL but could fail on SQLite or Firebird! Your construction now is more for advanced programmers and that is not default for the average gambas user ATM. Leuk die twee ronnies die in het engels pruttellen. :) Best regards, Ron_1st -- A: Delete the text you reply on. Q: What to do to get my post on top? A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? -- This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
Re: [Gambas-user] How to address more than one table in resultset
Benoit Minisini schreef: On jeudi 29 janvier 2009, Ron_1st wrote: On Thursday 29 January 2009, Benoit Minisini wrote: The '!' syntactic sugar works only if you use an identifier. Otherwise you must use the [] syntax: rResult[triggers.id]. Does gambas returns then from second table 'triggers' the field 'id' as triggers.id ? The query SELECT * FROM table1,table2 WHERe table1.id= table2.id I'v never seen every field is returned as table1.anyname. Just a question, I can't remember 100% or try ATM here to verify. Alas, it depends on the database driver. (SQL... standard ?) For my purpose it works, with MySQL, and rResult[triggers.id]. But my events.id is not linked to triggers.id, it event.trigger1 - triggers.id This solution with brackets is in my case better than the 'as xx' solution, since I use quite a few fields from both tables, and the query string got a bit long ;-) Regards, Ron_2nd. -- This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword ___ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user
