Denodo Platform 8.0t, Subscription: Enterprise, Administration Tool 8.0 20230301, VDP Server l 8.0 20230301
Windows 10 Enterprise
DATA SOURCE :Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64) Jan 22 2023 17:38:22 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
Denodo data source connection details:
Database adapter: Microsoft SQL Server 2016 (MS Driver)
Database URL: jdbc:sqlserver://xxxx.xxx.xxxt;databaseName=Mydb;encrypt=false;portNumber=1433
Transaction isolation:Database Default
Authentication: Use login and password
Driver class path: mssql-jdbc-10.x
Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver
**Problem description:**
**01 Source data from SQL procedure**:
```
[ALTER PROCEDURE [dbo].[GetPortfolioHoldingHistoryDenodo]
(@HistoryStartDate datetime2,
@HistoryEndDate datetime2
)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (1000) [PortfolioHoldingID]
,[HistoryDate]
,[UnitsOwned]
,[AvgPriceIncl]
FROM [Mydb].[dbo].[PortfolioHoldingHistory]
WHERE HistoryDate >= @HistoryStartDate
AND HistoryDate <= @HistoryEndDate
END
```
**02 Denodo base view wrapper to sql stored procedure above: - created via import from data source==>create base view**
Database: datafacadedb
View name: getportfolioholdinghistorydenodo_view
View type: Base
Cache status:Off
Data source: my_sandbox_july22
Owner: admin
....
Schema:
PK Field name Field Type Description
historystartdate_0 timestamp
historyenddate_0 timestamp
portfolioholdingid int
historydate timestamp
unitsowned decimal
avgpriceincl decimal
Search methods:
I18n options
Default i18n : za
NB: Virtual DataPort Default i18n : za
**03 Denodo VQL stored procedure calling base view wrapper**
`# Generated with Denodo Platform 8.0 update 20230301.
DROP PROCEDURE IF EXISTS facade_getportfolioholdingshistory CASCADE;
CREATE VQL PROCEDURE facade_getportfolioholdingshistory(
startdate IN timestamp,
enddate IN timestamp,
historystartdate_0 OUT timestamp,
historyenddate_0 OUT timestamp,
portfolioholdingid OUT integer,
historydate OUT timestamp,
unitsowned OUT decimal,
avgpriceincl OUT decimal
)
AS (
// Procedure variables
CURSOR cursorDataGetHoldingsHistory IS 'SELECT historystartdate_0, historyenddate_0, portfolioholdingid,historydate,unitsowned,avgpriceincl
FROM datafacadedb.getportfolioholdinghistorydenodo_view
WHERE historystartdate_0 = :param1
AND historyenddate_0 = :param2';
rportfolioholdinghistory cursorDataGetHoldingsHistory%ROWTYPE;
/* commented out code for testing without :param notation - this returns results succesfully as it is ignoring the passed IN params
CURSOR cursorDataGetHoldingsHistory IS 'select portfolioholdingid,historydate,unitsowned,avgpriceincl
from datafacadedb.getportfolioholdinghistorydenodo_view where historystartdate_0 = ''2021-06-01 00:00:00.000'' and historyenddate_0 = ''2021-06-02 00:00:00.000''';
*/
)
BEGIN
OPEN cursorDataGetHoldingsHistory PARAMETERS (param1,param2) VALUES(startdate,enddate);
LOOP
FETCH cursorDataGetHoldingsHistory INTO rportfolioholdinghistory;
RETURN ROW ( historystartdate_0, historyenddate_0, portfolioholdingid, historydate, unitsowned,avgpriceincl)
VALUES(rportfolioholdinghistory.historystartdate_0,
rportfolioholdinghistory.historyenddate_0,
rportfolioholdinghistory.portfolioholdingid,
rportfolioholdinghistory.historydate,
rportfolioholdinghistory.unitsowned,
rportfolioholdinghistory.avgpriceincl );
EXIT WHEN cursorDataGetHoldingsHistory%NOTFOUND;
END LOOP;
CLOSE cursorDataGetHoldingsHistory;
END;
`
Test scenarios (run from Dbeaver)
**1- select directly from denod wrapper view (getportfolioholdinghistorydenodo_view) ..(containing MS sql stored procedure) **
Multiple date format calls Result returns ***successfully***:
`--get data directly from view wrapper to existing SQL server proc
select *
from datafacadedb.getportfolioholdinghistorydenodo_view
where historystartdate_0 = '2021-06-02 00:00:00.000' and historyenddate_0 = '2021-06-03 00:00:00.000';
select *
from datafacadedb.getportfolioholdinghistorydenodo_view
where historystartdate_0 = '2021-06-02 00:00:00' and historyenddate_0 = '2021-06-03 00:00:00';
select *
from datafacadedb.getportfolioholdinghistorydenodo_view
where historystartdate_0 = '2021-06-02' and historyenddate_0 = '2021-06-03';`
Columns returned:
historystartdate_0 |historyenddate_0 |portfolioholdingid|historydate |unitsowned |avgpriceincl
2--** Error scenarios**
select from denod wrapper STORED PROCEDURE (facade_getportfolioholdingshistory()) with same date formats as SELECTS from view above.
`--get data from vql stored procedure wrapper to view wrapper(to existing sql server proc) -various date formats!
```
SELECT * FROM datafacadedb.facade_getportfolioholdingshistory() WHERE startdate='2021-06-02 00:00:00.000'
AND enddate='2021-06-03 00:00:00.000';
--full denodo timestamp format
SELECT * FROM datafacadedb.facade_getportfolioholdingshistory() WHERE startdate='2021-06-02 00:00:00.00'
AND enddate='2021-06-03 00:00:00.00';
--date only
SELECT * FROM datafacadedb.facade_getportfolioholdingshistory() WHERE startdate='2021-06-02'
AND enddate='2021-06-03';`
```
**All above calls fail with same error message**
SQL Error [50001] [HY000]: Error executing query. Total time 0.144 seconds.
**QUERY [SELECTION] [ERROR]
FACADE_GETPORTFOLIOHOLDINGSHISTORY [STORED_PROCEDURE] [ERROR]
FACADE_GETPORTFOLIOHOLDINGSHISTORY [STORED PROCEDURE] [ERROR] Received exception with message 'Syntax error: Exception parsing query near '00''**
Guidance will be most appreciated!
