1

I am looking for a way to execute an SQL script where I have two statements, a set and a select :

SET datefirst 1;

SELECT *
FROM  (SELECT [book],
              Datefromparts(Year(exposure_date), Month(exposure_date), 1) AS EXPOSURE_DATE,
              Datepart(year, exposure_date)                               AS YEAR,
              Datepart(month, exposure_date)                              AS MONTH,
              Datepart(quarter, exposure_date)                            AS QUARTER,
              [exposure]
       FROM   [REPORTING].[dbo].[v_exp_daily_gas_physfin_mapped]
       WHERE  risk_map = 'LCO'
              AND book = 'tpvh'
              AND report_asof_date = '2020-07-10') AS BASE
      PIVOT(Sum(exposure)
           FOR book IN ([TPVH])) AS pvt
ORDER  BY exposure_date;

If I try to fit this inside a SELECT statement it gives me the error that it expects a SELECT instead of the SET statement (i.e. "Query type must be one of [SELECT, STORE_PROCEDURE_CALL] but query 'SET datefirst 1;).

enter image description here

Now, I understand that what I need for my multiple SQL queries is a Database "Execute Script" block from the Mule Palette.

But when I try to execute the code inside it, it gives me the following error : "A result set was generated for update."

enter image description here

The connection to the database works OK as it can be seen :

enter image description here

I did check the following link (the official documentation) : https://docs.mulesoft.com/db-connector/1.11/database-execute-script but it does not help :(

Does anyone has a clue ? Many thanks !

R13mus
  • 752
  • 11
  • 20
  • 1
    Please use text instead of images. See https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question for more details on the reason. Don't use the debugger to read errors, use the log, and provide the text from the log for others to understand the error and details. You should be showing the actual XML text of the database connector execute script operation in your question instead of the screenshot of the palette which is not really useful. – aled Jan 21 '22 at 12:42
  • You're right @aled - I will correct my question with regard to your comment. Many thanks! – R13mus Jan 21 '22 at 17:03

1 Answers1

1

The Execute Script operation of the Database connector does not support Selects or operations that return rows.

You should use a Select operation for each Select in the script. Or the appropriate operation of the connector for that particular query. Remove the SET datefirst 1; for it to work. Do you really need that for this query? If the select doesn't seem to be usiung dateofweek.

aled
  • 21,330
  • 3
  • 27
  • 34
  • I see, thanks for the fast answer ! I really need that part of ```SET datefirst 1;``` as it used in other queries. I have many queries that I need to execute, the above one was just one example of what I would need and does not work. So, there is nothing which supports multiple sql statements with the return of rows ? – R13mus Jan 21 '22 at 13:48
  • You have to execute each query in its own separate operation. If you still have a problem with that particular query that uses SET datefirst then create a new question with all the details. – aled Jan 21 '22 at 16:43
  • I gave a +1 as your comment is helpful @aled, but i cannot mark it as the answer for my question as it doesn't offer a soluțion. It just says why it doesn't work. And opening a new questiom doesn't make sense for me. Regards – R13mus Jan 21 '22 at 17:07
  • The solution is what I mentioned in my comments and I updated it in the second paragraph. Use one operation per query. Just open a new question if you can't resolve the `set datefirst` part for the queries that use it. You'll probably need to either refactor the query to avoid using it, to transform the value after the query or to put that query in a store procedure with the set. – aled Jan 21 '22 at 18:47