tag:blogger.com,1999:blog-15319370.post3075991287469014747..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: MySQL Stored Procedure Result Sets in Pentaho Data Integrationrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger29125tag:blogger.com,1999:blog-15319370.post-2874295190497399722013-05-14T12:30:29.533+02:002013-05-14T12:30:29.533+02:00how to call stored procedure in pentaho report des...how to call stored procedure in pentaho report designer ?Ravurihttps://www.blogger.com/profile/05879176850714347184noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-73559830845346297862013-05-14T12:29:11.177+02:002013-05-14T12:29:11.177+02:00How to call mysql stored procedure in Pentaho repo...How to call mysql stored procedure in Pentaho report designer ?Ravurihttps://www.blogger.com/profile/05879176850714347184noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-17241209995265545112011-06-24T20:45:14.638+02:002011-06-24T20:45:14.638+02:00Hi Roland,
Alas, I suspected that would be the an...Hi Roland,<br /><br />Alas, I suspected that would be the answer. Maybe sometime in the future, then...<br /><br />The tip on switching to views is much appreciated. We have a pretty small data set (at present), so I don't have performance problems - yet. I'll experiment with this, though.<br /><br />Thanks!<br /><br />BenBen Uphoffhttps://www.blogger.com/profile/10529413920278890689noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-61798614012944789822011-06-23T22:41:01.847+02:002011-06-23T22:41:01.847+02:00Hi Ben,
thanks for your kind words, much appreci...Hi Ben, <br /><br />thanks for your kind words, much appreciated :)<br /><br />unfortunately, this is not possible. There must be a feature request somewhere in bugs.mysql.com, I've seen many people request this feature.<br /><br />I have a suggestion that might improve the workaround you're using at the moment.<br /><br />Instead of creating a (temporary) table, you can have your stored procedure create a view. The advantage of that it doesn't cost storage, and typically your generating procedure returns faster because it doesn't have wait until the data is stored to disk. If the query in which you then use the view allows the view to be queried using the MERGE algorithm, then that should be reasonably fast (provided there are good indexes on the underlying tables). If the view is evaluated using the TEMPTABLE algorithm then it could be costly because you won't have any indexes on the set from the view.<br /><br />kind regards, and good luck,<br /><br />Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-74467706629906291922011-06-23T22:08:50.619+02:002011-06-23T22:08:50.619+02:00Roland,
Your posts have been incredibly valuable ...Roland,<br /><br />Your posts have been incredibly valuable to me - glad I discovered your blog.<br /><br />Do you know of a way to select from a stored procedure in MySQL? For example, a hypothetical syntax might be:<br /><br />SELECT s.* FROM db.sp_my_stored_procedure(@arg1,@arg2) s;<br /><br />This would allow me to also join the stored procedure result set to other tables - handy. I've worked around this by having my stored procedures create temporary & permanent tables, but that's pretty clunky.<br /><br />Thanks in advance for your sage counsel!<br /><br />BenBen Uphoffhttps://www.blogger.com/profile/10529413920278890689noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-84114930625311946952011-05-17T16:25:20.793+02:002011-05-17T16:25:20.793+02:00Hi Robert!
heh, da's lang geleden :) Ja hoor...Hi Robert! <br /><br />heh, da's lang geleden :) Ja hoor, ik zit nog steeds in Leiden. Alles goed?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-12396761936949447052011-05-17T15:50:26.658+02:002011-05-17T15:50:26.658+02:00Hoi Roland,
Hartelijk gefeliciteerd met je boek. ...Hoi Roland,<br /><br />Hartelijk gefeliciteerd met je boek. We moeten nog eens afspreken, even bij bieren ;) Woon je nog in de zelfde plaats als Catena? <br /><br />Groetjes,<br /><br />Robert Nagtegaal.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-6629956635045210232011-04-15T12:08:51.983+02:002011-04-15T12:08:51.983+02:00Hello Roland,
I see its been a while since this t...Hello Roland,<br /><br />I see its been a while since this thread has been active. I'm also trying to execute a SP within Kettle (Spoon 4.1).<br /><br />I used your tip on using the Table input and calling the SP which works. Although.. My table gets filled with data but the transformation returns an error: 'Couldn't get row from resultset'. Maybe you already explained it in this thread, but I don't know how to make my transformation not returning the error. <br /><br />Perhaps you know a solution? <br />P.s. Your book is awesome! I got it as well since last month.<br /><br />Thanks,Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-60536681867694555782010-11-06T15:10:36.936+01:002010-11-06T15:10:36.936+01:00Visual Studio and SQL Server management Studio are...Visual Studio and SQL Server management Studio are two different entities. So it takes a lot of time when we try an interaction with the database from Visual Studio then it takes a lot of time.<br /><br />When we are using desktop applications then we do not realize this time delay, but while using web applications the time delay becomes an important factor because the database is on the remote server so a huge time delay is observed. <br /><br />Normally, the query that we write in Visual Studio is first compiled and then executed in Sql Server which then returns the results. So, this is why a time delay is observed.<br /><br />The answer to this is Stored Procedure usage. The Stored Procedure is stored in compiled format in SQL server so when we call it from Visual Studio application then it is just executed. Thus saving a lot of time.<br /><br />An illustration of proper usage of STORED PROCEDURE has been displayed in the following video. You may see it:<br /><br />http://www.visiontechno.net/studymats/storeprocedure.htmlAkhil Guptahttp://www.visiontechno.netnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-35157563483844712152010-07-26T07:37:43.951+02:002010-07-26T07:37:43.951+02:00If you need to use stored procedures to result dat...If you need to use stored procedures to result data sets, and include paramaters for ad hoc reporting, you can use a product called JEvolution from www.mcmsoftware.com see the following video: http://www.mcmsoftware.com/docs/jevolfromstp.htm<br /><br />good luckAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-18879136785614892882010-01-13T22:55:50.434+01:002010-01-13T22:55:50.434+01:00How to pass parameters from Pentaho Report Designe...How to pass parameters from Pentaho Report Designer to PDI tranfromation file. I already defined a parameter in PDI transformation file. Let me know how to pass parameters from PRD to PDI. <br /><br />I tried creating a parameter in PDI but, I do not find anything to specify that the created parameter will be passed to PDI.<br /><br />Let me know if there is any solution to achieve this.<br /><br />Thanks,<br />kalAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-87726807214912135002009-10-05T10:53:46.347+02:002009-10-05T10:53:46.347+02:00Hi Anonymous,
thanks for your interest and suppo...Hi Anonymous, <br /><br />thanks for your interest and support! I always enjoy hearing the book is useful to someone. <br /><br />Regarding your scenario: Although you could solve it in the SQL statement in the input step using a CASE...WHEN expression, I would advise against that. Best practice is to make all transformations to the data as explicit as possible, and writing SQL expressions hides the logic.<br /><br />In pentaho data integration, I would most probably use a "Value Mapper" step for this case. The value mapper operates on a field in the input stream and then looks if it matches one of the listed "source" values and then returns the mapped "target" value if there is a match. In this particular case, you would have the following source/target pairs: {A101, Jane Smith}, {A202, Jane Smith}, {B402, John Doe}. You can find the value mapper in the "Transform" category. It is first discussed in the book on page 273, "The Value Mapper Step".<br /><br />I should point out that the value mapper is the way to go if you "just" want to map a few values. If the mapping is actually stored in a database table, you should use a database lookup step. Especially if you need to manage the mapping over time (and you want to keep track of the histoy of your mapping practice) you could start to explictly store the mappings in your staging arera in a lookup table, and use the lookup step to do it.<br /><br />Now about your suggestions:<br /><br />You could do it with a javascript step too, but then again: you can do everything with a script step. Like writing SQL expressions in the input stetp, a java script step obscures the logic, and you should only use that step as a last resort. Also, the javascript step is quite slow.<br /><br />There is also a switch/case step (in the "Flow" category") but you would use that in case you actually wanted to apply a different treatment on the rows depending on a field value.<br /><br />kind regards, and good luck<br /><br />Roland Boumanrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-20794098626301366612009-10-05T05:57:46.565+02:002009-10-05T05:57:46.565+02:00Hey Roland, just got your book...congrats and a go...Hey Roland, just got your book...congrats and a good read! I have a transformation I'd like your take on from a "best practice" perspective. I'm thinking there's multiple ways of doing this, so I'd like to understand what the most efficient is. <br /><br />Scenario: table input with a field that I need to make consistent. Lets say the values I can receive from this field are as follows:<br />1. example - A101<br />2. example - A202<br />3. example - B402<br /><br />I'd like to create a transformation that would catch 1 and 2 and output as "Jane Smith". 3 would output as "John Doe".<br /><br />What is the "Pentaho way" of doing this? SQL case statement? Javascript step? built in transformation step?<br /><br />Thanks much for your time.<br />Regards,<br />UserErrorAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-36990683231107872652009-04-27T23:27:00.000+02:002009-04-27T23:27:00.000+02:00Hi Anonymous!
can you please explain a little bit...Hi Anonymous!<br /><br />can you please explain a little bit more about how you set this up? I should like to try and reproduce it before I attempt to explain.<br /><br />kind regards,<br /><br />Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-38119950037251533012009-04-27T23:12:00.000+02:002009-04-27T23:12:00.000+02:00Hi,
When using Table input step to get the value ...Hi,<br /><br />When using Table input step to get the value from stored function, everything is fine except the returned value is rounded to 1 decimal place although the function itself RETURNS DECIMAL(14,4)<br /><br />Any idea?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-81568155657317209672009-03-05T00:24:00.001+01:002009-03-05T00:24:00.001+01:00Kabel, gah...should beRoland.Bouman@gmail.comKabel, gah...<BR/>should be<BR/><BR/>Roland.Bouman@gmail.comrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-74472071751375534602009-03-05T00:24:00.000+01:002009-03-05T00:24:00.000+01:00Hi kabel, I checked, with and without Select Value...Hi kabel, <BR/><BR/>I checked, with and without Select Values, and it 'just works'.<BR/><BR/>Would you like me to send you my transformation? Just mail me at Roland.Bouman@google.com so I know where to send it.<BR/><BR/>Thanks!rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-4392998535717447632009-03-05T00:19:00.000+01:002009-03-05T00:19:00.000+01:00Wow, ignore that, I was making a very stupid mista...Wow, ignore that, I was making a very stupid mistake. I seem to have the same luck. <BR/><BR/>Using a Select Values did the trick. It still gives errors if I try to "get fields" or "get input/output fields" anywhere downstream from the Stream Lookup, but it appears that the Stream Lookup is working perfectly.<BR/><BR/>Thanks again!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-42303274282182614122009-03-05T00:14:00.000+01:002009-03-05T00:14:00.000+01:00Roland,You must have a lot better luck than I do. ...Roland,<BR/><BR/>You must have a lot better luck than I do. I'm returning a dataset out of my stored procedure using SELECT, sending that to a Select Values, where I try to set the data types of the two columns being returned from the SP, and sending that to the Stream Lookup, but am getting some null pointer errors from the Stream Lookup step It's not a deal-breaker, as I have a workaround in place, it's just frustrating.<BR/><BR/>Thanks for looking at it, though<BR/><BR/>kabelAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-15112179940582541562009-03-04T22:54:00.000+01:002009-03-04T22:54:00.000+01:00Hi kabel!yes, this is indeed a problem at least wh...Hi kabel!<BR/><BR/>yes, this is indeed a problem at least when designing the transformation. However, you can work around it by manually specifying the fields in the lookup step.<BR/><BR/>Ugly, yes. But work it will.<BR/><BR/>I checked myself just now, and I can successfully use the stream look up step on the result of a stored procedure. <BR/><BR/>I haven't looked into it very deeply, but it may be a good idea to use a Select Values step behind the table input to explicitly set the data types as well if you want to use the data to do lookups.<BR/><BR/>HTH, let me know if you find out more.<BR/><BR/>kinnd regards,<BR/>Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-23257141428275998552009-03-04T21:56:00.000+01:002009-03-04T21:56:00.000+01:00Roland,After some searching, I came across your re...Roland,<BR/><BR/>After some searching, I came across your recommendation. The problem I'm finding is that PDI does not detect the column names returned (as a resultset) from the call, so I am getting some rather dire errors. I'm sending the results of the stored procedure call to a stream lookup, which may be my problem. Not sure if you've encountered this, but it's a potential downside to this solution.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-9320834642019451492009-02-12T12:53:00.000+01:002009-02-12T12:53:00.000+01:00can anyone plz tell me how to obtain the result se...can anyone plz tell me how to obtain the result set produced by stored procedure in java ??<BR/>I am using MySQL 5.0Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-5029911639972223522009-01-21T05:33:00.000+01:002009-01-21T05:33:00.000+01:00FYI - I have not had luck in all "SHOW" statements...FYI - I have not had luck in all "SHOW" statements working with PDI - specifically "show slave status" - it returns values but for some reason PDI doesn't recognize them. Not sure the reason.Kylehttps://www.blogger.com/profile/01740729883347587689noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-40106180898776113752009-01-04T17:38:00.000+01:002009-01-04T17:38:00.000+01:00Hi Shlomi! Don't be ashamed - maybe gigs, many too...Hi Shlomi! <BR/><BR/>Don't be ashamed - maybe gigs, many tools - you can't devote everything to memory.<BR/><BR/>Maybe you can checkout Pentaho Reporting in that case (aka JFreeReport) - Like I said I think they added it. I believe BIRT also supports it.<BR/><BR/>That said, what you are describing sounds like a pretty complex thing to do, just to run a report. My gut feeling says that it would probably be better to set up an environment that is designed to cater to queries, and that uses some after hours to pre-process things so you know you can always efficiently get the answers whenever you need them. <BR/><BR/>I realize It might not always be possible and some customers are truly scared by the term "Data Warehouse" but really, I am feeling more and more inclined to advise against one-by-one copies of the source systems as "reporting environments" in favor of a proper, star schema - based data warehouse. This would in almost all cases eliminate the need for explicit temporary tables and outer joins at allrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-60772289220762554802009-01-04T17:31:00.000+01:002009-01-04T17:31:00.000+01:00Hi Tom! thanks for the kind words. I am pretty sur...Hi Tom! <BR/><BR/>thanks for the kind words. I am pretty sure you'll hear more about it. BTW - looking forward to meeting you again at the MySQL UC. I'm sure we can find some illegal roulette racket somewhere to spill some chips ;)rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.com