We are using MySQL stored procedure to execute some complex query logic. Is there a way to execute the same stored procedures using dremio?
@nageen20 Please check if External query will work
@balaji.ramaswamy , thanks for the reply. I understand we can run select queries using external queries. But the stored procedure we have has multiple select queries and some delete statements also in between to get the correct data.
Please can you let me know if external queries can handle this? Or if there is any other option?
@nageen20 If you can put/add all your commands into one stored procedure then we can do it via external query
Can you please elaborate? We already have a stored procedure on MySQL DB. Can we call that using external queries just how we do in MySQL?
Is it possible to give an example of the syntax?
Thanks
Hi @balaji.ramaswamy ,
I am using the following code to call the stored procedure using the external query.
SELECT * FROM table(“source name”.external_query(
‘CALL db_name.stored_prpcedure_name(’‘2020’’,1,’’-1’’,
‘’-1’’,’’-1’’,’’-1’’,’’-1’’,
‘‘2021-05-05’’,’‘2021-05-05’’,’’-1’’,’’-1’’, ‘’-1’’,
NULL,NULL,
TRUE,TRUE,TRUE,TRUE,
FALSE,FALSE,TRUE,
0,TRUE,TRUE)’
))
But when I do this, I get an error saying ‘Selected table has no columns.’.
Please can you let me know where I could be going wrong?
Thanks
What happens if you run the below query directly on MySQL?
CALL db_name.stored_prpcedure_name(’‘2020’’,1,’’-1’’,
‘’-1’’,’’-1’’,’’-1’’,’’-1’’,
‘‘2021-05-05’’,’‘2021-05-05’’,’’-1’’,’’-1’’, ‘’-1’’,
NULL,NULL,
TRUE,TRUE,TRUE,TRUE,
FALSE,FALSE,TRUE,
0,TRUE,TRUE)The output is returned on the MySQL client window i.e the output table is displayed on the client window. Let me know if any more is information is required.
@nageen20 Then you should be able to use it as an external query, can you please send us the profile when you got the error from Dremio?
@balaji.ramaswamy
Any news on this?
I am trying the same thing. This is my code.
SELECT * FROM table(mydb.external_query(
‘CALL insert_start_time(current_timestamp,’‘test2’‘)’
))
I get the following error message
This is my job profile
4233674f-11fe-4bd6-90c4-941e100abcf8.zip (8,1 KB)
Can you help me?
@vincent_mayer Wondering if this a quoting issue? WHat happens if you run the below directly on Mysql?
CALL insert_start_time(current_timestamp,''test2'')
@balaji.ramaswamy
if I run following command it works.
I have also tried in dremio
SELECT * FROM table(mydb.external_query(
‘CALL insert_start_time(current_timestamp,‘test2’)’
))
@vincent_mayer I assume when you run this from Dremio it does not work?