Execute sql query with declare and loop in Doctrine - Fusio

Question

I try to run a query which contain local variable and loop. But Fusio/Doctrine return empty/error on execute.

example (empty result or boolean):

$sum = $connection->fetchAll("declare @dateFrom DATETIME=CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1)));
declare @dateTo DATETIME=CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE()));
declare @dateFromtmp DATETIME=@dateFrom;

declare @tmpDate table(Dates  DATETIME);

WHILE (@dateFromtmp <= @dateTo)
BEGIN
    insert into @tmpDate values(@dateFromtmp)
    SET @dateFromtmp = DATEADD( DAY,1,@dateFromtmp)
END;

SELECT @dateFrom;
 ");
$type = "is " . gettype($sum);
$count = count($sum);
return $response->build(200, [], [
    'type'=>$type,
    'count'=>$count,
    'summary' => $sum,
]);

returns:

{
    "type": "is array",
    "count": 0,
    "summary": []
}

there is no problem to run below query:

$sum = $connection->fetchColumn("declare @dateFrom DATETIME=CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1)));
declare @dateTo DATETIME=CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE()));
declare @dateFromtmp DATETIME=@dateFrom;

declare @tmpDate table(Dates  DATETIME);

SELECT @dateFrom;
 ");
$type = "is " . gettype($sum);
$count = count($sum);
return $response->build(200, [], [
    'type'=>$type,
    'count'=>$count,
    'summary' => $sum,
]);

return :

{
    "type": "is string",
    "count": 1,
    "summary": "2017-08-01 00:00:00.000"
}

I'm suspecting that this is related to the loop. Is there a way to run this query in Fusio.?

Thanks

score 0 · Accepted Answer · answered Sep 21 '17 at 04:17

Found the answer here: alternative solutions

$sum = $connection->fetchAll("declare @dateFrom DATETIME=CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1)));
declare @dateTo DATETIME=CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE()));

WITH DateTable
AS
(
    SELECT CONVERT(date,@dateFrom) AS [DATE]
    UNION ALL
    SELECT CONVERT(date,DATEADD(dd, 1, [DATE]))
    FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) < @dateTo
)
SELECT dt.[DATE]
FROM [DateTable] dt;
 ");

$type = "is " . gettype($sum);
$count = count($sum);
return $response->build(200, [], [
    'type'=>$type,
    'count'=>$count,
    'summary' => $sum,
]);

returns:

{
    "type": "is array",
    "count": 30,
    "summary": [
        {
            "DATE": "2017-08-01"
        },
        {
            "DATE": "2017-08-02"
        },
        {
            "DATE": "2017-08-03"
        },
        {
            "DATE": "2017-08-04"
        },
        {
            "DATE": "2017-08-05"
        },
        {
            "DATE": "2017-08-06"
        },
        {
            "DATE": "2017-08-07"
        },
        {
            "DATE": "2017-08-08"
        },
        {
            "DATE": "2017-08-09"
        },
        {
            "DATE": "2017-08-10"
        },
        {
            "DATE": "2017-08-11"
        },
        {
            "DATE": "2017-08-12"
        },
        {
            "DATE": "2017-08-13"
        },
        {
            "DATE": "2017-08-14"
        },
        {
            "DATE": "2017-08-15"
        },
        {
            "DATE": "2017-08-16"
        },
        {
            "DATE": "2017-08-17"
        },
        {
            "DATE": "2017-08-18"
        },
        {
            "DATE": "2017-08-19"
        },
        {
            "DATE": "2017-08-20"
        },
        {
            "DATE": "2017-08-21"
        },
        {
            "DATE": "2017-08-22"
        },
        {
            "DATE": "2017-08-23"
        },
        {
            "DATE": "2017-08-24"
        },
        {
            "DATE": "2017-08-25"
        },
        {
            "DATE": "2017-08-26"
        },
        {
            "DATE": "2017-08-27"
        },
        {
            "DATE": "2017-08-28"
        },
        {
            "DATE": "2017-08-29"
        },
        {
            "DATE": "2017-08-30"
        }
    ]
}

Execute sql query with declare and loop in Doctrine - Fusio

1 Answers1