We changed from the net.sourceforge.jtds.jdbc.Driver to the com.microsoft.sqlserver.jdbc.SQLServerDriver
Since then, we get an arror when executing the databaseManager.getDataSetByQuery
Error :
- Code: Select all
The statement did not return a result set.
When executing the entire same query directly in Sql Server Management studio, one record is returned.
When googling this, I get several posts stating we should append
- Code: Select all
allowMultiQueries=true
to the connectionstring. Which I tried but no solution.
Could it be that this property is not passed by Servoy?
Or what is the solution to this problem?
FYI:
- Code: Select all
SelectMethod=cursor
The query is complex but Sql nonetheless and worked with the previous driver :
- Code: Select all
DECLARE @intDocHandle INT;
DECLARE @xmlString VARCHAR(max);
SET @xmlString = '<root><key ID=''0FD2AC09-9856-41B8-B5B6-0573775E5CE5''/> </root>';
EXEC sp_xml_preparedocument @intDocHandle OUTPUT
,@xmlString;
IF OBJECT_ID('tempdb..#temp_801873526') IS NOT NULL
DROP TABLE #temp_801873526;
CREATE TABLE #temp_801873526 (ID UNIQUEIDENTIFIER);
INSERT INTO #temp_801873526
SELECT *
FROM OPENXML(@intDocHandle, '/root/key') WITH (ID UNIQUEIDENTIFIER);
DECLARE @query AS NVARCHAR(max);
DECLARE @column_names AS NVARCHAR(max);
SELECT @column_names = ISNULL(@column_names + ',', '') + QUOTENAME(pde.tablename + '_' + pde.fieldname)
FROM price_rule
INNER JOIN price_dependency pde ON pde.price_rule_id = price_rule.price_rule_id
INNER JOIN #temp_801873526 ON pde.price_rule_id = #temp_801873526.ID
ORDER BY pde.sequence_nr;
IF @column_names IS NULL
OR LEN(@column_names) = 0
SET @query = N'select pri.valid_from,pri.valid_till,pri.result_value,pri.free_quantity, pri.price_item_id from price_item pri where 1 = 0; ';
ELSE
SET @query = N'select ' + @column_names +
',pvt.price_rule_type,pvt.return_result_type,pvt.price_rule_code,pvt.price_item_id,pvt.price_rule_id,pvt.uplift_helper, pvt.result_value,pvt.result_value_incl,pvt.currency_id,pvt.valid_from,valid_till, pvt.free_quantity, pvt.flag_default, pvt.flag_fixed_price, pvt.flag_free,pvt.formula_id,pvt.valid_when_formula_id, pvt.max_amount,pvt.result_value_extra_qty,pvt.result_value_per, pvt.result_value_per_unit_id,pvt.target_margin,pvt.current_margin ,pvt.formula_code, pvt.valid_when_formula_code,pvt.currency_code, pvt.result_value_per_unit_code FROM ( select pru.price_rule_type,pru.return_result_type,pru.price_rule_code,pri.price_item_id,pri.price_rule_id, cast(0 as numeric(18,6)) as uplift_helper,pri.valid_from,pri.valid_till, pri.result_value,pri.result_value_incl,pri.free_quantity,pri.currency_id, pri.flag_default, pri.flag_fixed_price, pri.flag_free, pri.formula_id,pri.valid_when_formula_id, pri.max_amount,pri.result_value_extra_qty,pri.result_value_per, pri.result_value_per_unit_id, concat(pde.tablename,''_'',pde.fieldname) as tablefield, target_margin,cast(0 as numeric(18,6)) as current_margin ,cast(space(1) as nvarchar(50)) as formula_code,cast(space(1) as nvarchar(50)) as valid_when_formula_code, isnull(currency.currency_code,space(10)) as currency_code, isnull(au.articleunit_code,'''') as result_value_per_unit_code, case when pva.price_value_id is null and pde.fieldtype=''UUID'' then cast(''00000000-0000-0000-0000-000000000000'' as nvarchar(64)) when pva.price_value_id is null then cast('''' as nvarchar(64)) when pde.fieldtype=''BOOL'' then cast(pva.value_boolean as nvarchar(64)) when pde.fieldtype=''DATE'' then convert(nvarchar(64),pva.value_datetime,103) when pde.fieldtype=''INTEGER'' then cast(pva.value_integer as nvarchar(64)) when pde.fieldtype=''NUMBER'' then cast(pva.value_number as nvarchar(64)) when pde.fieldtype=''UUID'' then cast(pva.value_uuid as nvarchar(64)) else cast(pva.value_text as nvarchar(64)) end as column_value from price_dependency pde inner join #temp_801873526 on pde.price_rule_id = #temp_801873526.ID inner join price_rule pru on pru.price_rule_id = #temp_801873526.ID inner join price_item pri on pri.price_rule_id = #temp_801873526.ID left join currency on pri.currency_id = currency.currency_id left join articleunits au on pri.result_value_per_unit_id = au.articleunit_id inner join price_value pva on pde.price_dependency_id = pva.price_dependency_id and pva.price_item_id = pri.price_item_id ) as data pivot ( max(column_value) for tablefield in ('
+ @column_names + ') ) as pvt ;';
EXEC sp_executesql @query;
IF OBJECT_ID('tempdb..#temp_801873526') IS NOT NULL
DROP TABLE #temp_801873526;